(mysql.info) cursors

Info Catalog

(mysql.info) conditions-and-handlers

(mysql.info) stored-procedure-syntax

(mysql.info) flow-control-constructs

 
 17.2.9 Cursors
 --------------
 

Menu

 
* declare-cursors              Declaring Cursors
* open                         Cursor `OPEN' Statement
* fetch                        Cursor `FETCH' Statement
* close                        Cursor `CLOSE' Statement
 
 Simple cursors are supported inside stored procedures and functions.
 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.
 
 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 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
 

Info Catalog

(mysql.info) conditions-and-handlers

(mysql.info) stored-procedure-syntax

(mysql.info) flow-control-constructs