DECLARE CONTINUE/EXIT HANDLER FOR SQLSTATE 中CONTINUE和EXIT的区别

CREATE DEFINER PROCEDURE TEST()
BEGIN
  DECLARE rec_curs_value INT;
  DECLARE curs_dot CURSOR FOR SELECT dot_no FROM test_table;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SELECT 'find null' title;
    OPEN curs_dot;
    FETCH curs_dot INTO rec_curs_value;
    WHILE rec_curs_value IS NOT NULL DO
      SELECT 'not null yet' title;
      FETCH curs_dot INTO rec_curs_value;
    END WHILE;
    CLOSE curs_dot;
  SELECT 'Go on after find null' title;
END;

执行结果：

mysql> call test;
+-----------+
| title             |
+-----------+
| find null       |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| title                               |
+-----------------------+
| Go on after find null       |
+-----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

如果把第五行中的CONTINUE换成EXIT

执行结果：

mysql> call test;
+-----------+
| title     |
+-----------+
| find null |
+-----------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.19 sec)

此例可见在捕获异常时EXIT和CONTINUE的区别。