Support PLPGSQL subtype

SUBTYPE subtype_name IS base_type
  { precision [, scale ] } [ NOT NULL ]

-- Scenario 1: Define subtype inside an anonymous block and use the
DECLARE
subtype varchar2_10 IS varchar2(10);
xx varchar2_10;
BEGIN
xx:='10';
raise notice '%',xx;
END;
NOTICE:  10
ANONYMOUS BLOCK EXECUTE

DECLARE
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
xx tyt_varchar2_10;
BEGIN
xx(1):='10';
xx(2):='x';
raise notice '%',xx;
END;
NOTICE:  {10,x}
ANONYMOUS BLOCK EXECUTE

-- Scenario 2: Define the subtype inside the procedure and use the
CREATE PROCEDURE test_subtype_proc IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
xx tyt_varchar2_10;
aa varchar2_10;
BEGIN
xx(1):='10';
xx(2):='x';
aa:='abc';
raise notice '%-%',xx,aa;
END;

BEGIN
test_subtype_proc;
END;
NOTICE:  {10,x}-abc
ANONYMOUS BLOCK EXECUTE

-- Scenario 3: Defining subtype inside a function and using it
CREATE FUNCTION test_subtype_func RETURN varchar2 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
xx tyt_varchar2_10;
aa varchar2_10;
BEGIN
xx(1):='10';
xx(2):='x';
aa:='abc';
RETURN xx(1)||'-'||xx(2)||'-'||aa;
END;

SELECT test_subtype_func;
 test_subtype_func
-------------------
 10-x-abc
(1 row)

-- Scenario 4: Defining subtype inside a package
CREATE PACKAGE test_subtype_pkg IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
END test_subtype_pkg;

-- Scenario 5: Define subtype inside the package and use the procedure inside the package as a parameter type
CREATE PACKAGE test_subtype_pkg1 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
PROCEDURE test_proc(a varchar2_10);
END test_subtype_pkg1;

CREATE PACKAGE test_subtype_pkg2 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
PROCEDURE test_proc(a test_subtype_pkg2.varchar2_10);
END test_subtype_pkg2;

CREATE PACKAGE test_subtype_pkg3 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
PROCEDURE test_proc;
END test_subtype_pkg3;
CREATE PACKAGE body test_subtype_pkg3 IS
PROCEDURE test_proc IS
xx varchar2_10;
BEGIN
xx:=3;
END;
END test_subtype_pkg3;

-- Scenario 6: Define subtype in package, call in external procedure
CREATE PACKAGE test_subtype_pkg4 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
END test_subtype_pkg4;

DECLARE
xx test_subtype_pkg4.varchar2_10;
BEGIN
xx:='10';
raise notice '%',xx;
END;

DECLARE
xx test_subtype_pkg4.tyt_varchar2_10;
BEGIN
xx(1):='10';
xx(2):='x';
raise notice '%',xx;
END;

-- Scenario 7: subtype default v
DECLARE
SUBTYPE empno IS VARCHAR2(10);
verb empno := 'run';
BEGIN
null;
END;

-- Scenario 8: Using subtype in the package's proc parameter
CREATE OR REPLACE package test_subtype_pkg5 IS
subtype varchar2_10 IS varchar2(10);
type tyt_varchar2_10 IS TABLE of varchar2_10;
END test_subtype_pkg5;

CREATE OR REPLACE package tst_pro IS
PROCEDURE tst_pro(a test_subtype_pkg5.varchar2_10);
END tst_pro;

CREATE OR REPLACE package body tst_pro IS
PROCEDURE tst_pro(a test_subtype_pkg5.varchar2_10) IS 
BEGIN 
null;
END;
END tst_pro;

-- Scenario 9: subtype as an element of record
CREATE OR REPLACE package pki_type as
subtype tstr IS varchar(60);
subtype tstatus IS varchar(3);
type t_data IS record(
cust_no tstr,
cust_flag tstatus);
type t_data_array IS TABLE of t_data INDEX BY binary_integer;
END pki_type;