Skip navigation links

Additional languages
22.2.9 Cursors »
Section Navigation      [Toggle]

22.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 22.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. You should not use SQLSTATE value '00000' or error code 0, because those indicate sucess rather than an error condition. For a list of SQLSTATE and error values, see Section B.2, “Server Error Codes and Messages”.

  • A condition name previously specified with DECLARE ... CONDITION. See Section 22.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. 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.

  • 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;

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 different strategies:

  • If you want to leave the block, you can use an EXIT handler: 

    DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

  • If you want to iterate, you can 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;

User Comments

Posted by Aliro Morales on June 15 2007 10:06pm[Delete] [Edit]

Hello everyone:

I was in the need to find way to handle a specific error on a query. I wasn’t able to find a way to easily detect the place that triggered the error, basically because the error trapping routines are general to a whole block of code/queries. Well, this is the way I solved the issue:

Ahora la versión en mi lengua nativa :P
estaba en la necesidad de encontrar una forma de manejar los errores por bloques de consultas y no de forma global para así saber donde ocurrió cierto error y poder reportarlo de forma mas especifica en un tabla de errores, bueno esta fue la forma que soluciones mi problema.

La tabla de pruebas.
Table for the test.

CREATE TABLE `datos` (
`id` int(11) NOT NULL,
`nombre` varchar(50) NOT NULL,
`apellido` varchar(50) NOT NULL,
`edad` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

El procedimiento almacenado
The store procedure.

DROP PROCEDURE IF EXISTS test.error;
CREATE PROCEDURE `error`()
BEGIN
DECLARE va INT;
#this is a handler for a global error, but not to handle the errors of the sub block BEGIN END inside of store procedures
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' BEGIN
INSERT INTO datos (id, nombre) VALUES ('14', 'error global');
END;
BEGIN
#here, a handler of errors only for this block
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' BEGIN
INSERT INTO datos (id, nombre) VALUES ('11', 'primer error select');
END;
SELECT id2
INTO va
FROM datos;
END;


BEGIN
#here, two handler of errors and single one are triggered
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN
INSERT INTO datos (id, nombre) VALUES ('12', 'segundo error insert');
END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' BEGIN
INSERT INTO datos (id, nombre) VALUES ('13', 'tercer erro select');
END;

INSERT INTO datos (id) VALUES ('1');

SELECT id
INTO va
FROM datos
LIMIT 1;
END;


#this error is handled by a global handler
SELECT id2
INTO va
FROM datos;

END


test it you self and enjoy!.

Posted by Nathan Moon on May 23 2008 4:13am[Delete] [Edit]

Along the same lines as the previous comment, I ran into a problem in a procedure like this:

...
BEGIN
DECLARE someid INTEGER;
DECLARE otherid INTEGER;
DECLARE done INTEGER DEFAULT 0;
DECLARE someids CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN someids;
REPEAT
FETCH someids INTO someid;
IF NOT done THEN
...
SELECT ... INTO otherid FROM ...;
...
END IF;
UNTIL done END REPEAT;
CLOSE someids;
END

The inner select can sometimes return nothing. That seems to trigger the not found handler and set done = 1, which ends my cursor loop. This is not what I wanted. I was able to get around it by adding a different (empty) handler inside the loop, like this:

...
BEGIN
DECLARE someid INTEGER;
DECLARE otherid INTEGER;
DECLARE done INTEGER DEFAULT 0;
DECLARE someids CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN someids;
REPEAT
FETCH someids INTO someid;
IF NOT done THEN
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
...
SELECT ... INTO otherid FROM ...;
...
END;
END IF;
UNTIL done END REPEAT;
CLOSE someids;
END


Add your own comment.