DB2 求助存储过程 - Chinaunix

drop procedure P68_DELETE_HISDATA;
create procedure P68_DELETE_HISDATA(IN A_INPUT_DT VARCHAR(10))
BEGIN
declare procedureName varchar[code](128);
declare input_dt varchar(10);
declare temp varchar(10);
declare exe_sql varchar(1000);
declare drop_table_name varchar(64);
declare drop_table_schema varchar(10);
declare drop_data_column varchar(64);
declare drop_data_column_format int;
declare keep_days int default 30;
declare drop_where varchar(256);
declare drop_status int;
declare not_found condition for sqlstate '02000';
declare at_endc1 int default 0;
declare cursor1 cursor with return for select drop_table_name,drop_table_schema,drop_data_column,drop_data_column_format,keep_days,drop_where,drop_status,varchar(date(A_INPUT_DT) - keep_days day) temp from T129_drop_hisdata_config where drop_status = 1 ;
declare continue handler for not_found set at_endc1 = 1;
--打开游标
open cursor1;
--遍历cursor1
while(at_endc1 < 1) do
fetch cursor1 into drop_table_name,drop_table_schema,drop_data_column,drop_data_column_format,keep_days,drop_where,drop_status,temp;
if (at_endc1 < 1 and drop_data_column_format=10 ) then
set input_dt = temp;
elseif (at_endc1 < 1 and drop_data_column_format=8) then
set input_dt = SUBSTR(temp,1,4) ||''||SUBSTR(temp,6,2) ||''||SUBSTR(temp,9,2);
end if;
set exe_sql = 'delete from ' ||drop_table_schema||'.'||drop_table_name|| ' '||drop_where || ' and '''||input_dt||''' >= '||drop_data_column;
prepare psql from exe_sql;
execute psql;
end while;
close cursor1;
END;

复制代码