DECLAREhandler_type
HANDLER FORcondition_value
[,condition_value
] ...statement
handler_type
: CONTINUE | EXIT | UNDOcondition_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
), or it can be a
compound statement written using var_name
=
value
BEGIN
and
END
(see Section 12.7.1, “BEGIN ... END
Compound Statement Syntax”).
For a CONTINUE
handler, execution of the
current program 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 supported.
If a condition occurs for which no handler has been declared,
the default action is EXIT
.
A condition_value
for
DECLARE ... HANDLER
can be any of the
following values:
An SQLSTATE value (a 5-character string literal) or a MySQL
error code (a number). You should not use SQLSTATE value
'00000'
or MySQL error code 0, because
those indicate sucess rather than an error condition. For a
list of SQLSTATE values and MySQL error codes, see
Section B.3, “Server Error Codes and Messages”.
A condition name previously specified with DECLARE
... CONDITION
. See
Section 12.7.4.1, “DECLARE
for Conditions”.
SQLWARNING
is shorthand for the class of
SQLSTATE values that begin with '01'
.
NOT FOUND
is shorthand for the class of
SQLSTATE values that begin with '02'
.
This is relevant only the context of cursors and is used to
control what happens when a cursor reaches the end of a data
set. If no more rows are available, a No Data condition
occurs with SQLSTATE value 02000. To detect this condition,
you can set up a handler for it (or for a NOT
FOUND
condition). An example is shown in
Section 12.7.5, “Cursors”. This condition also occurs for
SELECT ... INTO
statements
that retrieve no rows.
var_list
SQLEXCEPTION
is shorthand for the class
of SQLSTATE values that do not begin with
'00'
, '01'
, or
'02'
.
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 value
'23000'
, which occurs for a duplicate-key
error. Notice that @x
is 3
after the procedure executes, which shows that execution
continued to the end of the procedure. If the DECLARE
... HANDLER
statement 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 these strategies:
To leave the block, use an EXIT
handler:
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
To iterate, 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
Add your own comment.