Condition Handling
1147
statement such as
SET var_name = value
, or a compound statement written using
BEGIN
and
END
(see
Section 13.6.1, “
BEGIN ... END
Compound-Statement Syntax”
).
Handler declarations must appear after variable or condition declarations.
The
handler_action
value indicates what action the handler takes after execution of the handler
statement:
•
CONTINUE
: Execution of the current program continues.
•
EXIT
: 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.
•
UNDO
: Not supported.
The
condition_value
for
DECLARE ... HANDLER
indicates the specific condition or class of
conditions that activates the handler:
• A MySQL error code (a number) or an SQLSTATE value (a 5-character string literal). You should
not use MySQL error code 0 or SQLSTATE values that begin with
'00'
, because those indicate
success rather than an error condition. For a list of MySQL error codes and SQLSTATE values, see
Section C.3, “Server Error Codes and Messages”
.
• A condition name previously specified with
DECLARE ... CONDITION
. A condition name can be
associated with a MySQL error code or SQLSTATE value. See
Section 13.6.7.1, “
DECLARE ...
CONDITION
Syntax”
.
•
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
within 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).
For an example, see
Section 13.6.6, “Cursors”
. This condition also occurs for
SELECT ... INTO
var_list
statements that retrieve no rows.
•
SQLEXCEPTION
is shorthand for the class of SQLSTATE values that do not begin with
'00'
,
'01'
,
or
'02'
.
If a condition occurs for which no handler has been declared, the action taken depends on the
condition class:
• For
SQLEXCEPTION
conditions, the stored program terminates at the statement that raised the
condition, as if there were an
EXIT
handler. If the program was called by another stored program,
the calling program handles the condition using the handler selection rules applied to its own
handlers.
• For
SQLWARNING
or
NOT FOUND
conditions, the program continues executing, as if there were a
CONTINUE
handler.
The following example uses a handler for
SQLSTATE '23000'
, which occurs for a duplicate-key error:
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;
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
