15.6.7.2 DECLARE ... HANDLER Statement (original) (raw)

15.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. If one of these conditions occurs, the specified statement executes.statement can be a simple statement such as SET _`varname`_ =_`value`_, or a compound statement written using BEGIN andEND (see Section 15.6.1, “BEGIN ... END Compound Statement”).

Handler declarations must appear after variable or condition declarations.

The handleraction value indicates what action the handler takes after execution of the handler statement:

The conditionvalue forDECLARE ... HANDLER indicates the specific condition or class of conditions that activates the handler. It can take the following forms:

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

Do not use MySQL error code 0 because that indicates success rather than an error condition. For a list of MySQL error codes, see Server Error Message Reference.

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

Do not use SQLSTATE values that begin with'00' because those indicate success rather than an error condition. For a list of SQLSTATE values, see Server Error Message Reference.

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

For another example, see Section 15.6.6, “Cursors”. TheNOT FOUND condition also occurs forSELECT ... INTO_`varlist`_ statements that retrieve no rows.

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

For information about how the server chooses handlers when a condition occurs, see Section 15.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 theDECLARE ... 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, andSELECT @x would have returned2.

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 useITERATE orLEAVE to refer to labels for blocks that enclose the handler declaration. Consider the following example, where theREPEAT block has a label ofretry:

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 theIF 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:

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

Otherwise, put the cleanup statements in the handler body: 

DECLARE EXIT HANDLER FOR SQLWARNING  
  BEGIN  
    block cleanup statements  
  END;  
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;