關於Oracle臨時表的使用的小經歷

前幾天給同事一個建議—使用臨時表完成某些複雜邏輯的處理和輸出，今天突然告訴我說臨時表在儲存過程中用不了，所以回去特意除錯了一下，沒問題。

讓我們先看看Oracle官方文件中的說明吧

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.

DELETE ROWS

Specify DELETE ROWS for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS

Specify PRESERVE ROWS for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.

CREATE GLOBAL TEMPORARY TABLE T_TempTable_DeleteRows
(

testID number
)ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE p_TempTable_DeleteRows
(

oCursor OUT RefType.RefCursor
)
IS
BEGIN

INSERT INTO T_TempTable_DeleteRows

(

testID

)

SELECT 1 FROM DUAL;

--COMMIT;

OPEN OCURSOR FOR SELECT * FROM T_TempTable_DeleteRows;
END;
/

測試儲存過程後，可以看到遊標正常返回結果集
檢查該臨時表，發現該表並無資料，當該儲存過程的事務結束後，臨時表資料已經被清除
儲存過程中不能有COMMIT，否則遊標會無結果，顯式提交表示事務結束，所以沒有資料，倒也比較合情合理
SELECT * FROM T_TempTable_DeleteRows;

CREATE GLOBAL TEMPORARY TABLE T_TempTable_PreserveRows
(

testID number
)ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE PROCEDURE p_TempTable_PreserveRows
(

oCursor OUT RefType.RefCursor
)
IS
BEGIN

INSERT INTO T_TempTable_PreserveRows

(

testID

)

SELECT 1 FROM DUAL;

COMMIT;

OPEN OCURSOR FOR SELECT * FROM T_TempTable_PreserveRows;
END;
/

測試儲存過程後，可以看到遊標正常返回結果集，如果連續透過PL/SQL developer的test window執行多次，則發現在返回遊標的結果集中出現多次記錄
但是如果另開一個test視窗去測試，仍舊只返回一條記錄
如果檢查該臨時表，發現該表並無資料
SELECT * FROM T_TempTable_PreserveRows;

關於Oracle臨時表的使用的小經歷

相關文章