Syntax\n------\n\nDECLARE cursor_name CURSOR [(cursor_formal_parameter[,...])] FOR {select_statement | prepared_statement_name}\n\ncursor_formal_parameter:\n    [IN] name type [collate clause]\n\nDescription\n-----------\n\nThis 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.\n\nselect_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.\n\nA 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.\n\nStarting with MariaDB 12.3, the query can also be a prepared statement name, which allows the query to be dynamically composed. The SELECT statement cannot have an INTO clause.\n\nCursors must be declared before HANDLERs, but after local variables and CONDITIONs.\n\n> The DECLARE CURSOR statement provides a cursor to a specified SELECT statement.\n>\n> To declare a cursor variable type that can be connected with multiple queries at runtime (REF CURSOR), use DECLARE TYPE... IS REF CURSOR in Oracle mode. See DECLARE TYPE.\n\nDynamic Cursors\n\nStarting with MariaDB 12.3, a cursor can be declared for a prepared statement. This allows the use of Dynamic SQL within stored routines. The cursor is bound to a prepared statement name, which must be defined using the PREPARE statement before the cursor is opened.\n\n``sql\nCREATE OR REPLACE PROCEDURE p1(tab VARCHAR(64), min_id INTEGER)\nBEGIN\n  DECLARE v_id INT;\n  DECLARE v_c1 VARCHAR(100);\n  DECLARE no_data BOOL DEFAULT FALSE;\n  \n  -- 1. Declare cursor for the statement name 's1'\n  DECLARE c1 CURSOR FOR s1;\n  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = TRUE;\n\n  -- 2. Prepare the statement dynamically\n  PREPARE s1 FROM CONCAT('SELECT id, c1 FROM ', tab, ' WHERE id >= ?');\n  \n  -- 3. Open cursor and bind parameters\n  OPEN c1 USING min_id;\n  \n  fetch_loop: LOOP\n    FETCH c1 INTO v_id, v_c1;\n    IF no_data THEN\n      LEAVE fetch_loop;\n    END IF;\n    SELECT v_id, v_c1;\n  END LOOP;\n  \n  CLOSE c1;\n  DEALLOCATE PREPARE s1;\nEND;\n$$\n\nDELIMITER ;\n`\n\nParameters\n\nCursors 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).\n\nIN\n\nThe IN qualifier is supported in the cursor_formal_parameter part of the syntax.\n\nThe IN qualifier is not supported in the cursor_formal_parameter` part of the syntax.\n\nSee Cursor Overview for an example.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/declare-cursor