DECLARE ... HANDLER

The DECLARE ... HANDLER statement specifies one or more handlers for exception conditions. If a condition is encountered, the specified statements are executed.

Syntax and parameter description

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
}

The statement can be a simple statement, such as SET var_name = 1, or a compound statement written using BEGIN ... END blocks.

The handler_action indicates the action the handler takes after executing the handler statements. 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.

The condition_value indicates 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 an 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 No Data condition with an SQLSTATE value of "02000" will occur. To detect this, you can set a handler for it or for the NOT FOUND condition.

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

If a condition without a declared handler occurs, the following actions are taken based on the condition's category:

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

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

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

In the following example, a CONTINUE handler is declared for duplicate key errors using the DECLARE ... HANDLER statement. 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.

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

delimiter //

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

delimiter ;

CALL handler_demo();
Query OK, 0 rows affected 

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 the block label where the handler is declared.

In the following example, the REPEAT block has a label retry_lable, which is within the scope of the IF statement inside the block. However, it is not within the scope of the CONTINUE handler, so referencing retry_lable from the REPEAT block is invalid and results in an error.

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, you can 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.

  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