DB2 求助 存储过程

aleiliuwei

db2清除数据，保留N天记录的存储过程

我写了一个，但是我装的版本是9.7，银行用的是9.5，在9.5上不成功，9.7上可以。原因出在set exe_sql 这句上，请大虾们帮忙看看。以下是我写的存储过程。
里面用到的表：T129_drop_hisdata_config

1. drop procedure P68_DELETE_HISDATA;
   
2. create procedure P68_DELETE_HISDATA(IN A_INPUT_DT VARCHAR(10))
   
3. BEGIN
   
4.    declare procedureName varchar[code](128);
   
5.    declare input_dt varchar(10);
   
6.    declare temp varchar(10);
   
7.    declare  exe_sql  varchar(1000);
   
8.    declare drop_table_name varchar(64);
   
9.    declare drop_table_schema varchar(10);
   
10.    declare drop_data_column varchar(64);
    
11.    declare drop_data_column_format int;
    
12.    declare keep_days int default 30;
    
13.    declare drop_where varchar(256);
    
14.    declare drop_status int;
    
15.    declare not_found condition for sqlstate '02000';
    
16.    declare at_endc1 int default 0;
    
17.    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 ;
    
18.         declare continue handler for not_found set at_endc1 = 1;
    
19.         --打开游标
    
20.         open cursor1;
    
21.         --遍历cursor1
    
22.         while(at_endc1 < 1) do
    
23.         fetch cursor1 into drop_table_name,drop_table_schema,drop_data_column,drop_data_column_format,keep_days,drop_where,drop_status,temp;
    
24.   if (at_endc1 < 1 and drop_data_column_format=10 ) then
    
25.   set input_dt = temp;
    
26.   elseif (at_endc1 < 1 and drop_data_column_format=8) then
    
27.   set input_dt = SUBSTR(temp,1,4) ||''||SUBSTR(temp,6,2) ||''||SUBSTR(temp,9,2);  
    
28. end if;
    
29. set exe_sql = 'delete from ' ||drop_table_schema||'.'||drop_table_name|| ' '||drop_where || ' and '''||input_dt||''' >= '||drop_data_column;
    
30. prepare  psql  from  exe_sql;
    
31. execute  psql;
    
32. end while;
    
33. close cursor1;
    
34. 
    
35. END;

复制代码

aleiliuwei

什么屁论坛