MySQL 5.5 Reference Manual

12.7.4.2. `DECLARE` for Handlers
Prev	12.7.4. Conditions and Handlers	Next

12.7.4.2. `DECLARE` for Handlers

DECLARE handler_type HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

The DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using BEGIN and END (see Section 12.7.1, “BEGIN ... END Compound Statement Syntax”).

For a CONTINUE handler, execution of the current program continues after execution of the handler statement. For an EXIT handler, execution terminates for the BEGIN ... END compound statement in which the handler is declared. (This is true even if the condition occurs in an inner block.) The UNDO handler type statement is not supported.

If a condition occurs for which no handler has been declared, the default action is EXIT.

A condition_value for DECLARE ... HANDLER can be any of the following values:

An SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate sucess rather than an error condition. For a list of SQLSTATE values and MySQL error codes, see Section C.3, “Server Error Codes and Messages”.
A condition name previously specified with DECLARE ... CONDITION. See Section 12.7.4.1, “DECLARE for Conditions”.
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

Example:

mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

The example associates a handler with SQLSTATE value '23000', which occurs for a duplicate-key error. Notice that @x is 3 after the procedure executes, which shows that execution continued to the end of the procedure. If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default path (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2.

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

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

The statement associated with a handler cannot use ITERATE or LEAVE to refer to labels for blocks that enclose the handler declaration. That is, the scope of a block label does not include the code for handlers declared within the block. Consider the following example, where the REPEAT block has a label of retry:

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            ITERATE retry;  # illegal
          END;
      END;
      IF i < 0 THEN
        LEAVE retry;        # legal
      END IF;
      SET i = i - 1;
    UNTIL FALSE END REPEAT;
END;

The label is in scope for the IF statement within the block. It is not in scope for the CONTINUE handler, so the reference there is invalid and results in an error:

ERROR 1308 (42000): LEAVE with no matching label: retry

To avoid using references to outer labels in handlers, you can use these strategies:

To leave the block, use an EXIT handler:

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

To iterate, set a status variable in the handler that can be checked in the enclosing block to determine whether the handler was invoked. The following example uses the variable done for this purpose:

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  DECLARE done INT DEFAULT FALSE;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            SET done = TRUE;
          END;
      END;
      IF NOT done AND i < 0 THEN
        LEAVE retry;
      END IF;
      SET i = i - 1;
    UNTIL FALSE END REPEAT;
END;

Prev	Up	Next
12.7.4.1. `DECLARE` for Conditions	Home	12.7.5. Cursors

docs.sk

comprehensive documentation repository

12.7.4.2. `DECLARE` for Handlers