MySQL Cursors

       Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors in MySQL have these properties:

    *    Asensitive: The server may or may not make a copy of its result table
    *    Read only: Not updatable
    *    Nonscrollable: Can be traversed only in one direction and cannot skip rows

Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers.


DECLARE cursor_name CURSOR FOR select_statement

This statement declares a cursor. Multiple cursors may be declared in a stored program, but each cursor in a given block must have a unique name.

The SELECT statement cannot have an INTO clause.


OPEN cursor_name

This statement opens a previously declared cursor.



FETCH cursor_name INTO var_name [, var_name] ...

This statement fetches the next row (if a row exists) using the specified open cursor, and advances the cursor pointer.

If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).



CLOSE cursor_name

This statement closes a previously opened cursor.
If not closed explicitly, a cursor is closed at the end of the compound statement in which it was declared. 





 Example:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;