The DECLARE ... HANDLER statement is used to specify handlers for one or more exception conditions. When a condition occurs, the specified statements are 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
}
A statement can be a simple statement, such as SET var_name = 1, or a compound statement written using a BEGIN ... END block. For more information, see PL syntax structure.
handler_action specifies the operation to be performed by the handler after executing the handler statements. The details are as follows:
CONTINUE: Continues the execution of the current program.EXIT: Indicates that after executing the statements within the handler'sBEGIN ... ENDblock, exit the execution of the current stored procedure.UNDO: Not supported.
condition_value specifies the particular condition or condition category that activates the handler. It can take the following forms:
mysql_error_code: An integer literal representing the error code. For example, 5217 indicates "unknown column".SQLWARNING: Abbreviation forSQLSTATEvalues that start with '01'.condition_name: the name of the condition previously specified usingDECLARE ... CONDITION. The condition name can be associated with an error code or anSQLSTATEvalue.NOT FOUND: Abbreviation forSQLSTATEvalues that start with '02'. This is context-dependent for cursors and controls what happens when a cursor reaches the end of a dataset. If no more rows are available, a No Data condition withSQLSTATEvalue "02000" is returned. To detect this, you can set up handlers for it or for theNOT FOUNDcondition.SQLEXCEPTION: Abbreviation forSQLSTATEvalues that do not start with "00", "01", or "02".
If a condition has no handler declared, the following actions are taken based on the condition's category:
For an
SQLEXCEPTIONcondition, the stored program terminates at the statement that raised the condition, similar to using anEXIThandler. If this program is called by another stored program, the calling program will handle the condition according to its own selection rules.For an
SQLWARNINGcondition, the program continues execution, similar to using aCONTINUEhandler.For the
NOT FOUNDcondition, if it is raised normally, the action isCONTINUE. If it is raised by aSIGNALorRESIGNAL, the action isEXIT.
In the following example, the DECLARE ... HANDLER statement is used to declare that a CONTINUE operation will be executed when a duplicate key error occurs. Therefore, despite the PRIMARY KEY constraint, the default EXIT operation is not performed after the second INSERT fails, resulting in an execution result of 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, declare a CONTINUE handler for it and associate it with an empty block. Example:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
Limitations on block tags
The scope of a block label does not include the code for handlers declared within that block. Therefore, statements associated with a handler cannot use ITERATE or LEAVE to reference the block label that contains the handler declaration.
In the following example, the REPEAT block has a retry_label tag. The retry_label tag is within the scope of the IF statement inside the block, but it is not within the scope of the CONTINUE handler. Therefore, the REPEAT block's reference to the retry_label tag 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
Use the following strategies to avoid referencing external tags in handlers:
Use the
EXIThandler to leave the block and place cleanup statements within the handler body. If no cleanup is needed, the body of theBEGIN ... ENDblock can be left empty. Example:DECLARE EXIT HANDLER FOR SQLWARNING BEGIN block cleanup statements # Block cleanup statements END;To continue execution, set a status variable in the
CONTINUEhandler and check this variable within the enclosing block to determine whether to invoke the handler. The following example uses the variabledoneto 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