DECLARE ... HANDLER

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

Syntax and parameters

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

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

• CONTINUE: continues execution of 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 for "unknown column".

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

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

• NOT FOUND: a shorthand for SQLSTATE values starting with '02'. This is relevant in the context of cursors 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 or for the NOT FOUND condition to detect it.

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

If a condition occurs that does not have a declared handler, 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 will handle the condition using its own selection rules.

• For SQLWARNING conditions, the procedure 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. Therefore, even though there is a PRIMARY KEY constraint, the default EXIT action is not taken after the second INSERT fails, resulting in 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 is 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 the block label where the handler is declared.

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 handlers, 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 is 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 in the enclosing block to determine if the handler was called. Here is an example using 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