一个临时表的问题？

Question

我用存储过程建立了一个临时表，然后往里面插入数据，按理来说每执行完一次存储过程临时表应该会自动清空的，但我发现它没自动清空这是为何，请高手们帮看看谢谢，以下是存储过程代码
create or replace procedure sp_getResult(rst out sys_refcursor) is
begin
---判断是否创建临时表
select count(*) into tempisexist from all_tables where table_name='TMP'; 
if tempisexist=0 then
execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10)) on commit DELETE rows';
end if;execute immediate 'insert into TMP (id,nameyzl) values(1,''AA'')';open rst for 'select * from tmp';
end;当我执行完一次后的结果是
1    AA跟着执行完第二次后的结果是
1    AA
1    AA
也就是说每次执行完一次存储过程后临时表没自动清空，这是为何，麻烦高手帮看看，谢谢

Answer 1

在Oracle中，可以创建以下两种临时表：  
1。会话特有的临时表  
  CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )  
  ON COMMIT PRESERVE ROWS；  2。事务特有的临时表  
  CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )  
  ON COMMIT DELETE ROWS；  
    
  在Oracle中，全局临时表并不会删除，实际上你只需要建立一次，以后直接应用就行了.
所以你没必要动态创建表.另外你的建表语句相当于是建立了第一种临时表,所以数据不会清空.

Answer 2

N COMMIT DELETE ROWS；  

Answer 3

to minitoy我用的是execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10)) on commit DELETE rows';是DELETE rows呀，应该属于第二种吧？
那如果我想每次执行完存储过程后都会自动清除临时表数据的语句改怎么写呢？麻烦告知谢谢！

Answer 4

哦,那是我看错了,没看到后面的commit

Answer 5

如4楼,第一次取得数据后commit没?  
on commit DELETE rows
提交事物时删除数据.

Answer 6

to wildwave
没有commit...?是这样写吗？
create global temporary table TMP(id number,nameyzl varchar2(10)) on DELETE rows执行不了报错呀

Answer 7

你创建的临时表是事务级别的（on commit DELETE rows）就是要在数据insert 完之后，然后再commit提交，临时表里的数据才清空！
你往临时表insert数据后要commit！

Answer 8

是不是应该改成这样create or replace procedure sp_getResult(rst out sys_refcursor) is
begin
---判断是否创建临时表
select count(*) into tempisexist from all_tables where table_name='TMP';  
if tempisexist=0 then
execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10));
end if;execute immediate 'insert into TMP (id,nameyzl) values(1,''AA'')';open rst for 'select * from tmp';
execute immediate 'on commit DELETE rows'; --但执行这句话的时候出错呀，麻烦再帮看看谢谢
end;

Answer 9

过程不要改,在你select得到结果后,记得执行下commit;
然后再次查询,这样两次的结果就不会重叠了.

Answer 10

临时表的类型是on commit DELETE rows
所以执行完后 commit 就可以情空数据

Answer 11

呵，不好意思！我刚接触orcale,以前用的是sql server，我发现orcale和sql server有很多地方不一样还望大家耐心些教教小弟，谢谢各位了！我改成了如下代码create or replace procedure sp_getResult(rst out sys_refcursor) is
begin
---判断是否创建临时表
select count(*) into tempisexist from all_tables where table_name='TMP';   
if tempisexist=0 then
execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10));
end if;execute immediate 'insert into TMP (id,nameyzl) values(1,''AA'')';open rst for 'select * from tmp';
commit; 
end;
执行是没报错，但返回的数据是空，也就是说数据在返回前就被清空了，我想要的是返回数据集后在自动清空临时表该如何写呢？麻烦再帮指导指导,谢谢

Answer 12

