DECLARE ... HANDLER

The DECLARE ... HANDLER statement specifies a handler for one or more exception conditions. If a condition occurs, the specified statement is executed.

Syntax and parameters

Handler declarations must appear after variable or condition declarations. The syntax for 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 using BEGIN ... END blocks. For more information, see PL syntax structure.

handler_action specifies the action the handler takes after executing the handler statement. Details are as follows:

• CONTINUE: Continue executing the current program.

• EXIT: Indicates that after the Statement in the BEGIN ... END block of the handler is executed, the execution of the current stored procedure should exit.

• UNDO: Not supported.

condition_value specifies the specific condition or condition category that activates the handler. It can take the following forms:

• mysql_error_code: An integer literal representing an error code, such as 5217, which indicates "unknown column".

• SQLWARNING: A shorthand for SQLSTATE values starting with '01'.

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

• NOT FOUND: A shorthand for SQLSTATE values starting with '02'. This is relevant in cursor contexts and is used to control what happens when a cursor reaches the end of a dataset. If no more rows are available, a NOT FOUND condition with an SQLSTATE value of "02000" occurs. You can set a handler for this condition to detect it.

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

If a condition occurs for which no handler is declared, the following actions are taken based on the condition's category:

• For SQLEXCEPTION conditions, the stored procedure terminates at the statement that raised the condition, similar to using an EXIT handler. If the procedure is called by another stored procedure, the calling procedure uses its own selection rules to handle the condition.

• For SQLWARNING conditions, the program continues execution, similar to using a CONTINUE handler.

• For NOT FOUND conditions, if the condition is raised normally, the action is CONTINUE. If it is raised by SIGNAL or RESIGNAL, the action is EXIT.

In the following example, the DECLARE ... HANDLER statement specifies that the CONTINUE action should be taken when a duplicate key error occurs. As a result, even though there is a PRIMARY KEY constraint, the default EXIT action is not taken after the second INSERT fails, leading to the final execution result being 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 

To ignore a condition, you can declare a CONTINUE handler and associate it with an empty block. Here's an example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

Limitations on block labels

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

In the following example, the REPEAT block has a retry_lable label, which is within the scope of the IF statement inside the block. However, it is not within the scope of the CONTINUE handler, making the reference to retry_lable invalid and causing 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

To avoid referencing external labels in a handler, use the following strategies:

• Use an EXIT handler to exit the block and place cleanup statements within the handler's body. If block cleanup is not needed, the BEGIN ... END block containing the handler can be empty. Here's an example:

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

• To continue execution, set a status variable in the CONTINUE handler and check this variable within the enclosing block to determine if the handler was called. The following example uses the variable done 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