Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-12007

Allow ROW variables as a cursor FETCH target

    XMLWordPrintable

Details

    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.