Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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.
Group multiple SQL statements into a logical block. This construct defines a compound statement, creating a new scope for variables and exception handling.
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 mode, every statement will be committed separately. If you are not running in autocommit mode, you must execute a or 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 with the command.
Changing the ; end-of-statement delimiter (for example, to//) allows ; to be used in a program body.
A compound statement within a can be . 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 , 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:
Below is an example of nested blocks in a stored procedure:
In this example, a variable, x is declared in the outter block. But in the inner block x is re-declared as a and an y variable is declared. The inner 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 doesn't try to read y, because it doesn't exist in that context.
This page is licensed: GPLv2, originally from
[begin_label:] BEGIN [NOT ATOMIC]
[statement_list]
END [end_label]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.
CASEThe 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 empty statement_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:
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 in CASE 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
Jump to a labeled point in the code. This Oracle-compatible statement transfers execution control to a specific label within the stored program.
Restart the current loop. This statement jumps back to the beginning of a LOOP, REPEAT, or WHILE block, skipping any remaining statements in the current iteration.
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:
Below is an example of how ITERATE might be used:
- Exits a loop (or any labeled code block)
This page is licensed: GPLv2, originally from
Execute code based on conditions. This control flow statement runs different blocks of SQL statements depending on whether a specified condition is true.
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 , which differs from the IF statement described above.
.
The .
This page is licensed: GPLv2, originally from
Identify blocks and loops for flow control. Labels provide names for BEGIN...END blocks or loops, allowing them to be targeted by LEAVE, ITERATE, or GOTO statements.
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:
and statements can be used to exit or repeat a portion of code identified by a label. They must be in the same , or which contains the target label.
Below is an example using a simple label that is used to exit a :
The following label is used to exit a procedure, and has an end part:
This page is licensed: CC BY-SA / Gnu FDL
Exit a stored function and return a value. This statement terminates function execution and sends the specified result back to the caller.
Compound statements can also be used outside of .
Using compound statements this way is subject to following limitations:
Only , , , , , statements may start a compound statement outside of stored programs.
must use the BEGIN NOT ATOMIC syntax (otherwise it'll be confused with that starts a transaction).
Exit a labeled block or loop immediately. This statement terminates the execution of the current loop or compound statement and continues after the block.
This statement is used to exit the flow control construct that has the given . The label must be in the same stored program, not in a caller procedure. LEAVE can be used within or loop constructs (, , ). In , and , LEAVE can refer to the outmost BEGIN ... END construct; in that case, the program exits the procedure. In , can be used instead.
Execute a block until a condition is met. This loop construct runs at least once and continues repeating as long as the UNTIL condition remains false.
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 . end_label cannot be given unless begin_label is also present. If both are present, they must be the same.
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASECASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASEDELIMITER |
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;
|BEGIN NOT ATOMIC
SET @a=1;
CREATE TABLE test.t1(a INT);
END|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;The following example shows that RETURN can return the result of a scalar subquery:
This page is licensed: GPLv2, originally from fill_help_tables.sql
RETURN exprCREATE FUNCTION users_count() RETURNS BOOL
READS SQL DATA
BEGIN
RETURN (SELECT COUNT(DISTINCT User) FROM mysql.user);
END;No dataLIMIT 1The 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.
If you want to use this construct with UNION you have to use the syntax:
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
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:
Example how to use IF:
Example of how to use WHILE loop:
This page is licensed: CC BY-SA / Gnu FDL
IF (1>0) THEN BEGIN NOT ATOMIC SELECT 1; END ; END IF;;LEAVE cannot be used to exit a DECLARE HANDLER block.
If you try to LEAVE a non-existing label, or if you try to LEAVE a HANDLER block, the following error will be produced:
The following example uses LEAVE to exit the procedure if a condition is true:
ITERATE - Repeats a loop execution
This page is licensed: GPLv2, originally from fill_help_tables.sql
See Delimiters in the mariadb client for more on client delimiter usage.
This page is licensed: GPLv2, originally from fill_help_tables.sql
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]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 |
+------+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
OPEN cursor_name [expression[,...]];OPEN cursor_namesearch_conditionA 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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]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)ERROR 1308 (42000): ITERATE with no matching label: <label_name>ERROR 1309 (42000): Redefining label <label_name>Initialize local variables within a stored program. This statement defines variables with a specific data type and optional 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 .
Local variables must be declared before CONDITIONs, 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 variables. It is not possible to use ROW TYPE OF variables in a 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. or 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 or 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. or will not check the referenced tables for existence.
TYPE OF and ROW TYPE OF :
This page is licensed: GPLv2, originally from
Create a simple loop construct. This statement repeatedly executes a block of code until explicitly terminated by a LEAVE statement.
[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 statement.
A LOOP statement can be . end_label cannot be given unlessbegin_label also is present. If both are present, they must be the same.
See in the client for more on delimiter usage in the client.
This page is licensed: GPLv2, originally from
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 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 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 , the statement can be used: the property is called RETURNED_SQLSTATE. For user-defined conditions ( and statements), a SQLSTATE value must be set via the SQLSTATE clause. However, and 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 built-in conditions which do not have a specific SQLSTATE class.
A partial list of error codes and matching SQLSTATE values can be found under .
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 for an example.
This page is licensed: GPLv2, originally from
SELECT col_name [, col_name] ...
INTO var_name [, var_name] ...
table_exprSELECT id, data INTO @x,@y
FROM test.t1 LIMIT 1;
SELECT * FROM t1 WHERE t1.a=@x AND t1.b=@ySELECT * INTO @x FROM (SELECT t1.a FROM t1 UNION SELECT t2.a FROM t2) dt;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)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|DELIMITER |
BEGIN NOT ATOMIC
DECLARE x INT DEFAULT 0;
WHILE x <= 10 DO
SET x = x + 1;
SELECT x;
END WHILE;
END|
DELIMITER ;LEAVE labelERROR 1308 (42000): LEAVE with no matching label: <label_name>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 |
+---+CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
ENDCREATE 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;
ENDCREATE PROCEDURE `test_sp`()
BEGIN
`my_label`:
LOOP
SELECT 'looping';
LEAVE `my_label`;
END LOOP;
SELECT 'out of loop';
END;CREATE PROCEDURE `test_sp`()
`my_label`:
BEGIN
IF @var = 1 THEN
LEAVE `my_label`;
END IF;
DO something();
END `my_label`;DECLARE var_name [, var_name] ... [[ROW] TYPE OF]] type [DEFAULT value]DECLARE name VARCHAR(5) DEFAULT 'monty';
DECLARE x INT DEFAULT 10;
DECLARE Y SMALLINT;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}}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
Specify actions for error conditions. This statement defines handler routines (CONTINUE or EXIT) to manage exceptions or warnings within a block.
The DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using .
Handlers must be declared after local variables, a CONDITION and a .
For a CONTINUE handler, execution of the current program continues after execution of the handler statement. For an EXIT handler, execution terminates for the 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 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 success rather than an error condition. For a list of SQLSTATE values and MariaDB error codes, see .
A condition name previously specified with DECLARE ... CONDITION. It must be in the same stored program. See .
SQLWARNING
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
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 statement.
An error that is handled by a DECLARE HANDLER construct can be issued again using the statement.
Below is an example using DECLARE HANDLER:
This page is licensed: GPLv2, originally from
Loop through a range or cursor result set. This control flow statement repeatedly executes a block of code for each item in a specified range or query.
Integer range FOR loop:
Explicit cursor FOR loop:
Explicit cursor FOR loop ():
Implicit cursor FOR loop:
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:
REVERSE integer range FOR loop:
Explicit cursor in :
This page is licensed: CC BY-SA / Gnu FDL
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 . Multiple cursors may be declared in a , but each cursor in a given block must have a unique name.
select_statement is not executed until the 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 , but after local variables and .
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 for an example.
This page is licensed: GPLv2, originally from
A cursor is a structure that allows you to go over records sequentially, and perform processing based on the result.
MariaDB permits cursors inside , 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.
Define data types for Oracle compatibility. This statement allows declaring PL/SQL-style record types and associative arrays within stored procedures.
FETCH cursor_name INTO var_name [, var_name] ...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_code[begin_label:]
FOR var_name IN [ REVERSE ] lower_bound .. upper_bound
DO statement_list
END FOR [ end_label ][begin_label:]
FOR record_name IN cursor_name [ ( cursor_actual_parameter_list)]
DO statement_list
END FOR [ end_label ][begin_label:]
FOR record_name IN cursor_name [ ( cursor_actual_parameter_list)]
LOOP
statement_list
END LOOP [ end_label ]Learn about diagnostics in programmatic compound statements. This section covers error handling and information retrieval within stored procedures and functions for effective debugging.
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'.
DECLARE TYPE adds support for Oracle-style INDEX BY tables (associative arrays) for stored routines and anonymous blocks, using this syntax:type_name supports explicit and anchored data types (for instance, t1.col1%TYPE).
The INDEX BY clause supports integer and string data types.
rec_type_name supports scalar and record data types.
It supports the following associative array methods:
FIRST — a function that returns the first key
LAST — a function that returns the last key
NEXT — a function that returns the key after the given one
PRIOR — a function that returns the key before the given one
COUNT — a function that returns the number of elements
EXISTS — a function that returns TRUE if the key exists
DELETE — a procedure that removes a specific key, or clears the array
In Oracle, associative arrays (called index-by tables) are sparse collections of elements indexed by keys, which can be integers or strings.
Here’s an example of how to declare an associative array in Oracle:
While the MariaDB implementation is largely aligned with Oracle’s implementation, there are a few differences:
Only literals as keys in the constructor: When using constructors, keys must be literals — Oracle allows expressions.
Collation control: Instead of NLS_SORT or NLS_COMP, MariaDB uses the SQL-standard COLLATE clause.
No nested associative arrays: Arrays of arrays are not supported.
These differences are largely rooted in architectural constraints — MariaDB is aiming at staying as close to Oracle semantics as possible while maintaining performance and predictability.
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 |
+------+DECLARE
TYPE type_name TABLE OF rec_type_name INDEX BY idx_type_nameDECLARE
TYPE array_t IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;
array array_t;
BEGIN
array(1) := 'Hello';
array(2) := 'World';
DBMS_OUTPUT.PUT_LINE(array(1));
END;DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
salary_list('Rajnisj') := 62000;
salary_list('James') := 78000;
name:= salary_list.FIRST;
WHILE name IS NOT NULL
LOOP
dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));
name:= salary_list.NEXT(name);
END LOOP;
END;
/CREATE TABLE t1 (a INT);
DECLARE
TYPE salary IS TABLE OF t1.a%TYPE INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
salary_list('Rajnisj') := 62000;
salary_list('James') := 78000;
name:= salary_list.FIRST;
WHILE name IS NOT NULL
LOOP
dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));
name:= salary_list.NEXT(name);
END LOOP;
END;
/DECLARE
TYPE person_t IS RECORD
(
first_name VARCHAR(64),
last_name VARCHAR(64)
);
person person_t;
TYPE table_of_peson_t IS TABLE OF person_t INDEX BY VARCHAR(20);
person_by_nickname table_of_peson_t;
nick VARCHAR(20);
BEGIN
person_by_nickname('Monty') := person_t('Michael', 'Widenius');
person_by_nickname('Serg') := person_t('Sergei ', 'Golubchik');
nick:= person_by_nickname.FIRST;
WHILE nick IS NOT NULL
LOOP
person:= person_by_nickname(nick);
dbms_output.put_line(nick || ' ' || person.first_name || ' '|| person.last_name);
nick:= person_by_nickname.NEXT(nick);
END LOOP;
/DROP TABLE persons;
CREATE TABLE persons (nickname VARCHAR(64), first_name VARCHAR(64), last_name VARCHAR(64));
INSERT INTO persons VALUES ('Serg','Sergei ', 'Golubchik');
INSERT INTO persons VALUES ('Monty','Michael', 'Widenius');
DECLARE
TYPE table_of_person_t IS TABLE OF persons%ROWTYPE INDEX BY persons.nickname%TYPE;
person_by_nickname table_of_person_t;
nickname persons.nickname%TYPE;
person persons%ROWTYPE;
BEGIN
FOR rec IN (SELECT * FROM persons)
LOOP
person_by_nickname(rec.nickname):= rec;
END LOOP;
nickname:= person_by_nickname.FIRST;
WHILE nickname IS NOT NULL
LOOP
person:= person_by_nickname(nickname);
dbms_output.put_line(person.nickname || ' ' || person.first_name || ' '|| person.last_name);
nickname:= person_by_nickname.NEXT(nickname);
END LOOP;
END;
/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:
To 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.
This page is licensed: CC BY-SA / Gnu FDL
The 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 page. However, there is one more property: RETURNED_SQLSTATE, which indicates the condition's .
For a list of SQLSTATE values and MariaDB error codes, see .
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 .
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:
Then we can see the warnings:
This page is licensed: CC BY-SA / Gnu FDL
Propagate error conditions. This statement allows a handler to pass an error condition back to the caller or modify the error information before passing it on.
The syntax of RESIGNAL and its semantics are very similar to . This statement can only be used within an error . It produces an error, like . RESIGNAL clauses are the same as SIGNAL, except that they all are optional, even . All the properties which are not specified in RESIGNAL, will be identical to the properties of the error that was received by the error . For a description of the clauses, see .
RESIGNAL, without any clauses, produces an error which is identical to the error that was received by .
If used out of a construct, RESIGNAL produces the following error:
If a contains a to another procedure, that procedure can use RESIGNAL, but trying to do this raises the above error.
If a contains a to another procedure, that procedure can use RESIGNAL.
For a list of SQLSTATE values and MariaDB error codes, see .
The following procedure tries to query two tables which don't exist, producing a 1146 error in both cases. Those errors will trigger the . 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.
The following procedure re-signals an error, modifying only the error message to clarify the cause of the problem.
This page is licensed: CC BY-SA / Gnu FDL
Define named error conditions. This statement associates a name with a specific SQLSTATE or MariaDB error code for easier handling in stored programs.
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 , or statement (as long as the statement is located in the same block).
Conditions must be declared after , but before and .
A condition_value for DECLARE ... CONDITION can be an 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:
For a list of SQLSTATE values and MariaDB error codes, see .
This page is licensed: GPLv2, originally from
Raise a custom error condition. This statement allows stored programs to generate specific error messages and SQLSTATEs to handle application logic exceptions.
SIGNAL empties the and produces a custom error. This statement can be used anywhere but is generally useful when used inside a . When the error is produced, it can be caught by a . If not, the current stored program, or the current statement, will terminate with the specified error.
Sometimes an error just needs to the same error it received, optionally with some changes. Usually, the statement is the most convenient way to do this.
error_condition
ERROR 1329 (02000): No data - zero rows fetched, selected, or processedCREATE 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 |
+------+------+[begin_label:]
FOR record_name IN ( select_statement )
DO statement_list
END FOR [ end_label ]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 |
+------+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 |
+------+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)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_NUMBERRESIGNAL [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_NAMECREATE 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 |
+-----------------+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 |
+----------+ERROR 1645 (0K000): RESIGNAL when handler not activeCREATE 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 existCREATE 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;ERROR 1407 (42000): Bad SQLSTATE: '00000'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:
If a property is specified more than once, an error like this will be produced:
If you specify a condition name which is not declared, an error like this will be produced:
If MYSQL_ERRNO is out of range, you will get an error like this:
Here's what happens if SIGNAL is used in the client to generate errors:
How to specify MYSQL_ERRNO and MESSAGE_TEXT properties:
The following code shows how to use user variables, local variables and character set conversion with SIGNAL:
How to use named error conditions:
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:
This page is licensed: CC BY-SA / Gnu FDL
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 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:
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 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 . 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'.
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 . 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:
: produces a custom error.
: after an error is produced, generates a modified version of that error.
The following statements read contents from the diagnostics area:
is the only way to read all information.
shows a summary of errors, warnings and notes.
shows a summary of errors.
can be used to handle error conditions within stored programs.
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 statement, the diagnostics area contains information about the prepared statement, if it produces error conditions.
This page is licensed: CC BY-SA / Gnu FDL
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_NAMEERROR 1407 (42000): Bad SQLSTATE: '123456'ERROR 1641 (42000): Duplicate condition information item 'MESSAGE_TEXT'ERROR 1319 (42000): Undefined CONDITION: cond_nameERROR 1231 (42000): Variable 'MYSQL_ERRNO' can't be set to the value of '0'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 conditionSIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='H
ello, world!';
ERROR 30001 (45000): Hello, world!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;CREATE PROCEDURE test_error(n INT)
BEGIN
DECLARE `too_big` CONDITION FOR SQLSTATE '45000';
IF n > 10 THEN
SIGNAL `too_big`;
END IF;
END;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;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)
For all other cases, including the '45000' value, default MYSQL_ERRNO is 1644 and default MESSAGE_TEXT is 'Unhandled user-defined exception condition'.
RETURNED_SQLSTATE
VARCHAR(5)
MYSQL_ERRNO
SMALLINT UNSIGNED
MESSAGE_TEXT
VARCHAR(512)
CLASS_ORIGIN
VARCHAR(64)
SUBCLASS_ORIGIN
VARCHAR(64)
CONSTRAINT_CATALOG
VARCHAR(64)