Oracle的动态查询

declare

#定义变量

vc_sql varchar(2000);

num number;

begin

#num 从331到459循环，自加1，默认升序，降序用reverse

for num in 331 .. 459

loop

#定义sql语句，将查询到的数据插入到新表中

vc_sql := 'insert into c_13912345678 select sssubmittime,sourceterminalid,destaddr,stat,msgcontent from '||

'(select scmsgid,sssubmittime,sourceterminalid,destaddr,msgcontent from T_00'||

replace(to_char(num,'999'),' ')||

'0SUBMIT where destaddr=13912345678) t'||

' left join t_00'||

replace(to_char(num,'999'),' ')||

'0rpt r using(scmsgid)';

execute immediate vc_sql; 

execute immediate 'commit';

end loop;

end;