呵，不好意思！我刚接触orcale,以前用的是sql server，我发现orcale和sql server有很多地方不一样还望大家耐心些教教小弟，谢谢各位了！我改成了如下代码create or replace procedure sp_getResult(rst out sys_refcursor) is
begin
---判断是否创建临时表
select count(*) into tempisexist from all_tables where table_name='TMP';   
if tempisexist=0 then
execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10));
end if;execute immediate 'insert into TMP (id,nameyzl) values(1,''AA'')';open rst for 'select * from tmp';
commit; 
end;
执行是没报错，但返回的数据是空，也就是说数据在返回前就被清空了，我想要的是返回数据集后在自动清空临时表该如何写呢？麻烦再帮指导指导,谢谢

Answer 13

我不是说了commit 会清空数据的 

Answer 14

to wkc168
那根据我上面的代码如何改才能实现先返回数据后再清空临时表呢？麻烦帮改改我写的存储过程实现这个功能，万分感谢！

Answer 15

难道是这样写?
create or replace procedure sp_getResult(rst out sys_refcursor) is
begin
---判断是否创建临时表
select count(*) into tempisexist from all_tables where table_name='TMP';   
if tempisexist=0 then
execute immediate 'create global temporary table TMP(id number,nameyzl varchar2(10)) on commit delete rows';
end if;
commit;  --写在前面？那commit和delete from TMP有什么区别呢？execute immediate 'insert into TMP (id,nameyzl) values(1,''AA'')';open rst for 'select * from tmp';end;

Answer 16

--你到底是要什么目的调用过程做什么你是想在调用的时候数据自动清空 还是怎么的
SQL> edi
已写入 file afiedt.buf  1  create or replace procedure sp_getResult(v_id number,rst out sys_refcursor) is
  2  tempisexist number;
  3  str varchar2(1000);
  4  begin
  5  str:='create global temporary table TMP(id number,nameyzl varchar2(10)) on commit delete rows';
  6  select count(*) into tempisexist from all_tables where table_name='TMP';
  7  if tempisexist=0 then
  8  execute immediate str;
  9  end if;
 10  commit;
 11  execute immediate 'insert into TMP (id,nameyzl) values(:v_id,''AA'')' using v_id;
 12  open rst for 'select * from tmp';
 13* end;
SQL> /过程已创建。
SQL> edi
已写入 file afiedt.buf  1  declare
  2  v_id number:=&a;
  3  c1 sys_refcursor;
  4  begin
  5  sp_getResult(v_id,c1);
  6  close c1;
  7  for i in(select * from tmp) loop
  8  dbms_output.put_line(i.id||','||i.nameyzl);
  9  end loop;
 10* end;
SQL> /
输入 a 的值:  3
原值    2: v_id number:=&a;
新值    2: v_id number:=3;
3,AAPL/SQL 过程已成功完成。SQL> /
输入 a 的值:  5
原值    2: v_id number:=&a;
新值    2: v_id number:=5;
5,AAPL/SQL 过程已成功完成。

Answer 17

--或者在调用的时候加个commit就可以自动清空前面的数据
SQL> edi
已写入 file afiedt.buf  1  create or replace procedure sp_getResult(v_id number,rst out sys_refcursor) is
  2  tempisexist number;
  3  str varchar2(1000);
  4  begin
  5  str:='create global temporary table TMP(id number,nameyzl varchar2(10)) on commit delete rows';
  6  select count(*) into tempisexist from all_tables where table_name='TMP';
  7  if tempisexist=0 then
  8  execute immediate str;
  9  end if;
 10  execute immediate 'insert into TMP (id,nameyzl) values(:v_id,''AA'')' using v_id;
 11  open rst for 'select * from tmp';
 12* end;
SQL> /过程已创建。SQL> edi
已写入 file afiedt.buf  1  declare
  2  v_id number:=&a;
  3  c1 sys_refcursor;
  4  begin
  5  sp_getResult(v_id,c1);
  6  close c1;
  7  for i in(select * from tmp) loop
  8  dbms_output.put_line(i.id||','||i.nameyzl);---在这里面做你调用过程的目的
  9  end loop;
 10  commit;
 11* end;
SQL> /
输入 a 的值:  2
原值    2: v_id number:=&a;
新值    2: v_id number:=2;
2,AAPL/SQL 过程已成功完成。SQL> select * from tmp
  2  /未选定行