[MDEV-12011] sql_mode=ORACLE: cursor%ROWTYPE in variable declarations - Jira

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.0
Component/s: Parser, Stored routines
Labels:
- Compatibility

Sprint:
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

blocks

MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP

Closed

MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

Closed

is blocked by

MDEV-10914 ROW data type for stored routine variables

Closed

MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2017-02-09 06:12 - 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

Alexander Barkov added a comment - 2017-02-09 06:12 - 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

Alexander Barkov added a comment - 2017-03-10 10:20

Pushed to bb-10.2-compatibility

Alexander Barkov added a comment - 2017-03-10 10:20 Pushed to bb-10.2-compatibility

Michael Widenius added a comment - 2017-03-11 09:58

Review not done

Michael Widenius added a comment - 2017-03-11 09:58 Review not done

Michael Widenius added a comment - 2017-03-11 11:05

Now review done. Ok to push after review fixes.

Michael Widenius added a comment - 2017-03-11 11:05 Now review done. Ok to push after review fixes.

Alexander Barkov added a comment - 2017-04-04 06:41

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

Alexander Barkov added a comment - 2017-04-04 06:41 Addressed Monty's review suggestions. Pushed a cleanup patch to bb-10.2-compatibility.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-02-07 10:10

Updated:: 2020-08-27 07:12

Resolved:: 2017-04-04 06:38

Git Integration

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

MariaDB Server