oralce里如何建立临时表及会话问题。SQL code-- Created on 2012-2-25 by ADMINISTRATOR declare-- Local v
oralce里如何建立临时表及会话问题。
- SQL code
-- Created on 2012-2-25 by ADMINISTRATOR declare -- Local variables here cursor cur_jg is select is_jg,spid from spkfk where spid in (select spid from xt_sjd_mx where djbh in ('XSC00425794', 'XSC00426199', 'XSC00426159', 'XSC00425793', 'XSC00425899', 'XSC00425802', 'XSC00426007', 'XSC00425810')); v_jg varchar2(4); v_spid varchar2(40); sql_str varchar2(400); v_ls int; v_num int; /* sql_new_str varchar2(200);*//* ls_table varchar2(4);*/begin -- Test statements here sql_str:= 'CREATE GLOBAL TEMPORARY TABLE ls_table( a varchar2(4) not null, b varchar2(40)) ON COMMIT PRESERVE ROWS'; execute immediate sql_str; open cur_jg; loop fetch cur_jg into v_jg, v_spid; exit when cur_jg%notfound; sql_str:='insert into ls_table values('''||v_jg||''','''||v_spid||''')'; execute immediate sql_str; sql_str:='commit'; execute immediate sql_str; --update spkfk set is_jg = '否' where spid = v_spid; end loop; close cur_jg; for v_num in 1..9 loop sql_str:='select a into v_ls from ls_table'; execute immediate sql_str; dbms_output.put_line(v_ls); end loop;exception when others then dbms_output.put_line(substr(sqlerrm,1,200));end;ON COMMIT PRESERVE ROWS
想建个历史表把一些数据保存下，然后在下边的for循环里，在单独显示出来，上边这种写法应该是在同一session里都可以取到值吧，为什么我循环出来的就全为null呢？我没写过临时表，请大侠指点下，谢谢。
[解决办法]
oracle 的临时表是都是先在数据库里建立后，你在过程中调用，并对在transaction里DML操作临时表
不要像SQLSERVER那样在过程里建临时表
[解决办法]
请参考这篇文章，写的很详细：http://blog.csdn.net/zikwang/article/details/2163435
[解决办法]
明显的是你的for 循环有问题
for v_num in 1..9 loop
sql_str:='select a into v_ls from ls_table';
execute immediate sql_str;
dbms_output.put_line(v_ls);
end loop;
你这样循环应该要报错的才对，除非你上面的游标只往临时表里插了一条数据，插多条肯定要报错
还有你拼字符串都拼错了
sql_str:='select a into v_ls from ls_table'
应该是
sql_str:='select a into '||v_ls||' from ls_table'