Package-wide TYPE for variable declarations

This tasks is a subtask of MDEV-13139.

Under terms of this task we'll implement a way to declare SP variables using TYPE declarations from package specifications.

For example:

This package specification declares a public data type varchar_array:

SET sql_mode=ORACLE;

DELIMITER $$

CREATE OR REPLACE PACKAGE pkg AS

  TYPE varchar_array IS TABLE OF VARCHAR(2000) INDEX BY INTEGER;

END;

$$

DELIMITER ;

This procedure uses the data type varchar_array using a qualified notation:

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1 AS

  v1 pkg.varchar_array; -- 2-step qualified data type

  v2 test.pkg.varchar_array; -- 3-step qualified data type

BEGIN

  v1(0):='test';

  SELECT v1(0);

END;

$$

DELIMITER ;

CALL p1();

+------+

| v(0) |

+------+

| test |

+------+

Where package-wide types will be available

• Variabe type:

  DECLARE

  var pkg1.type1;

• RETURN type for a package function:

  CREATE FUNCTION .. RETURN pkg1.type1 ...

• Parameter type for a package routine:

  PROCEDURE p1(param1 pkg1.type1);

• Assoc array element type:

  TYPE assoc1_t IS TABLE OF pkg1.type1 ...

• REF CURSOR RETURN type:

  TYPE cur1_t IS REF CURSOR RETURN pkg1.type1;

Note, package-wide types won't be available for schema public routine parameter types and for schema public function RETURN types. These types are exposed to INFORMATION_SCHEMA.PARAMETERS. The code is not ready to handle this properly yet.

Inter-package type definitions - no mariadb-dump support

SET sql_mode=ORACLE;

DELIMITER $$

CREATE PACKAGE z_pkg AS TYPE base_t IS RECORD (a INT, b VARCHAR(10)); END;$$

CREATE PACKAGE a_pkg AS TYPE arr_t IS TABLE OF z_pkg.base_t INDEX BY INTEGER; END;$$

DELIMITER ;

Package a_pkg dumped first before package z_pkg thus it shows:

ERROR 4161 Unknown data type error

while restoring the dumped file.

We won't implement a correct order in mariadb-dump for now.
If one needs inter-package type dependencies, please dump and restore mysql.proc instead.