The complete execution process of a cursor consists of the following operations: use the DECLARE statement to declare the cursor, use the OPEN statement to open the cursor, use the FETCH statement to fetch the next row, and use the CLOSE statement to close the cursor.
Declare a cursor
The syntax is as follows:
DECLARE cursor_name CURSOR FOR select_statement
This syntax associates a cursor with a SELECT statement that does not contain the INTO clause. The SELECT statement can retrieve rows to be traversed by the cursor. To fetch rows later, use the FETCH statement. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.
The cursor declaration must be prior to the handler declaration but after the variable and condition declaration. A stored program can contain multiple cursor declarations. However, a cursor declared in a specified block must have a unique name.
In most cases, you can use a cursor with the INFORMATION_SCHEMA table to obtain equivalent information as that obtained by the SHOW statement.
Open a cursor
The syntax is as follows:
OPEN cursor_name
Fetch the next row
To fetch the next row of the SELECT statement associated with the specified cursor, which must have been opened, and advance the pointer of the cursor, use the following syntax:
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
If the row exists, the retrieved column is stored in the var_name variable. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.
If no more row is available, the value of SQLSTATE will be 02000, which indicates no data. To detect this situation, you can set an exception handler or set a NOT FOUND condition. Note that the SELECT statement or other FETCH statements may also encounter the same situation, which will then cause the execution of the exception handler. To identify the operation that causes this situation, put this operation in its BEGIN END block so that the operation is associated only with its own exception handler.
Close a cursor
The syntax is as follows:
CLOSE cursor_name
If you execute this statement on a cursor that is not opened, an error is returned. If a cursor is not explicitly closed, it will be closed when the execution of the BEGIN END block where this cursor is declared is completed.
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