The complete process for cursor execution includes declaring the cursor (DECLARE), opening the cursor (OPEN), fetching the next row (FETCH), and closing the cursor (CLOSE).
DECLARE cursor
The syntax for declaring a cursor is as follows:
DECLARE cursor_name CURSOR FOR select_statement
This syntax associates a cursor with a SELECT statement (which cannot contain an INTO clause), and the SELECT statement retrieves the rows that the cursor will traverse. To fetch rows later, use the FETCH statement, which requires that the number of columns retrieved by the SELECT statement match the number of output variables specified in the FETCH statement.
Cursor declarations must appear before handler declarations and after variable and condition declarations. A stored program can contain multiple cursor declarations, but cursors declared within the same block must have unique names.
In most cases, using a cursor with the INFORMATION_SCHEMA tables can yield information equivalent to that obtained by using SHOW statements.
Open cursor (OPEN)
The syntax for opening a declared cursor is as follows:
OPEN cursor_name
FETCH the next row
The syntax for fetching the next row of the SELECT statement associated with a specified cursor (which must be open) and advancing the cursor pointer is as follows:
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
If a row exists, the retrieved columns are stored in the named variables var_name. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.
When no more rows are available, a "no data" condition occurs, and the SQLSTATE value is set to "02000". To detect this situation, you can set up an exception handler (or a NOT FOUND condition handler). Note that a SELECT or another FETCH statement may also trigger the same condition, causing the handler to execute. If it is necessary to determine which operation caused this condition, enclose that operation in its own BEGIN END block so that it is associated only with its own handler.
Close cursor (CLOSE)
The syntax for closing a previously opened cursor is as follows:
CLOSE cursor_name
An error occurs if this statement is executed when the cursor is not open. If not explicitly closed, the cursor will be closed automatically when the BEGIN END block in which it was declared finishes executing.
Examples
obclient> DELIMITER //
obclient> CREATE PROCEDURE hr_curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE x, y, z INT;
DECLARE cur1 CURSOR FOR SELECT id,salary FROM hr.emp;
DECLARE cur2 CURSOR FOR SELECT avg_sal FROM hr.avg;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO x, y;
FETCH cur2 INTO z;
IF done THEN
LEAVE read_loop;
END IF;
IF y < z THEN
INSERT INTO hr.low_sal VALUES (x,y);
ELSE
INSERT INTO hr.high_sal VALUES (x,z);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END //
Query OK, 0 rows affected