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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
执行效果如下：
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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.
————————————————
