Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-10592

sql_mode=ORACLE: TYPE .. TABLE OF for scalar data types

    XMLWordPrintable

Details

    Description

      Add support for TABLE OF data type declaration, for scalar data types.

      If TYPE declaration appears in AS or DECLARE section of a stored routine, then the data type is created temporary.
      A CREATE TYPE statement creates types permanently.

      The below example:

      • Declares a data type table_of_int_t as TABLE OF INT
      • Declares a table variable tbl of this type
      • Loads data from the table t1 into the table variable tbl
      • Appends one more record to tbl using methods tbl.extend and tbl.count
      • Iterates through all records in tbl and inserts data into t2

      DROP TABLE t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (10);
      INSERT INTO t1 VALUES (20);
      DROP TABLE t2;
      CREATE TABLE t2 (a INT);
      DROP PROCEDURE p1;
      CREATE PROCEDURE p1
      AS
        TYPE table_of_int_t IS TABLE OF INT;
        tbl table_of_int_t;
      BEGIN
        SELECT * BULK COLLECT INTO tbl FROM t1;
        tbl.extend;
        tbl(tbl.count):=1;
       
        FOR Y IN 1 .. tbl.count
        LOOP
          EXECUTE IMMEDIATE 'INSERT INTO t2 VALUES(:param)' USING tbl(Y)+1;
        END LOOP;
      END;
      /
      CALL p1();
      SELECT * FROM t2;
      

      	 A
      ----------
      	11
      	21
      	 2
      

      Passing TABLE-type values to stored procedures

      DROP PACKAGE g1;
      DROP TABLE t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1);
      CREATE PACKAGE g1 IS
      TYPE table_t IS TABLE OF INT;
      PROCEDURE consume(t IN table_t);
      PROCEDURE test_t;
      END;
      /
      CREATE PACKAGE BODY g1 IS
      PROCEDURE consume(t IN table_t) IS
      BEGIN
      FOR c IN 1 .. t.count
      LOOP
      EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(:p1)' USING t(c);
      END LOOP;
      NULL;
      END;
      PROCEDURE test_t IS
      t table_t;
      BEGIN
      SELECT * BULK COLLECT INTO t FROM t1;
      consume(t);
      END;
      END;
      /
      CALL g1.test_t();
      SELECT * FROM t1;

       

      	 A B
      ---------- --------------------
      	 1 b
      	 2 bb
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              5 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.