Oracle中建立和刪除暫存資料表

Oracle中建立和刪除暫存資料表

CREATE GLOBAL TEMPORARY TABLE TABLENAME (
    COL1  VARCHAR2(10),
    COL2  NUMBER
 ) ON COMMIT PRESERVE(DELETE) ROWS ;

--ON COMMIT DELETE ROWS 說明暫存資料表是事務指定，每次提交後Oracle將截斷表（刪除全部行）
---------------------------------------
在Oracle8i中，可以建立以下兩種暫存資料表：
1。會話特有的暫存資料表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
 ON COMMIT PRESERVE ROWS；

2。事務特有的暫存資料表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
 ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的暫存資料表雖然是存在的，但是你試一下insert 一條記錄然後用別的串連登上去select，記錄是空的，明白了吧。
 下面兩句話再貼一下：
--ON COMMIT DELETE ROWS 說明臨敀?表是事務指定，每次提交後ORACLE將截斷表（刪除全部行）
--ON COMMIT PRESERVE ROWS 說明暫存資料表是會話指定，當中斷會話時ORACLE將截斷表。

procedure 執行一系列的操作
package  可以在其中定義一些量、函數、過程等；

 預存程序裡不能直接使用DDL語句，所以只能使用動態SQL語句來執行

create procedure pro
 as
 str varchar2(100);
 begin

str := 'CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
          enddate DATE,
          class CHAR(20))
      ON COMMIT DELETE ROWS’;
execute immediate str;
 end;

刪除：

truncate table  MyTempTable
 drop table  MyTempTable

ORA-22992: 無法使用從遠端資料表選擇的 LOB 定位器 

解決辦法:
可以先建立一個暫存資料表,然後把遠程有LOB欄位的表複製到暫存資料表中,然後再進行連結操作
1.本地建立暫存資料表
Sql代碼
 
create global temporary table photo_temp as select * from photo@photo_link where 1=2 ;

2.用database link匯入遠端資料到暫存資料表
Sql代碼
 
insert into photo_temp select * from photo@photo_link;--不要commit;否則暫存資料表中資料消失

3.把暫存資料表資料插入到永久表中：
Sql代碼
insert into photo select * from photo_temp;
commit;

執行個體：

create global temporary table pic_temp as select * from WH_REGISTERPIC@ogdpshdb where 1=2 ;
insert into pic_temp select * from WH_REGISTERPIC@ogdpshdb;
insert into WH_REGISTERPIC select * from pic_temp;
commit;

刪除暫存資料表：

truncate table  pic_temp ;
drop table  pic_temp ;

解決普通使用者在預存程序中無權建暫存資料表問題：

在包頭中加 AUTHID CURRENT_USER

例子：

create or replace package WH_Info_Output AUTHID CURRENT_USER is

  -- Author  : 
  -- Created : 2014年9月2日 15:27:25
  -- Purpose : 

  --刪除原有資料匯入全部資料
  procedure SP_WH_Info_Output_All(Fid in number default 1);
  -- Public type declarations
  --傳入參數預設0：匯入沒有的資料  1：刪除原有資料匯入全部資料
  procedure SP_WH_Info_Output(Fid in number default 0);

  --匯入WH_REGISTERPIC表（含有ＢＬＯＧ欄位） 0：匯入沒有的資料  1：刪除原有資料匯入全部資料
  procedure SP_WH_PIC_BLOGInfo_Output(Fid in number default 0);
 
end WH_Info_Output;

 

  --匯入WH_REGISTERPIC表（含有ＢＬＯＧ欄位） 0：匯入沒有的資料  1：刪除原有資料匯入全部資料
  procedure SP_WH_PIC_BLOGInfo_Output(Fid in number default 0)
  is
  str varchar2(300);
  begin
  if Fid = 0 then
  str:='create global temporary table pic_temp as select * from WH_REGISTERPIC@ogdpshdb where 1=2'; 
  execute immediate str; 
  str:='insert into pic_temp select * from WH_REGISTERPIC@ogdpshdb w where (w.WELLID, w.PICTYPECODE, w.VERSIONNO, w.PICFILENAME) not in (select WELLID, PICTYPECODE, VERSIONNO, PICFILENAME from WH_REGISTERPIC)'; 
  execute immediate str;    ----使用動態SQL語句來執行 
  str:='insert into WH_REGISTERPIC select * from pic_temp'; 
      execute immediate str; 
  end if;
 

  end SP_WH_PIC_BLOGInfo_Output;

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2