关于Oracle临时表的使用的小经历
前几天给同事一个建议—使用临时表完成某些复杂逻辑的处理和输出，今天突然告诉我说临时表在存储过程中用不了，所以回去特意调试了一下，没问题。
让我们先看看Oracle官方文档中的说明吧
(
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官方文档中的说明吧
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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6517/viewspace-134192/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
关于主外键表数据提交过程中的一点疑惑
请登录后发表评论
登录
全部评论
