(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
automatically generated byinfo2html