(mysql.info.gz) DECLARE Handlers

Info Catalog

(mysql.info.gz) DECLARE Conditions

(mysql.info.gz) Conditions and Handlers

 
 19.1.7.2 `DECLARE' Handlers
 ...........................
 
      DECLARE HANDLER_TYPE HANDLER FOR CONDITION_VALUE[,...] SP_STATEMENT
 
      HANDLER_TYPE:
          CONTINUE
        | EXIT
        | UNDO
 
      CONDITION_VALUE:
          SQLSTATE [VALUE] SQLSTATE_VALUE
        | CONDITION_NAME
        | SQLWARNING
        | NOT FOUND
        | SQLEXCEPTION
        | MYSQL_ERROR_CODE
 
 This statement specifies handlers that each may deal with one or more
 conditions. If one of these conditions occurs, the specified statement
 is executed.
 
 For a `CONTINUE' handler, execution of the current routine continues
 after execution of the handler statement.  For an `EXIT' handler,
 execution of the current `BEGIN...END' compound statement is terminated.
 The `UNDO' handler type statement is not yet supported.
 
    * `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'.
 
 In addition to SQLSTATE values, MySQL error codes are also supported.
 
 For 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)
 
 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 (`EXIT') path after the second `INSERT' failed due to the
 `PRIMARY KEY' constraint, and `SELECT @x' would have returned `2'.
 

Info Catalog

(mysql.info.gz) DECLARE Conditions

(mysql.info.gz) Conditions and Handlers