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
Add support for the ROW data type variables in stored routines according to this SQL Standard syntax:
<row type> ::= ROW <row type body>
|
|
<row type body> ::= <left paren> <field definition> [ { <comma> <field definition> }... ] <right paren>
|
|
<field definition> ::= <field name> <data type>
|
|
<data type> ::= <predefined type>
|
and add support for a new expression type, a ROW field reference, as follows:
<field reference> ::= <row variable> <period> <field name>
|
where <row variable> is a stored routine variable declared using <row type>.
Example:
DELIMITER $$
|
CREATE PROCEDURE p1() |
BEGIN
|
DECLARE a ROW (c1 INT, c2 VARCHAR(10)); |
SET a.c1= 10; |
SET a.c2= 'test'; |
INSERT INTO t1 VALUES (a.c1, a.c2); |
END; |
$$
|
DELIMITER ;
|
CALL p1();
|
This task is needed to create infrastructure for MDEV-10593
This task is also needed as a prerequisite for MDEV-10581, where this statement:
FOR rec IN cursor |
will automatically declare an index variable rec of the ROW data type, according to the cursor structure.
Row data type features:
- Declaration of a ROW type stored routine variable (both local variables and parameters)
- Declaration of a ROW type stored procedure OUT parameter
- Default values in a ROW type variable declaration (e.g. DEFAULT ROW(1,2))
- Assignment of a ROW type variable from another ROW type variable (using the SET command and the := operator in sql_mode=ORACLE)
- Assignment of a ROW type variable from a ROW() function result (using the SET command and the := operator in sql_mode=ORACLE)
- Passing a ROW type variable and a ROW() function result to stored routines
- Comparison of a ROW type variable to another ROW type variable
- Comparison of a ROW type variable to ROW() function
Row field features:
ROW fields (members) will act as normal variables, and will be able to appear in all query parts where an SP variable is allowed:
- assignment (using the SET command)
SET a.x= 10, a.y=20, a.z= b.z;
- assignment (sql_mode=ORACLE specific syntax)
a.x:= 10;
a.x:= b.x;
- passing to functions and operators
SELECT f1(rec.a), rec.a<10;
- clauses: select list, WHERE, HAVING, LIMIT, etc
SELECT var.a, t1.b FROM t1 WHERE t1.b=var.b LIMIT var.c;
- INSERT values
INSERT INTO t1 VALUES (rec.a, rec.b, rec.c);
- SELECT .. INTO targets
SELECT a,b INTO rec.a, rec.b FROM t1 WHERE t1.id=10;
- Dynamic SQL out parameters (EXECUTE and EXECUTE IMMEDIATE)
EXECUTE IMMEDIATE 'CALL proc_with_out_param(?)' USING rec.a;
Features not supported:
The following features are out of scope of this task and will be implemented separately:
- Returning a ROW type expression from a stored function (see
MDEV-12252). This will need some grammar change to support field names after parentheses:SELECT f1().x FROM DUAL;
- Returning a ROW type expression from a built-in hybrid type function, such as CASE, IF, etc.
- ROW of ROWs
Attachments
Issue Links
- blocks
-
MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
- Closed
-
MDEV-10593 sql_mode=ORACLE: TYPE .. AS OBJECT: basic functionality
- Open
-
MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations
- Closed
-
MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
- Closed
-
MDEV-12252 ROW data type for stored function return values
- Closed
- is blocked by
-
MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
- Closed
- relates to
-
MDEV-11210 TYPE OF and %TYPE in stored function RETURN data type
- Open
-
MDEV-13527 Crash when EXPLAIN SELECT .. INTO row_sp_variable.field
- Closed
-
MDEV-31250 ROW variables do not get assigned from subselects
- Closed
-
MDEV-32380 Array data type for stored routnes
- Stalled