create or replace procedure pzy()
language sql
begin
declare v_xbm varchar(50);
declare v_jbm varchar(50);
declare v_a1 integer default 0;
declare v_b1 integer default 0;
declare v_bmhz varchar(20) default '2013';
declare v_sql varchar(1000);
declare exit handler for sqlexception
begin
rollback ;
end;
a1:begin
declare s_cur cursor with hold for
select
a.tabname
from
syscat.tables a,
syscat.columns b
where
a.tabname = b.tabname
and b.colname = 'BATCHDATE'
and type = 'T'
and card > '1000000'
;
declare continue handler for not found set v_a1 = 1;
declare global temporary table session.tmp(
name varchar(50)
) on commit preserve rows with replace not logged;
open s_cur;
fetch_loop1:loop
fetch s_cur into v_jbm;
if v_a1 = 0 then
set v_sql = 'insert into session.tmp select '''||v_jbm||''' from '||v_jbm||' where substr(trim(batchdate),1,4) = '||v_bmhz||' having count(*) > 1000';
prepare cre from v_sql;
execute cre;
commit;
set v_a1 = 0;
else
leave fetch_loop1;
end if ;
end loop fetch_loop1;
close s_cur;
commit;
b1:begin
declare c_cur cursor with hold for select name from session.tmp ;
declare continue handler for not found set v_b1 = 1;
open c_cur;
fetch_loop2:loop
fetch c_cur into v_jbm;
if v_b1 = 0 then
set v_xbm = v_jbm||'_'||v_bmhz;
set v_sql = 'create table '||v_xbm||' as (select * from '||v_jbm||') definition only';
prepare cre from v_sql;
execute cre;
set v_sql = 'insert into '||v_xbm||' select * from '||v_jbm||' where substr(trim(batchdate),1,4) = '||v_bmhz;
prepare cre from v_sql;
execute cre;
set v_sql = 'delete from '||v_jbm||' where substr(trim(batchdate),1,4) = '||v_bmhz;
prepare cre from v_sql;
execute cre;
commit;
else
leave fetch_loop2;
end if ;
end loop fetch_loop2;
close c_cur;
end b1;
end a1;
end@