关于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;

ITPub博客

关于Oracle临时表的使用的小经历

最新文章