Skip to main content

Cursor execution process

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

Declare a cursor​

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), and the SELECT statement retrieves the rows that the cursor will traverse. If you want to extract 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 cursors declared in a specified block must have unique names.

In most cases, cursors based on INFORMATION_SCHEMA tables can provide information equivalent to SHOW statements.

Open a cursor​

The syntax for opening a declared cursor is as follows:

OPEN cursor_name

Fetch the next row​

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 fetched 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 up an exception handler (or use the NOT FOUND condition). Note that the SELECT or other FETCH statement may also trigger the same condition, leading to the execution of the handler. If you need to distinguish which operation caused the condition, place the operation in its own BEGIN END block to associate it with its own handler.

Close a cursor​

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 automatically closed when the BEGIN END block in which it was declared completes.

Example​

DELIMITER //

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

Contents

Edit this page