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.
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
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 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 statement and opened with an statement. Rows are read with a statement before the cursor is finally closed with a statement.
When FETCH is issued and there are no more rows to extract, the following error is produced:
To avoid problems, a statement is generally used. The HANDLER should handler the 1329 error, or the '02000' , or the NOT FOUND error class.
Only 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 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 select_statement where a stored procedure variable is allowed (select list, WHERE, HAVING, LIMIT etc). See and for syntax, and below for an example.
Cursors cannot have parameters.
This page is licensed: CC BY-SA / Gnu FDL
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 |
+------+------+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 , but not a .
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 for an example.
This page is licensed: GPLv2, originally from
OPEN cursor_name [expression[,...]];OPEN cursor_nameThis statement opens a which was previously declared with .
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 rows from a cursor.
See for an example.
This page is licensed: GPLv2, originally from