(mysql.info) declare-handlers

Info Catalog

(mysql.info) declare-conditions

(mysql.info) conditions-and-handlers

 
 17.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 
 begin-end).
 
 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  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;
 

Info Catalog

(mysql.info) declare-conditions

(mysql.info) conditions-and-handlers