ORACLE 臨時表用法

2019-11-09 13:38:39

字體：大中小

來源：轉載

供稿：網友

臨時表概念

臨時表就是用來暫時保存臨時數據（亦或叫中間數據）的一個數據庫對象，它和普通表有些類似，然而又有很大區別。它只能存儲在臨時表空間，而非用戶的表空間。Oracle臨時表是會話或事務級別的，只對當前會話或事務可見。每個會話只能查看和修改自己的數據。

臨時表語法

clip_image002

臨時表分類

ORACLE臨時表有兩種類型：會話級的臨時表和事務級的臨時表。

1）ON COMMIT DELETE ROWS

它是臨時表的默認參數，表示臨時表中的數據僅在事物過程（Transaction）中有效，當事物提交（COMMIT）后，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。

2）ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以跨事物而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的數據自然也就隨之丟棄。但是臨時表的結構以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。

1:會話級的臨時表的數據和你當前會話有關系，當前session不退出的情況下，臨時表中的數據就還存在，臨時表的數據只有當你退出當前SESSION的時候才被截斷（TRUNCATE TABLE），如下所示：

會話級別的臨時表創建：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT PRESERVE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWSASSELECT * FROM TEST;操作示例：SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST (　　 ID NUMBER , 　　NAME VARCHAR2(32) ) ON COMMIT PRESERVE ROWS;Table createdSQL> INSERT INTO TMP_TEST    SELECT 1, 'kerry' FROM DUAL;row insertedSQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　NAME---------- ----------------　　　　　　　　kerrySQL> INSERT INTO TMP_TEST 　　SELECT 2, 'rouce' FROM DUAL;row insertedSQL> ROLLBACK;Rollback completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ----------------------　　　　　　　　　　kerrySQL>
2:事務級的臨時表（默認），這種類型的臨時表與事務有關，當進行事務提交或者事務回滾的時候，臨時表的數據將自行截斷，即當COMMIT或ROLLBACK時，數據就會被TRUNCATE掉，其它的特性和會話級的臨時表一致。
事務級臨時表的創建方法：
CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT DELETE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST( 　　　　ID NUMBER , 　　　　NAME VARCHAR2(32) ) ON COMMIT DELETE ROWS;Table createdSQL> INSERT INTO TMP_TEST 　　SELECT 1, 'kerry' FROM DUAL;row insertedSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ----------------------　　　　　　　　　　kerrySQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　  NAME---------- ------------------------SQL>3:關于臨時表只對當前會話或事務可見。每個會話只能查看和修改自己的數據。
用DM用戶登錄數據庫，打開SESSION 1后，創建臨時表TMP_TEST
CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT DELETE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST ( 　　　　ID NUMBER , 　　　　NAME VARCHAR2(32) ) ON COMMIT DELETE ROWS;Table createdSQL> INSERT INTO TMP_TEST　　 SELECT 1, 'kerry' FROM DUAL;row insertedSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ---------------------kerrySQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- -----------------------SQL>用sys用戶登錄數據庫，打開SESSION 2
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到臨時表數據
SELECT * FROM DM.TMP_TEST; --查不到數據，即使TMP_TEST臨時表存在數據。
 
臨時表與永久表區別
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",　　  "TEMPORARY", DURATION, "MONITORING"　　 FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING------------- --------------  ------- --------- ----------- ---------TEST          TBS_EDS_DATA    YES       N                      YESTMP_TEST                      NO        Y     SYS$SESSION       NO如上所示，臨時表是存儲在臨時表空間里面的，但是上面腳本可以看出，臨時表在數據字典中沒有指定其表空間，臨時表是NOLOGGING，DURATION為SYS$SESSION
臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少其實在應用中，往往會創建一個NOLOGGING的永久表（中間表）來保存中間數據，從而代替臨時表，至于這這兩者有啥優劣，真是很難說清道明(歡迎大家探討)。
 
臨時表用途
 
什么時候使用臨時表？用臨時表和用中間表有啥區別呢？
我覺得是在需要的時候應用，下面是David Dai關于臨時表的一個應用說明，我覺得非常形象的說明了臨時表的應用場景：對于一個電子商務類網站，不同消費者在網站上購物，就是一個獨立的 SESSION，選購商品放進購物車中，最后將購物車中的商品進行結算。也就是說，必須在整個SESSION期間保存購物車中的信息。同時，還存在有些消費者，往往最終結賬時放棄購買商品。如果，直接將消費者選購信息存放在最終表（PERMANENT）中，必然對最終表造成非常大的壓力。因此，對于這種案例，就可以采用創建臨時表(ON COMMIT PRESERVE ROWS)的方法來解決。數據只在 SESSION 期間有效，對于結算成功的有效數據，轉移到最終表中后，ORACLE自動TRUNCATE 臨時數據；對于放棄結算的數據，ORACLE 同樣自動進行 TRUNCATE ，而無須編碼控制，并且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。
1：當處理某一批臨時數據，需要多次DML操作時（插入、更新等），建議使用臨時表。
2：當某些表在查詢里面，需要多次用來做連接時。（為了獲取目標數據需要關聯A、B、C， 同時為了獲取另外一個目標數據，需要關聯D、B、C....）
關于臨時表和中間表（NOLOGGING，保存中間數據，使用完后刪除）那個更適合用來存儲中間數據，我個人更傾向于使用臨時表，而不建議使用中間表。
 
注意事項
1 ) 不支持 lob 對象，這也許是設計者基于運行效率的考慮，但實際應用中確實需要此功能時就無法使用臨時表了。這點網上很多資料都這么說，我沒有追查到底是那個版本不支持lob對象，至少在ORACLE 10g這個版本中，臨時表是支持lob對象的.
2 ) 不支持主外鍵關系
3 ）臨時表不能永久的保存數據。
4 ）臨時表的數據不會備份，恢復，對其的修改也不會有任何日志信息
5 ）臨時表不會有DML 鎖
DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.
6 ）盡管對臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少。請見官方文檔：
DML statements on temporary tables do not generate redo logs for the data changes.However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination,either when the user logs off or when the session terminates abnormally such as during a session or instance failure.
7 ） 臨時表可以創建臨時的索引、視圖、觸發器。
8 ) 如果要DROP會話級別臨時表，并且其中包含數據時，必須先截斷其中的數據。否則會報錯。