MySQL AB :: MySQL 5.1 Reference Manual :: 18.2.8.2 DECLARE Handlers

18.2.8.2. `DECLARE` Handlers

DECLARE handler_type HANDLER FOR 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 18.2.5, “BEGIN ... END Compound Statement Syntax”).

For a CONTINUE handler, execution of the current routine 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 yet supported.

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

A condition_value can be any of the following values:

An SQLSTATE value or a MySQL error code.
A condition name previously specified with DECLARE ... CONDITION. See Section 18.2.8.1, “DECLARE Conditions”.
SQLWARNING is shorthand for all SQLSTATE codes that begin with 01.
NOT FOUND is shorthand for all SQLSTATE codes that begin with 02.
SQLEXCEPTION is shorthand for all SQLSTATE codes not caught by SQLWARNING or NOT FOUND.

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 23000, which occurs for a duplicate-key error. Notice that @x is 3, which shows that MySQL executed to the end of the procedure. If the line DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 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;

User Comments

Posted by David Hammink on April 26 2005 6:55am

[Delete] [Edit]

In use with Cursor these handlers are not always usefull. If one does SELECT statements with row values found by the cursor and the item is not found it triggers the HANDLER and thus your cursor loop is gone.

It would be nice if you could use handler more fine grained for one cursor for example.
(or why not a boolean return of FETCH so one can write nice things like
WHILE (FETCH ....)

END WHILE

Posted by Anders Karlsson on April 26 2005 4:06pm

For an empty handler, for example if you want to exit and do nothing else if a specific error occurs, you can't just do:
DECLARE EXIT HANDLER FOR 1001;

Instead specify the action as BEGIN END (an empty block). Example:
DECLARE EXIT HANDLER FOR 1001 BEGIN END;

Add your own comment.

18.2.8.2. DECLARE Handlers

User Comments

18.2.8.2. `DECLARE` Handlers