Spec-Zone .ru
спецификации, руководства, описания, API

13.6.7.2. DECLARE ...HANDLER Syntax

DECLARE handler_action HANDLER    FOR condition_value [, condition_value] ...    statementhandler_action:    CONTINUE  | EXIT  | UNDOcondition_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. If one of these conditions occurs, the specified statement executes. 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 Syntax").

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:

The condition_value for DECLARE ... HANDLER indicates the specific condition or class of conditions that activates the handler:

For information about how the server chooses handlers when a condition occurs, see Section 13.6.7.6, "Scope Rules for Handlers".

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:

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. 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.

To ignore a condition, declare a CONTINUE handler for it and associate it with an empty block. 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. Consider the following example, where the REPEAT block has a label of 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. It is not in scope for the CONTINUE handler, so the reference there is invalid and results in an error:

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

To avoid references to outer labels in handlers, use one of these strategies: