13.6.7.2 DECLARE ... HANDLER Statement语句

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
}

The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. DECLARE ... HANDLER语句指定处理一个或多个条件的处理程序。If one of these conditions occurs, the specified statement executes. 如果出现这些条件之一，则执行指定的statement。statement can be a simple statement such as SET var_name = value, or a compound statement written using BEGIN and END (see Section 13.6.1, “BEGIN ... END Compound Statement”).statement可以是一个简单的语句，如SET var_name = value，也可以是使用BEGIN和END编写的复合语句（请参阅第13.6.1节，“BEGIN ... END 复合语句”）。

Handler declarations must appear after variable or condition declarations.处理程序声明必须出现在变量或条件声明之后。

The handler_action value indicates what action the handler takes after execution of the handler statement:handler_action值指示处理程序在执行handler语句后执行的操作：

The condition_value for DECLARE ... HANDLER indicates the specific condition or class of conditions that activates the handler. DECLARE ... HANDLER的condition_value表示激活处理程序的特定条件或条件类。It can take the following forms:它可以采取以下形式：

For information about how the server chooses handlers when a condition occurs, see Section 13.6.7.6, “Scope Rules for Handlers”.有关发生条件时服务器如何选择处理程序的信息，请参阅第13.6.7.6节，“处理程序的作用域规则”。

If a condition occurs for which no handler has been declared, the action taken depends on the condition class:如果发生没有声明处理程序的条件，则所采取的操作取决于条件类：

The following example uses a handler for SQLSTATE '23000', which occurs for a duplicate-key error:以下示例使用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)

Notice that @x is 3 after the procedure executes, which shows that execution continued to the end of the procedure after the error occurred. 请注意，在过程执行后@x是3，这表明在错误发生后，执行一直持续到过程结束。If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default action (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2.如果没有出现DECLARE ... HANDLER语句，MySQL将在第二次INSERT由于PRIMARY KEY约束而失败后执行默认操作（EXIT），SELECT @x将返回2。

To ignore a condition, declare a CONTINUE handler for it and associate it with an empty block. 若要忽略某个条件，请为其声明一个CONTINUE处理程序并将其与空块关联。For example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

The scope of a block label does not include the code for handlers declared within the block. 块标签的范围不包括块内声明的处理程序的代码。Therefore, the statement associated with a handler cannot use ITERATE or LEAVE to refer to labels for blocks that enclose the handler declaration. 因此，与处理程序关联的语句不能使用ITERATE或LEAVE来引用包含处理程序声明的块的标签。Consider the following example, where the REPEAT block has a label of retry:考虑下面的示例，其中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;

The retry label is in scope for the IF statement within the block. retry标签在块中IF语句的作用域内。It is not in scope for the CONTINUE handler, so the reference there is invalid and results in an error:它不在CONTINUE处理程序的作用域中，因此那里的引用无效并导致错误：

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

To avoid references to outer labels in handlers, use one of these strategies:要避免在处理程序中引用外部标签，请使用以下策略之一：