本帖最后由 highroom 于 2016-3-28 21:42 编辑
各位大大好,小弟写一个存储过程,里面要使用到12个月的数据表,每个月一个表,然后把每个月数据用每个月表插入到指定表,这里有12个月的表,所以想通过循环来实现,但是不知道怎么处理啊?
create or replace procedure p_test
is
v_sql varchar2(800);
tt varchar2(40);
tab1 varchar2(20) :='test1';
tab2 varchar2(20) :='test2';
tab3 varchar2(20) :='test3';
tab4 varchar2(20) :='test5';
tab5 varchar2(20) :='test6';
tab6 varchar2(20) :='0';
tab7 varchar2(20) :='0';
tab8 varchar2(20) :='0';
tab9 varchar2(20) :='0';
tab10 varchar2(20) :='0';
tab11 varchar2(20) :='0';
tab12 varchar2(20) :='0';
begin
for i in 1..12 loop
tt := 'tab'||i;
if tt <>'0' then
v_sql := 'insert into t select * from '||tt;
--execute immediate v_sql;
dbms_output.put_line(v_sql);
end if;
end loop;
end;
---执行
begin
p_test;
end;
这样输出会有问题,得出的结果
insert into t select * from tab1
insert into t select * from tab2
insert into t select * from tab3
insert into t select * from tab4
insert into t select * from tab5
insert into t select * from tab6
insert into t select * from tab7
insert into t select * from tab8
insert into t select * from tab9
insert into t select * from tab10
insert into t select * from tab11
insert into t select * from tab12
我希望的应该是,在tab变量为0的时候不插入
insert into t select * from test1;insert into t select * from test2;
insert into t select * from test3;
insert into t select * from test5;
insert into t select * from test6;
有什么比较好的办法呢,请大大指导下,感谢!!