MDEV-11952 Oracle-style packages: stage#5mariadb-10.3.5bb-10.3-compatibility

- CREATE PACKAGE [BODY] statements are now
  entirely written to mysql.proc with type='PACKAGE' and type='PACKAGE BODY'.
- CREATE PACKAGE BODY now supports IF NOT EXISTS
- DROP PACKAGE BODY now supports IF EXISTS
- CREATE OR REPLACE PACKAGE [BODY] is now supported
- CREATE PACKAGE [BODY] now support the DEFINER clause:

    CREATE DEFINER user@host PACKAGE pkg ... END;
    CREATE DEFINER user@host PACKAGE BODY pkg ... END;

- CREATE PACKAGE [BODY] now supports SQL SECURITY and COMMENT clauses, e.g.:

    CREATE PACKAGE p1 SQL SECURITY INVOKER COMMENT "comment" AS ... END;

- Package routines are now created from the package CREATE PACKAGE BODY
  statement and don't produce individual records in mysql.proc.

- CREATE PACKAGE BODY now supports package-wide variables.
  Package variables can be read and set inside package routines.
  Package variables are stored in a separate sp_rcontext,
  which is cached in THD on the first packate routine call.

- CREATE PACKAGE BODY now supports the initialization section.

- All public routines (i.e. declared in CREATE PACKAGE)
  must have implementations in CREATE PACKAGE BODY

- Only public package routines are available outside of the package

- {CREATE|DROP} PACKAGE [BODY] now respects CREATE ROUTINE and ALTER ROUTINE
  privileges

- "GRANT EXECUTE ON PACKAGE BODY pkg" is now supported

- SHOW CREATE PACKAGE [BODY] is now supported

- SHOW PACKAGE [BODY] STATUS is now supported

- CREATE and DROP for PACKAGE [BODY] now works for non-current databases

- mysqldump now supports packages

- "SHOW {PROCEDURE|FUNCTION) CODE pkg.routine" now works for package routines

- "SHOW PACKAGE BODY CODE pkg" now works (the package initialization section)

- A new package body level MDL was added

- Recursive calls for package procedures are now possible

- Routine forward declarations in CREATE PACKATE BODY are now supported.

- Package body variables now work as SP OUT parameters

- Package body variables now work as SELECT INTO targets

- Package body variables now support ROW, %ROWTYPE, %TYPE

1 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-package-innodb.result b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result
new file mode 100644
index 00000000000..50eb2dc6cd0
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result
@@ -0,0 +1,75 @@
+SET default_storage_engine=InnoDB;
+SET sql_mode=ORACLE;
+CREATE TABLE t1 (a INT, routine TEXT);
+SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+ENGINE
+InnoDB
+INSERT INTO t1 VALUES (10,'none');
+CREATE PACKAGE pkg1 AS
+PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+a INT;
+PROCEDURE p1 AS
+BEGIN
+a:=a+1;
+INSERT INTO t1 VALUES (a,'p1');
+END;
+BEGIN
+SELECT MAX(t1.a) FROM t1 INTO a;
+a:=a+1;
+INSERT INTO t1 VALUES (a,'pkg1 initialization');
+END;
+$$
+CALL pkg1.p1;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+10	none
+11	pkg1 initialization
+12	p1
+DELETE FROM t1;
+# sp-cache-invalidate
+START TRANSACTION;
+CALL pkg1.p1;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+NULL	pkg1 initialization
+NULL	p1
+ROLLBACK;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+DELETE FROM t1;
+# sp-cache-invalidate
+INSERT INTO t1 VALUES (20,'none');
+START TRANSACTION;
+CALL pkg1.p1;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+20	none
+21	pkg1 initialization
+22	p1
+COMMIT;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+20	none
+21	pkg1 initialization
+22	p1
+DELETE FROM t1;
+# sp-cache-invalidate
+INSERT INTO t1 VALUES (20,'none');
+START TRANSACTION;
+CALL pkg1.p1;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+20	none
+21	pkg1 initialization
+22	p1
+ROLLBACK;
+SELECT * FROM t1 ORDER BY a;
+a	routine
+20	none
+DELETE FROM t1;
+DROP PACKAGE pkg1;
+DROP TABLE t1;