-- 表 DDL

create table t1(c1 int primary key, c2 int);

create table t2(c1 int primary key, c2 int);

-- 数据

insert into t1 values(1,1),(2,2);

insert into t2 values(1,11);

commit;

-- 使用 CONTINUE HANDLER 的存储过程

DELIMITER $$

CREATE PROCEDURE demo(IN p1 int)

BEGIN

DECLARE s bigint DEFAULT 0;

DECLARE v1 bigint DEFAULT 0;

DECLARE v2 bigint DEFAULT 0;

DECLARE cur1 CURSOR FOR select c2 from t1 where c1=p1;

DECLARE CONTINUE HANDLER FOR not found SET s=1;

OPEN cur1;

r_loop: LOOP

IF s = 1 THEN

LEAVE r_loop;

END IF;

-- 如果游标 fetch 结果 not found，则设置 s=1

FETCH cur1 INTO v1;

IF s = 0 THEN

-- 如果查询结果 not found，则设置 s=1

select c2 into v2 from t2 where c1=v1;

-- 调试：输出 v2 值

select v2;

-- 调试：输出 s 值

select s;

END IF;

END LOOP r_loop;

CLOSE cur1;

END$$

DELIMITER ;

ITPub博客

【YashanDB 知识库】共享从 MySQL 异常处理 CONTINUE HANDLER 的改写方法