Menu:
condition-handling:: Condition Handling
This section describes the syntax for the note 'BEGIN ... END': begin-end. compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events. These objects are defined in terms of SQL code that is stored on the server for later invocation (see note stored-objects::).
A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests.
File: manual.info.tmp, Node: begin-end, Next: statement-labels, Prev: sql-compound-statements, Up: sql-compound-statements
[BEGIN_LABEL:] BEGIN
[STATEMENT_LIST]
END [END_LABEL]*note 'BEGIN ... END': begin-end. syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). A compound statement can contain multiple statements, enclosed by the 'BEGIN' and 'END' keywords. STATEMENT_LIST represents a list of one or more statements, each terminated by a semicolon (';') statement delimiter. The STATEMENT_LIST itself is optional, so the empty compound statement ('BEGIN END') is legal.
*note 'BEGIN ... END': begin-end. blocks can be nested.
Use of multiple statements requires that a client is able to send statement strings containing the ';' statement delimiter. In the note 'mysql': mysql. command-line client, this is handled with the 'delimiter' command. Changing the ';' end-of-statement delimiter (for example, to '//') permit ';' to be used in a program body. For an example, see note stored-programs-defining::.
A note 'BEGIN ... END': begin-end. block can be labeled. See note statement-labels::.
The optional '[NOT] ATOMIC' clause is not supported. This means that no transactional savepoint is set at the start of the instruction block and the 'BEGIN' clause used in this context has no effect on the current transaction.
Note:
Within all stored programs, the parser treats note 'BEGIN [WORK]': commit. as the beginning of a note 'BEGIN ... END': begin-end. block. To begin a transaction in this context, use *note 'START TRANSACTION': commit. instead.
File: manual.info.tmp, Node: statement-labels, Next: declare, Prev: begin-end, Up: sql-compound-statements
[BEGIN_LABEL:] BEGIN
[STATEMENT_LIST]
END [END_LABEL]
[BEGIN_LABEL:] LOOP
STATEMENT_LIST
END LOOP [END_LABEL]
[BEGIN_LABEL:] REPEAT
STATEMENT_LIST
UNTIL SEARCH_CONDITION
END REPEAT [END_LABEL]
[BEGIN_LABEL:] WHILE SEARCH_CONDITION DO
STATEMENT_LIST
END WHILE [END_LABEL]Labels are permitted for note 'BEGIN ... END': begin-end. blocks and for the note 'LOOP': loop, note 'REPEAT': repeat, and note 'WHILE': while. statements. Label use for those statements follows these rules:
BEGIN_LABEL must be followed by a colon.
BEGIN_LABEL can be given without END_LABEL. If END_LABEL is present, it must be the same as BEGIN_LABEL.
END_LABEL cannot be given without BEGIN_LABEL.
Labels at the same nesting level must be distinct.
Labels can be up to 16 characters long.
To refer to a label within the labeled construct, use an note 'ITERATE': iterate. or note 'LEAVE': leave. statement. The following example uses those statements to continue iterating or terminate the loop:
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
END;The scope of a block label does not include the code for handlers declared within the block. For details, see *note declare-handler::.
File: manual.info.tmp, Node: declare, Next: stored-program-variables, Prev: statement-labels, Up: sql-compound-statements
The *note 'DECLARE': declare. statement is used to define various items local to a program:
Local variables. See *note stored-program-variables::.
Conditions and handlers. See *note condition-handling::.
Cursors. See *note cursors::.
note 'DECLARE': declare. is permitted only inside a note 'BEGIN ... END': begin-end. compound statement and must be at its start, before any other statements.
Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.
File: manual.info.tmp, Node: stored-program-variables, Next: flow-control-statements, Prev: declare, Up: sql-compound-statements
Menu:
local-variable-scope:: Local Variable Scope and Resolution
System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context. In addition, stored programs can use 'DECLARE' to define local variables, and stored routines (procedures and functions) can be declared to take parameters that communicate values between the routine and its caller.
To declare local variables, use the note 'DECLARE': declare-local-variable. statement, as described in note declare-local-variable::.
Variables can be set directly with the note 'SET': set-variable. statement. See note set-variable::.
Results from queries can be retrieved into local variables using note 'SELECT ... INTO VAR_LIST': select-into. or by opening a cursor and using note 'FETCH ... INTO VAR_LIST': fetch. See note select-into::, and note cursors::.
For information about the scope of local variables and how MySQL resolves ambiguous names, see *note local-variable-scope::.
It is not permitted to assign the value 'DEFAULT' to stored procedure or function parameters or stored program local variables (for example with a 'SET VAR_NAME = DEFAULT' statement). In MySQL 5.7, this results in a syntax error.
File: manual.info.tmp, Node: declare-local-variable, Next: local-variable-scope, Prev: stored-program-variables, Up: stored-program-variables
13.6.4.1 Local Variable DECLARE Statement .........................................
DECLARE VAR_NAME [, VAR_NAME] ... TYPE [DEFAULT VALUE]This statement declares local variables within stored programs. To provide a default value for a variable, include a 'DEFAULT' clause. The value can be specified as an expression; it need not be a constant. If the 'DEFAULT' clause is missing, the initial value is 'NULL'.
Local variables are treated like stored routine parameters with respect to data type and overflow checking. See *note create-procedure::.
Variable declarations must appear before cursor or handler declarations.
Local variable names are not case-sensitive. Permissible characters and quoting rules are the same as for other identifiers, as described in *note identifiers::.
The scope of a local variable is the *note 'BEGIN ... END': begin-end. block within which it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.
For examples of variable declarations, see *note local-variable-scope::.
File: manual.info.tmp, Node: local-variable-scope, Prev: declare-local-variable, Up: stored-program-variables
13.6.4.2 Local Variable Scope and Resolution ............................................
The scope of a local variable is the *note 'BEGIN ... END': begin-end. block within which it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.
Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example, 'SELECT ... INTO LOCAL_VAR' cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters. See *note prepare::.
A local variable should not have the same name as a table column. If an SQL statement, such as a *note 'SELECT ... INTO': select. statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. Consider the following procedure definition:
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
SELECT xname, id INTO newname, xid
FROM table1 WHERE xname = xname;
SELECT newname;
END;MySQL interprets 'xname' in the *note 'SELECT': select. statement as a reference to the 'xname' variable rather than the 'xname' column. Consequently, when the procedure 'sp1()'is called, the 'newname' variable returns the value ''bob'' regardless of the value of the 'table1.xname' column.
Similarly, the cursor definition in the following procedure contains a *note 'SELECT': select. statement that refers to 'xname'. MySQL interprets this as a reference to the variable of that name rather than a column reference.
CREATE PROCEDURE sp2 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
DECLARE done TINYINT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH FROM cur1 INTO newname, xid;
IF done THEN LEAVE read_loop; END IF;
SELECT newname;
END LOOP;
CLOSE cur1;
END;See also *note stored-program-restrictions::.
File: manual.info.tmp, Node: flow-control-statements, Next: cursors, Prev: stored-program-variables, Up: sql-compound-statements
Menu:
while:: WHILE Statement
MySQL supports the note 'IF': if, note 'CASE': case, note 'ITERATE': iterate, note 'LEAVE': leave. note 'LOOP': loop, note 'WHILE': while, and note 'REPEAT': repeat. constructs for flow control within stored programs. It also supports note 'RETURN': return. within stored functions.
Many of these constructs contain other statements, as indicated by the grammar specifications in the following sections. Such constructs may be nested. For example, an note 'IF': if. statement might contain a note 'WHILE': while. loop, which itself contains a *note 'CASE': case. statement.
MySQL does not support 'FOR' loops.
File: manual.info.tmp, Node: case, Next: if, Prev: flow-control-statements, Up: flow-control-statements
13.6.5.1 CASE Statement .......................
CASE CASE_VALUE
WHEN WHEN_VALUE THEN STATEMENT_LIST
[WHEN WHEN_VALUE THEN STATEMENT_LIST] ...
[ELSE STATEMENT_LIST]
END CASEOr:
CASE
WHEN SEARCH_CONDITION THEN STATEMENT_LIST
[WHEN SEARCH_CONDITION THEN STATEMENT_LIST] ...
[ELSE STATEMENT_LIST]
END CASEThe *note 'CASE': case. statement for stored programs implements a complex conditional construct.
Note:
There is also a 'CASE' operator, which differs from the note 'CASE': case. statement described here. See note flow-control-functions::. The *note 'CASE': case. statement cannot have an 'ELSE NULL' clause, and it is terminated with 'END CASE' instead of 'END'.
For the first syntax, CASE_VALUE is an expression. This value is compared to the WHEN_VALUE expression in each 'WHEN' clause until one of them is equal. When an equal WHEN_VALUE is found, the corresponding 'THEN' clause STATEMENT_LIST executes. If no WHEN_VALUE is equal, the 'ELSE' clause STATEMENT_LIST executes, if there is one.
This syntax cannot be used to test for equality with 'NULL' because 'NULL = NULL' is false. See *note working-with-null::.
For the second syntax, each 'WHEN' clause SEARCH_CONDITION expression is evaluated until one is true, at which point its corresponding 'THEN' clause STATEMENT_LIST executes. If no SEARCH_CONDITION is equal, the 'ELSE' clause STATEMENT_LIST executes, if there is one.
If no WHEN_VALUE or SEARCH_CONDITION matches the value tested and the *note 'CASE': case. statement contains no 'ELSE' clause, a 'Case not found for CASE statement' error results.
Each STATEMENT_LIST consists of one or more SQL statements; an empty STATEMENT_LIST is not permitted.
To handle situations where no value is matched by any 'WHEN' clause, use an 'ELSE' containing an empty *note 'BEGIN ... END': begin-end. block, as shown in this example. (The indentation used here in the 'ELSE' clause is for purposes of clarity only, and is not otherwise significant.)
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END;
|File: manual.info.tmp, Node: if, Next: iterate, Prev: case, Up: flow-control-statements
13.6.5.2 IF Statement .....................
IF SEARCH_CONDITION THEN STATEMENT_LIST
[ELSEIF SEARCH_CONDITION THEN STATEMENT_LIST] ...
[ELSE STATEMENT_LIST]
END IFThe *note 'IF': if. statement for stored programs implements a basic conditional construct.
Note:
There is also an 'IF()' function, which differs from the note 'IF': if. statement described here. See note flow-control-functions::. The *note 'IF': if. statement can have 'THEN', 'ELSE', and 'ELSEIF' clauses, and it is terminated with 'END IF'.
If a given SEARCH_CONDITION evaluates to true, the corresponding 'THEN' or 'ELSEIF' clause STATEMENT_LIST executes. If no SEARCH_CONDITION matches, the 'ELSE' clause STATEMENT_LIST executes.
Each STATEMENT_LIST consists of one or more SQL statements; an empty STATEMENT_LIST is not permitted.
An 'IF ... END IF' block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon, as shown in this example:
DELIMITER //
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE s VARCHAR(20);
IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF;
SET s = CONCAT(n, ' ', s, ' ', m);
RETURN s;
END //
DELIMITER ;As with other flow-control constructs, 'IF ... END IF' blocks may be nested within other flow-control constructs, including other note 'IF': if. statements. Each note 'IF': if. must be terminated by its own 'END IF' followed by a semicolon. You can use indentation to make nested flow-control blocks more easily readable by humans (although this is not required by MySQL), as shown here:
DELIMITER //
CREATE FUNCTION VerboseCompare (n INT, m INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE s VARCHAR(50);
IF n = m THEN SET s = 'equals';
ELSE
IF n > m THEN SET s = 'greater';
ELSE SET s = 'less';
END IF;
SET s = CONCAT('is ', s, ' than');
END IF;
SET s = CONCAT(n, ' ', s, ' ', m, '.');
RETURN s;
END //
DELIMITER ;In this example, the inner *note 'IF': if. is evaluated only if 'n' is not equal to 'm'.
File: manual.info.tmp, Node: iterate, Next: leave, Prev: if, Up: flow-control-statements
13.6.5.3 ITERATE Statement ..........................
ITERATE LABELnote 'ITERATE': iterate. can appear only within note 'LOOP': loop, note 'REPEAT': repeat, and note 'WHILE': while. statements. *note 'ITERATE': iterate. means 'start the loop again.'
For an example, see *note loop::.
File: manual.info.tmp, Node: leave, Next: loop, Prev: iterate, Up: flow-control-statements
13.6.5.4 LEAVE Statement ........................
LEAVE LABELThis statement is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block, *note 'LEAVE': leave. exits the program.
note 'LEAVE': leave. can be used within note 'BEGIN ... END': begin-end. or loop constructs (note 'LOOP': loop, note 'REPEAT': repeat, *note 'WHILE': while.).
For an example, see *note loop::.
File: manual.info.tmp, Node: loop, Next: repeat, Prev: leave, Up: flow-control-statements
13.6.5.5 LOOP Statement .......................
[BEGIN_LABEL:] LOOP
STATEMENT_LIST
END LOOP [END_LABEL]note 'LOOP': loop. implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements, each terminated by a semicolon (';') statement delimiter. The statements within the loop are repeated until the loop is terminated. Usually, this is accomplished with a note 'LEAVE': leave. statement. Within a stored function, *note 'RETURN': return. can also be used, which exits the function entirely.
Neglecting to include a loop-termination statement results in an infinite loop.
A note 'LOOP': loop. statement can be labeled. For the rules regarding label use, see note statement-labels::.
Example:
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END;File: manual.info.tmp, Node: repeat, Next: return, Prev: loop, Up: flow-control-statements
13.6.5.6 REPEAT Statement .........................
[BEGIN_LABEL:] REPEAT
STATEMENT_LIST
UNTIL SEARCH_CONDITION
END REPEAT [END_LABEL]The statement list within a note 'REPEAT': repeat. statement is repeated until the SEARCH_CONDITION expression is true. Thus, a note 'REPEAT': repeat. always enters the loop at least once. STATEMENT_LIST consists of one or more statements, each terminated by a semicolon (';') statement delimiter.
A note 'REPEAT': repeat. statement can be labeled. For the rules regarding label use, see note statement-labels::.
Example:
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT
SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
END
//
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)File: manual.info.tmp, Node: return, Next: while, Prev: repeat, Up: flow-control-statements
13.6.5.7 RETURN Statement .........................
RETURN EXPRThe note 'RETURN': return. statement terminates execution of a stored function and returns the value EXPR to the function caller. There must be at least one note 'RETURN': return. statement in a stored function. There may be more than one if the function has multiple exit points.
This statement is not used in stored procedures, triggers, or events. The *note 'LEAVE': leave. statement can be used to exit a stored program of those types.
File: manual.info.tmp, Node: while, Prev: return, Up: flow-control-statements
13.6.5.8 WHILE Statement ........................
[BEGIN_LABEL:] WHILE SEARCH_CONDITION DO
STATEMENT_LIST
END WHILE [END_LABEL]The statement list within a *note 'WHILE': while. statement is repeated as long as the SEARCH_CONDITION expression is true. STATEMENT_LIST consists of one or more SQL statements, each terminated by a semicolon (';') statement delimiter.
A note 'WHILE': while. statement can be labeled. For the rules regarding label use, see note statement-labels::.
Example:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END;File: manual.info.tmp, Node: cursors, Next: condition-handling, Prev: flow-control-statements, Up: sql-compound-statements
Menu:
cursor-restrictions:: Restrictions on Server-Side Cursors
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows
Cursor declarations must appear before handler declarations and after variable and condition declarations.
Example:
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;File: manual.info.tmp, Node: close, Next: declare-cursor, Prev: cursors, Up: cursors
13.6.6.1 Cursor CLOSE Statement ...............................
CLOSE CURSOR_NAMEThis statement closes a previously opened cursor. For an example, see *note cursors::.
An error occurs if the cursor is not open.
If not closed explicitly, a cursor is closed at the end of the *note 'BEGIN ... END': begin-end. block in which it was declared.
File: manual.info.tmp, Node: declare-cursor, Next: fetch, Prev: close, Up: cursors
13.6.6.2 Cursor DECLARE Statement .................................
DECLARE CURSOR_NAME CURSOR FOR SELECT_STATEMENTThis statement declares a cursor and associates it with a note 'SELECT': select. statement that retrieves the rows to be traversed by the cursor. To fetch the rows later, use a note 'FETCH': fetch. statement. The number of columns retrieved by the note 'SELECT': select. statement must match the number of output variables specified in the note 'FETCH': fetch. statement.
The *note 'SELECT': select. statement cannot have an 'INTO' clause.
Cursor declarations must appear before handler declarations and after variable and condition declarations.
A stored program may contain multiple cursor declarations, but each cursor declared in a given block must have a unique name. For an example, see *note cursors::.
For information available through *note 'SHOW': show. statements, it is possible in many cases to obtain equivalent information by using a cursor with an 'INFORMATION_SCHEMA' table.
File: manual.info.tmp, Node: fetch, Next: open, Prev: declare-cursor, Up: cursors
13.6.6.3 Cursor FETCH Statement ...............................
FETCH [[NEXT] FROM] CURSOR_NAME INTO VAR_NAME [, VAR_NAME] ...This statement fetches the next row for the note 'SELECT': select. statement associated with the specified cursor (which must be open), and advances the cursor pointer. If a row exists, the fetched columns are stored in the named variables. The number of columns retrieved by the note 'SELECT': select. statement must match the number of output variables specified in the *note 'FETCH': fetch. statement.
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 *note cursors::.
Be aware that another operation, such as a 'SELECT' or another 'FETCH', may also cause the handler to execute by raising the same condition. If it is necessary to distinguish which operation raised the condition, place the operation within its own *note 'BEGIN ... END': begin-end. block so that it can be associated with its own handler.
File: manual.info.tmp, Node: open, Next: cursor-restrictions, Prev: fetch, Up: cursors
13.6.6.4 Cursor OPEN Statement ..............................
OPEN CURSOR_NAMEThis statement opens a previously declared cursor. For an example, see *note cursors::.
File: manual.info.tmp, Node: cursor-restrictions, Prev: open, Up: cursors
13.6.6.5 Restrictions on Server-Side Cursors ............................................
Server-side cursors are implemented in the C API using the 'mysql_stmt_attr_set()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-stmt-attr-set.html) function. The same implementation is used for cursors in stored routines. A server-side cursor enables a result set to be generated on the server side, but not transferred to the client except for those rows that the client requests. For example, if a client executes a query but is only interested in the first row, the remaining rows are not transferred.
In MySQL, a server-side cursor is materialized into an internal temporary table. Initially, this is a 'MEMORY' table, but is converted to a 'MyISAM' table when its size exceeds the minimum value of the 'max_heap_table_size' and 'tmp_table_size' system variables. The same restrictions apply to internal temporary tables created to hold the result set for a cursor as for other uses of internal temporary tables. See *note internal-temporary-tables::. One limitation of the implementation is that for a large result set, retrieving its rows through a cursor might be slow.
Cursors are read only; you cannot use a cursor to update rows.
'UPDATE WHERE CURRENT OF' and 'DELETE WHERE CURRENT OF' are not implemented, because updatable cursors are not supported.
Cursors are nonholdable (not held open after a commit).
Cursors are asensitive.
Cursors are nonscrollable.
Cursors are not named. The statement handler acts as the cursor ID.
You can have open only a single cursor per prepared statement. If you need several cursors, you must prepare several statements.
You cannot use a cursor for a statement that generates a result set if the statement is not supported in prepared mode. This includes statements such as note 'CHECK TABLE': check-table, 'HANDLER READ', and note 'SHOW BINLOG EVENTS': show-binlog-events.
File: manual.info.tmp, Node: condition-handling, Prev: cursors, Up: sql-compound-statements
Menu:
condition-handling-restrictions:: Restrictions on Condition Handling
Conditions may arise during stored program execution that require special handling, such as exiting the current program block or continuing execution. Handlers can be defined for general conditions such as warnings or exceptions, or for specific conditions such as a particular error code. Specific conditions can be assigned names and referred to that way in handlers.
To name a condition, use the note 'DECLARE ... CONDITION': declare-condition. statement. To declare a handler, use the note 'DECLARE ... HANDLER': declare-handler. statement. See note declare-condition::, and note declare-handler::. For information about how the server chooses handlers when a condition occurs, see *note handler-scope::.
To raise a condition, use the note 'SIGNAL': signal. statement. To modify condition information within a condition handler, use note 'RESIGNAL': resignal. See note declare-condition::, and note declare-handler::.
To retrieve information from the diagnostics area, use the note 'GET DIAGNOSTICS': get-diagnostics. statement (see note get-diagnostics::). For information about the diagnostics area, see *note diagnostics-area::.
File: manual.info.tmp, Node: declare-condition, Next: declare-handler, Prev: condition-handling, Up: condition-handling
13.6.7.1 DECLARE ... CONDITION Statement ........................................
DECLARE CONDITION_NAME CONDITION FOR CONDITION_VALUE
CONDITION_VALUE: {
MYSQL_ERROR_CODE
| SQLSTATE [VALUE] SQLSTATE_VALUE
}The note 'DECLARE ... CONDITION': declare-condition. statement declares a named error condition, associating a name with a condition that needs specific handling. The name can be referred to in a subsequent note 'DECLARE ... HANDLER': declare-handler. statement (see *note declare-handler::).
Condition declarations must appear before cursor or handler declarations.
The CONDITION_VALUE for *note 'DECLARE ... CONDITION': declare-condition. indicates the specific condition or class of conditions to associate with the condition name. It can take the following forms:
MYSQL_ERROR_CODE: An integer literal indicating a MySQL error code.
Do not use MySQL error code 0 because that indicates success rather than an error condition. For a list of MySQL error codes, see Server Error Message Reference (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html).
SQLSTATE [VALUE] SQLSTATE_VALUE: A 5-character string literal indicating an SQLSTATE value.
Do not use SQLSTATE values that begin with ''00'' because those indicate success rather than an error condition. For a list of SQLSTATE values, see Server Error Message Reference (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html).
Condition names referred to in note 'SIGNAL': signal. or use note 'RESIGNAL': resignal. statements must be associated with SQLSTATE values, not MySQL error codes.
Using names for conditions can help make stored program code clearer. For example, this handler applies to attempts to drop a nonexistent table, but that is apparent only if you know that 1051 is the MySQL error code for 'unknown table':
DECLARE CONTINUE HANDLER FOR 1051
BEGIN
-- body of handler
END;By declaring a name for the condition, the purpose of the handler is more readily seen:
DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;Here is a named condition for the same condition, but based on the corresponding SQLSTATE value rather than the MySQL error code:
DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;File: manual.info.tmp, Node: declare-handler, Next: get-diagnostics, Prev: declare-condition, Up: condition-handling
13.6.7.2 DECLARE ... HANDLER Statement ......................................
DECLARE HANDLER_ACTION HANDLER
FOR CONDITION_VALUE [, CONDITION_VALUE] ...
STATEMENT
HANDLER_ACTION: {
CONTINUE
| EXIT
| UNDO
}
CONDITION_VALUE: {
MYSQL_ERROR_CODE
| SQLSTATE [VALUE] SQLSTATE_VALUE
| CONDITION_NAME
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}The note 'DECLARE ... HANDLER': declare-handler. statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified STATEMENT executes. STATEMENT can be a simple statement such as 'SET VAR_NAME = VALUE', or a compound statement written using 'BEGIN' and 'END' (see note begin-end::).
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 *note 'BEGIN ... END': 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 *note 'DECLARE ... HANDLER': declare-handler. indicates the specific condition or class of conditions that activates the handler. It can take the following forms:
MYSQL_ERROR_CODE: An integer literal indicating a MySQL error code, such as 1051 to specify 'unknown table':
DECLARE CONTINUE HANDLER FOR 1051
BEGIN
-- body of handler
END;Do not use MySQL error code 0 because that indicates success rather than an error condition. For a list of MySQL error codes, see Server Error Message Reference (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html).
SQLSTATE [VALUE] SQLSTATE_VALUE: A 5-character string literal indicating an SQLSTATE value, such as ''42S01'' to specify 'unknown table':
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
BEGIN
-- body of handler
END;Do not use SQLSTATE values that begin with ''00'' because those indicate success rather than an error condition. For a list of SQLSTATE values, see Server Error Message Reference (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html).
CONDITION_NAME: A condition name previously specified with note 'DECLARE ... CONDITION': declare-condition. A condition name can be associated with a MySQL error code or SQLSTATE value. See note declare-condition::.
'SQLWARNING': Shorthand for the class of SQLSTATE values that begin with ''01''.
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
-- body of handler
END;'NOT FOUND': 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.
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
-- body of handler
END;For another example, see *note cursors::. The 'NOT FOUND' condition also occurs for 'SELECT ... INTO VAR_LIST' statements that retrieve no rows.
'SQLEXCEPTION': Shorthand for the class of SQLSTATE values that do not begin with ''00'', ''01'', or ''02''.
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
-- body of handler
END;For information about how the server chooses handlers when a condition occurs, see *note handler-scope::.
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' conditions, the program continues executing, as if there were a 'CONTINUE' handler.
For 'NOT FOUND' conditions, if the condition was raised normally, the action is 'CONTINUE'. If it was raised by note 'SIGNAL': signal. or note 'RESIGNAL': resignal, the action is 'EXIT'.
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;
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' after the procedure executes, which shows that execution continued to the end of the procedure after the error occurred. If the note 'DECLARE ... HANDLER': declare-handler. statement had not been present, MySQL would have taken the default action ('EXIT') after the second note 'INSERT': insert. failed due to the 'PRIMARY KEY' constraint, and 'SELECT @x' would have returned '2'.
To ignore a condition, declare a 'CONTINUE' handler for it and associate it with an empty block. For example:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;The scope of a block label does not include the code for handlers declared within the block. Therefore, the statement associated with a handler cannot use note 'ITERATE': iterate. or note 'LEAVE': leave. to refer to labels for blocks that enclose the handler declaration. Consider the following example, where the *note 'REPEAT': 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;
IF i < 0 THEN
LEAVE retry; # legal
END IF;
SET i = i - 1;
END;
UNTIL FALSE END REPEAT;
END;The 'retry' label is in scope for the *note 'IF': 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: retryTo avoid references to outer labels in handlers, use one of these strategies:
To leave the block, use an 'EXIT' handler. If no block cleanup is required, the *note 'BEGIN ... END': begin-end. handler body can be empty:
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;Otherwise, put the cleanup statements in the handler body:
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
BLOCK CLEANUP STATEMENTS
END;To continue execution, set a status variable in a 'CONTINUE' 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;
IF done OR i < 0 THEN
LEAVE retry;
END IF;
SET i = i - 1;
END;
UNTIL FALSE END REPEAT;
END;File: manual.info.tmp, Node: get-diagnostics, Next: resignal, Prev: declare-handler, Up: condition-handling
13.6.7.3 GET DIAGNOSTICS Statement ..................................
GET [CURRENT | STACKED] DIAGNOSTICS {
STATEMENT_INFORMATION_ITEM
[, STATEMENT_INFORMATION_ITEM] ...
| CONDITION CONDITION_NUMBER
CONDITION_INFORMATION_ITEM
[, CONDITION_INFORMATION_ITEM] ...
}
STATEMENT_INFORMATION_ITEM:
TARGET = STATEMENT_INFORMATION_ITEM_NAME
CONDITION_INFORMATION_ITEM:
TARGET = CONDITION_INFORMATION_ITEM_NAME
STATEMENT_INFORMATION_ITEM_NAME: {
NUMBER
| ROW_COUNT
}
CONDITION_INFORMATION_ITEM_NAME: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| RETURNED_SQLSTATE
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}
CONDITION_NUMBER, TARGET:
(see following discussion)SQL statements produce diagnostic information that populates the diagnostics area. The note 'GET DIAGNOSTICS': get-diagnostics. statement enables applications to inspect this information. (You can also use note 'SHOW WARNINGS': show-warnings. or *note 'SHOW ERRORS': show-errors. to see conditions or errors.)
No special privileges are required to execute *note 'GET DIAGNOSTICS': get-diagnostics.
The keyword 'CURRENT' means to retrieve information from the current diagnostics area. The keyword 'STACKED' means to retrieve information from the second diagnostics area, which is available only if the current context is a condition handler. If neither keyword is given, the default is to use the current diagnostics area.
The note 'GET DIAGNOSTICS': get-diagnostics. statement is typically used in a handler within a stored program. It is a MySQL extension that note 'GET [CURRENT] DIAGNOSTICS': get-diagnostics. is permitted outside handler context to check the execution of any SQL statement. For example, if you invoke the *note 'mysql': mysql. client program, you can enter these statements at the prompt:
mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1 | @p2 |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_such_table' |
+-------+------------------------------------+This extension applies only to the current diagnostics area. It does not apply to the second diagnostics area because 'GET STACKED DIAGNOSTICS' is permitted only if the current context is a condition handler. If that is not the case, a 'GET STACKED DIAGNOSTICS when handler not active' error occurs.
For a description of the diagnostics area, see *note diagnostics-area::. Briefly, it contains two kinds of information:
Statement information, such as the number of conditions that occurred or the affected-rows count.
Condition information, such as the error code and message. If a statement raises multiple conditions, this part of the diagnostics area has a condition area for each one. If a statement raises no conditions, this part of the diagnostics area is empty.
For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:
Statement information:
row count
... other statement information items ...
Condition area list:
Condition area 1:
error code for condition 1
error message for condition 1
... other condition information items ...
Condition area 2:
error code for condition 2:
error message for condition 2
... other condition information items ...
Condition area 3:
error code for condition 3
error message for condition 3
... other condition information items ...*note 'GET DIAGNOSTICS': get-diagnostics. can obtain either statement or condition information, but not both in the same statement:
To obtain statement information, retrieve the desired statement items into target variables. This instance of *note 'GET DIAGNOSTICS': get-diagnostics. assigns the number of available conditions and the rows-affected count to the user variables '@p1' and '@p2':
GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;To obtain condition information, specify the condition number and retrieve the desired condition items into target variables. This instance of *note 'GET DIAGNOSTICS': get-diagnostics. assigns the SQLSTATE value and error message to the user variables '@p3' and '@p4':
GET DIAGNOSTICS CONDITION 1
@p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;The retrieval list specifies one or more 'TARGET = ITEM_NAME' assignments, separated by commas. Each assignment names a target variable and either a STATEMENT_INFORMATION_ITEM_NAME or CONDITION_INFORMATION_ITEM_NAME designator, depending on whether the statement retrieves statement or condition information.
Valid TARGET designators for storing item information can be stored procedure or function parameters, stored program local variables declared with *note 'DECLARE': declare, or user-defined variables.
Valid CONDITION_NUMBER designators can be stored procedure or function parameters, stored program local variables declared with *note 'DECLARE': declare, user-defined variables, system variables, or literals. A character literal may include a _CHARSET introducer. A warning occurs if the condition number is not in the range from 1 to the number of condition areas that have information. In this case, the warning is added to the diagnostics area without clearing it.
When a condition occurs, MySQL does not populate all condition items recognized by *note 'GET DIAGNOSTICS': get-diagnostics. For example:
mysql> GET DIAGNOSTICS CONDITION 1
@p5 = SCHEMA_NAME, @p6 = TABLE_NAME;
mysql> SELECT @p5, @p6;
+------+------+
| @p5 | @p6 |
+------+------+
| | |
+------+------+In standard SQL, if there are multiple conditions, the first condition relates to the 'SQLSTATE' value returned for the previous SQL statement. In MySQL, this is not guaranteed. To get the main error, you cannot do this:
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;Instead, retrieve the condition count first, then use it to specify which condition number to inspect:
GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;For information about permissible statement and condition information items, and which ones are populated when a condition occurs, see *note diagnostics-area-information-items::.
Here is an example that uses note 'GET DIAGNOSTICS': get-diagnostics. and an exception handler in stored procedure context to assess the outcome of an insert operation. If the insert was successful, the procedure uses note 'GET DIAGNOSTICS': get-diagnostics. to get the rows-affected count. This shows that you can use *note 'GET DIAGNOSTICS': get-diagnostics. multiple times to retrieve information about a statement as long as the current diagnostics area has not been cleared.
CREATE PROCEDURE do_insert(value INT)
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE nrows INT;
DECLARE result TEXT;
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;
-- Perform the insert
INSERT INTO t1 (int_col) VALUES(value);
-- Check whether the insert was successful
IF code = '00000' THEN
GET DIAGNOSTICS nrows = ROW_COUNT;
SET result = CONCAT('insert succeeded, row count = ',nrows);
ELSE
SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
END IF;
-- Say what happened
SELECT result;
END;Suppose that 't1.int_col' is an integer column that is declared as 'NOT NULL'. The procedure produces these results when invoked to insert non-'NULL' and 'NULL' values, respectively:
mysql> CALL do_insert(1);
+---------------------------------+
| result |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+
mysql> CALL do_insert(NULL);
+-------------------------------------------------------------------------+
| result |
+-------------------------------------------------------------------------+
| insert failed, error = 23000, message = Column 'int_col' cannot be null |
+-------------------------------------------------------------------------+When a condition handler activates, a push to the diagnostics area stack occurs:
The first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it.
note 'GET [CURRENT] DIAGNOSTICS': get-diagnostics. and note 'GET STACKED DIAGNOSTICS': get-diagnostics. can be used within the handler to access the contents of the current and stacked diagnostics areas.
Initially, both diagnostics areas return the same result, so it is possible to get information from the current diagnostics area about the condition that activated the handler, as long as you execute no statements within the handler that change its current diagnostics area.
However, statements executing within the handler can modify the current diagnostics area, clearing and setting its contents according to the normal rules (see *note diagnostics-area-populating::).
A more reliable way to obtain information about the handler-activating condition is to use the stacked diagnostics area, which cannot be modified by statements executing within the handler except note 'RESIGNAL': resignal. For information about when the current diagnostics area is set and cleared, see note diagnostics-area::.
The next example shows how 'GET STACKED DIAGNOSTICS' can be used within a handler to obtain information about the handled exception, even after the current diagnostics area has been modified by handler statements.
Within a stored procedure 'p()', we attempt to insert two values into a table that contains a 'TEXT NOT NULL' column. The first value is a non-'NULL' string and the second is 'NULL'. The column prohibits 'NULL' values, so the first insert succeeds but the second causes an exception. The procedure includes an exception handler that maps attempts to insert 'NULL' into inserts of the empty string:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS p;
delimiter //
CREATE PROCEDURE p ()
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Here the current DA is nonempty because no prior statements
-- executing within the handler have cleared it
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
-- Map attempted NULL insert to empty string insert
INSERT INTO t1 (c1) VALUES('');
-- Here the current DA should be empty (if the INSERT succeeded),
-- so check whether there are conditions before attempting to
-- obtain condition information
GET CURRENT DIAGNOSTICS errcount = NUMBER;
IF errcount = 0
THEN
SELECT 'mapped insert succeeded, current DA is empty' AS op;
ELSE
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA after mapped insert' AS op, errno, msg;
END IF ;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA after mapped insert' AS op, errno, msg;
END;
INSERT INTO t1 (c1) VALUES('string 1');
INSERT INTO t1 (c1) VALUES(NULL);
END;
//
delimiter ;
CALL p();
SELECT * FROM t1;When the handler activates, a copy of the current diagnostics area is pushed to the diagnostics area stack. The handler first displays the contents of the current and stacked diagnostics areas, which are both the same initially:
+---------------------------------+-------+----------------------------+
| op | errno | msg |
+---------------------------------+-------+----------------------------+
| current DA before mapped insert | 1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+
+---------------------------------+-------+----------------------------+
| op | errno | msg |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert | 1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+Statements executing after the *note 'GET DIAGNOSTICS': get-diagnostics. statements may reset the current diagnostics area. statements may reset the current diagnostics area. For example, the handler maps the 'NULL' insert to an empty-string insert and displays the result. The new insert succeeds and clears the current diagnostics area, but the stacked diagnostics area remains unchanged and still contains information about the condition that activated the handler:
+----------------------------------------------+
| op |
+----------------------------------------------+
| mapped insert succeeded, current DA is empty |
+----------------------------------------------+
+--------------------------------+-------+----------------------------+
| op | errno | msg |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert | 1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+When the condition handler ends, its current diagnostics area is popped from the stack and the stacked diagnostics area becomes the current diagnostics area in the stored procedure.
After the procedure returns, the table contains two rows. The empty row results from the attempt to insert 'NULL' that was mapped to an empty-string insert:
+----------+
| c1 |
+----------+
| string 1 |
| |
+----------+In the preceding example, the first two note 'GET DIAGNOSTICS': get-diagnostics. statements within the condition handler that retrieve information from the current and stacked diagnostics areas return the same values. This is not the case if statements that reset the current diagnostics area executed earlier within the handler. Suppose that 'p()' is rewritten to place the note 'DECLARE': declare. statements within the handler definition rather than preceding it:
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
...In this case, the result is version dependent:
Before MySQL 5.7.2, note 'DECLARE': declare. does not change the current diagnostics area, so the first two note 'GET DIAGNOSTICS': get-diagnostics. statements return the same result, just as in the original version of 'p()'.
In MySQL 5.7.2, work was done to ensure that all nondiagnostic statements populate the diagnostics area, per the SQL standard. note 'DECLARE': declare. is one of them, so in 5.7.2 and higher, note 'DECLARE': declare. statements executing at the beginning of the handler clear the current diagnostics area and the *note 'GET DIAGNOSTICS': get-diagnostics. statements produce different results:
+---------------------------------+-------+------+
| op | errno | msg |
+---------------------------------+-------+------+
| current DA before mapped insert | NULL | NULL |
+---------------------------------+-------+------+
+---------------------------------+-------+----------------------------+
| op | errno | msg |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert | 1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+To avoid this issue within a condition handler when seeking to obtain information about the condition that activated the handler, be sure to access the stacked diagnostics area, not the current diagnostics area.
File: manual.info.tmp, Node: resignal, Next: signal, Prev: get-diagnostics, Up: condition-handling
13.6.7.4 RESIGNAL Statement ...........................
RESIGNAL [CONDITION_VALUE]
[SET SIGNAL_INFORMATION_ITEM
[, SIGNAL_INFORMATION_ITEM] ...]
CONDITION_VALUE: {
SQLSTATE [VALUE] SQLSTATE_VALUE
| CONDITION_NAME
}
SIGNAL_INFORMATION_ITEM:
CONDITION_INFORMATION_ITEM_NAME = SIMPLE_VALUE_SPECIFICATION
CONDITION_INFORMATION_ITEM_NAME: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}
CONDITION_NAME, SIMPLE_VALUE_SPECIFICATION:
(see following discussion)note 'RESIGNAL': resignal. passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event. note 'RESIGNAL': resignal. may change some or all information before passing it on. note 'RESIGNAL': resignal. is related to note 'SIGNAL': signal, but instead of originating a condition as note 'SIGNAL': signal. does, note 'RESIGNAL': resignal. relays existing condition information, possibly after modifying it.
note 'RESIGNAL': resignal. makes it possible to both handle an error and return the error information. Otherwise, by executing an SQL statement within the handler, information that caused the handler's activation is destroyed. note 'RESIGNAL': resignal. also can make some procedures shorter if a given handler can handle part of a situation, then pass the condition 'up the line' to another handler.
No privileges are required to execute the *note 'RESIGNAL': resignal. statement.
All forms of note 'RESIGNAL': resignal. require that the current context be a condition handler. Otherwise, note 'RESIGNAL': resignal. is illegal and a 'RESIGNAL when handler not active' error occurs.
To retrieve information from the diagnostics area, use the note 'GET DIAGNOSTICS': get-diagnostics. statement (see note get-diagnostics::). For information about the diagnostics area, see *note diagnostics-area::.
*note resignal-overview::
*note resignal-alone::
*note resignal-with-new-signal::
*note resignal-with-condition::
*note resignal-handler::
RESIGNAL Overview
For CONDITION_VALUE and SIGNAL_INFORMATION_ITEM, the definitions and rules are the same for note 'RESIGNAL': resignal. as for note 'SIGNAL': signal. For example, the CONDITION_VALUE can be an 'SQLSTATE' value, and the value can indicate errors, warnings, or 'not found.' For additional information, see *note signal::.
The *note 'RESIGNAL': resignal. statement takes CONDITION_VALUE and 'SET' clauses, both of which are optional. This leads to several possible uses:
*note 'RESIGNAL': resignal. alone:
RESIGNAL;*note 'RESIGNAL': resignal. with new signal information:
RESIGNAL SET SIGNAL_INFORMATION_ITEM [, SIGNAL_INFORMATION_ITEM] ...;*note 'RESIGNAL': resignal. with a condition value and possibly new signal information:
RESIGNAL CONDITION_VALUE
[SET SIGNAL_INFORMATION_ITEM [, SIGNAL_INFORMATION_ITEM] ...];These use cases all cause changes to the diagnostics and condition areas:
A diagnostics area contains one or more condition areas.
A condition area contains condition information items, such as the 'SQLSTATE' value, 'MYSQL_ERRNO', or 'MESSAGE_TEXT'.
There is a stack of diagnostics areas. When a handler takes control, it pushes a diagnostics area to the top of the stack, so there are two diagnostics areas during handler execution:
The first (current) diagnostics area, which starts as a copy of the last diagnostics area, but is overwritten by the first statement in the handler that changes the current diagnostics area.
The last (stacked) diagnostics area, which has the condition areas that were set up before the handler took control.
The maximum number of condition areas in a diagnostics area is determined by the value of the 'max_error_count' system variable. See *note diagnostics-area-system-variables::.
RESIGNAL Alone
A simple *note 'RESIGNAL': resignal. alone means 'pass on the error with no change.' It restores the last diagnostics area and makes it the current diagnostics area. That is, it 'pops' the diagnostics area stack.
Within a condition handler that catches a condition, one use for *note 'RESIGNAL': resignal. alone is to perform some other actions, and then pass on without change the original condition information (the information that existed before entry into the handler).
Example:
DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET @error_count = @error_count + 1;
IF @a = 0 THEN RESIGNAL; END IF;
END;
DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();Suppose that the 'DROP TABLE xx' statement fails. The diagnostics area stack looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'Then execution enters the 'EXIT' handler. It starts by pushing a diagnostics area to the top of the stack, which now looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'
DA 2. ERROR 1051 (42S02): Unknown table 'xx'At this point, the contents of the first (current) and second (stacked) diagnostics areas are the same. The first diagnostics area may be modified by statements executing subsequently within the handler.
Usually a procedure statement clears the first diagnostics area. 'BEGIN' is an exception, it does not clear, it does nothing. 'SET' is not an exception, it clears, performs the operation, and produces a result of 'success.' The diagnostics area stack now looks like this:
DA 1. ERROR 0000 (00000): Successful operation
DA 2. ERROR 1051 (42S02): Unknown table 'xx'At this point, if '@a = 0', *note 'RESIGNAL': resignal. pops the diagnostics area stack, which now looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'And that is what the caller sees.
If '@a' is not 0, the handler simply ends, which means that there is no more use for the current diagnostics area (it has been 'handled'), so it can be thrown away, causing the stacked diagnostics area to become the current diagnostics area again. The diagnostics area stack looks like this:
DA 1. ERROR 0000 (00000): Successful operationThe details make it look complex, but the end result is quite useful: Handlers can execute without destroying information about the condition that caused activation of the handler.
RESIGNAL with New Signal Information
*note 'RESIGNAL': resignal. with a 'SET' clause provides new signal information, so the statement means 'pass on the error with changes':
RESIGNAL SET SIGNAL_INFORMATION_ITEM [, SIGNAL_INFORMATION_ITEM] ...;As with note 'RESIGNAL': resignal. alone, the idea is to pop the diagnostics area stack so that the original information goes out. Unlike note 'RESIGNAL': resignal. alone, anything specified in the 'SET' clause changes.
Example:
DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET @error_count = @error_count + 1;
IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
END;
DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();Remember from the previous discussion that *note 'RESIGNAL': resignal. alone results in a diagnostics area stack like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'The 'RESIGNAL SET MYSQL_ERRNO = 5' statement results in this stack instead, which is what the caller sees:
DA 1. ERROR 5 (42S02): Unknown table 'xx'In other words, it changes the error number, and nothing else.
The *note 'RESIGNAL': resignal. statement can change any or all of the signal information items, making the first condition area of the diagnostics area look quite different.
RESIGNAL with a Condition Value and Optional New Signal Information
*note 'RESIGNAL': resignal. with a condition value means 'push a condition into the current diagnostics area.' If the 'SET' clause is present, it also changes the error information.
RESIGNAL CONDITION_VALUE
[SET SIGNAL_INFORMATION_ITEM [, SIGNAL_INFORMATION_ITEM] ...];This form of note 'RESIGNAL': resignal. restores the last diagnostics area and makes it the current diagnostics area. That is, it 'pops' the diagnostics area stack, which is the same as what a simple note 'RESIGNAL': resignal. alone would do. However, it also changes the diagnostics area depending on the condition value or signal information.
Example:
DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET @error_count = @error_count + 1;
IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
END;
DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
SET @@max_error_count = 2;
CALL p();
SHOW ERRORS;This is similar to the previous example, and the effects are the same, except that if *note 'RESIGNAL': resignal. happens, the current condition area looks different at the end. (The reason the condition adds to rather than replaces the existing condition is the use of a condition value.)
The *note 'RESIGNAL': resignal. statement includes a condition value ('SQLSTATE '45000''), so it adds a new condition area, resulting in a diagnostics area stack that looks like this:
DA 1. (condition 2) ERROR 1051 (42S02): Unknown table 'xx'
(condition 1) ERROR 5 (45000) Unknown table 'xx'The result of note 'CALL p()': call. and note 'SHOW ERRORS': show-errors. for this example is:
mysql> CALL p();
ERROR 5 (45000): Unknown table 'xx'
mysql> SHOW ERRORS;
+-------+------+----------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------+
| Error | 1051 | Unknown table 'xx' |
| Error | 5 | Unknown table 'xx' |
+-------+------+----------------------------------+RESIGNAL Requires Condition Handler Context
All forms of note 'RESIGNAL': resignal. require that the current context be a condition handler. Otherwise, note 'RESIGNAL': resignal. is illegal and a 'RESIGNAL when handler not active' error occurs. For example:
mysql> CREATE PROCEDURE p () RESIGNAL;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p();
ERROR 1645 (0K000): RESIGNAL when handler not activeHere is a more difficult example:
delimiter //
CREATE FUNCTION f () RETURNS INT
BEGIN
RESIGNAL;
RETURN 5;
END//
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f();
SIGNAL SQLSTATE '55555';
END//
delimiter ;
CALL p();*note 'RESIGNAL': resignal. occurs within the stored function 'f()'. Although 'f()' itself is invoked within the context of the 'EXIT' handler, execution within 'f()' has its own context, which is not handler context. Thus, 'RESIGNAL' within 'f()' results in a 'handler not active' error.
File: manual.info.tmp, Node: signal, Next: handler-scope, Prev: resignal, Up: condition-handling
13.6.7.5 SIGNAL Statement .........................
SIGNAL CONDITION_VALUE
[SET SIGNAL_INFORMATION_ITEM
[, SIGNAL_INFORMATION_ITEM] ...]
CONDITION_VALUE: {
SQLSTATE [VALUE] SQLSTATE_VALUE
| CONDITION_NAME
}
SIGNAL_INFORMATION_ITEM:
CONDITION_INFORMATION_ITEM_NAME = SIMPLE_VALUE_SPECIFICATION
CONDITION_INFORMATION_ITEM_NAME: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}
CONDITION_NAME, SIMPLE_VALUE_SPECIFICATION:
(see following discussion)note 'SIGNAL': signal. is the way to 'return' an error. note 'SIGNAL': signal. provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, 'SQLSTATE' value, message). Without *note 'SIGNAL': signal, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error.
No privileges are required to execute the *note 'SIGNAL': signal. statement.
To retrieve information from the diagnostics area, use the note 'GET DIAGNOSTICS': get-diagnostics. statement (see note get-diagnostics::). For information about the diagnostics area, see *note diagnostics-area::.
*note signal-overview::
*note signal-condition-information-items::
*note signal-effects::
SIGNAL Overview
The CONDITION_VALUE in a note 'SIGNAL': signal. statement indicates the error value to be returned. It can be an 'SQLSTATE' value (a 5-character string literal) or a CONDITION_NAME that refers to a named condition previously defined with note 'DECLARE ... CONDITION': declare-condition. (see *note declare-condition::).
An 'SQLSTATE' value can indicate errors, warnings, or 'not found.' The first two characters of the value indicate its error class, as discussed in note signal-condition-information-items::. Some signal values cause statement termination; see note signal-effects::.
The 'SQLSTATE' value for a note 'SIGNAL': signal. statement should not start with ''00'' because such values indicate success and are not valid for signaling an error. This is true whether the 'SQLSTATE' value is specified directly in the note 'SIGNAL': signal. statement or in a named condition referred to in the statement. If the value is invalid, a 'Bad SQLSTATE' error occurs.
To signal a generic 'SQLSTATE' value, use ''45000'', which means 'unhandled user-defined exception.'
The *note 'SIGNAL': signal. statement optionally includes a 'SET' clause that contains multiple signal items, in a list of CONDITION_INFORMATION_ITEM_NAME = SIMPLE_VALUE_SPECIFICATION assignments, separated by commas.
Each CONDITION_INFORMATION_ITEM_NAME may be specified only once in the 'SET' clause. Otherwise, a 'Duplicate condition information item' error occurs.
Valid SIMPLE_VALUE_SPECIFICATION designators can be specified using stored procedure or function parameters, stored program local variables declared with *note 'DECLARE': declare, user-defined variables, system variables, or literals. A character literal may include a _CHARSET introducer.
For information about permissible CONDITION_INFORMATION_ITEM_NAME values, see *note signal-condition-information-items::.
The following procedure signals an error or warning depending on the value of 'pval', its input parameter:
CREATE PROCEDURE p (pval INT)
BEGIN
DECLARE specialty CONDITION FOR SQLSTATE '45000';
IF pval = 0 THEN
SIGNAL SQLSTATE '01000';
ELSEIF pval = 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred';
ELSEIF pval = 2 THEN
SIGNAL specialty
SET MESSAGE_TEXT = 'An error occurred';
ELSE
SIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
END IF;
END;If 'pval' is 0, 'p()' signals a warning because 'SQLSTATE' values that begin with ''01'' are signals in the warning class. The warning does not terminate the procedure, and can be seen with *note 'SHOW WARNINGS': show-warnings. after the procedure returns.
If 'pval' is 1, 'p()' signals an error and sets the 'MESSAGE_TEXT' condition information item. The error terminates the procedure, and the text is returned with the error information.
If 'pval' is 2, the same error is signaled, although the 'SQLSTATE' value is specified using a named condition in this case.
If 'pval' is anything else, 'p()' first signals a warning and sets the message text and error number condition information items. This warning does not terminate the procedure, so execution continues and 'p()' then signals an error. The error does terminate the procedure. The message text and error number set by the warning are replaced by the values set by the error, which are returned with the error information.
note 'SIGNAL': signal. is typically used within stored programs, but it is a MySQL extension that it is permitted outside handler context. For example, if you invoke the note 'mysql': mysql. client program, you can enter any of these statements at the prompt:
SIGNAL SQLSTATE '77777';
CREATE TRIGGER t_bi BEFORE INSERT ON t
FOR EACH ROW SIGNAL SQLSTATE '77777';
CREATE EVENT e ON SCHEDULE EVERY 1 SECOND
DO SIGNAL SQLSTATE '77777';*note 'SIGNAL': signal. executes according to the following rules:
If the *note 'SIGNAL': signal. statement indicates a particular 'SQLSTATE' value, that value is used to signal the condition specified. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012';
END IF;
END;If the note 'SIGNAL': signal. statement uses a named condition, the condition must be declared in some scope that applies to the note 'SIGNAL': signal. statement, and must be defined using an 'SQLSTATE' value, not a MySQL error number. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
IF divisor = 0 THEN
SIGNAL divide_by_zero;
END IF;
END;If the named condition does not exist in the scope of the *note 'SIGNAL': signal. statement, an 'Undefined CONDITION' error occurs.
If *note 'SIGNAL': signal. refers to a named condition that is defined with a MySQL error number rather than an 'SQLSTATE' value, a 'SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE' error occurs. The following statements cause that error because the named condition is associated with a MySQL error number:
DECLARE no_such_table CONDITION FOR 1051;
SIGNAL no_such_table;If a condition with a given name is declared multiple times in different scopes, the declaration with the most local scope applies. Consider the following procedure:
CREATE PROCEDURE p (divisor INT)
BEGIN
DECLARE my_error CONDITION FOR SQLSTATE '45000';
IF divisor = 0 THEN
BEGIN
DECLARE my_error CONDITION FOR SQLSTATE '22012';
SIGNAL my_error;
END;
END IF;
SIGNAL my_error;
END;If 'divisor' is 0, the first *note 'SIGNAL': signal. statement executes. The innermost 'my_error' condition declaration applies, raising 'SQLSTATE' ''22012''.
If 'divisor' is not 0, the second *note 'SIGNAL': signal. statement executes. The outermost 'my_error' condition declaration applies, raising 'SQLSTATE' ''45000''.
For information about how the server chooses handlers when a condition occurs, see *note handler-scope::.
Signals can be raised within exception handlers:
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SIGNAL SQLSTATE VALUE '99999'
SET MESSAGE_TEXT = 'An error occurred';
END;
DROP TABLE no_such_table;
END;'CALL p()' reaches the *note 'DROP TABLE': drop-table. statement. There is no table named 'no_such_table', so the error handler is activated. The error handler destroys the original error ('no such table') and makes a new error with 'SQLSTATE' ''99999'' and message 'An error occurred'.
Signal Condition Information Items
The following table lists the names of diagnostics area condition information items that can be set in a note 'SIGNAL': signal. (or note 'RESIGNAL': resignal.) statement. All items are standard SQL except 'MYSQL_ERRNO', which is a MySQL extension. For more information about these items see *note diagnostics-area::.
Item Name Definition
--------- ----------
CLASS_ORIGIN VARCHAR(64)
SUBCLASS_ORIGIN VARCHAR(64)
CONSTRAINT_CATALOG VARCHAR(64)
CONSTRAINT_SCHEMA VARCHAR(64)
CONSTRAINT_NAME VARCHAR(64)
CATALOG_NAME VARCHAR(64)
SCHEMA_NAME VARCHAR(64)
TABLE_NAME VARCHAR(64)
COLUMN_NAME VARCHAR(64)
CURSOR_NAME VARCHAR(64)
MESSAGE_TEXT VARCHAR(128)
MYSQL_ERRNO SMALLINT UNSIGNEDThe character set for character items is UTF-8.
It is illegal to assign 'NULL' to a condition information item in a *note 'SIGNAL': signal. statement.
A *note 'SIGNAL': signal. statement always specifies an 'SQLSTATE' value, either directly, or indirectly by referring to a named condition defined with an 'SQLSTATE' value. The first two characters of an 'SQLSTATE' value are its class, and the class determines the default value for the condition information items:
Class = ''00'' (success)
Illegal. 'SQLSTATE' values that begin with ''00'' indicate success and are not valid for *note 'SIGNAL': signal.
Class = ''01'' (warning)
MESSAGE_TEXT = 'Unhandled user-defined warning condition';
MYSQL_ERRNO = ER_SIGNAL_WARN (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_signal_warn)Class = ''02'' (not found)
MESSAGE_TEXT = 'Unhandled user-defined not found condition';
MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_signal_not_found)Class > ''02'' (exception)
MESSAGE_TEXT = 'Unhandled user-defined exception condition';
MYSQL_ERRNO = ER_SIGNAL_EXCEPTION (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_signal_exception)For legal classes, the other condition information items are set as follows:
CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
CURSOR_NAME = '';The error values that are accessible after note 'SIGNAL': signal. executes are the 'SQLSTATE' value raised by the note 'SIGNAL': signal. statement and the 'MESSAGE_TEXT' and 'MYSQL_ERRNO' items. These values are available from the C API:
'mysql_sqlstate()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-sqlstate.html) returns the 'SQLSTATE' value.
'mysql_errno()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-errno.html) returns the 'MYSQL_ERRNO' value.
'mysql_error()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-error.html) returns the 'MESSAGE_TEXT' value.
At the SQL level, the output from note 'SHOW WARNINGS': show-warnings. and note 'SHOW ERRORS': show-errors. indicates the 'MYSQL_ERRNO' and 'MESSAGE_TEXT' values in the 'Code' and 'Message' columns.
To retrieve information from the diagnostics area, use the note 'GET DIAGNOSTICS': get-diagnostics. statement (see note get-diagnostics::). For information about the diagnostics area, see *note diagnostics-area::.
Effect of Signals on Handlers, Cursors, and Statements
Signals have different effects on statement execution depending on the signal class. The class determines how severe an error is. MySQL ignores the value of the 'sql_mode' system variable; in particular, strict SQL mode does not matter. MySQL also ignores 'IGNORE': The intent of *note 'SIGNAL': signal. is to raise a user-generated error explicitly, so a signal is never ignored.
In the following descriptions, 'unhandled' means that no handler for the signaled 'SQLSTATE' value has been defined with *note 'DECLARE ... HANDLER': declare-handler.
Class = ''00'' (success)
Illegal. 'SQLSTATE' values that begin with ''00'' indicate success and are not valid for *note 'SIGNAL': signal.
Class = ''01'' (warning)
The value of the 'warning_count' system variable goes up. *note 'SHOW WARNINGS': show-warnings. shows the signal. 'SQLWARNING' handlers catch the signal.
Warnings cannot be returned from stored functions because the *note 'RETURN': return. statement that causes the function to return clears the diagnostic area. The statement thus clears any warnings that may have been present there (and resets 'warning_count' to 0).
Class = ''02'' (not found)
'NOT FOUND' handlers catch the signal. There is no effect on cursors. If the signal is unhandled in a stored function, statements end.
Class > ''02'' (exception)
'SQLEXCEPTION' handlers catch the signal. If the signal is unhandled in a stored function, statements end.
Class = ''40''
Treated as an ordinary exception.
File: manual.info.tmp, Node: handler-scope, Next: diagnostics-area, Prev: signal, Up: condition-handling
13.6.7.6 Scope Rules for Handlers .................................
A stored program may include handlers to be invoked when certain conditions occur within the program. The applicability of each handler depends on its location within the program definition and on the condition or conditions that it handles:
A handler declared in a *note 'BEGIN ... END': begin-end. block is in scope only for the SQL statements following the handler declarations in the block. If the handler itself raises a condition, it cannot handle that condition, nor can any other handlers declared in the block. In the following example, handlers 'H1' and 'H2' are in scope for conditions raised by statements STMT1 and STMT2. But neither 'H1' nor 'H2' are in scope for conditions raised in the body of 'H1' or 'H2'.
BEGIN -- outer block
DECLARE EXIT HANDLER FOR ...; -- handler H1
DECLARE EXIT HANDLER FOR ...; -- handler H2
STMT1;
STMT2;
END;A handler is in scope only for the block in which it is declared, and cannot be activated for conditions occurring outside that block. In the following example, handler 'H1' is in scope for STMT1 in the inner block, but not for STMT2 in the outer block:
BEGIN -- outer block
BEGIN -- inner block
DECLARE EXIT HANDLER FOR ...; -- handler H1
STMT1;
END;
STMT2;
END;A handler can be specific or general. A specific handler is for a MySQL error code, 'SQLSTATE' value, or condition name. A general handler is for a condition in the 'SQLWARNING', 'SQLEXCEPTION', or 'NOT FOUND' class. Condition specificity is related to condition precedence, as described later.
Multiple handlers can be declared in different scopes and with different specificities. For example, there might be a specific MySQL error code handler in an outer block, and a general 'SQLWARNING' handler in an inner block. Or there might be handlers for a specific MySQL error code and the general 'SQLWARNING' class in the same block.
Whether a handler is activated depends not only on its own scope and condition value, but on what other handlers are present. When a condition occurs in a stored program, the server searches for applicable handlers in the current scope (current *note 'BEGIN ... END': begin-end. block). If there are no applicable handlers, the search continues outward with the handlers in each successive containing scope (block). When the server finds one or more applicable handlers at a given scope, it chooses among them based on condition precedence:
A MySQL error code handler takes precedence over an 'SQLSTATE' value handler.
An 'SQLSTATE' value handler takes precedence over general 'SQLWARNING', 'SQLEXCEPTION', or 'NOT FOUND' handlers.
An 'SQLEXCEPTION' handler takes precedence over an 'SQLWARNING' handler.
It is possible to have several applicable handlers with the same precedence. For example, a statement could generate multiple warnings with different error codes, for each of which an error-specific handler exists. In this case, the choice of which handler the server activates is nondeterministic, and may change depending on the circumstances under which the condition occurs.
One implication of the handler selection rules is that if multiple applicable handlers occur in different scopes, handlers with the most local scope take precedence over handlers in outer scopes, even over those for more specific conditions.
If there is no appropriate handler when a condition occurs, the action taken depends on the class of the condition:
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' conditions, the program continues executing, as if there were a 'CONTINUE' handler.
For 'NOT FOUND' conditions, if the condition was raised normally, the action is 'CONTINUE'. If it was raised by note 'SIGNAL': signal. or note 'RESIGNAL': resignal, the action is 'EXIT'.
The following examples demonstrate how MySQL applies the handler selection rules.
This procedure contains two handlers, one for the specific 'SQLSTATE' value (''42S02'') that occurs for attempts to drop a nonexistent table, and one for the general 'SQLEXCEPTION' class:
CREATE PROCEDURE p1()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SELECT 'SQLSTATE handler was activated' AS msg;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SQLEXCEPTION handler was activated' AS msg;
DROP TABLE test.t;
END;Both handlers are declared in the same block and have the same scope. However, 'SQLSTATE' handlers take precedence over 'SQLEXCEPTION' handlers, so if the table 't' is nonexistent, the *note 'DROP TABLE': drop-table. statement raises a condition that activates the 'SQLSTATE' handler:
mysql> CALL p1();
+--------------------------------+
| msg |
+--------------------------------+
| SQLSTATE handler was activated |
+--------------------------------+This procedure contains the same two handlers. But this time, the *note 'DROP TABLE': drop-table. statement and 'SQLEXCEPTION' handler are in an inner block relative to the 'SQLSTATE' handler:
CREATE PROCEDURE p2()
BEGIN -- outer block
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SELECT 'SQLSTATE handler was activated' AS msg;
BEGIN -- inner block
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SQLEXCEPTION handler was activated' AS msg;
DROP TABLE test.t; -- occurs within inner block
END;
END;In this case, the handler that is more local to where the condition occurs takes precedence. The 'SQLEXCEPTION' handler activates, even though it is more general than the 'SQLSTATE' handler:
mysql> CALL p2();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+In this procedure, one of the handlers is declared in a block inner to the scope of the *note 'DROP TABLE': drop-table. statement:
CREATE PROCEDURE p3()
BEGIN -- outer block
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SQLEXCEPTION handler was activated' AS msg;
BEGIN -- inner block
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SELECT 'SQLSTATE handler was activated' AS msg;
END;
DROP TABLE test.t; -- occurs within outer block
END;Only the 'SQLEXCEPTION' handler applies because the other one is not in scope for the condition raised by the *note 'DROP TABLE': drop-table.:
mysql> CALL p3();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+In this procedure, both handlers are declared in a block inner to the scope of the *note 'DROP TABLE': drop-table. statement:
CREATE PROCEDURE p4()
BEGIN -- outer block
BEGIN -- inner block
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SQLEXCEPTION handler was activated' AS msg;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SELECT 'SQLSTATE handler was activated' AS msg;
END;
DROP TABLE test.t; -- occurs within outer block
END;Neither handler applies because they are not in scope for the *note 'DROP TABLE': drop-table. The condition raised by the statement goes unhandled and terminates the procedure with an error:
mysql> CALL p4();
ERROR 1051 (42S02): Unknown table 'test.t'File: manual.info.tmp, Node: diagnostics-area, Next: conditions-and-parameters, Prev: handler-scope, Up: condition-handling
13.6.7.7 The MySQL Diagnostics Area ...................................
SQL statements produce diagnostic information that populates the diagnostics area. Standard SQL has a diagnostics area stack, containing a diagnostics area for each nested execution context. Standard SQL also supports *note 'GET STACKED DIAGNOSTICS': get-diagnostics. syntax for referring to the second diagnostics area during condition handler execution. MySQL supports the 'STACKED' keyword as of MySQL 5.7. Before that, MySQL does not support 'STACKED'; there is a single diagnostics area containing information from the most recent statement that wrote to it.
The following discussion describes the structure of the diagnostics area in MySQL, the information items recognized by MySQL, how statements clear and set the diagnostics area, and how diagnostics areas are pushed to and popped from the stack.
*note diagnostics-area-structure::
*note diagnostics-area-information-items::
*note diagnostics-area-populating::
*note diagnostics-area-stack::
*note diagnostics-area-system-variables::
Diagnostics Area Structure
The diagnostics area contains two kinds of information:
Statement information, such as the number of conditions that occurred or the affected-rows count.
Condition information, such as the error code and message. If a statement raises multiple conditions, this part of the diagnostics area has a condition area for each one. If a statement raises no conditions, this part of the diagnostics area is empty.
For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:
Statement information:
row count
... other statement information items ...
Condition area list:
Condition area 1:
error code for condition 1
error message for condition 1
... other condition information items ...
Condition area 2:
error code for condition 2:
error message for condition 2
... other condition information items ...
Condition area 3:
error code for condition 3
error message for condition 3
... other condition information items ...Diagnostics Area Information Items
The diagnostics area contains statement and condition information items. Numeric items are integers. The character set for character items is UTF-8. No item can be 'NULL'. If a statement or condition item is not set by a statement that populates the diagnostics area, its value is 0 or the empty string, depending on the item data type.
The statement information part of the diagnostics area contains these items:
'NUMBER': An integer indicating the number of condition areas that have information.
'ROW_COUNT': An integer indicating the number of rows affected by the statement. 'ROW_COUNT' has the same value as the 'ROW_COUNT()' function (see *note information-functions::).
The condition information part of the diagnostics area contains a condition area for each condition. Condition areas are numbered from 1 to the value of the 'NUMBER' statement condition item. If 'NUMBER' is 0, there are no condition areas.
Each condition area contains the items in the following list. All items are standard SQL except 'MYSQL_ERRNO', which is a MySQL extension. The definitions apply for conditions generated other than by a signal (that is, by a note 'SIGNAL': signal. or note 'RESIGNAL': resignal. statement). For nonsignal conditions, MySQL populates only those condition items not described as always empty. The effects of signals on the condition area are described later.
'CLASS_ORIGIN': A string containing the class of the 'RETURNED_SQLSTATE' value. If the 'RETURNED_SQLSTATE' value begins with a class value defined in SQL standards document ISO 9075-2 (section 24.1, SQLSTATE), 'CLASS_ORIGIN' is ''ISO 9075''. Otherwise, 'CLASS_ORIGIN' is ''MySQL''.
'SUBCLASS_ORIGIN': A string containing the subclass of the 'RETURNED_SQLSTATE' value. If 'CLASS_ORIGIN' is ''ISO 9075'' or 'RETURNED_SQLSTATE' ends with ''000'', 'SUBCLASS_ORIGIN' is ''ISO 9075''. Otherwise, 'SUBCLASS_ORIGIN' is ''MySQL''.
'RETURNED_SQLSTATE': A string that indicates the 'SQLSTATE' value for the condition.
'MESSAGE_TEXT': A string that indicates the error message for the condition.
'MYSQL_ERRNO': An integer that indicates the MySQL error code for the condition.
'CONSTRAINT_CATALOG', 'CONSTRAINT_SCHEMA', 'CONSTRAINT_NAME': Strings that indicate the catalog, schema, and name for a violated constraint. They are always empty.
'CATALOG_NAME', 'SCHEMA_NAME', 'TABLE_NAME', 'COLUMN_NAME': Strings that indicate the catalog, schema, table, and column related to the condition. They are always empty.
'CURSOR_NAME': A string that indicates the cursor name. This is always empty.
For the 'RETURNED_SQLSTATE', 'MESSAGE_TEXT', and 'MYSQL_ERRNO' values for particular errors, see Server Error Message Reference (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html).
If a note 'SIGNAL': signal. (or note 'RESIGNAL': resignal.) statement populates the diagnostics area, its 'SET' clause can assign to any condition information item except 'RETURNED_SQLSTATE' any value that is legal for the item data type. note 'SIGNAL': signal. also sets the 'RETURNED_SQLSTATE' value, but not directly in its 'SET' clause. That value comes from the note 'SIGNAL': signal. statement 'SQLSTATE' argument.
*note 'SIGNAL': signal. also sets statement information items. It sets 'NUMBER' to 1. It sets 'ROW_COUNT' to −1 for errors and 0 otherwise.
How the Diagnostics Area is Cleared and Populated
Nondiagnostic SQL statements populate the diagnostics area automatically, and its contents can be set explicitly with the note 'SIGNAL': signal. and note 'RESIGNAL': resignal. statements. The diagnostics area can be examined with note 'GET DIAGNOSTICS': get-diagnostics. to extract specific items, or with note 'SHOW WARNINGS': show-warnings. or *note 'SHOW ERRORS': show-errors. to see conditions or errors.
SQL statements clear and set the diagnostics area as follows:
When the server starts executing a statement after parsing it, it clears the diagnostics area for nondiagnostic statements. Diagnostic statements do not clear the diagnostics area. These statements are diagnostic:
* *note 'GET DIAGNOSTICS': get-diagnostics.
* *note 'SHOW ERRORS': show-errors.
* *note 'SHOW WARNINGS': show-warnings.If a statement raises a condition, the diagnostics area is cleared of conditions that belong to earlier statements. The exception is that conditions raised by note 'GET DIAGNOSTICS': get-diagnostics. and note 'RESIGNAL': resignal. are added to the diagnostics area without clearing it.
Thus, even a statement that does not normally clear the diagnostics area when it begins executing clears it if the statement raises a condition.
The following example shows the effect of various statements on the diagnostics area, using *note 'SHOW WARNINGS': show-warnings. to display information about conditions stored there.
This *note 'DROP TABLE': drop-table. statement clears the diagnostics area and populates it when the condition occurs:
mysql> DROP TABLE IF EXISTS test.no_such_table;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1051 | Unknown table 'test.no_such_table' |
+-------+------+------------------------------------+
1 row in set (0.00 sec)This *note 'SET': set-variable. statement generates an error, so it clears and populates the diagnostics area:
mysql> SET @x = @@x;
ERROR 1193 (HY000): Unknown system variable 'x'
mysql> SHOW WARNINGS;
+-------+------+-----------------------------+
| Level | Code | Message |
+-------+------+-----------------------------+
| Error | 1193 | Unknown system variable 'x' |
+-------+------+-----------------------------+
1 row in set (0.00 sec)The previous note 'SET': set-variable. statement produced a single condition, so 1 is the only valid condition number for note 'GET DIAGNOSTICS': get-diagnostics. at this point. The following statement uses a condition number of 2, which produces a warning that is added to the diagnostics area without clearing it:
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------+
| Level | Code | Message |
+-------+------+------------------------------+
| Error | 1193 | Unknown system variable 'xx' |
| Error | 1753 | Invalid condition number |
+-------+------+------------------------------+
2 rows in set (0.00 sec)Now there are two conditions in the diagnostics area, so the same *note 'GET DIAGNOSTICS': get-diagnostics. statement succeeds:
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @p;
+--------------------------+
| @p |
+--------------------------+
| Invalid condition number |
+--------------------------+
1 row in set (0.01 sec)How the Diagnostics Area Stack Works
When a push to the diagnostics area stack occurs, the first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it. Diagnostics areas are pushed to and popped from the stack under the following circumstances:
Execution of a stored program
A push occurs before the program executes and a pop occurs afterward. If the stored program ends while handlers are executing, there can be more than one diagnostics area to pop; this occurs due to an exception for which there are no appropriate handlers or due to *note 'RETURN': return. in the handler.
Any warning or error conditions in the popped diagnostics areas then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area.
Execution of a condition handler within a stored program
When a push occurs as a result of condition handler activation, the stacked diagnostics area is the area that was current within the stored program prior to the push. The new now-current diagnostics area is the handler's current diagnostics area. note 'GET [CURRENT] DIAGNOSTICS': get-diagnostics. and note 'GET STACKED DIAGNOSTICS': get-diagnostics. can be used within the handler to access the contents of the current (handler) and stacked (stored program) diagnostics areas. Initially, they return the same result, but statements executing within the handler modify the current diagnostics area, clearing and setting its contents according to the normal rules (see note diagnostics-area-populating::). The stacked diagnostics area cannot be modified by statements executing within the handler except note 'RESIGNAL': resignal.
If the handler executes successfully, the current (handler) diagnostics area is popped and the stacked (stored program) diagnostics area again becomes the current diagnostics area. Conditions added to the handler diagnostics area during handler execution are added to the current diagnostics area.
Execution of *note 'RESIGNAL': resignal.
The note 'RESIGNAL': resignal. statement passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored program. note 'RESIGNAL': resignal. may change some or all information before passing it on, modifying the diagnostics stack as described in *note resignal::.
Diagnostics Area-Related System Variables
Certain system variables control or are related to some aspects of the diagnostics area:
'max_error_count' controls the number of condition areas in the diagnostics area. If more conditions than this occur, MySQL silently discards information for the excess conditions. (Conditions added by *note 'RESIGNAL': resignal. are always added, with older conditions being discarded as necessary to make room.)
'warning_count' indicates the number of conditions that occurred. This includes errors, warnings, and notes. Normally, 'NUMBER' and 'warning_count' are the same. However, as the number of conditions generated exceeds 'max_error_count', the value of 'warning_count' continues to rise whereas 'NUMBER' remains capped at 'max_error_count' because no additional conditions are stored in the diagnostics area.
'error_count' indicates the number of errors that occurred. This value includes 'not found' and exception conditions, but excludes warnings and notes. Like 'warning_count', its value can exceed 'max_error_count'.
If the 'sql_notes' system variable is set to 0, notes are not stored and do not increment 'warning_count'.
Example: If 'max_error_count' is 10, the diagnostics area can contain a maximum of 10 condition areas. Suppose that a statement raises 20 conditions, 12 of which are errors. In that case, the diagnostics area contains the first 10 conditions, 'NUMBER' is 10, 'warning_count' is 20, and 'error_count' is 12.
Changes to the value of 'max_error_count' have no effect until the next attempt to modify the diagnostics area. If the diagnostics area contains 10 condition areas and 'max_error_count' is set to 5, that has no immediate effect on the size or content of the diagnostics area.
File: manual.info.tmp, Node: conditions-and-parameters, Next: condition-handling-restrictions, Prev: diagnostics-area, Up: condition-handling
13.6.7.8 Condition Handling and OUT or INOUT Parameters .......................................................
If a stored procedure exits with an unhandled exception, modified values of 'OUT' and 'INOUT' parameters are not propogated back to the caller.
If an exception is handled by a 'CONTINUE' or 'EXIT' handler that contains a note 'RESIGNAL': resignal. statement, execution of note 'RESIGNAL': resignal. pops the Diagnostics Area stack, thus signalling the exception (that is, the information that existed before entry into the handler). If the exception is an error, the values of 'OUT' and 'INOUT' parameters are not propogated back to the caller.
File: manual.info.tmp, Node: condition-handling-restrictions, Prev: conditions-and-parameters, Up: condition-handling
13.6.7.9 Restrictions on Condition Handling ...........................................
note 'SIGNAL': signal, note 'RESIGNAL': resignal, and *note 'GET DIAGNOSTICS': get-diagnostics. are not permissible as prepared statements. For example, this statement is invalid:
PREPARE stmt1 FROM 'SIGNAL SQLSTATE "02000"';'SQLSTATE' values in class ''04'' are not treated specially. They are handled the same as other exceptions.
In standard SQL, the first condition relates to the 'SQLSTATE' value returned for the previous SQL statement. In MySQL, this is not guaranteed, so to get the main error, you cannot do this:
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;Instead, do this:
GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;File: manual.info.tmp, Node: sql-server-administration-statements, Next: sql-utility-statements, Prev: sql-compound-statements, Up: sql-statements