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

          blocks

          Technical task - A technical task. MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

          • Major - Major loss of function.
          • Closed

          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.