DECLARE ... HANDLER

You can use the DECLARE ... HANDLER statement to specify a handler that handles one or more exception conditions. When a condition occurs, the handler executes the specified statement.

Syntax and parameters

The handler declaration must be subsequent to the variable or condition declaration. The syntax of the DECLARE ... HANDLER statement is as follows:

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
}

statement can be a simple statement such as SET var_name = 1, or a compound statement written in a BEGIN ... END block. For more information, see PL syntax.

handler_action specifies the action that the handler takes after executing the exception handling statements.

• CONTINUE: continues with the current procedure.

• EXIT: exits the execution of this stored procedure after the statements in the BEGIN ... END block of the handler are executed.

• UNDO: not supported.

condition_value specifies the condition or condition class for triggering the handler. It can be in any of the following forms:

• mysql_error_code: an integer literal of the error code. For example, 5217 indicates an unknown column.

• SQLWARNING: shorthand for an SQLSTATE value that starts with ‘01’.

• condition_name: the name of the condition specified previously by the DECLARE ... CONDITION statement. The condition name can be associated with an error code or an SQLSTATE value.

• NOT FOUND: shorthand for a SQLSTATE value that starts with ‘02’. This value is related with the context of the cursor and is used to control what happens when the cursor reaches the end of the dataset. If no more rows are available, a No Data condition with an SQLSTATE value 02000 appears. To detect this situation, you can set a handler for it or set a handler for the NOT FOUND condition.

• SQLEXCEPTION: shorthand for SQLSTATE values not starting with ‘00’, ‘01’, or ‘02’.

If a condition that has never been declared for any handler occurs, the system takes the following actions based on the condition class:

• For an SQLEXCEPTION condition, the stored program terminates at the statement that raised the condition, which is similar to an EXIT handler. If the program is called by another stored program, the calling program follows its own rules to handle the condition.

• For an SQLWARNING condition, the program continues the procedure, which is similar to a CONTINUE handler.

• For a NOT FOUND condition, if the condition is raised normally, the program takes the CONTINUE action. If the condition is raised by a SIGNAL or RESIGNAL statement, the program takes the EXIT action.

In the following example, the DECLARE ... HANDLER statement is used to declare that the CONTINUE operation is to be performed when a duplicate key error occurs, regardless of the PRIMARY KEY constraints. Therefore, the default EXIT action is not taken upon the second INSERT failure. As a result, the final execution result is 3.

obclient> CREATE TABLE test.tbl1 (c1 INT, PRIMARY KEY (c1));
Query OK, 0 rows affected

obclient> delimiter//

obclient> CREATE PROCEDURE handler_demo()
       BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = 1;
         INSERT INTO test.tbl1 VALUES (101);
         SET @x = 2;
         INSERT INTO test.tbl1 VALUES (101);
         SET @x = 3;
       END;
       //
Query OK, 0 rows affected

obclient> delimiter;

obclient> CALL handler_demo();
Query OK, 0 rows affected

obclient> SELECT @x;
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
1 row in set

If you need to ignore a condition, you can declare a CONTINUE handler for it and associate it with an empty block. Here is an example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

Limitations on block labels

The scope of a block label does not include the code of the handler declared within the block. Therefore, statements associated with handlers cannot use ITERATE or LEAVE to reference block labels that contain handler declarations.

In the following example, the REPEAT block has a retry_lable label, and the retry_lable label is within the scope of the IF statement in the block, but it is not within the scope of the CONTINUE handler. Therefore, the reference of the REPEAT block to the retry_lable label is invalid and causes an error.

obclient> CREATE PROCEDURE proc1 ()
   BEGIN
     DECLARE n INT DEFAULT 10;
     retry_lable:
       REPEAT
         BEGIN
           DECLARE CONTINUE HANDLER FOR SQLWARNING
             BEGIN
               ITERATE retry_lable;    # Invalid label
             END;
           IF n < 0 THEN
             LEAVE retry_lable;        # Valid label
           END IF;
           SET n = n - 1;
         END;
       UNTIL FALSE END REPEAT;
   END //

ERROR 1308 (42000): no matching label: retry_lable

You can adopt the following strategies to avoid referencing external labels in handlers:

• Use an EXIT handler to leave the block and place the cleanup statement in the handler body. If block cleanup is not required, the handler body contained in the BEGIN ... END block can be empty. Here is an example:

  DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
      block cleanup statements # Block cleanup statement
    END;
  

• To continue execution, set a status variable in the CONTINUE handler and check the status variable in the closed block to check whether the handler was called. The following example uses the done variable to achieve this:

  obclient> CREATE PROCEDURE proc2()
     BEGIN
       DECLARE n INT DEFAULT 10;
       DECLARE done INT DEFAULT FALSE;
       retry_lable:
         REPEAT
           BEGIN
             DECLARE CONTINUE HANDLER FOR SQLWARNING
               BEGIN
                 SET done = TRUE;
               END;
             IF done OR n < 0 THEN
               LEAVE retry_lable;
             END IF;
             SET n = n - 2;
           END;
         UNTIL FALSE END REPEAT;
     END;//
  Query OK, 0 rows affected