Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12461

TYPE OF and ROW TYPE OF anchored data types for stored routine variables

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: