PL/SQL parser (MDEV-10142)

[MDEV-10577] sql_mode=ORACLE: %TYPE in variable declarations Created: 2016-08-17  Updated: 2018-08-31  Resolved: 2016-12-06

Status: Closed
Project: MariaDB Server
Component/s: Parser, Stored routines
Affects Version/s: None
Fix Version/s: 10.3.0

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Blocks
blocks MDEV-10914 ROW data type for stored routine vari... Closed
blocks MDEV-12461 TYPE OF and ROW TYPE OF anchored data... Closed
is blocked by MDEV-11245 Move prepare_create_field and sp_prep... Closed
Relates
relates to MDEV-14139 Anchored data types for variables Closed
Sprint: 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.

 Comments   
Comment by Alexander Barkov [ 2017-04-06 ]

Oracle's %TYPE syntax is similar to:

  • IBM: 

    DECLARE va ANCHOR DATA TYPE TO t1.a;

  • Firebird: 

    DECLARE va TYPE OF COLUMN t1.a;

Generated at Thu Feb 08 07:43:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.