oracle的存储过程里怎样调用动态变量

作者:网络 来源:佚名 更新时间:2008-11-17 15:07:44 点击:

最大的网站源码资源下载站,

create or replace procedure sysringall_test is
all_tmp_name char(30);
all_name char(30);
begin
if all_tmp_name is null then
select 'ring_all_old_'||to_char(sysdate,'yyyymm')||'_tmp' into all_tmp_name from dual;
end if;
if all_name is null then
select 'ring_all_old_'||to_char(sysdate,'yyyymm') into all_name from dual;
end if;
------由all进tmp---------
insert into all_tmp_name
select * from all_name;
commit;
end sysringall_test;

  ==========================

  其实就是想动态生成ring_all_old_200606和ring_all_old_200606_tmp

  这两个表名让下面使用

  但老是编译出错,如果把下面改成

  insert into ring_all_old_200606_tmp

  select * from ring_all_old_200606;

  就能编译通过.

  请问该怎样改啊?

create or replace procedure sysringall_test is
all_tmp_name char(30);
all_name char(30);
tmp_str varchar(2000);
begin
if all_tmp_name is null then
select 'ring_inkfish_'||to_char(sysdate,'yyyymm')||'_tmp' into all_tmp_name from dual;
end if;
if all_name is null then
select 'ring_inkfish_'||to_char(sysdate,'yyyymm') into all_name from dual;
end if;
tmp_str :='insert into '||all_tmp_name||' select * from '||all_name;
execute immediate tmp_str;
commit;
end sysringall_test;

  嘿嘿,不容易啊!!自己搞定啦!希望能对大家有帮助!