调用DB2分表存储过程

CREATE PROCEDURE “DB2”.”FENBIAO” ()
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 ‘2016’;
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
@

–调用存储过程
db2 -tvf 1.sql -td@