What is a cursor?
A cursor allows real-time access to the data of each row in a query. This mechanism is very useful when connecting MySQL with applications or performing complex queries.
Declaration
DECLARE cursor_name CURSOR FOR select_statement
Example:
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
OPEN Statement
This command opens a previously declared cursor.
Syntax: OPEN cursor_name
Example: OPEN cur1;
FETCH Statement
Reading the results of a cursor is done with the FETCH command. This allows us to access the first row generated by the query. If the cursor is used again, it moves on to point to the second row, then to the third, and so on until the cursor has no more results to reference.
Including the FETCH command inside a loop allows reading all the results of a cursor. When the cursor reaches the end of the query results, the loop terminates. But ending a loop of this type requires a special stop condition in MySQL.
Syntax:
FETCH cursor_name INTO var_name [, var_name] ...
Example:
FETCH cur1 INTO a, b; where DECLARE a CHAR(16); DECLARE b,c INT
CLOSE Statement
Once all the cursor results have been read, we proceed to close and free memory space with CLOSE.
Syntax:
CLOSE cursor_name;
Example:
CLOSE cur1; Example
BEGIN DECLARE variable VARIABLE_TYPE; DECLARE error INT DEFAULT 0; DECLARE cursor1 cursor for SELECT X FROM Y; DECLARE CONTINUE handler for sqlstate '02000' set error = 1;
open cursor1;
repeat fetch cursor1 into variable; if not error then [DO SOMETHING] end if; until error end repeat;