Introduction of MySql Cursor

 Mysql supports curosr inside stored programs. This is an embedded SQL. Cursors have  some  properties:

i) Asensitive - Server may or may not make a copy of its result table.

ii) Its read only. It means it can't update.

iii) Nonscrollable: It can traversed only one direction, and rows can't skip.

To be noted: Cursor should must appear before handler declarations and after variable and condition declarations.

Example:


CREATE PROCEDURE myCurDemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE mycur1 CURSOR FOR SELECT id,data FROM test.table1;
  DECLARE mycur2 CURSOR FOR SELECT id FROM test.table2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN mycur1;
  OPEN mycur2;

  read_loop: LOOP
    FETCH mycur1 INTO a, b;
    FETCH mycur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.table3 VALUES (a,b);
    ELSE
      INSERT INTO test.table3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE mycur1;
  CLOSE mycur2;
END;