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;
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...