Oracle、SqlServer——暫存資料表

標籤：style   io   os   ar   使用   strong   sp   資料   on   

一、概述：

oracle資料庫的暫存資料表的特點：

• 暫存資料表預設儲存在TEMP中；
• 表結構一直存在，直到刪除；即建立一次，永久使用；
• 不支援主外鍵。
• 可以索引暫存資料表和在暫存資料表基礎上建立視圖。建立在暫存資料表上的索引也是臨時的,也是只對當前會話或者事務有效. 暫存資料表可以擁有觸發器.

暫存資料表分為事務型和會話型

• 會話型：基於會話的暫存資料表，資料從會話開始到會話結束之間是有效，當會話結束時，表中的資料會自動清空。不同會話之間的資料是隔離的，互不影響。
• 事務型：基於事務的暫存資料表，其比會話型的暫存資料表更靈活，可以認為是從會話型暫存資料表的最佳化，因為表中的資料的儲存時間與會話型相同，有效期間從會話開始，在會話結束時，資料庫自動清空暫存資料表中的資料。與會話型暫存資料表不同的是 在事務提交或者交易回復 時將清空暫存資料表中的資料。當然，會話型暫存資料表在會話期間可以採用 delete 暫存資料表名;的方式清空暫存資料表資料。

文法:

create global temporary table 暫存資料表名
(
……
)
on commit [preserve|delete] rows;　

• preserve時就是會話（SESSION）型的暫存資料表
• delete就是事務（TRANSACTION）型的暫存資料表 

 

而TRANSACTION級的暫存資料表資料在TRANACTION結束後消失，即COMMIT/ROLLBACK或結束SESSION都會清除TRANACTION暫存資料表資料。 

二、樣本：

1、建立暫存資料表

declare tempisexist integer:=0;
begin
　　select count(*) into tempisexist from all_tables where table_name=‘NK_SLTJ‘;
　　if tempisexist=0 then--不存在暫存資料表就建立一個
　　　　execute immediate(‘
　　　　　　CREATE GLOBAL TEMPORARY TABLE NK_SLTJ
　　　　　　(
　　　　　　　　LCK_FJNM varchar(36),
　　　　　　　　LCMC varchar(70),
　　　　　　　　GFX integer,
　　　　　　　　ZFX integer,
　　　　　　　　DFX integer,
　　　　　　　　KZDSL integer
　　　　　　)
　　　　　　on commit preserve rows‘　　--preserve表示回話級。
　　　　);
　　end if;
end;

二、使用暫存資料表

　　declare
　　　　FXZ NUMBER;
　　　　FJNM varchar(36);
　　　　ZZNM varchar(36):=‘77c48880-a2be-4d3c-97b7-26f8de0bee63‘;
　　　　CURSOR NKFXZcur is select NKFXJZ_FXZ,NKLCK_FJNM from NKFXJZ INNER JOIN NKLCK ON NKLCK_NM=NKFXJZ_LCNM where NKLCK_ZZNM=ZZNM;
　　begin
　　　　delete NK_SLTJ;　--防止在統一會話中多次執行導致資料重複，因此程式一開始就應清空暫存資料表資料　
　　　　insert into NK_SLTJ SELECT NKLCK_FJNM, NKLCK_MC,0,0,0,KZD FROM　　--向暫存資料表插入資料。
　　　　　　( select KZDSL.NKLCK_FJNM,KZDSL.KZD,LCJZ.NKLCK_MC from
　　　　　　(select substr(NKLCK_FJNM,1,4) as NKLCK_FJNM, count(NKNKJZ_KZD) AS KZD from NKLCK LEFT join NKNKJZ ON NKNKJZ_LCNM =NKLCK_NM WHERE NKLCK_ZZNM=ZZNM group by substr(NKLCK_FJNM,1,4)) KZDSL
　　　　INNER JOIN
　　　　　　(select NKLCK_FJNM, NKLCK_MC from NKLCK WHERE NKLCK_JC=1 and NKLCK_ZZNM=ZZNM) LCJZ ON LCJZ.NKLCK_FJNM=KZDSL.NKLCK_FJNM) ccc;

　　　　open NKFXZcur; --開啟遊標
　　　　fetch NKFXZcur INTO FXZ,FJNM; --提取遊標資料
　　　　while NKFXZcur%FOUND loop --迴圈
　　　　　　if FXZ>=3.5 and FXZ<=5 then --高風險
　　　　　　　　update NK_SLTJ set GFX=GFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　elsif FXZ>2 and FXZ<3.5 then --中風險
　　　　　　　　update NK_SLTJ set ZFX=ZFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　else
　　　　　　　　if FXZ>=0 and FXZ<=2 then --低風險
　　　　　　　　　　update NK_SLTJ set DFX=DFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　　　end if;
　　　　　　end if;
　　　　　　fetch NKFXZcur INTO FXZ,FJNM ;
　　　　end loop;--結束迴圈
　　close NKFXZcur; --關閉遊標
end;

三、與SqlServer 暫存資料表的區別

oracle：

• 有事務表和會話表兩種；
• 暫存資料表永遠保留表結構，資料儲存在磁碟上。與永久表唯一不同的是在使用者會話結束或者事務提交、復原後刪除資料，但是保留表結構；

SqlServer則是本地和全域暫存資料表。

• 本地表：表名以 # 開頭；在會話結束時徹底刪除表結構，不同使用者之間彼此隔絕；
• 全域表：表名以 ## 開頭，對所有使用者均可見，當一個使用者建立全域表後，其他使用者可以訪問全域表，但建立的使用者結束回話時，全域表雖然接受現有串連全域表的使用者訪問，但不再接受新使用者的訪問，當已串連的使用者中斷連線後將徹底刪除表結構。
• SqlServer：在需要暫存資料表時手動建立，資料儲存在記憶體中，會話結束時直接刪除表結構。

 

 

Oracle、SqlServer——暫存資料表