PL/SQL parser (MDEV-10142)

[MDEV-12011] sql_mode=ORACLE: cursor%ROWTYPE in variable declarations Created: 2017-02-07  Updated: 2020-08-27  Resolved: 2017-04-04

Status: Closed
Project: MariaDB Server
Component/s: Parser, Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.3.0

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Blocks
blocks MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR ... Closed
blocks MDEV-12461 TYPE OF and ROW TYPE OF anchored data... Closed
is blocked by MDEV-10914 ROW data type for stored routine vari... Closed
is blocked by MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in var... Closed
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;



 Comments   
Comment by Alexander Barkov [ 2017-02-09 ]

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

Comment by Alexander Barkov [ 2017-03-10 ]

Pushed to bb-10.2-compatibility

Comment by Michael Widenius [ 2017-03-11 ]

Review not done

Comment by Michael Widenius [ 2017-03-11 ]

Now review done. Ok to push after review fixes.

Comment by Alexander Barkov [ 2017-04-04 ]

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

Generated at Thu Feb 08 07:54:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.