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

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

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

ON COMMIT

只有當建立臨時表的時候ON COMMIT語法才會被用到，這個語法指明瞭臨時表資料以會話還是以事務的生命週期進行儲存。

DELETE ROWS

Delete Rows選項表示在事務級別刪除資料的臨時表（預設值），每次事務結束後Oracle都會刪除所有記錄。

PRESERVE ROWS

Preserve Rows選項表示在會話級刪除資料的臨時表，當結束本次會話時Oracle才會刪除所有記錄。

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臨時表的使用的小經歷和一點疑問？

相關文章