這篇文章主要介紹了Oracle數據庫中的臨時表用法，希望對大家的學習和工作有所幫助。
一：語法
在Oracle中，可以創建以下兩種臨時表：
(1) 會話特有的臨時表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT PRESERVE ROWS；
(2) 事務特有的臨時表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的臨時表雖然是存在的，但是如果insert 一條記錄然後用別的連接登上去select，記錄是空的。
--ON COMMIT DELETE ROWS 說明臨時表是事務指定，每次提交後ORACLE將截斷表（刪除全部行）
--ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當中斷會話時ORACLE將截斷表。
二：動態創建
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from
user_tables where table_name=''T_TEMP'';
--create temporary table
if v_num<1 then
execute immediate ''CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS'';
end if;
--insert data
execute immediate ''insert into t_temp values
('''''' v_col1 '''''','''''' v_col2 '''''')'';
execute immediate ''select col1 from t_temp'' into v_num;
dbms_output.put_line(v_num);
execute immediate ''delete from t_temp'';
commit;
execute immediate ''drop table t_temp'';
end pro_temp;
測試:
15:23:54 SQL> set serveroutput on
15:24:01 SQL> exec pro_temp(''11'',''22'');
11
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.79
15:24:08 SQL> desc t_temp;
ERROR：
ORA-04043: 對象 t_temp 不存在
三:特性和性能(與普通表和視圖的比較)
臨時表只在當前連接內有效
臨時表不建立索引,所以如果數據量比較大或進行多次查詢時,不推薦使用
數據處理比較複雜的時候時表快,反之視圖快點
在僅僅查詢數據的時候建議用游標: open cursor for ''sql clause'';
==================
臨時表特性
1、數據庫中的所有會話均可以訪問同一臨時表，但只有插入數據到臨時表中的會話才能看到它本身插入的數據。
2、可以把臨時表指定為事務相關（默認）或者是會話相關：
3、如果臨時表中有記錄的話，是無法刪除表的。即無法drop table。
4、雖然臨時表不產生 "REDO" ,但卻是要產生 "UNDO" 的 臨時表的分類及創建
1、會話特有的臨時表
記錄將留在此表中，直到會話斷開或通過DELETE或TRUNCATE從物理上刪除這些記錄。
CREATE GLOBAL TEMPORARY TABLE <TABLE_NAME> (<column specification> )
ON COMMIT PRESERVE ROWS;
2、事務特有的臨時表
當事務提交後，在事務之中插入的記錄不會被保留，自動刪除。
CREATE GLOBAL TEMPORARY TABLE <TABLE_NAME> (<column specification> )
ON COMMIT DELETE ROWS;
3、DDL語法註釋
這裡的“GLOBAL”表示臨時表的定義，是所有會話都能看見的。建立臨時表只有 CREATE GLOBAL TEMPORARY TABLE，而沒有其它CREATE ****** TEMPORARY TABLE形式的命令。臨時表的刪除 1、刪除會話特有的臨時表
想快速刪除此類臨時表，必須先truncate表中的數據，然後drop表結構。如果使用DELETE命令先刪除表中記錄的話，還無法直接刪除表。只有等當前會話退出後，在其它會話或新的會話中刪除表結構。
使用DELTETE後DROP表報錯：
SQL> DELETE tmp_test;
8 rows deleted
SQL> commit;
Commit complete
SQL> drop table tmp_test;
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
這是因為用“ON COMMIT PRESERVE ROWS”子句時,會加行鎖(ROW-X).
TYPE=TO
TO Lock "Temporary Table Object Enqueue"
具體請看DOC ID:186854.1
2、刪除事務特有的臨時表
用ON COMMIT DELETE ROWS 子句就不會有那麼多限制。 COMMIT以後，記錄自動清除，可以直接就刪除表。臨時表的表空間的分配
臨時表在創建的時候，是不分配表空間的。當用戶使用臨時表存儲數據時，從該用戶默認的臨時表空間來分配存儲空間。參考資料
1、http://blog.itpub.net/post/10/8764
使用臨時表的sql優化案例二－臨時表的統計信息
2、http://www.itpub.net/178008.html&highlight=%C1%D9%CA%B1%B1%ED
拋磚引玉：三種主流數據庫中臨時表的使用.doc
MS SQLSERVER
SQL Server 支持臨時表。臨時表就是那些名稱以井號 (#) 開頭的表。如果當用戶斷開連接時沒有除去臨時表，SQL Server 將自動除去臨時表。臨時表不存儲在當前數據庫內，而是存儲在系統數據庫 tempdb 內。
臨時表有兩種類型：
本地臨時表：本地臨時表的名稱以單個數字符號 (#) 打頭；它們僅對當前的用戶連接是可見的；當用戶從 Microsoft SQL Server 2000 實例斷開連接時被刪除。
全局臨時表：全局臨時表的名稱以數學符號 (##) 打頭，創建後對任何用戶都是可見的。如果在創建全局臨時表的連接斷開前沒有顯式地除去這些表，那麼只要所有其它任務停止引用它們，這些表即被除去。當創建全局臨時表的連接斷開後，新的任務不能再引用它們。當前的語句一執行完，任務與表之間的關聯即被除去；因此通常情況下，只要創建全局臨時表的連接斷開，全局臨時表即被除去。
例如，如果創建名為 employees 的表，則任何人只要在數據庫中有使用該表的安全權限就可以使用該表，除非它已刪除。如果創建名為 #employees 的本地臨時表，只有您能對該表執行操作且在斷開連接時該表刪除。如果創建名為 ##employees 的全局臨時表，數據表中的任何用戶均可對該表執行操作。如果該表在您創建後沒有其他用戶使用，則當您斷開連接時該表刪除。如果該表在您創建後有其他用戶使用，則 SQL Server在所有用戶斷開連接後刪除該表。
現在，臨時表的許多傳統用途可由具有 table 數據類型的變量替換。
ORACLE
Oracle支持臨時表。臨時表用來保存事務或會話期間的中間結果。在臨時表中保存的數據只有對當前會話是可見的，任何會話都不能看到其他會話的數據，即使在當前會話COMMIT數據以後也是不可見的。多用戶並行不是問題，一個會話從來不阻塞另一個會話使用臨時表。即使鎖定臨時表，一個會話也不會阻塞其他會話使用臨時表。臨時表比正常表產生的REDO少得多，然而，由於臨時表必須產生包含數據的UNDO信息，所以會產生一定數量的REDO日誌。
臨時表將從用戶臨時表空間的的目前日誌中分配空間，或者如果從有定義權的程序中訪問，將使用程序所有者的臨時表空間。全局臨時表實際上只是表本身的模板。創建臨時表的行為不包括存儲空間的分配，也不包括INITIAL的分配。因此，在運行時當一個會話首先將數據放到臨時表中時，這時將創建這個會話的臨時段。由於每個會話獲取自己的臨時段，每個用戶可能在不同的表空間中為臨時表分配空間。 USER1的default臨時表空間為TEMP1，他的臨時表將從TEMP1中分配空間，USER2的default臨時表空間為TEMP2，他的臨時表將從TEMP2中分配空間。
臨時表在每個數據庫中只需創建一次，不必在每個存儲過程中創建。臨時表總是存在的，除非手動的刪除他。臨時表作為對象存在數據字典中，並且總是保持為空，直到有會話在其中放入數據。 Oracle允許創建基於臨時表的視圖和存儲過程。
臨時表可以是以會話為基礎的，也可以是以事務為基礎的。 ON COMMIT PRESERVE ROWS子句使臨時表成為基於會話的模式。行將留在此表中，直到會話斷開或通過DELETE或TRUNCATE從物理上刪除這些行。 ON COMMIT DELETE ROWS子句使臨時表成為基於事務的模式。當會話提交後，行消失。這個臨時表的自動清除過程不會有額外的開銷。
在oracle中，應用程序需要的臨時表應該在程序安裝時創建，而不是在程序運行時創建。 （這是與ms sqlserver或sybase的使用的不同）
在任何數據庫中，臨時表的一個缺點是：事實上優化器在臨時表中沒有真正的統計功能。然而，在oracle中，一系列較好的統計猜測可以通過DBMS_STATS包在臨時表中設置。
DB2
可使用 DECLARE GLOBAL TEMPORARY TABLE 語句來定義臨時表。 DB2的臨時表是基於會話的，且在會話之間是隔離的。當會話結束時，臨時表的數據被刪除，臨時表被隱式卸下。對臨時表的定義不會在SYSCAT.TABLES中出現
下面是定義臨時表的一個示例：
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
此語句創建一個名為 gbl_temp 的用戶臨時表。定義此用戶臨時表 所使用的列的名稱和說明與 empltabl 的列的名稱和說明完全相同。隱式定義 只包括列名、數據類型、可為空特性和列缺省值屬性。未定義所有其他列屬性，包括唯一約束、外部關鍵字約束、觸發器和索引。執行 COMMIT 操作時， 若未對該表打開 WITH HOLD 游標，則該表中的所有數據都被刪除。不記錄 對用戶臨時表所作的更改。用戶臨時表被放在指定的用戶臨時表空間中。此表空間必須存在，否則此表的聲明將失敗。
戶定義臨時表不支持：
LOB 類型的列（或基於 LOB 的單值類型列）
用戶定義類型列
LONG VARCHAR 列
DATALINK 列
