MySQL Cursors

DECLARE cursor_name CURSOR FOR select_statement

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name

Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors are currently asensitive, read-only, and non-scrolling. Asensitive means that the server may or may not make a copy of its result table.

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

Ex
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 SQLSTATE '02000' SET done = 1;

 OPEN cur1;
 OPEN cur2;

 REPEAT
   FETCH cur1 INTO a, b;
   FETCH cur2 INTO c;
   IF NOT done THEN
      IF b < c THEN
         INSERT INTO test.t3 VALUES (a,b);
      ELSE
         INSERT INTO test.t3 VALUES (a,c);
      END IF;
   END IF;
 UNTIL done END REPEAT;

 CLOSE cur1;
 CLOSE cur2;
END