'CREATE GLOBAL TEMPORARY TABLE的问题

[shdcsdn]

前提这是个SP,TEMP表中有2条记录
create or replace procedure aaa is
S_SQL VARCHAR2(5000);
I_SQL VARCHAR2(5000);
V_COUNT integer;
begin
S_SQL :='CREATE GLOBAL TEMPORARY TABLE AAA_TEMP AS (SELECT * from temp) ' ;
execute immediate S_SQL ; --这时候AAA_TEMP中有数据吗?
I_SQL :='INSERT INTO AAA_TEMP VALUE (SELECT * from temp);
execute immediate I_SQL ; --这时候AAA_TEMP中有数据吗?
execute immediate 'SELECT COUNT(*) INTO V_COUNT FROM AAA_TEMP ' ; --显示错误：表不存在
DBMS_OUTPUT.put_line(TO_CHAR(V_COUNT));
end aaa;
这个临时表何时有数据?
如何证明已经插入数据了?
谢谢!

[boydgmx]

S_SQL :='CREATE GLOBAL TEMPORARY TABLE AAA_TEMP AS (SELECT * from temp) ' ;
execute immediate S_SQL ; --这时候AAA_TEMP中有数据吗?

--没有数据

I_SQL :='INSERT INTO AAA_TEMP (SELECT * from temp);
execute immediate I_SQL ; --这时候AAA_TEMP中有数据吗?

--有数据，但一旦commit就没有数据了


CREATE GLOBAL TEMPORARY TABLE
默认情况下是 on commit delete rows,
而create命令之后有一个隐式的提交，所以创建完毕之后，没有数据

如果你希望看到数据：

CREATE GLOBAL TEMPORARY TABLE AAA_TEMP ON COMMIT PRESERVE ROWS AS SELECT * FROM TEMP;