Details

    • Sprint:
      10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

      Description

      We'll allow ROW type variables as FETCH targets:

      FETCH cur INTO rec;
      

      where cur is a CURSOR and rec is a ROW type SP variable.
      Note, currently an attempt to use FETCH for a ROW type variable returns this error:

      ERROR 1328 (HY000): Incorrect number of FETCH variables
      

      FETCH from a cursor cur into a ROW variable rec will work as follows:

      • The number of fields in cur must match the number of fields in rec. Otherwise, an error is reported.
      • Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
      • Field names in rec are not important and can differ from field names in cur.

      A complete example for sql_mode=ORACLE:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b VARCHAR(32));
      INSERT INTO t1 VALUES (10,'b10');
      INSERT INTO t1 VALUES (20,'b20');
      INSERT INTO t1 VALUES (30,'b30');
       
      SET sql_mode=oracle;
      DROP PROCEDURE IF EXISTS p1;
      DELIMITER $$
      CREATE PROCEDURE p1 AS
        rec ROW(a INT, b VARCHAR(32));
        CURSOR c IS SELECT a,b FROM t1;
      BEGIN
        OPEN c;
        LOOP
          FETCH c INTO rec;
          EXIT WHEN c%NOTFOUND;
          SELECT ('rec=(' || rec.a ||','|| rec.b||')');
        END LOOP;
        CLOSE c;
      END;
      $$
      DELIMITER ;
      CALL p1();
      

      A complete example for sql_mode=DEFAULT:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b VARCHAR(32));
      INSERT INTO t1 VALUES (10,'b10');
      INSERT INTO t1 VALUES (20,'b20');
      INSERT INTO t1 VALUES (30,'b30');
       
      SET sql_mode=DEFAULT;
      DROP PROCEDURE IF EXISTS p1;
      DELIMITER $$
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE rec ROW(a INT, b VARCHAR(32));
        DECLARE c CURSOR FOR SELECT a,b FROM t1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN c;
      read_loop:
        LOOP
          FETCH c INTO rec;
          IF done THEN
            LEAVE read_loop;
          END IF;
          SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
        END LOOP;
        CLOSE c;
      END;
      $$
      DELIMITER ;
      CALL p1();
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: