This task will port a few tasks that were previously done for sql_mode=ORACLE, now for sql_mode=DEFAULT. See the list of "is blocked by" tasks for the list.

Under terms of this task we'll add a new syntax in SP variable declarations:

  DECLARE tmp TYPE OF t1.a;  -- Get the data type from the column {{a}} in the table {{t1}}

  DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}

  DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}

This non-standard syntax will provide the functionality available in some other databases, often referenced to as anchored data type:

Oracle

DECLARE va t1.a%TYPE;

DECLARE rec1 t1%ROWTYPE;

DECLARE rec2 cur1%ROWTYPE;

IBM:

DECLARE va ANCHOR DATA TYPE TO t1.a;

DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;

DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;

Firebird:
DECLARE va TYPE OF COLUMN t1.a;

Example: using `TYPE OF` to in combination with `MAX()` and `MIN()`

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (10);

INSERT INTO t1 VALUES (20);

DROP FUNCTION IF EXISTS f1;

DELIMITER $$

CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT

BEGIN

  DECLARE tmp TYPE OF t1.a;

  SELECT MAX(a) INTO tmp FROM t1;

  RETURN tmp = prm;

END;

$$

DELIMITER ;

SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;

Example: Using `TYPE OF` variables with a cursor

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1, t2;

DROP PROCEDURE IF EXISTS p1;

CREATE TABLE t1 (a INT);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1 VALUES (10),(20);

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE va TYPE OF t1.a;

  DECLARE cur CURSOR FOR SELECT a FROM t1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO va;

    IF done THEN

      LEAVE read_loop;

    END IF;

    INSERT INTO t2 VALUES (va);

  END LOOP;

  CLOSE cur;

END;

$$

DELIMITER ;

CALL p1();

SELECT * FROM t2;

Example: using `ROW TYPE OF` for a cursor

DROP TABLE IF EXISTS t1,t2;

DROP PROCEDURE IF EXISTS p1;

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

CREATE TABLE t2 LIKE t1;

INSERT INTO t1 VALUES (10,'b10');

INSERT INTO t1 VALUES (20,'b20');

INSERT INTO t1 VALUES (30,'b30');

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE c CURSOR FOR SELECT a,b FROM t1;

  BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE rec ROW TYPE OF c;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

    OPEN c;

    read_loop: LOOP

      FETCH c INTO rec;

      IF done THEN

        LEAVE read_loop;

      END IF;

      INSERT INTO t2 VALUES (rec.a, rec.b);

    END LOOP;

    CLOSE c;

  END;

END;

$$

DELIMITER ;

CALL p1();

SELECT * FROM t2;

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

| a    | b    |

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

|   10 | b10  |

|   20 | b20  |

|   30 | b30  |

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

Using `ROW TYPE OF` for a table

DROP TABLE IF EXISTS t1,t2;

DROP PROCEDURE IF EXISTS p1;

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

CREATE TABLE t2 LIKE t1;

INSERT INTO t1 VALUES (10,'b10');

INSERT INTO t1 VALUES (20,'b20');

INSERT INTO t1 VALUES (30,'b30');

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE c CURSOR FOR SELECT * FROM t1;

  BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE rec ROW TYPE OF t1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

    OPEN c;

    read_loop: LOOP

      FETCH c INTO rec;

      IF done THEN

        LEAVE read_loop;

      END IF;

      INSERT INTO t2 VALUES (rec.a, rec.b);

    END LOOP;

    CLOSE c;

  END;

END;

$$

DELIMITER ;

CALL p1();

SELECT * FROM t2;

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

| a    | b    |

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

|   10 | b10  |

|   20 | b20  |

|   30 | b30  |

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

Scope of this task

This task will implement anchored types only for local variables and parameters. Using TYPE OF in a stored function RETURNS clause will be done separately (similar to ~~MDEV-10577~~).

Variables declared with ROW TYPE OF will have the same features with implicit ROW variables (see ~~MDEV-10914~~). It will not be however possible to use ROW TYPE OF variables in LIMIT clause.

Real data type instantiation

The real data type of TYPE OF and ROW TYPE OF table_name will become known at the very beginning of the stored routine call, namely during the sp_rcontext::create() call. ALTER TABLE or DROP TABLE done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a ALTER TABLE or DROP TABLE statement.

The real data type of a ROW TYPE OF cursor_name variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor ROW TYPE OF variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

Referenced tables existence

The tables referenced in TYPE OF and ROW TYPE OF declarations will be checked for existence at the beginning of the stored routine call. CREATE PROCEDURE or CREATE FUNCTION will not check the referenced tables for existence.

[MDEV-12461] TYPE OF and ROW TYPE OF anchored data types for stored routine variables Created: 2017-04-06 Updated: 2017-08-18 Resolved: 2017-04-07
Status:	Closed
Project:	MariaDB Server
Component/s:	Parser, Stored routines
Fix Version/s:	10.3.0

[MDEV-12461] TYPE OF and ROW TYPE OF anchored data types for stored routine variables Created: 2017-04-06 Updated: 2017-08-18 Resolved: 2017-04-07

Example: using TYPE OF to in combination with MAX() and MIN()

Example: Using TYPE OF variables with a cursor

Example: using ROW TYPE OF for a cursor