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