关于DB2中使用游标中止循环的问题

关于DB2中使用游标中止循环的问题

DB2数据库游标循环中止

个人工作中遇到的问题，做一下记录。
问题描述：由于需求，需要通过存储过程实现删除一批表的数据。然而在写存储过程中，发现使用游标的时候，不能很好的循环删除各张表数据，例如：在这一批表中，有多个账期（20190610,20190611,20190612,20190613,20190614），循环删除时只删除了20190610一天的数据，就直接跳出循环了，，，
解决方法：在游标关闭之前设置异常为0，set IS_NOT_FOUND=0;

代码示例

CREATE PROCEDURE PROCEDURE_NAME
(OUT “OP_V_ERR_MSG” VARCHAR(5000),
OUT “DEBUG_MSG” VARCHAR(5000)
)
SPECIFIC “JCXXAPP”.“SQL190613181136535”
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
LB_MAIN:
BEGIN

–自定义字段
DECLARE V_SQLSTATE CHAR(5);
DECLARE I_SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE INTEGER;
DECLARE IS_NOT_FOUND INTEGER DEFAULT 0;

DECLARE V_DATE date ;
DECLARE V_SQL VARCHAR(500);

–自定义游标

declare MYCURSOR CURSOR with hold fOR s1 ;

–异常处理
DECLARE exit HANDLER FOR SqlException
BEGIN
VALUES(SQLCODE,SQLSTATE) INTO I_SQLCODE,V_SQLSTATE;
SET OP_V_ERR_MSG = VALUE(OP_V_ERR_MSG,’’)
||‘SYSTEM:SQLCODE=’||RTRIM(CHAR(I_SQLCODE))
||’,SQLSTATE=’||VALUE(RTRIM(V_SQLSTATE),’’)||’; ';
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET IS_NOT_FOUND=1;
set DEBUG_MSG = DEBUG_MSG||‘IS_NOT_FOUND leave’;
END;

—创建临时表
DECLARE GLOBAL TEMPORARY TABLE DELETES_RQ(
RQ date —日期
)with replace on commit preserve rows LOGGED;

insert into session.DELETES_RQ(RQ)
select distinct date(DB_FIELD01||’-’||DB_FIELD02||’-’||DB_FIELD03) as RQ from table_name1 where date(DB_FIELD01||’-’||DB_FIELD02||’-’||DB_FIELD03)<current date - 15 days
;

set DEBUG_MSG =’’;
set V_SQL=‘select RQ from session.DELETES_RQ’;

PREPARE s1 from V_SQL ;

open MYCURSOR;
MYCURSOR_Loop:
loop
fetch MYCURSOR into V_DATE;
if IS_NOT_FOUND=1 then
set IS_NOT_FOUND=0 ;
leave MYCURSOR_Loop ;

end if ;

set DEBUG_MSG = DEBUG_MSG||rtrim(char(V_DATE))||’;’;

delete from table_name1 where date(DB_FIELD01||’-’||DB_FIELD02||’-’||DB_FIELD03)=date(V_DATE)

delete from table_name2 where date(DB_FIELD01||’-’||DB_FIELD02||’-’||DB_FIELD03)=date(V_DATE)

**

set IS_NOT_FOUND=0 ;-------如果删除的数据有异常，会抛出not found警告，所以需要在删除语句运行完成后，强制设置IS_NOT_FOUND标记等于0，防止跳出循环

**

commit;

END LOOP MYCURSOR_Loop;

set DEBUG_MSG = DEBUG_MSG||‘END;’;

close MYCURSOR;

END LB_MAIN;