Cursor execution process

The complete cursor execution process includes declaring a cursor (DECLARE), opening a cursor (OPEN), fetching the next row (FETCH), and closing a cursor (CLOSE).

Declare a cursor (DECLARE)

The syntax for declaring a cursor is as follows:

DECLARE cursor_name CURSOR FOR select_statement

This syntax associates the cursor with a SELECT statement (which cannot contain an INTO clause), which retrieves the rows to be traversed by the cursor. If you want to retrieve rows later, use the FETCH statement. In this case, the number of columns retrieved by the SELECT statement must 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 the cursors declared in a specified block must have unique names.

In most cases, using a cursor with the INFORMATION_SCHEMA table can provide information equivalent to SHOW statements.

Open a cursor (OPEN)

The syntax for opening a declared cursor is as follows:

OPEN cursor_name

Fetch the next row (FETCH)

The syntax for fetching the next row from the SELECT statement associated with the 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 variable var_name. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.

If no more rows are available, an "no data" condition occurs, indicated by an SQLSTATE value of "02000". To detect this condition, you can set an exception handler (or specify the NOT FOUND condition). Note that the SELECT or another FETCH statement may also trigger this condition, thereby invoking the handler. If you need to distinguish which operation caused this condition, place the operation in its own BEGIN END block so that it is associated only with its own handler.

Close a cursor (CLOSE)

The syntax for closing a previously opened cursor is as follows:

CLOSE cursor_name

If this statement is executed when the cursor is not open, an error occurs. If not explicitly closed, the cursor is closed when the BEGIN END block in which it was declared is executed.

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