sql_mode=ORACLE: table%ROWTYPE in variable declarations

This task will implement Oracle-stype table%ROWTYPE declarations, for sql_mode=ORACLE.

Example:

rec t1%ROWTYPE;

The record variable rec can store the entire row of the data fetched from the table t1. There is no a need to specify column names and data types. They're automatically copied from t1.

Under scope of this task, we'll implement table%ROWTYPE for routine variables. Using table%ROWTYPE for routine parameters and function return values will be done separately.

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');

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  rec t1%ROWTYPE; 

  CURSOR c IS SELECT * FROM t1;

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;

$$

DELIMITER ;

CALL p1();

rec=(10,b10)

Data types will be resolved at the very beginning of a routine execution, in sp_rcontext::create(). If the tables referenced in %ROWTYPE declarations are altered inside the routine, this will not affect structures of the referencing %ROWTYPE variables. In the below example the variable rec will have only two fields a and b, it will not have the field c. The fact that rec is declared after the ALTER statement does not matter. This implementations will be close to Oracle, who determines all data types at CREATE PROCEDURE time.

table%ROWTYPE and implicit ROW variables will be mutually assignable if they have the same number of fields. Note, Oracle has stricter rules, it also checks field names, but in a very strange way. See "Oracle implementation details". We won't check field names. Assignment will be done from left to right (the N'th source field is assigned to the N'th destination field).

In the below example all three variables rec0, rec1, rec2 will be mutually assignable, because they have the same number of fields:

SET sql_mode=ORACLE;

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (a INT, b VARCHAR(32));

CREATE TABLE t2 (c INT, d VARCHAR(32));

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  rec0 ROW(x INT,y INT);

  rec1 t1%ROWTYPE;

  rec2 t2%ROWTYPE;

BEGIN

  rec0:=rec1;

  rec0:=rec2;

  rec1:=rec0;

  rec1:=rec2;

  rec2:=rec0;

  rec2:=rec1;

END;

$$

DELIMITER ;

CALL p1();

It will be possible to pass a table%ROWTYPE variable into a routine with a compatible explicit ROW argument:

SET sql_mode=ORACLE;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(32));

DROP PROCEDURE IF EXISTS p1;

DROP PROCEDURE IF EXISTS p2;

DELIMITER $$

CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(20)))

AS

BEGIN

  SELECT a.a, a.b;

END;

$$

CREATE PROCEDURE p2 AS

  rec1 t1%ROWTYPE:=ROW(10,'bb');

BEGIN

  CALL p1(rec1);

END;

$$

DELIMITER ;

CALL p2();

+------+------+

| a.a  | a.b  |

+------+------+

| 10   | bb   |

+------+------+