This task will implement Oracle-stype %ROWTYPE declaration for cursors, for sql_mode=ORACLE.
Example:
CURSOR cur ISSELECT 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;
DROPTABLE t1;
CREATETABLE t1 (a INT, b VARCHAR(32));
INSERTINTO t1 VALUES (10,'b10');
INSERTINTO t1 VALUES (20,'b20');
INSERTINTO t1 VALUES (30,'b30');
DROPPROCEDURE p1;
DELIMITER $$
CREATEPROCEDURE p1 AS
CURSOR c ISSELECT 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;
DROPPROCEDURE p1;
DELIMITER $$
CREATEPROCEDURE p1 AS
CURSOR c ISSELECT 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 ISSELECT a,b FROM t1;
rec cur%ROWTYPE;
BEGIN
-- statements
END;
One will have to use an additional nested block:
DECLARE
CURSOR cur ISSELECT a,b FROM t1;
DECLARE
rec cur%ROWTYPE;
BEGIN
-- statements
END;
END;
Attachments
Issue Links
blocks
MDEV-10581sql_mode=ORACLE: Explicit cursor FOR LOOP
Closed
MDEV-12461TYPE OF and ROW TYPE OF anchored data types for stored routine variables
Closed
is blocked by
MDEV-10914ROW data type for stored routine variables
Closed
MDEV-12133sql_mode=ORACLE: table%ROWTYPE in variable declarations
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:
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.
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:
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:
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.
4/8 PLS-00402: alias required in SELECT list of cursor to avoid
duplicate column names
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
Oracle implementation detais:
Variables referencing the same cursor can be assigned to each other both by the := assignment and the default value assignment:
rec1 c%ROWTYPE;
rec2 c%ROWTYPE;
BEGIN
rec1.a:=10;
rec2:= rec1;
rec3 c%ROWTYPE:=rec2;
/
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:
rec1 c1%ROWTYPE;
rec2 c2%ROWTYPE;
BEGIN
rec1.a:=10;
rec2:= rec1;
rec3 c3%ROWTYPE:=rec2;
/
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.
rec1 c1%ROWTYPE;
rec2 c2%ROWTYPE;
BEGIN
rec1.a:=10;
rec1.b:=11;
rec2:= rec1;
rec3 c3%ROWTYPE:=rec2;
/
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:
rec1 c%ROWTYPE;
rec2 rec_t;
BEGIN
rec1.a:=10;
rec2:=rec1;
rec1:=rec2;
rec3 c%ROWTYPE:=rec2;
rec4 rec_t:=rec1;
/
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:
rec1 c1%ROWTYPE;
rec2 t1%ROWTYPE;
BEGIN
rec1.a:=10;
rec2:=rec1;
rec1:=rec2;
rec3 c1%ROWTYPE:=rec2;
rec4 t1%ROWTYPE:=rec1;
/
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.
rec1 c1%ROWTYPE;
BEGIN
/
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.
AS
rec1 cur1%ROWTYPE;
BEGIN
/
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