|
This task is related to MDEV-12252.
Under terms of this task we'll make it possible to return ROW data values from built-in hybrid functions:
Other hybrid functions (COALESCE, IFNULL, NULLIF, LEAST, GREATEST) are out of scope of this task, because they involve NULL tests or comparison, which are not well defined for the ROW data type.
This SQL script:
SET sql_mode=DEFAULT;
|
DROP PROCEDURE p1;
|
DELIMITER $$
|
CREATE PROCEDURE p1()
|
BEGIN
|
DECLARE rec0 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(12,'b2');
|
DECLARE rec1 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(10,'b0');
|
DECLARE rec2 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(11,'b1');
|
rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
|
SELECT rec0.a, rec0.b;
|
rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
|
SELECT rec0.a, rec0.b;
|
END;
|
$$
|
DELIMITER ;
|
CALL p1();
|
will return
+--------+--------+
|
| rec0.a | rec0.b |
|
+--------+--------+
|
| 10 | b0 |
|
+--------+--------+
|
+--------+--------+
|
| rec0.a | rec0.b |
|
+--------+--------+
|
| 12 | b2 |
|
+--------+--------+
|
So will this script:
SET sql_mode=ORACLE;
|
DROP PROCEDURE p1;
|
DELIMITER $$
|
CREATE PROCEDURE p1
|
AS
|
rec0 ROW (a INT, b VARCHAR(10)) := ROW(12,'b2');
|
rec1 ROW (a INT, b VARCHAR(10)) := ROW(10,'b0');
|
rec2 ROW (a INT, b VARCHAR(10)) := ROW(11,'b1');
|
BEGIN
|
rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
|
SELECT rec0.a, rec0.b;
|
rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
|
SELECT rec0.a, rec0.b;
|
END;
|
$$
|
DELIMITER ;
|
CALL p1();
|
An equivalent script in Oracle looks like:
SET SERVEROUTPUT ON;
|
DROP PROCEDURE p1;
|
CREATE PROCEDURE p1
|
AS
|
TYPE rec_t IS RECORD (a INT := 10, b VARCHAR(10) :='b0');
|
rec0 rec_t;
|
rec1 rec_t;
|
rec2 rec_t;
|
BEGIN
|
rec2.a:= 12;
|
rec2.b:= 'b2';
|
rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
|
DBMS_OUTPUT.PUT_LINE(rec0.a || ' ' || rec0.b);
|
rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
|
DBMS_OUTPUT.PUT_LINE(rec0.a || ' ' || rec0.b);
|
END;
|
/
|
CALL p1();
|
|
[MDEV-12307]