Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
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
-
MDEV-10914
ROW data type for stored routine variables
-
- Closed
-
-
MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables
-
- Closed
-
- is blocked by
-
-
- Closed
-
- relates to
-
MDEV-14139
Anchored data types for variables
-
- Closed
-