Oracle Temporary Tables(Oracle 臨時表)

Oracle Temporary Tables(Oracle 臨時表)

1. 建立臨時表語法

A．ON COMMIT DELETE ROWS 定義了建立事務級臨時表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

-----AS SELECT … FROM TABLE…

ON COMMIT DELETE ROWS;

當前session發出commit/rollback命令，則該事務週期發生的所有資料自動被Oracle刪除(Oracle truncate table)。但不影響任何其他session的資料。

B．ON COMMIT PRESERVE ROWS 定義了建立會話級臨時表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

-----AS SELECT … FROM TABLE…

ON COMMIT PRESERVE ROWS;

當前session結束(使用者正常退出 / 使用者不正常退出 / Oracle例項崩潰)，Oracle對這個會話的中發生的資料進行刪除(Oracle truncate table)。但不影響任何其他session的資料。

2. 特點說明

A．臨時表資料自動清空後，但是臨時表的結構以及後設資料還儲存在使用者的資料字典中。表的定義對所有的會話可見

B．臨時表不需要DML鎖

C．可以索引臨時表和在臨時表基礎上建立檢視

D．在臨時表上的索引也是臨時的，也是隻對當前會話或者事務有效

E．臨時表可以擁有觸發器

F．可以用export和import工具匯入匯出臨時表的定義，但是不能匯出資料

3. 使用技巧

A．當某一個SQL語句關聯的表在2張及以上，並且和一些小表關聯。可以採用將大表進行分拆並且得到比較小的結果集合存放在臨時表中

B．程式執行過程中可能需要存放一些臨時的資料，可以將這類資料放在臨時表裡非常方便

C．儲存過程中用到臨時表:

1> 在建立臨時表前，應先加上對錶名的判斷

Select count(*) into v_count from user_tables where table_name = ‘XXX’;

If v_count=0 then

Create global temporary table …

在儲存過程結束處，應該記得刪除表

execute immediate 'drop table t_temp';