臨時表語法

臨時表分類

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

1）ON COMMIT DELETE ROWS

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

2）ON COMMIT PRESERVE ROWS

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

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

會話級別的臨時表創(chuàng)建：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
　　　　ID NUMBER ,
　　　　NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
AS
SELECT * FROM TEST;
操作示例：
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 (
　　 ID NUMBER ,
 　　NAME VARCHAR2(32)
 ) ON COMMIT PRESERVE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
    SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　NAME
---------- ----------------
1 　　　　　　　　kerry
SQL> INSERT INTO TMP_TEST
 　　SELECT 2, 'rouce' FROM DUAL;
1 row inserted
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ----------------------
1 　　　　　　　　　　kerry
SQL>

2:事務(wù)級的臨時表（默認(rèn)），這種類型的臨時表與事務(wù)有關(guān)，當(dāng)進(jìn)行事務(wù)提交或者事務(wù)回滾的時候，臨時表的數(shù)據(jù)將自行截斷，即當(dāng)COMMIT或ROLLBACK時，數(shù)據(jù)就會被TRUNCATE掉，其它的特性和會話級的臨時表一致。

事務(wù)級臨時表的創(chuàng)建方法：

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 created
SQL> INSERT INTO TMP_TEST
 　　SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ----------------------
1 　　　　　　　　　　kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　  NAME
---------- ------------------------

SQL>

3:關(guān)于臨時表只對當(dāng)前會話或事務(wù)可見。每個會話只能查看和修改自己的數(shù)據(jù)。

用DM用戶登錄數(shù)據(jù)庫，打開SESSION 1后，創(chuàng)建臨時表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 created
SQL> INSERT INTO TMP_TEST
　　 SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ---------------------
1 kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- -----------------------

SQL>

用sys用戶登錄數(shù)據(jù)庫，打開SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到臨時表數(shù)據(jù)

SELECT * FROM DM.TMP_TEST; --查不到數(shù)據(jù)，即使TMP_TEST臨時表存在數(shù)據(jù)。

臨時表與永久表區(qū)別

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                      YES
TMP_TEST                      NO        Y     SYS$SESSION       NO

如上所示，臨時表是存儲在臨時表空間里面的，但是上面腳本可以看出，臨時表在數(shù)據(jù)字典中沒有指定其表空間，臨時表是NOLOGGING，DURATION為SYS$SESSION

臨時表的DML操作速度比較快，但同樣也是要產(chǎn)生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產(chǎn)生的Redo Log 少其實在應(yīng)用中，往往會創(chuàng)建一個NOLOGGING的永久表（中間表）來保存中間數(shù)據(jù)，從而代替臨時表，至于這這兩者有啥優(yōu)劣，真是很難說清道明(歡迎大家探討)。

臨時表用途

什么時候使用臨時表？用臨時表和用中間表有啥區(qū)別呢？

我覺得是在需要的時候應(yīng)用，下面是David Dai關(guān)于臨時表的一個應(yīng)用說明，我覺得非常形象的說明了臨時表的應(yīng)用場景：對于一個電子商務(wù)類網(wǎng)站，不同消費者在網(wǎng)站上購物，就是一個獨立的 SESSION，選購商品放進(jìn)購物車中，最后將購物車中的商品進(jìn)行結(jié)算。也就是說，必須在整個SESSION期間保存購物車中的信息。同時，還存在有些消費者，往往最終結(jié)賬時放棄購買商品。如果，直接將消費者選購信息存放在最終表（PERMANENT）中，必然對最終表造成非常大的壓力。因此，對于這種案例，就可以采用創(chuàng)建臨時表(ON COMMIT PRESERVE ROWS)的方法來解決。數(shù)據(jù)只在 SESSION 期間有效，對于結(jié)算成功的有效數(shù)據(jù)，轉(zhuǎn)移到最終表中后，ORACLE自動TRUNCATE 臨時數(shù)據(jù)；對于放棄結(jié)算的數(shù)據(jù)，ORACLE 同樣自動進(jìn)行 TRUNCATE ，而無須編碼控制，并且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。

1：當(dāng)處理某一批臨時數(shù)據(jù)，需要多次DML操作時（插入、更新等），建議使用臨時表。

2：當(dāng)某些表在查詢里面，需要多次用來做連接時。（為了獲取目標(biāo)數(shù)據(jù)需要關(guān)聯(lián)A、B、C， 同時為了獲取另外一個目標(biāo)數(shù)據(jù)，需要關(guān)聯(lián)D、B、C....）

關(guān)于臨時表和中間表（NOLOGGING，保存中間數(shù)據(jù)，使用完后刪除）那個更適合用來存儲中間數(shù)據(jù)，我個人更傾向于使用臨時表，而不建議使用中間表。

注意事項

1 ) 不支持 lob 對象，這也許是設(shè)計者基于運行效率的考慮，但實際應(yīng)用中確實需要此功能時就無法使用臨時表了。這點網(wǎng)上很多資料都這么說，我沒有追查到底是那個版本不支持lob對象，至少在ORACLE 10g這個版本中，臨時表是支持lob對象的.

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

2 (

3 ID NUMBER ,

4 NAME CLOB

5 ) ON COMMIT PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TMP_TEST

2 SELECT 1, 'ADF' FROM DUAL;

1 row inserted

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2 ) 不支持主外鍵關(guān)系

3 ）臨時表不能永久的保存數(shù)據(jù)。

4 ）臨時表的數(shù)據(jù)不會備份，恢復(fù)，對其的修改也不會有任何日志信息

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操作速度比較快，但同樣也是要產(chǎn)生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產(chǎn)生的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 ） 臨時表可以創(chuàng)建臨時的索引、視圖、觸發(fā)器。

8 ) 如果要DROP會話級別臨時表，并且其中包含數(shù)據(jù)時，必須先截斷其中的數(shù)據(jù)。否則會報錯。

SQL> DROP TABLE TMP_TEST PURGE;

DROP TABLE TMP_TEST PURGE

ORA-14452: 試圖創(chuàng)建, 更改或刪除正在使用的臨時表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped