Explore programmatic compound statements in MariaDB Server. This section covers BEGIN...END blocks, loops, and conditional logic for writing complex stored routines and event definitions.
[begin_label:] BEGIN [NOT ATOMIC]
[statement_list]
END [end_label]NOT ATOMIC is required when used outside of a stored procedure. Inside stored procedures or within an anonymous block, BEGIN alone starts a new anonymous block.
BEGIN ... END syntax is used for writing compound statements. 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 (i.e., ;) statement delimiter. statement_list is
optional, which means that the empty compound statement (BEGIN END) is legal.
Note that END will perform a commit. If you are running in autocommit mode, every statement will be committed separately. If you are not running in autocommit mode, you must execute a COMMIT or ROLLBACK after END to get the database up to date.
Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. This is handled in the mysql command-line client with the DELIMITER command.
Changing the ; end-of-statement delimiter (for example, to//) allows ; to be used in a program body.
A compound statement within a stored program can belabeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.
BEGIN ... END constructs can be nested. Each block can define its own variables, a CONDITION, a HANDLER and a CURSOR, which don't exist in the outer blocks. The most local declarations override the outer objects which use the same name (see example below).
The declarations order is the following:
Note that DECLARE HANDLER contains another BEGIN ... END construct.
Here is an example of a very simple, anonymous block:
BEGIN NOT ATOMIC
SET @a=1;
CREATE TABLE test.t1(a INT);
END|Below is an example of nested blocks in a stored procedure:
CREATE PROCEDURE t( )
BEGIN
DECLARE x TINYINT UNSIGNED DEFAULT 1;
BEGIN
DECLARE x CHAR(2) DEFAULT '02';
DECLARE y TINYINT UNSIGNED DEFAULT 10;
SELECT x, y;
END;
SELECT x;
END;In this example, a TINYINT variable, x is declared in the outter block. But in the inner block x is re-declared as a CHAR and an y variable is declared. The inner SELECT shows the "new" value of x, and the value of y. But when x is selected in the outer block, the "old" value is returned. The final SELECT doesn't try to read y, because it doesn't exist in that context.
This page is licensed: GPLv2, originally from fill_help_tables.sql
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 text on this page describes the CASE statement for stored programs. See the CASE OPERATOR for details on the CASE operator outside of stored programs.
The CASE statement for stored programs implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.
The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END. implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.
If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results.
Each statement_list consists of one or more statements; an emptystatement_list is not allowed. To handle situations where no value is matched by any WHEN clause, use an ELSE containing an empty BEGIN ... END block, as shown in this example:
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;
|The indentation used here in the ELSE clause is for purposes of clarity only, and is not otherwise significant. See Delimiters in the mariadb client for more on the use of the delimiter command.
Note: The syntax of the CASE statement used inside stored programs differs slightly from that of the SQL CASE expression described inCASE OPERATOR. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
The CASE operator, which differs from the CASE statement described above.
The IF statement.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_codeThe DECLARE ... CONDITION statement defines a named error condition. It specifies a condition that needs specific handling and associates a name with that condition. Later, the name can be used in a DECLARE ... HANDLER, SIGNAL or RESIGNAL statement (as long as the statement is located in the same BEGIN ... END block).
Conditions must be declared after local variables, but before CURSORs and HANDLERs.
A condition_value for DECLARE ... CONDITION can be an SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate success rather than an error condition. If you try, or if you specify an invalid SQLSTATE value, an error like this is produced:
ERROR 1407 (42000): Bad SQLSTATE: '00000'For a list of SQLSTATE values and MariaDB error codes, see MariaDB Error Codes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECLARE handler_type HANDLER
FOR condition_value [, condition_value] ...
statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mariadb_error_codeThe DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using BEGIN and END.
Handlers must be declared after local variables, a CONDITION and a CURSOR.
For a CONTINUE handler, execution of the current program continues after execution of the handler statement. For an EXIT handler, execution terminates for the BEGIN ... END compound statement in which the handler is declared. (This is true even if the condition occurs in an inner block.) The UNDO handler type statement is not supported.
If a condition occurs for which no handler has been declared, the default action is EXIT.
A condition_value for DECLARE ... HANDLER can be any of the following values:
An SQLSTATE value (a 5-character string literal) or a MariaDB error code (a number). You should not use SQLSTATE value '00000' or MariaDB error code 0, because those indicate sucess rather than an error condition. For a list of SQLSTATE values and MariaDB error codes, seeMariaDB Error Codes.
A condition name previously specified with DECLARE ... CONDITION. It must be in the same stored program. See DECLARE CONDITION.
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Cursor Overview. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
When an error raises, in some cases it could be handled by multiple HANDLERs. For example, there may be an handler for 1050 error, a separate handler for the 42S01 SQLSTATE, and another separate handler for the SQLEXCEPTION class: in theory all occurrences of HANDLER may catch the 1050 error, but MariaDB chooses the HANDLER with the highest precedence. Here are the precedence rules:
Handlers which refer to an error code have the highest precedence.
Handlers which refer to a SQLSTATE come next.
Handlers which refer to an error class have the lowest precedence.
In some cases, a statement could produce multiple errors. If this happens, in some cases multiple handlers could have the highest precedence. In such cases, the choice of the handler is indeterminate.
Note that if an error occurs within a CONTINUE HANDLER block, it can be handled by another HANDLER. However, a HANDLER which is already in the stack (that is, it has been called to handle an error and its execution didn't finish yet) cannot handle new errors—this prevents endless loops. For example, suppose that a stored procedure contains a CONTINUE HANDLER for SQLWARNING and another CONTINUE HANDLER for NOT FOUND. At some point, a NOT FOUND error occurs, and the execution enters the NOT FOUND HANDLER. But within that handler, a warning occurs, and the execution enters the SQLWARNING HANDLER. If another NOT FOUND error occurs, it cannot be handled again by the NOT FOUND HANDLER, because its execution is not finished.
When a DECLARE HANDLER block can handle more than one error condition, it may be useful to know which errors occurred. To do so, you can use the GET DIAGNOSTICS statement.
An error that is handled by a DECLARE HANDLER construct can be issued again using the RESIGNAL statement.
Below is an example using DECLARE HANDLER:
CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
DELIMITER //
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;
//
DELIMITER ;
CALL handlerdemo( );
SELECT @x;
+------+
| @x |
+------+
| 3 |
+------+This page is licensed: GPLv2, originally from fill_help_tables.sql
DECLARE var_name [, var_name] ... [[ROW] TYPE OF]] type [DEFAULT value]This statement is used to declare local variables within stored programs. To provide a default value for the variable, include a DEFAULT clause. The value can be specified as an expression (even subqueries are permitted); it need not be a constant. If theDEFAULT 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 CREATE PROCEDURE.
Local variables must be declared before CONDITIONs, CURSORs and HANDLERs.
Local variable names are not case sensitive.
The scope of a local variable is within the BEGIN ... END block where 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.
Anchored data types allow a data type to be defined based on another object, such as a table row, rather than specifically set in the declaration. If the anchor object changes, so will the anchored data type. This can lead to routines being easier to maintain, so that if the data type in the table is changed, it will automatically be changed in the routine as well.
Variables declared with ROW TYPE OF will have the same features as implicit ROW variables. It is not possible to use ROW TYPE OF variables in a LIMIT clause.
The real data type of TYPE OF and ROW TYPE OF table_name will become known at the very beginning of the stored routine call. ALTER TABLE or DROP TABLE statements performed inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declared after an ALTER TABLE or DROP TABLE statement.
The real data type of a ROW TYPE OF cursor_name variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only once. In a cursor ROW TYPE OF variable that is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.
The tables referenced in TYPE OF and ROW TYPE OF declarations will be checked for existence at the beginning of the stored routine call. CREATE PROCEDURE or CREATE FUNCTION will not check the referenced tables for existence.
DECLARE name VARCHAR(5) DEFAULT 'monty';
DECLARE x INT DEFAULT 10;
DECLARE Y SMALLINT;TYPE OF and ROW TYPE OF :
DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}This page is licensed: GPLv2, originally from fill_help_tables.sql
Integer range FOR loop:
[begin_label:]
FOR var_name IN [ REVERSE ] lower_bound .. upper_bound
DO statement_list
END FOR [ end_label ]Explicit cursor FOR loop:
[begin_label:]
FOR record_name IN cursor_name [ ( cursor_actual_parameter_list)]
DO statement_list
END FOR [ end_label ]Explicit cursor FOR loop (Oracle mode):
[begin_label:]
FOR record_name IN cursor_name [ ( cursor_actual_parameter_list)]
LOOP
statement_list
END LOOP [ end_label ]Implicit cursor FOR loop:
[begin_label:]
FOR record_name IN ( select_statement )
DO statement_list
END FOR [ end_label ]FOR loops allow code to be executed a fixed number of times.
In an integer range FOR loop, MariaDB will compare the lower bound and upper bound values, and assign the lower bound value to a counter. If REVERSE is not specified, and the upper bound value is greater than or equal to the counter, the counter will be incremented and the statement will continue, after which the loop is entered again. If the upper bound value is greater than the counter, the loop will be exited.
If REVERSE is specified, the counter is decremented, and the upper bound value needs to be less than or equal for the loop to continue.
Integer range FOR loop:
CREATE TABLE t1 (a INT);
DELIMITER //
FOR i IN 1..3
DO
INSERT INTO t1 VALUES (i);
END FOR;
//
DELIMITER ;
SELECT * FROM t1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+REVERSE integer range FOR loop:
CREATE OR REPLACE TABLE t1 (a INT);
DELIMITER //
FOR i IN REVERSE 4..12
DO
INSERT INTO t1 VALUES (i);
END FOR;
//
Query OK, 9 rows affected (0.422 sec)
DELIMITER ;
SELECT * FROM t1;
+------+
| a |
+------+
| 12 |
| 11 |
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
+------+Explicit cursor in Oracle mode:
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b0');
INSERT INTO t1 VALUES (11,'b1');
INSERT INTO t1 VALUES (12,'b2');
DELIMITER //
CREATE OR REPLACE PROCEDURE p1(pa INT) AS
CURSOR cur(va INT) IS
SELECT a, b FROM t1 WHERE a=va;
BEGIN
FOR rec IN cur(pa)
LOOP
SELECT rec.a, rec.b;
END LOOP;
END;
//
DELIMITER ;
CALL p1(10);
+-------+-------+
| rec.a | rec.b |
+-------+-------+
| 10 | b0 |
+-------+-------+
CALL p1(11);
+-------+-------+
| rec.a | rec.b |
+-------+-------+
| 11 | b1 |
+-------+-------+
CALL p1(12);
+-------+-------+
| rec.a | rec.b |
+-------+-------+
| 12 | b2 |
+-------+-------+
CALL p1(13);
Query OK, 0 rows affected (0.000 sec)This page is licensed: CC BY-SA / Gnu FDL
GOTO labelThe GOTO statement causes the code to jump to the specified label, and continue operating from there. It is only accepted when in Oracle mode.
SET sql_mode=ORACLE;
DELIMITER //
CREATE OR REPLACE PROCEDURE p1 AS
BEGIN
SELECT 1;
GOTO label;
SELECT 2;
<<label>>
SELECT 3;
END;
//
DELIMITER
call p1();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.000 sec)
+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.000 sec)This page is licensed: CC BY-SA / Gnu FDL
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF;IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement list is executed. If no search_condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.
The IF() function, which differs from the IF statement described above.
The CASE statement.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ITERATE labelITERATE can appear only within LOOP, REPEAT, and WHILE statements.ITERATE means "do the loop again", and uses the statement's label to determine which statements to repeat. The label must be in the same stored program, not in a caller procedure.
If you try to use ITERATE with a non-existing label, or if the label is associated to a construct which is not a loop, the following error will be produced:
ERROR 1308 (42000): ITERATE with no matching label: <label_name>Below is an example of how ITERATE might be used:
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;
ENDLEAVE - Exits a loop (or any labeled code block)
This page is licensed: GPLv2, originally from fill_help_tables.sql
label: <construct>
[label]Labels are MariaDB identifiers which can be used to identify a BEGIN ... END construct or a loop. They have a maximum length of 16 characters and can be quoted with backticks (i.e.., ```).
Labels have a start part and an end part. The start part must precede the portion of code it refers to, must be followed by a colon (:) and can be on the same or different line. The end part is optional and adds nothing, but can make the code more readable. If used, the end part must precede the construct's delimiter (;). Constructs identified by a label can be nested. Each construct can be identified by only one label.
Labels need not be unique in the stored program they belong to. However, a label for an inner loop cannot be identical to a label for an outer loop. In this case, the following error would be produced:
ERROR 1309 (42000): Redefining label <label_name>LEAVE and ITERATE statements can be used to exit or repeat a portion of code identified by a label. They must be in the same Stored Routine, Trigger or Event which contains the target label.
Below is an example using a simple label that is used to exit a LOOP:
CREATE PROCEDURE `test_sp`()
BEGIN
`my_label`:
LOOP
SELECT 'looping';
LEAVE `my_label`;
END LOOP;
SELECT 'out of loop';
END;The following label is used to exit a procedure, and has an end part:
CREATE PROCEDURE `test_sp`()
`my_label`:
BEGIN
IF @var = 1 THEN
LEAVE `my_label`;
END IF;
DO something();
END `my_label`;This page is licensed: CC BY-SA / Gnu FDL
LEAVE labelThis statement is used to exit the flow control construct that has the given label. The label must be in the same stored program, not in a caller procedure. LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE). In Stored Procedures, Triggers and Events, LEAVE can refer to the outmost BEGIN ... END construct; in that case, the program exits the procedure. In Stored Functions, RETURN can be used instead.
If you try to LEAVE a non-existing label, or if you try to LEAVE a HANDLER block, the following error will be produced:
ERROR 1308 (42000): LEAVE with no matching label: <label_name>The following example uses LEAVE to exit the procedure if a condition is true:
CREATE PROCEDURE proc(IN p TINYINT)
CONTAINS SQL
`whole_proc`:
BEGIN
SELECT 1;
IF p < 1 THEN
LEAVE `whole_proc`;
END IF;
SELECT 2;
END;
CALL proc(0);
+---+
| 1 |
+---+
| 1 |
+---+ITERATE - Repeats a loop execution
This page is licensed: GPLv2, originally from fill_help_tables.sql
[begin_label:] LOOP
statement_list
END LOOP [end_label]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 (i.e., ;) statement delimiter. The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement.
A LOOP statement can be labeled. end_label cannot be given unlessbegin_label also is present. If both are present, they must be the same.
See Delimiters in the mariadb client for more on delimiter usage in the client.
This page is licensed: GPLv2, originally from fill_help_tables.sql
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]The statement list within a REPEAT statement is repeated until the search_condition is true. Thus, a REPEAT always enters the loop at least once. statement_list consists of one or more statements, each
terminated by a semicolon (i.e., ;) statement delimiter.
A REPEAT statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.
See Delimiters in the mariadb client for more on client delimiter usage.
DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//
CALL dorepeat(1000)//
SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+This page is licensed: GPLv2, originally from fill_help_tables.sql
RESIGNAL [error_condition]
[SET error_property
[, error_property] ...]
error_condition:
SQLSTATE [VALUE] 'sqlstate_value'
| condition_name
error_property:
error_property_name = <error_property_value>
error_property_name:
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAMEThe syntax of RESIGNAL and its semantics are very similar to SIGNAL. This statement can only be used within an error HANDLER. It produces an error, like SIGNAL. RESIGNAL clauses are the same as SIGNAL, except that they all are optional, even SQLSTATE. All the properties which are not specified in RESIGNAL, will be identical to the properties of the error that was received by the error HANDLER. For a description of the clauses, see diagnostics area.
RESIGNAL, without any clauses, produces an error which is identical to the error that was received by HANDLER.
If used out of a HANDLER construct, RESIGNAL produces the following error:
ERROR 1645 (0K000): RESIGNAL when handler not activeFor a list of SQLSTATE values and MariaDB error codes, see MariaDB Error Codes.
The following procedure tries to query two tables which don't exist, producing a 1146 error in both cases. Those errors will trigger the HANDLER. The first time the error will be ignored and the client will not receive it, but the second time, the error is re-signaled, so the client will receive it.
CREATE PROCEDURE test_error( )
BEGIN
DECLARE CONTINUE HANDLER
FOR 1146
BEGIN
IF @hide_errors IS FALSE THEN
RESIGNAL;
END IF;
END;
SET @hide_errors = TRUE;
SELECT 'Next error will be ignored' AS msg;
SELECT `c` FROM `temptab_one`;
SELECT 'Next error won''t be ignored' AS msg;
SET @hide_errors = FALSE;
SELECT `c` FROM `temptab_two`;
END;
CALL test_error( );
+----------------------------+
| msg |
+----------------------------+
| Next error will be ignored |
+----------------------------+
+-----------------------------+
| msg |
+-----------------------------+
| Next error won't be ignored |
+-----------------------------+
ERROR 1146 (42S02): Table 'test.temptab_two' doesn't existThe following procedure re-signals an error, modifying only the error message to clarify the cause of the problem.
CREATE PROCEDURE test_error()
BEGIN
DECLARE CONTINUE HANDLER
FOR 1146
BEGIN
RESIGNAL SET
MESSAGE_TEXT = '`temptab` does not exist';
END;
SELECT `c` FROM `temptab`;
END;
CALL test_error( );
ERROR 1146 (42S02): `temptab` does not existCREATE PROCEDURE handle_error()
BEGIN
RESIGNAL;
END;
CREATE PROCEDURE p()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL p();
SIGNAL SQLSTATE '45000';
END;This page is licensed: CC BY-SA / Gnu FDL
RETURN exprThe RETURN statement terminates execution of a stored function and returns the value expr to the function caller. There must be at least one RETURN statement in a stored function. If the function has multiple exit points, all exit points must have a RETURN.
This statement is not used in stored procedures, triggers, or events. LEAVE can be used instead.
The following example shows that RETURN can return the result of a scalar subquery:
CREATE FUNCTION users_count() RETURNS BOOL
READS SQL DATA
BEGIN
RETURN (SELECT COUNT(DISTINCT User) FROM mysql.user);
END;This page is licensed: GPLv2, originally from fill_help_tables.sql
SELECT col_name [, col_name] ...
INTO var_name [, var_name] ...
table_exprSELECT ... INTO enables selected columns to be stored directly into variables. No resultset is produced. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can useLIMIT 1 to limit the result set to a single row.
The INTO clause can also be specified at the end of the statement.
In the context of such statements that occur as part of events executed by the Event Scheduler, diagnostics messages (not only errors, but also warnings) are written to the error log, and, on Windows, to the application event log.
This statement can be used with both local variables and user-defined variables.
For the complete syntax, see SELECT.
Another way to set a variable's value is the SET statement.
SELECT ... INTO results are not stored in the query cache even if SQL_CACHE is specified.
SELECT id, data INTO @x,@y
FROM test.t1 LIMIT 1;
SELECT * from t1 where t1.a=@x and t1.b=@yIf you want to use this construct with UNION you have to use the syntax:
SELECT * INTO @x FROM (SELECT t1.a FROM t1 UNION SELECT t2.a FROM t2) dt;SELECT - full SELECT syntax.
SELECT INTO OUTFILE - formatting and writing the result to an external file.
SELECT INTO DUMPFILE - binary-safe writing of the unformatted results to an external file.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET var_name = expr [, var_name = expr] ...The SET statement in stored programs is an extended version of the general SET statement. Referenced variables may be ones declared inside a stored program, global system variables, or user-defined variables.
The SET statement in stored programs is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, global and session server variables, user-defined variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored.
SET can be used with both local variables and user-defined variables.
When setting several variables using the columns returned by a query, SELECT INTO should be preferred.
To set many variables to the same value, the LAST_VALUE( ) function can be used.
Below is an example of how a user-defined variable may be set:
SET @x = 1;This page is licensed: GPLv2, originally from fill_help_tables.sql
SIGNAL error_condition
[SET error_property
[, error_property] ...]
error_condition:
SQLSTATE [VALUE] 'sqlstate_value'
| condition_name
error_property:
error_property_name = <error_property_value>
error_property_name:
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAMESIGNAL empties the diagnostics area and produces a custom error. This statement can be used anywhere, but is generally useful when used inside a stored program. When the error is produced, it can be caught by a HANDLER. If not, the current stored program, or the current statement, will terminate with the specified error.
Sometimes an error HANDLER just needs to SIGNAL the same error it received, optionally with some changes. Usually the RESIGNAL statement is the most convenient way to do this.
error_condition can be an SQLSTATE value or a named error condition defined via DECLARE CONDITION. SQLSTATE must be a constant string consisting of five characters. These codes are standard to ODBC and ANSI SQL. For customized errors, the recommended SQLSTATE is '45000'. For a list of SQLSTATE values used by MariaDB, see the MariaDB Error Codes page. The SQLSTATE can be read via the API method mysql_sqlstate( ).
To specify error properties user-defined variables and local variables can be used, as well as character set conversions (but you can't set a collation).
The error properties, their type and their default values are explained in the diagnostics area page.
If the SQLSTATE is not valid, the following error like this will be produced:
ERROR 1407 (42000): Bad SQLSTATE: '123456'If a property is specified more than once, an error like this will be produced:
ERROR 1641 (42000): Duplicate condition information item 'MESSAGE_TEXT'If you specify a condition name which is not declared, an error like this will be produced:
ERROR 1319 (42000): Undefined CONDITION: cond_nameIf MYSQL_ERRNO is out of range, you will get an error like this:
ERROR 1231 (42000): Variable 'MYSQL_ERRNO' can't be set to the value of '0'Here's what happens if SIGNAL is used in the client to generate errors:
SIGNAL SQLSTATE '01000';
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1642 | Unhandled user-defined warning condition |
+---------+------+------------------------------------------+
1 row in set (0.06 sec)
SIGNAL SQLSTATE '02000';
ERROR 1643 (02000): Unhandled user-defined not found conditionHow to specify MYSQL_ERRNO and MESSAGE_TEXT properties:
SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='H
ello, world!';
ERROR 30001 (45000): Hello, world!The following code shows how to use user variables, local variables and character set conversion with SIGNAL:
CREATE PROCEDURE test_error(x INT)
BEGIN
DECLARE errno SMALLINT UNSIGNED DEFAULT 31001;
SET @errmsg = 'Hello, world!';
IF x = 1 THEN
SIGNAL SQLSTATE '45000' SET
MYSQL_ERRNO = errno,
MESSAGE_TEXT = @errmsg;
ELSE
SIGNAL SQLSTATE '45000' SET
MYSQL_ERRNO = errno,
MESSAGE_TEXT = _utf8'Hello, world!';
END IF;
END;How to use named error conditions:
CREATE PROCEDURE test_error(n INT)
BEGIN
DECLARE `too_big` CONDITION FOR SQLSTATE '45000';
IF n > 10 THEN
SIGNAL `too_big`;
END IF;
END;In this example, we'll define a HANDLER for an error code. When the error occurs, we SIGNAL a more informative error which makes sense for our procedure:
CREATE PROCEDURE test_error()
BEGIN
DECLARE EXIT HANDLER
FOR 1146
BEGIN
SIGNAL SQLSTATE '45000' SET
MESSAGE_TEXT = 'Temporary tables not found; did you call init() procedure?';
END;
-- this will produce a 1146 error
SELECT `c` FROM `temptab`;
END;This page is licensed: CC BY-SA / Gnu FDL
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]The statement list within a WHILE statement is repeated as long as thesearch_condition is true. statement_list consists of one or more statements. If the loop must be executed at least once, REPEAT ... LOOP can be used instead.
A WHILE statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
ENDThis page is licensed: GPLv2, originally from fill_help_tables.sql
Compound statements can also be used outside of stored programs.
delimiter |
IF @have_innodb THEN
CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
END IF|
Query OK, 0 rows affected, 2 warnings (0.00 sec)Using compound statements this way is subject to following limitations:
A compound statement might not start with a label.
A compound statement is parsed completely—note "2 warnings" in the above example, even if the condition was false (InnoDB was, indeed, disabled), and the CREATE TABLE statement was not executed, it was still parsed and the parser produced "Unknown storage engine" warning.
Inside a compound block first three limitations do not apply, one can use anything that can be used inside a stored program — including labels, condition handlers, variables, and so on:
BEGIN NOT ATOMIC
DECLARE foo CONDITION FOR 1146;
DECLARE x INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SET x=1;
INSERT INTO test.t1 VALUES ("hndlr1", val, 2);
END|Example how to use IF:
IF (1>0) THEN BEGIN NOT ATOMIC SELECT 1; END ; END IF;;Example of how to use WHILE loop:
DELIMITER |
BEGIN NOT ATOMIC
DECLARE x INT DEFAULT 0;
WHILE x <= 10 DO
SET x = x + 1;
SELECT x;
END WHILE;
END|
DELIMITER ;This page is licensed: CC BY-SA / Gnu FDL
Learn about cursors in MariaDB Server's programmatic compound statements. This section details how to iterate over result sets row-by-row within stored procedures and functions.
A cursor is a structure that allows you to go over records sequentially, and perform processing based on the result.
MariaDB permits cursors inside stored programs, and MariaDB cursors are non-scrollable, read-only and case-insensitive.
Non-scrollable means that the rows can only be fetched in the order specified by the SELECT statement. Rows cannot be skipped, you cannot jump to a specific row, and you cannot fetch rows in reverse order.
Read-only means that data cannot be updated through the cursor.
Assensitive means that the cursor points to the actual underlying data. This kind of cursor is quicker than the alternative, an insensitive cursor, as no data is copied to a temporary table. However, changes to the data being used by the cursor will affect the cursor data.
Cursors are created with a DECLARE CURSOR statement and opened with an OPEN statement. Rows are read with a FETCH statement before the cursor is finally closed with a CLOSE statement.
When FETCH is issued and there are no more rows to extract, the following error is produced:
ERROR 1329 (02000): No data - zero rows fetched, selected, or processedTo avoid problems, a DECLARE HANDLER statement is generally used. The HANDLER should handler the 1329 error, or the '02000' SQLSTATE, or the NOT FOUND error class.
Only SELECT statements are allowed for cursors, and they cannot be contained in a variable - so, they cannot be composed dynamically. However, it is possible to SELECT from a view. Since the CREATE VIEW statement can be executed as a prepared statement, it is possible to dynamically create the view that is queried by the cursor.
Cursors can have parameters. Cursor parameters can appear in any part of the DECLARE CURSOR select_statement where a stored procedure variable is allowed (select list, WHERE, HAVING, LIMIT etc). See DECLARE CURSOR and OPEN for syntax, and below for an example.
Cursors cannot have parameters.
CREATE TABLE c1(i INT);
CREATE TABLE c2(i INT);
CREATE TABLE c3(i INT);
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE x, y INT;
DECLARE cur1 CURSOR FOR SELECT i FROM test.c1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.c2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO x;
FETCH cur2 INTO y;
IF done THEN
LEAVE read_loop;
END IF;
IF x < y THEN
INSERT INTO test.c3 VALUES (x);
ELSE
INSERT INTO test.c3 VALUES (y);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END; //
DELIMITER ;
INSERT INTO c1 VALUES(5),(50),(500);
INSERT INTO c2 VALUES(10),(20),(30);
CALL p1;
SELECT * FROM c3;
+------+
| i |
+------+
| 5 |
| 20 |
| 30 |
+------+DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old');
DELIMITER //
CREATE PROCEDURE p1(min INT,max INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE va INT;
DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN cur(min,max);
read_loop: LOOP
FETCH cur INTO va;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO t1 VALUES (va,'new');
END LOOP;
CLOSE cur;
END;
//
DELIMITER ;
CALL p1(2,4);
SELECT * FROM t1;
+------+------+
| a | b |
+------+------+
| 1 | old |
| 2 | old |
| 3 | old |
| 4 | old |
| 5 | old |
| 2 | new |
| 3 | new |
| 4 | new |
+------+------+This page is licensed: CC BY-SA / Gnu FDL
CLOSE cursor_nameThis statement closes a previously opened cursor. The cursor must have been previously opened or else an error occurs.
If not closed explicitly, a cursor is closed at the end of the compound statement in which it was declared.
See Cursor Overview for an example.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECLARE cursor_name CURSOR [(cursor_formal_parameter[,...])] FOR select_statement
cursor_formal_parameter:
[IN] name type [collate clause]DECLARE cursor_name CURSOR [(cursor_formal_parameter[,...])] FOR select_statement
cursor_formal_parameter:
name type [collate clause]This statement declares a cursor. Multiple cursors may be declared in a stored program, but each cursor in a given block must have a unique name.
select_statement is not executed until the OPEN statement is executed. It is important to remember this if the query produces an error, or calls functions which have side effects.
A SELECT associated to a cursor can use variables, but the query itself cannot be a variable, and cannot be dynamically composed. The SELECT statement cannot have an INTO clause.
Cursors must be declared before HANDLERs, but after local variables and CONDITIONs.
Cursors can have parameters. This is a non-standard SQL extension. Cursor parameters can appear in any part of the DECLARE CURSOR select_statement where a stored procedure variable is allowed (select list, WHERE, HAVING, LIMIT , and so forth).
The IN qualifier is supported in the cursor_formal_parameter part of the syntax.
The IN qualifier is not supported in the cursor_formal_parameter part of the syntax.
See Cursor Overview for an example.
This page is licensed: GPLv2, originally from fill_help_tables.sql
FETCH cursor_name INTO var_name [, var_name] ...This statement fetches the next row (if a row exists) using the specified open cursor, and advances the cursor pointer.
var_name can be a local variable, but not a user-defined variable.
If no more rows are available, a No Data condition occurs withSQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).
See Cursor Overview for an example.
This page is licensed: GPLv2, originally from fill_help_tables.sql
OPEN cursor_name [expression[,...]];OPEN cursor_nameThis statement opens a cursor which was previously declared with DECLARE CURSOR.
The query associated to the DECLARE CURSOR is executed when OPEN is executed. It is important to remember this if the query produces an error, or calls functions which have side effects.
This is necessary in order to FETCH rows from a cursor.
See Cursor Overview for an example.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Learn about diagnostics in programmatic compound statements. This section covers error handling and information retrieval within stored procedures and functions for effective debugging.
The diagnostics area contains information about the error conditions which were produced by an SQL statement, as well as some information about the statement which generated them.
The statement information area contains the following data:
NUMBER is the number of conditions which are present in the diagnostics area.
ROW_COUNT has the same value as the ROW_COUNT() function for the statement that produced the conditions.
Each condition has several properties, which are explained here.
The following table shows the type and size of all the properties:
RETURNED_SQLSTATE
VARCHAR(5)
MYSQL_ERRNO
SMALLINT UNSIGNED
MESSAGE_TEXT
VARCHAR(512)
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)
These properties can never be set to NULL. If they are empty, the empty string is used.
The most common ones have a value for all built-in errors, and can be read both via SQL and via the API:
RETURNED_SQLSTATE is the SQLSTATE of the condition. It is a five characters code, composed by a class (first two characters) and a subclass (last three characters). For more information about this property, refer to the SQLSTATE page.
MYSQL_ERRNO is the error code. Each built-in condition has a unique numeric code. 0 indicates success, but it cannot be explicitly set or read via SQL. For a list of built-in error codes, refer to MariaDB Error Codes. The API function to read it is mysql_errno().
MESSAGE_TEXT is a descriptive, human-readable message. For built-in errors, parsing this string is the only way to get more information about the error. For example, parsing a message like "Table 'tab1' already exists", a program can find out that the missing table is tab1. The API function to read it is mysql_error().
For conditions generated by the user via SIGNAL, if MYSQL_ERRNO and MESSAGE_TEXT are not specified, their default values depend on the first two SQLSTATE characters:
'00' means 'success'. It can not be set in any way, and can only be read via the API.
For '01' class, default MYSQL_ERRNO is 1642 and default MESSAGE_TEXT is 'Unhandled user-defined warning condition'.
For '02' class, default MYSQL_ERRNO is 1643 and default MESSAGE_TEXT is 'Unhandled user-defined not found condition'.
For all other cases, including the '45000' value, default MYSQL_ERRNO is 1644 and default MESSAGE_TEXT is 'Unhandled user-defined exception condition'.
There are more condition properties, which are never set for built-in errors. They can only be set via SIGNAL and RESIGNAL statements, and can only be read via GET DIAGNOSTICS - not via the API. Such properties are:
CLASS_ORIGIN indicates whether the SQLSTATE uses a standard class or a software-specific class. If it is defined in the SQL standards document ISO 9075-2 (section 24.1, SQLSTATE), this property's value is supposed to be 'ISO 9075', otherwise it is supposed to be 'MySQL'. However, any string is accepted.
SUBCLASS_ORIGIN indicates whether the SQLSTATE uses a standard subclass or a software-specific class. If the SQLSTATE is defined in the SQL standards document ISO 9075-2 (section 24.1, SQLSTATE), this property's value is supposed to be 'ISO 9075', otherwise it is supposed to be 'MySQL'. However, any string is accepted.
SCHEMA_NAME indicates in which schema (database) the error occurred.
TABLE_NAME indicates the name of the table which was accessed by the failed statement.
COLUMN_NAME indicates the name of the column which was accessed by the failed statement.
CONSTRAINT_NAME indicates the name of the constraint that was violated.
CONSTRAINT_SCHEMA indicates in which schema the violated constraint is located.
CURSOR_NAME indicates the name of the cursor which caused the error.
The following properties can be used and are defined in the standard SQL, but have no meaning because MariaDB doesn't currently support catalogs:
CATALOG_NAME is used by the standard SQL to indicate in which catalog the error occurred.
CONSTRAINT_CATALOG is used by the standard SQL to indicate in which catalog the violated constraint is located.
When a statement produces one or more error conditions (errors, warnings, notes) the conditions are inserted into the diagnostics area, and the statement information area is updated with that statement’s information. Usually, this also clears all the old conditions from the diagnostics area, but there is an exception: if the new statement is a correctly parsed RESIGNAL or GET DIAGNOSTICS, the old contents will remain in the diagnostics area. SIGNAL clears the old conditions.
When a table-based statement (like INSERT) is executed, the old data in the diagnostics area is cleared even if the statement doesn't produce any condition. However, statements which don't access any table (like SET, or a SELECT with no FROM clause) is executed and produces no warnings, the diagnostics area remains unchanged.
The maximum number of conditions that can be in the diagnostics area is max_error_count. If this value is 0, the diagnostics area is empty. If this variable is changed, the new value takes effect with the next statement (that is, the diagnostics area is not immediately truncated).
The following statements explicitly add conditions to the diagnostics area:
SIGNAL: produces a custom error.
RESIGNAL: after an error is produced, generates a modified version of that error.
The following statements read contents from the diagnostics area:
GET DIAGNOSTICS is the only way to read all information.
SHOW WARNINGS shows a summary of errors, warnings and notes.
SHOW ERRORS shows a summary of errors.
DECLARE HANDLER can be used to handle error conditions within stored programs.
DECLARE CONDITION can be used to associate an SQLSTATE or an error code to a name. That name can be referenced in DECLARE HANDLER, SIGNAL and RESIGNAL statements.
All these statements can also be executed inside a stored routine. However, only SHOW WARNINGS and SHOW ERRORS can be executed as a prepared statement. After an EXECUTE statement, the diagnostics area contains information about the prepared statement, if it produces error conditions.
This page is licensed: CC BY-SA / Gnu FDL
GET [CURRENT] DIAGNOSTICS
{
statement_property
[, statement_property] ...
| CONDITION condition_number
condition_property
[, condition_property] ...
}
statement_property:
variable = statement_property_name
condition_property:
variable = condition_property_name
statement_property_name:
NUMBER
| ROW_COUNT
condition_property_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
| ROW_NUMBERThe diagnostics area contains information about the errors, warnings and notes which were produced by the last SQL statement. If that statement didn't produce any warnings, the diagnostics area contains information about the last executed statement which involved a table. The GET DIAGNOSTICS statement copies the requested information from the diagnostics area to the specified variables. It is possible to use both user variables or local variables.
To use GET DIAGNOSTICS, it is important to know how the diagnostics area is structured. It has two sub-areas: the statement information area and the error conditions information area. For details, please refer to the diagnostics area page.
Each single GET DIAGNOSTICS command can read information from the statement information area or from a single error condition. This means that, if you have two warnings and you want to know the number of warnings, and read both the warnings, you need to issue GET DIAGNOSTICS three times.
The CURRENT keywords adds nothing to the statement, because MariaDB has only one diagnostics area.
If GET DIAGNOSTICS produces an error condition (because the command is properly parsed but not correctly used), the diagnostics area is not emptied, and the new condition is added.
To read information from a condition, the CONDITION keyword must be specified and it must be followed by the condition number. This number can be specified as a constant value or as a variable. The first condition's index is 1. If the error condition does not exist, the variables will not change their value and a 1758 error will be produced ("Invalid condition number").
The condition properties that can be read with GET DIAGNOSTICS are the same that can be set with SIGNAL and RESIGNAL statements. They are explained in the diagnostics area page. However, there is one more property: RETURNED_SQLSTATE, which indicates the condition's SQLSTATE.
For a list of SQLSTATE values and MariaDB error codes, see MariaDB Error Codes.
The type for all the condition properties is VARCHAR(64), except for MYSQL_ERRNO, whose valid range is 1 to 65534.
You can use the ROW_NUMBER property to retrieve the row number, too, even if the error text does not mention it. This property is named ERROR_INDEX . ROW_NUMBER is a reserved word.
There is no way, short of parsing the error text, to know in what row an error had happened.
In the following example, a statement generates two warnings, and GET DIAGNOSTICS is used to get the number of warnings:
CREATE TABLE `test`.`t` (`c` INT) ENGINE = x;
Query OK, 0 rows affected, 2 warnings (0.19 sec)
GET DIAGNOSTICS @num_conditions = NUMBER;
SELECT @num_conditions;
+-----------------+
| @num_conditions |
+-----------------+
| 2 |
+-----------------+Then we can see the warnings:
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;
+-----------+--------+----------------------------+
| @sqlstate | @errno | @text |
+-----------+--------+----------------------------+
| 42000 | 1286 | Unknown storage engine 'x' |
+-----------+--------+----------------------------+
GET DIAGNOSTICS CONDITION 2 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;
+-----------+--------+-------------------------------------------+
| @sqlstate | @errno | @text |
+-----------+--------+-------------------------------------------+
| HY000 | 1266 | Using storage engine InnoDB for table 't' |
+-----------+--------+-------------------------------------------+INSERT INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
GET DIAGNOSTICS CONDITION 1 @row_num= ROW_NUMBER; -- from MariaDB 10.7
SELECT @row_num;
+----------+
| @row_num |
+----------+
| 2 |
+----------+This page is licensed: CC BY-SA / Gnu FDL
SQLSTATE is a code which identifies SQL error conditions. It composed by five characters, which can be numbers or uppercase ASCII letters. An SQLSTATE value consists of a class (first two characters) and a subclass (last three characters).
There are three important standard classes. They all indicate in which logical group of errors the condition falls. They match to a particular keyword which can be used with DECLARE HANDLER. Also, the SQLSTATE class determines the default value for the MYSQL_ERRNO and MESSAGE_TEXT condition properties.
'00' means 'success'. It can not be set in any way, and can only be read via the API.
'01' contains all warnings, and matches to the SQLWARNING keyword. The default MYSQL_ERRNO is 1642 and default MESSAGE_TEXT is 'Unhandled user-defined warning condition'.
'02' is the NOT FOUND class. The default MYSQL_ERRNO is 1643 and default MESSAGE_TEXT is 'Unhandled user-defined not found condition'.
All other classes match the SQLEXCEPTION keyword. The default MYSQL_ERRNO is 1644 and default MESSAGE_TEXT is 'Unhandled user-defined exception condition'.
The subclass, if it is set, indicates a particular condition, or a particular group of conditions within the class. However the '000' sequence means 'no subclass'.
For example, if you try to SELECT from a table which does not exist, a 1109 error is produced, with a '42S02' SQLSTATE. '42' is the class and 'S02' is the subclass. This value matches to the SQLEXCEPTION keyword. When FETCH is called for a cursor which has already reached the end, a 1329 error is produced, with a '02000' SQLSTATE. The class is '02' and there is no subclass (because '000' means 'no subclass'). It can be handled by a NOT FOUND handlers.
The standard SQL specification says that classes beginning with 0, 1, 2, 3, 4, A, B, C, D, E, F and G are reserved for standard-defined classes, while other classes are vendor-specific. It also says that, when the class is standard-defined, subclasses starting with those characters (except for '000') are standard-defined subclasses, while other subclasses are vendor-defined. However, MariaDB and MySQL do not strictly obey this rule.
To read the SQLSTATE of a particular condition which is in the diagnostics area, the GET DIAGNOSTICS statement can be used: the property is called RETURNED_SQLSTATE. For user-defined conditions (SIGNAL and RESIGNAL statements), a SQLSTATE value must be set via the SQLSTATE clause. However, SHOW WARNINGS and SHOW ERRORS do not display the SQLSTATE.
For user-defined conditions, MariaDB and MySQL recommend the '45000' SQLSTATE class.
'HY000' is called the "general error": it is the class used for builtin conditions which do not have a specific SQLSTATE class.
A partial list of error codes and matching SQLSTATE values can be found under MariaDB Error Codes.
This page is licensed: CC BY-SA / Gnu FDL