Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10577

sql_mode=ORACLE: %TYPE in variable declarations

Details

    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      When running in sql_mode=ORACLE, MariaDB should support %TYPE in variable declarations, in function and procedure parameter delcarations, and function RETURN clauses:

        tmp t1.a%TYPE;

      The above statement declares a variable that has the same data type with the column a in the table t1.

      Example:

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
       
      		 
      DROP FUNCTION f1;
      		 
      CREATE FUNCTION f1(prm t1.a%TYPE) RETURN t1.a%TYPE
      		 
      AS
      		 
        tmp t1.a%TYPE;
      		 
      BEGIN
      		 
        SELECT MAX(a) INTO tmp FROM t1;
      		 
        RETURN tmp + prm;
      		 
      END;
      		 
      /
      		 
      SELECT f1(5) FROM DUAL;


      SQL> 
           F1(5)
      		 
      ----------
      		 
      	25

      Scope of this task

      This task will implement %TYPE only for local variables and parameters. Using %TYPE in stored function RETURN clause will be done under terms of MDEV-11210.

      Oracle behavior

      1. %TYPE and missing tables during routine CREATE time

      Oracle checks if the referenced table exists during the routine CREATE time. If the referenced table does not exists, a warning is issued, but the routine is created.

      DROP TABLE t1;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
       a t1.a%TYPE := 123;
      		 
      BEGIN
      		 
        EXECUTE IMMEDIATE 'INSERT INTO t1 (a) VALUES(:a)' USING a;
      		 
      END;
      		 
      /
      		 
      SHOW ERRORS;


      LINE/COL ERROR
      		 
      -------- -----------------------------------------------------------------
      		 
      3/4	 PL/SQL: Item ignored
      		 
      3/4	 PLS-00201: identifier 'T1.A' must be declared
      		 
      5/3	 PL/SQL: Statement ignored
      		 
      5/59	 PLS-00320: the declaration of the type of this expression is
      		 
      	 incomplete or malformed

      The routine created with a missing identifier warning can be used as soon as the referenced table is created:

      CREATE TABLE t1 (a INT);
      		 
      CALL p1();
      		 
      SELECT * FROM t1;


      	 A
      		 
      ----------
      		 
             123

      Notice, the CALL statement inserted 123 into t1.

      2. Dropping the referenced table before the routine execution leads to an error on CALL

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a VARCHAR(10));
      		 
      DROP PROCEDURE p1;
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        a t1.a%TYPE:='xxx';
      		 
      BEGIN
      		 
        NULL;
      		 
      END;
      		 
      /
      		 
      CALL p1();
      		 
      DROP TABLE t1;
      		 
      CALL p1();
      		 
      SELECT * FROM user_errors;


      NAME			       TYPE	      SEQUENCE	     LINE   POSITION
      		 
      ------------------------------ ------------ ---------- ---------- ---------- 
      TEXT
      		 
      --------------------------------------------------------------------------------
      		 
      ATTRIBUTE MESSAGE_NUMBER
      		 
      --------- --------------
      		 
      P1			       PROCEDURE	     2		3	   5
      		 
      PL/SQL: Item ignored
      		 
      ERROR		       0
      		 
       
      		 
      P1			       PROCEDURE	     1		3	   5
      		 
      PLS-00201: identifier 'T1.A' must be declared
      		 
      ERROR		     201
      		 
       
      		 
      NAME			       TYPE	      SEQUENCE	     LINE   POSITION
      		 
      ------------------------------ ------------ ---------- ---------- ----------
      		 
      TEXT
      		 
      --------------------------------------------------------------------------------
      		 
      ATTRIBUTE MESSAGE_NUMBER
      		 
      --------- --------------

      3. Dropping the referenced table during the routine execution does not affect %TYPE variables

      DROP TABLE t1;
      		 
      DROP TABLE t2;
      		 
      CREATE TABLE t1 (a VARCHAR(10));
      		 
      CREATE TABLE t2 (a VARCHAR(10));
      		 
      DROP PROCEDURE p1;
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
      BEGIN
      		 
        EXECUTE IMMEDIATE 'DROP TABLE t1';
      		 
        DECLARE
      		 
          a t1.a%TYPE:='xxx';
      		 
        BEGIN
      		 
          EXECUTE IMMEDIATE 'INSERT INTO t2 VALUES (:1)' USING a;
      		 
        END;
      		 
      END;
      		 
      /
      		 
      CALL p1();
      		 
      SELECT * FROM t2;


      A
      		 
      ----------
      		 
      xxx

      Notice, the variable was declared after DROP TABLE.

      MariaDB behavior

      To make MariaDB reproduce Oracle's behavior as close as possible, we'll assing data type to %TYPE variables at sp_rcontext::create() time.

      Like Oracle, we'll not require the referenced tables to exists at the routine CREATE time. Unlike Oracle, we won't check the referenced tables and won't generate warnings.

      Attachments

        Issue Links

          blocks

          Technical task - A technical task. MDEV-10914 ROW data type for stored routine variables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

          • Major - Major loss of function.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-11245 Move prepare_create_field and sp_prepare_create_field() as methods to Column_definition

          • Major - Major loss of function.
          • Closed
          relates to

          Technical task - A technical task. MDEV-14139 Anchored data types for variables

          • Major - Major loss of function.
          • Closed

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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