本文内容来自YashanDB官网，原文内容请见https://www.yashandb.com/newsinfo/7575253.html?templateId=1718516
MySQL的CONTINUE HANDLER
迁移MySQL业务时，遇到了MySQL的CONTINUE HANDLER FOR not found用法。详情如下：
-- 表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 ;
执行效果如下：
mysql> CALL DEMO(1);
+------+
| v2 |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
+------+
| s |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL DEMO(2);
+------+
| v2 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
+------+
| s |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL DEMO(3);
Query OK, 0 rows affected (0.00 sec)
YashanDB的改写方法
MySQL的CONTINUE HANDLER需要在YashanDB进行改写。改写原则：1）对于游标的执行，需要使用游标属性%notfound来改写；2）对于select语句的执行，则需要通过NO_DATA_FOUND的异常处理来改写。例如，以上示例的改写详情如下：
CREATE OR REPLACE PROCEDURE demo(p1 IN int) IS
s bigint DEFAULT 0;
v1 bigint DEFAULT 0;
v2 bigint DEFAULT 0;
CURSOR cur1 is select c2 from t1 where c1=p1;
BEGIN
OPEN cur1;
<<r_loop>>
LOOP
IF s = 1 THEN
EXIT r_loop;
END IF;
-- 如果游标fetch结果not found，则设置s=1
FETCH cur1 INTO v1;
IF cur1%notfound THEN
s :=1 ;
END IF;
IF s = 0 THEN
-- 如果查询结果not found，则设置s=1
BEGIN
select c2 into v2 from t2 where c1=v1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
s :=1 ;
END;
-- 调试：输出v2值
DBMS_OUTPUT.PUT_LINE(v2);
-- 调试：输出s值
DBMS_OUTPUT.PUT_LINE(s);
END IF;
END LOOP r_loop;
CLOSE cur1;
END;
/
YashanDB的执行效果
改写后的执行效果如下：
SQL> SET SERVEROUTPUT ON
SQL> CALL DEMO(1);
11
0
PL/SQL Succeed.
SQL> CALL DEMO(2);
0
1
PL/SQL Succeed.
SQL> CALL DEMO(3);
PL/SQL Succeed.
