DB2 求助 存储过程
本帖最后由 aleiliuwei 于 2011-06-20 14:48 编辑db2清除数据，保留N天记录的存储过程
我写了一个，但是我装的版本是9.7，银行用的是9.5，在9.5上不成功，9.7上可以。原因出在set exe_sql 这句上，请大虾们帮忙看看。以下是我写的存储过程。
里面用到的表：T129_drop_hisdata_config
drop procedure P68_DELETE_HISDATA;
create procedure P68_DELETE_HISDATA(IN A_INPUT_DT VARCHAR(10))
BEGIN
declare procedureName varchar(128);
declare input_dt varchar(10);
declare temp varchar(10);
declareexe_sqlvarchar(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;
preparepsqlfromexe_sql;
executepsql;
end while;
close cursor1;
END; 什么屁论坛
页:
[1]