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

sql_mode=ORACLE: cursor%ROWTYPE in variable declarations

    XMLWordPrintable

Details

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

    Description

      This task will implement Oracle-stype %ROWTYPE declaration for cursors, for sql_mode=ORACLE.

      Example:

        CURSOR cur IS SELECT a,b FROM t1;
        rec cur%ROWTYPE;
      

      The record rec can store the entire row of data fetched from the cursor cur. There is no a need to specify column names and data types. They're automatically copied from the result set of the cursor cur.

      A complete working example:

      SET sql_mode=ORACLE;
      DROP TABLE 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');
      DROP PROCEDURE p1;
      DELIMITER $$
      CREATE PROCEDURE p1 AS
        CURSOR c IS SELECT a,b FROM t1;
      BEGIN
        DECLARE
          rec c%ROWTYPE; 
        BEGIN
          OPEN c;
          LOOP
            FETCH c INTO rec;
            EXIT WHEN c%NOTFOUND;
            SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c FROM dual;
          END LOOP;
          CLOSE c;
        END;
      END;
      $$
      DELIMITER ;
      CALL p1();
      

      Note, in Oracle it's possible to use %ROWTYPE variables before opening the referenced cursor, or even without opening it. Also, record variables declared with %ROWTYPE can be initialized by the assignment operator instead of FETCH.

      SET sql_mode=ORACLE;
      DROP PROCEDURE p1;
      DELIMITER $$
      CREATE PROCEDURE p1 AS
        CURSOR c IS SELECT 10 AS a,20 AS b FROM t1;
      BEGIN
        DECLARE
          rec c%ROWTYPE; 
        BEGIN
          rec.a:= 10;
          rec.b:= 20;
          SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c;
        END;
      END;
      $$
      DELIMITER ;
      CALL p1();
      

      %ROWTYPE can be used with open and closed cursors

      Also, in MariaDB it's now not possible to declare a variable after cursors (see MDEV-10598). So this won't work:

      DECLARE
        CURSOR cur IS SELECT a,b FROM t1;
        rec cur%ROWTYPE;
      BEGIN
        -- statements
      END;
      

      One will have to use an additional nested block:

      DECLARE
        CURSOR cur IS SELECT a,b FROM t1;
        DECLARE
          rec cur%ROWTYPE;
        BEGIN
          -- statements
        END;
      END;
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.