【官方原版翻译】DECLARE ... HANDLER语法

本文仅适用于MySQL 5.7

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定义一个handler处理一个或多个条件，如果某一个条件被触发, 则会执行定义的statement。statement 可以被简单的定义为 SETvar_name = value,，或者一组用BEGIN 和END包裹的语句块。 

Handler必须在条件及变量声明之后定义，不可提前。

 handler_action 表示执行handler语句后处理程序执行的操作：

• CONTINUE: 继续执行

• EXIT: 终止执行处理程序的BEGIN ... END复合语句。 即使条件发生在内部块中也是如此。

• UNDO: 不支持（ps：慎用）

对于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值类的简写。 这与游标的上下文相关，用于控制光标到达数据集末尾时发生的情况。 如果没有更多行可用，则SQLSTATE值为“02000”时会出现“无数据”条件。 要检测此情况，您可以为其设置处理程序或为NOT FOUND条件设置处理程序。

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

   对于不检索任何行的SELECT ... INTO var_list语句，也会出现NOT FOUND条件。

• SQLEXCEPTION: SQLSTATE值类的简写，不以'00'，'01'或'02'开头。

  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语句不存在，由于PRIMARY KEY约束，MySQL将在第二次INSERT失败后采取默认操作（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

要避免在处理程序中引用外部标签，请使用以下策略之一：

• 要leave块，请使用EXIT Handler。 如果不需要块清理，则BEGIN ... END处理程序主体可以为空：

  DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
  

  否则，将清理语句放在handler body中：

  DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
      
  block cleanup statements
  
    END;

   

• 要继续执行，请在CONTINUE Handler中设置状态变量，该Handler可在封闭块中检查以确定是否已调用Handler。 以下示例使用为此目的完成的变量：

  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;