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

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

            bar Alexander Barkov added a comment - - edited

            Oracle implementation detais:

            Variables referencing the same cursor can be assigned to each other both by the := assignment and the default value assignment:

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            CREATE TABLE t1 (a INT);
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c IS SELECT a FROM t1;
              rec1 c%ROWTYPE;
              rec2 c%ROWTYPE;
            BEGIN
              rec1.a:=10;
              rec2:= rec1;
              DECLARE
                rec3 c%ROWTYPE:=rec2;
              BEGIN
                DBMS_OUTPUT.PUT_LINE(rec1.a||','||rec2.a || ',' || rec3.a);
              END;
            END;
            /
            CALL p1();
            

            10,10,10
            

            Variables referencing cursors with compatible components (i.e. with fields having the same field names and compatible types) can also be assigned to each other both by the := assignment and the default value assignment:

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            DROP TABLE t2;
            DROP TABLE t3;
            CREATE TABLE t1 (a NUMERIC(10,1));
            CREATE TABLE t2 (a FLOAT);
            CREATE TABLE t3 (a INT);
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c1 IS SELECT a FROM t1;
              CURSOR c2 IS SELECT a FROM t2;
              rec1 c1%ROWTYPE;
              rec2 c2%ROWTYPE;
            BEGIN
              rec1.a:=10;
              rec2:= rec1;
              DECLARE
                CURSOR c3 IS SELECT a FROM t3;
                rec3 c3%ROWTYPE:=rec2;
              BEGIN
                DBMS_OUTPUT.PUT_LINE(rec1.a||','||rec2.a || ',' || rec3.a);
              END;
            END;
            /
            CALL p1();
            

            10,10,10
            

            In case if the fields with the same names are on different positions, assignment is still possible and is done by name rather than the field ordinal number. I.e. two cursor%ROWTYPE variables can be assigned if they have the same set of fields of the same types, but the order of the fields in the record is not important. This is different from variables declared with an explicit type TYPE rec_t IS RECORD and variables declared as table%ROWTYPE, which requires the same order of equally named fields.

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            DROP TABLE t2;
            DROP TABLE t3;
            CREATE TABLE t1 (a NUMERIC(10,1), b INT);
            CREATE TABLE t2 (b INT, a FLOAT);
            CREATE TABLE t3 (b INT, a INT);
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c1 IS SELECT * FROM t1;
              CURSOR c2 IS SELECT * FROM t2;
              rec1 c1%ROWTYPE;
              rec2 c2%ROWTYPE;
            BEGIN
              rec1.a:=10;
              rec1.b:=11;
              rec2:= rec1;
              DECLARE
                CURSOR c3 IS SELECT * FROM t3;
                rec3 c3%ROWTYPE:=rec2;
              BEGIN
                DBMS_OUTPUT.PUT_LINE('rec1=('||rec1.a || ',' || rec1.b || ')');
                DBMS_OUTPUT.PUT_LINE('rec2=('||rec2.a || ',' || rec2.b || ')');
                DBMS_OUTPUT.PUT_LINE('rec3=('||rec3.a || ',' || rec3.b || ')');
              END;
            END;
            /
            CALL p1();
            

            rec1=(10,11)
            rec2=(11,10)
            rec3=(11,10)
            

            Cursor %ROWTYPE variables and implicit RECORD variables with compatible structure are also mutually assignable, but only if the fields with the same names are in the same order:

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c IS SELECT * FROM t1;
              rec1 c%ROWTYPE;
              TYPE rec_t IS RECORD (a INT, b VARCHAR(32));
              rec2 rec_t;
            BEGIN
              rec1.a:=10;
              rec1.b:='b20';
              rec2:=rec1;
              rec1:=rec2;
              DBMS_OUTPUT.PUT_LINE('rec1=('||rec1.a || ',' || rec1.b || ')');
              DBMS_OUTPUT.PUT_LINE('rec2=('||rec2.a || ',' || rec2.b || ')');
              DECLARE
                rec3 c%ROWTYPE:=rec2;
                rec4 rec_t:=rec1;
              BEGIN
                DBMS_OUTPUT.PUT_LINE('rec3=('||rec3.a || ',' || rec3.b || ')');
                DBMS_OUTPUT.PUT_LINE('rec4=('||rec4.a || ',' || rec4.b || ')');
              END;
            END;
            /
            CALL p1();
            

            rec1=(10,b20)
            rec2=(10,b20)
            rec3=(10,b20)
            rec4=(10,b20)
            

            Cursor %ROWTYPE variables and table %ROWTYPE variables with compatible structure are also mutually assignable, but only if the fields with the same names are in the same order:

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c1 IS SELECT * FROM t1;
              rec1 c1%ROWTYPE;
              rec2 t1%ROWTYPE;
            BEGIN
              rec1.a:=10;
              rec1.b:='b20';
              rec2:=rec1;
              rec1:=rec2;
              DBMS_OUTPUT.PUT_LINE('rec1=('||rec1.a || ',' || rec1.b || ')');
              DBMS_OUTPUT.PUT_LINE('rec2=('||rec2.a || ',' || rec2.b || ')');
              DECLARE
                rec3 c1%ROWTYPE:=rec2;
                rec4 t1%ROWTYPE:=rec1;
              BEGIN
                DBMS_OUTPUT.PUT_LINE('rec3=('||rec3.a || ',' || rec3.b || ')');
                DBMS_OUTPUT.PUT_LINE('rec4=('||rec4.a || ',' || rec4.b || ')');
              END;
            END;
            /
            CALL p1();
            

            rec1=(10,b20)
            rec2=(10,b20)
            rec3=(10,b20)
            rec4=(10,b20)
            

            FETCH into a cursor %ROWTYPE variable requires that the number of fields in this variable matches the number of fields in the cursor query. Field names in the cursor %ROWTYPE variable are not important. Assignment is done by ordinal positions.

            SET SERVEROUTPUT ON;
            DROP TABLE t1;
            CREATE TABLE t1 (a INT, b VARCHAR(21));
            DROP PROCEDURE p1;
            CREATE PROCEDURE p1 AS
              CURSOR c1 IS SELECT a,b FROM t1;
              CURSOR c2 IS SELECT 11 AS c, 'd11' AS d FROM DUAL;
              rec1 c1%ROWTYPE;
            BEGIN
              OPEN c2;
              FETCH c2 INTO rec1;
              CLOSE c2;
              DBMS_OUTPUT.PUT_LINE('rec1=('||rec1.a || ',' || rec1.b || ')');
            END;
            /
            CALL p1();
            

            rec1=(11,d11)
            

            Duplicate column names in cursor%ROWTYPE variables

            Oracle returns a compile time error if a cursor references by a cursor%ROWTYPE has duplicate column names.

            DROP PROCEDURE p1;
            CREATE PROCEDURE p1
            AS
              CURSOR cur1 IS SELECT 'x' AS x, 'y' AS x FROM DUAL;
              rec1 cur1%ROWTYPE;
            BEGIN
              NULL;
            END;
            /
            SHOW ERRORS;
            

            LINE/COL ERROR
            -------- -----------------------------------------------------------------
            4/8	 PL/SQL: Item ignored
            4/8	 PLS-00402: alias required in SELECT list of cursor to avoid
            	 duplicate column names
            

            bar Alexander Barkov added a comment - - edited Oracle implementation detais: Variables referencing the same cursor can be assigned to each other both by the := assignment and the default value assignment: SET SERVEROUTPUT ON ; DROP TABLE t1; CREATE TABLE t1 (a INT ); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c IS SELECT a FROM t1; rec1 c%ROWTYPE; rec2 c%ROWTYPE; BEGIN rec1.a:=10; rec2:= rec1; DECLARE rec3 c%ROWTYPE:=rec2; BEGIN DBMS_OUTPUT.PUT_LINE(rec1.a|| ',' ||rec2.a || ',' || rec3.a); END ; END ; / CALL p1(); 10,10,10 Variables referencing cursors with compatible components (i.e. with fields having the same field names and compatible types) can also be assigned to each other both by the := assignment and the default value assignment: SET SERVEROUTPUT ON ; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; CREATE TABLE t1 (a NUMERIC (10,1)); CREATE TABLE t2 (a FLOAT ); CREATE TABLE t3 (a INT ); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c1 IS SELECT a FROM t1; CURSOR c2 IS SELECT a FROM t2; rec1 c1%ROWTYPE; rec2 c2%ROWTYPE; BEGIN rec1.a:=10; rec2:= rec1; DECLARE CURSOR c3 IS SELECT a FROM t3; rec3 c3%ROWTYPE:=rec2; BEGIN DBMS_OUTPUT.PUT_LINE(rec1.a|| ',' ||rec2.a || ',' || rec3.a); END ; END ; / CALL p1(); 10,10,10 In case if the fields with the same names are on different positions, assignment is still possible and is done by name rather than the field ordinal number. I.e. two cursor%ROWTYPE variables can be assigned if they have the same set of fields of the same types, but the order of the fields in the record is not important. This is different from variables declared with an explicit type TYPE rec_t IS RECORD and variables declared as table%ROWTYPE , which requires the same order of equally named fields. SET SERVEROUTPUT ON ; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; CREATE TABLE t1 (a NUMERIC (10,1), b INT ); CREATE TABLE t2 (b INT , a FLOAT ); CREATE TABLE t3 (b INT , a INT ); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c1 IS SELECT * FROM t1; CURSOR c2 IS SELECT * FROM t2; rec1 c1%ROWTYPE; rec2 c2%ROWTYPE; BEGIN rec1.a:=10; rec1.b:=11; rec2:= rec1; DECLARE CURSOR c3 IS SELECT * FROM t3; rec3 c3%ROWTYPE:=rec2; BEGIN DBMS_OUTPUT.PUT_LINE( 'rec1=(' ||rec1.a || ',' || rec1.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec2=(' ||rec2.a || ',' || rec2.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec3=(' ||rec3.a || ',' || rec3.b || ')' ); END ; END ; / CALL p1(); rec1=(10,11) rec2=(11,10) rec3=(11,10) Cursor %ROWTYPE variables and implicit RECORD variables with compatible structure are also mutually assignable, but only if the fields with the same names are in the same order: SET SERVEROUTPUT ON ; DROP TABLE t1; CREATE TABLE t1 (a INT , b VARCHAR (32)); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c IS SELECT * FROM t1; rec1 c%ROWTYPE; TYPE rec_t IS RECORD (a INT , b VARCHAR (32)); rec2 rec_t; BEGIN rec1.a:=10; rec1.b:= 'b20' ; rec2:=rec1; rec1:=rec2; DBMS_OUTPUT.PUT_LINE( 'rec1=(' ||rec1.a || ',' || rec1.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec2=(' ||rec2.a || ',' || rec2.b || ')' ); DECLARE rec3 c%ROWTYPE:=rec2; rec4 rec_t:=rec1; BEGIN DBMS_OUTPUT.PUT_LINE( 'rec3=(' ||rec3.a || ',' || rec3.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec4=(' ||rec4.a || ',' || rec4.b || ')' ); END ; END ; / CALL p1(); rec1=(10,b20) rec2=(10,b20) rec3=(10,b20) rec4=(10,b20) Cursor %ROWTYPE variables and table %ROWTYPE variables with compatible structure are also mutually assignable, but only if the fields with the same names are in the same order: SET SERVEROUTPUT ON ; DROP TABLE t1; CREATE TABLE t1 (a INT , b VARCHAR (32)); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c1 IS SELECT * FROM t1; rec1 c1%ROWTYPE; rec2 t1%ROWTYPE; BEGIN rec1.a:=10; rec1.b:= 'b20' ; rec2:=rec1; rec1:=rec2; DBMS_OUTPUT.PUT_LINE( 'rec1=(' ||rec1.a || ',' || rec1.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec2=(' ||rec2.a || ',' || rec2.b || ')' ); DECLARE rec3 c1%ROWTYPE:=rec2; rec4 t1%ROWTYPE:=rec1; BEGIN DBMS_OUTPUT.PUT_LINE( 'rec3=(' ||rec3.a || ',' || rec3.b || ')' ); DBMS_OUTPUT.PUT_LINE( 'rec4=(' ||rec4.a || ',' || rec4.b || ')' ); END ; END ; / CALL p1(); rec1=(10,b20) rec2=(10,b20) rec3=(10,b20) rec4=(10,b20) FETCH into a cursor %ROWTYPE variable requires that the number of fields in this variable matches the number of fields in the cursor query. Field names in the cursor %ROWTYPE variable are not important. Assignment is done by ordinal positions. SET SERVEROUTPUT ON ; DROP TABLE t1; CREATE TABLE t1 (a INT , b VARCHAR (21)); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR c1 IS SELECT a,b FROM t1; CURSOR c2 IS SELECT 11 AS c, 'd11' AS d FROM DUAL; rec1 c1%ROWTYPE; BEGIN OPEN c2; FETCH c2 INTO rec1; CLOSE c2; DBMS_OUTPUT.PUT_LINE( 'rec1=(' ||rec1.a || ',' || rec1.b || ')' ); END ; / CALL p1(); rec1=(11,d11) Duplicate column names in cursor%ROWTYPE variables Oracle returns a compile time error if a cursor references by a cursor%ROWTYPE has duplicate column names. DROP PROCEDURE p1; CREATE PROCEDURE p1 AS CURSOR cur1 IS SELECT 'x' AS x, 'y' AS x FROM DUAL; rec1 cur1%ROWTYPE; BEGIN NULL ; END ; / SHOW ERRORS; LINE/COL ERROR -------- ----------------------------------------------------------------- 4/8 PL/SQL: Item ignored 4/8 PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

            Pushed to bb-10.2-compatibility

            bar Alexander Barkov added a comment - Pushed to bb-10.2-compatibility

            Review not done

            monty Michael Widenius added a comment - Review not done

            Now review done. Ok to push after review fixes.

            monty Michael Widenius added a comment - Now review done. Ok to push after review fixes.

            Addressed Monty's review suggestions.
            Pushed a cleanup patch to bb-10.2-compatibility.

            bar Alexander Barkov added a comment - Addressed Monty's review suggestions. Pushed a cleanup patch to bb-10.2-compatibility.

            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.