Examples Using Cursors

DROP PROCEDURE IF EXISTS prc_cursor1;
SET DELIMITER @
CREATE PROCEDURE prc_cursor1
    RETURNS tmptable(number INT, name STRING, position STRING, team STRING)
AS
    VAR l_number INT, l_name STRING, l_position STRING, l_team STRING;
    DECLARE cursor1 CURSOR
        FOR SELECT NUMBER, NAME, POSITION, TEAM
            FROM hockey ORDER BY NUMBER LIMIT 3;
    OPEN cursor1;
    FETCH cursor1 INTO l_number, l_name, l_position, l_team;
    WHILE ( FETCH_STATUS() = 0 )
        INSERT INTO tmptable VALUES (l_number, l_name, l_position, l_team);
        FETCH cursor1 INTO l_number, l_name, l_position, l_team;
    END_WHILE;
    CLOSE cursor1;
END_PROCEDURE;
@
SET DELIMITER ;
CALL prc_cursor1;

  NUMBER        NAME       POSITION   TEAM
 ------- ---------------- --------- ------

    1    MAX SUMMIT        Fan      Bruins
   11    GREGORY CAMPBELL  Forward  Bruins
   17    MILAN LUCIC       Forward  Bruins

DROP PROCEDURE IF EXISTS prc_cursor1;
SET DELIMITER @
CREATE PROCEDURE prc_cursor1
AS
    VAR l_number INT, l_name STRING, l_position STRING, l_team STRING;
    DECLARE cursor1 CURSOR
        FOR SELECT NUMBER, NAME, POSITION, TEAM
            FROM hockey LIMIT 3 FOR UPDATE;
    START TRANSACTION;
    OPEN cursor1;
    FETCH cursor1 INTO l_number, l_name, l_position, l_team;
    WHILE ( FETCH_STATUS() = 0 )
        UPDATE HOCKEY SET NUMBER = l_number+1000
                     WHERE CURRENT OF cursor1;
        FETCH cursor1 INTO l_number, l_name, l_position, l_team;
    END_WHILE;
    CLOSE cursor1;
    COMMIT;
END_PROCEDURE;
@
SET DELIMITER ;
SELECT NUMBER, NAME, POSITION, TEAM FROM hockey LIMIT 3;
  NUMBER        NAME       POSITION   TEAM
 ------- ---------------- --------- ------

   37    PATRICE BERGERON  Forward  Bruins
   48    CHRIS BOURQUE     Forward  Bruins
   11    GREGORY CAMPBELL  Forward  Bruins

CALL prc_cursor1;
SELECT NUMBER, NAME, POSITION, TEAM FROM hockey LIMIT 3;
 NUMBER        NAME       POSITION   TEAM
 ------- ---------------- --------- ------

  1037   PATRICE BERGERON  Forward  Bruins
  1048   CHRIS BOURQUE     Forward  Bruins
  1011   GREGORY CAMPBELL  Forward  Bruins