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 asensitive.
- 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.
- Asensitive 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.
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 processed
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.
From MariaDB 10.3.0, 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:
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 | +------+
From MariaDB 10.3.0
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;