Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
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