MySQL的SQL语句 -条件处理语句（2） - DECLARE ... HANDLER 语句

数据库杂货铺 2021-04-12

1685

DECLARE ... HANDLER 语句

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement


handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}


condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

DECLARE ... HANDLER 语句指定处理一个或多个条件的句柄。如果出现这些条件之一，则执行指定的 statement。Statement 可以是一个简单的语句，如 SET var_name = value，也可以是使用 BEGIN 和 END 编写的复合语句。

句柄声明必须出现在变量或条件声明之后。

handler_action 值指示在执行句柄语句后执行的操作：

● CONTINUE：继续执行当前程序。

● EXIT：执行终止在句柄声明所在的 BEGIN ... END 复合语句。即使条件发生在内部块中，也是如此。

● UNDO：不支持。

DECLARE ... HANDLER 的 condition_value 指示激活句柄的特定条件或条件类。可以采取以下形式：

● mysql_error_code：表示 MySQL 错误码的整数，如 1051 表示 “unknown table”：

DECLARE CONTINUE HANDLER FOR 1051
  BEGIN
    -- body of handler
  END;

不要使用 MySQL 错误代码 0，因为这表示成功，而不是错误条件。

● SQLSTATE [VALUE] sqlstate_value：5个字符的字符串文本，表示 SQLSTATE 值，例如 '42S01' 表示 “unknown table”：

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  BEGIN
    -- body of handler
  END;

不要使用以 '00' 开头的 SQLSTATE 值，因为这些值表示成功，而不是错误条件。

● condition_name：以前用 DECLARE ... CONDITION 指定的条件名称。条件名称可以与 MySQL 错误代码或 SQLSTATE 值相关联。

● SQLWARNING：以 '01' 开头的 SQLSTATE 值缩写。

DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    -- body of handler
  END;

● NOT FOUND：以 '02' 开头的 SQLSTATE 值的缩写。这在游标上下文中是相关的，用于控制游标到达数据集末尾时发生的操作。如果没有更多的行可用，则会出现 No Data 情况，SQLSTATE 值为 '02000'。要检测此条件，可以为它或 NOT FOUND 条件设置句柄。

DECLARE CONTINUE HANDLER FOR NOT FOUND
  BEGIN
    -- body of handler
  END;

如果 SELECT ... INTO var_list 语句检索不到任何数据，也会触发 NOT FOUND 条件。

● SQLEXCEPTION：不是以 '00'、'01' 或 '02' 开头的 SQLSTATE 值的缩写。

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- body of handler
  END;

如果发生没有声明句柄的条件，则所采取的操作取决于具体的条件类：

● 对于 SQLEXCEPTION 条件，存储程序在引发该条件的语句处终止，就好像存在一个 EXIT 句柄一样。如果程序被另一个存储程序调用，则调用程序将使用自己的句柄选择规则来处理该条件。

● 对于 SQLWARNING 条件，程序将继续执行，就像有一个 CONTINUE 句柄一样。

● 对于 NOT FOUND 条件，如果条件正常引发，则操作为 CONTINUE。如果是通过 SIGNAL 或 RESIGNAL 引发的，则操作是 EXIT。

以下示例使用句柄处理 SQLSTATE '23000'，该情况会在有重复键错误时发生：

mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter //


mysql> CREATE PROCEDURE handlerdemo ()
       BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
         SET @x = 1;
         INSERT INTO test.t VALUES (1);
         SET @x = 2;
         INSERT INTO test.t VALUES (1);
         SET @x = 3;
       END;
       //
Query OK, 0 rows affected (0.00 sec)


mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

请注意，在过程执行之后 @x 是 3，这表明在错误发生之后，执行一直持续到过程结束。如果没有 DECLARE ... HANDLER 语句，MySQL 会在由于主键约束第二次插入失败后执行默认操作（EXIT），SELECT @x 会返回 2。

若要忽略某个条件，请为其声明一个 CONTINUE 句柄并将其与空块相关联。例如：

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

块标签的范围不包括块内声明句柄的代码。因此，与句柄关联的语句不能使用 ITERATE 或 LEAVE 来引用包含句柄声明块的标签。考虑以下示例，其中 REPEAT 块具有 retry 标签：

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            ITERATE retry;    # illegal
          END;
        IF i < 0 THEN
          LEAVE retry;        # legal
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;

retry 标签在块中 IF 语句的作用域中。没有在 CONTINUE 句柄的作用域内，因此引用无效，会导致报错：

ERROR 1308 (42000): LEAVE with no matching label: retry

要避免在句柄中引用外部标签，请使用以下策略之一：

● 要离开块，请使用 EXIT 句柄。如果不需要块清理，则 BEGIN ... END 句柄主体可以为空：

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

否则，将清理语句放在句柄主体中：

DECLARE EXIT HANDLER FOR SQLWARNING
  BEGIN
    block cleanup statements
  END;

● 要继续执行，可以在 CONTINUE 句柄中设置一个状态变量，可以在闭合块中检查该状态变量以确定是否调用了该句柄。以下示例使用变量 done 来实现此目的：

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  DECLARE done INT DEFAULT FALSE;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            SET done = TRUE;
          END;
        IF done OR i < 0 THEN
          LEAVE retry;
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 -条件处理语句（2） - DECLARE ... HANDLER 语句

评论