使用子类型

目前PL/SQL中支持的子类型操作包括：

1. 子类型嵌套，嵌套时会对约束进行继承。
2. 通过INTO语句更新子类型数据。
3. 基类型更改，子类型值集随基类型更改。

示例：使用子类型

-- 子类型嵌套
DECLARE
    SUBTYPE My_Subtype_0 IS char(4);
    SUBTYPE My_Subtype0 IS My_Subtype_0;
    SUBTYPE My_Subtype1 IS My_Subtype0;
    a1 My_Subtype1;
BEGIN
    a1 :='abcd';
    DBMS_OUTPUT.PUT_LINE(a1);
END;
/

DECLARE
    SUBTYPE My_Subtype_0 IS char(4) NOT NULL;
    SUBTYPE My_Subtype0 IS My_Subtype_0;
    SUBTYPE My_Subtype1 IS My_Subtype0;
    a1 My_Subtype1;
BEGIN
    a1 := NULL;
END;
ERROR, SUBTYPE 声明为 NOT NULL 的变量必须有初始化赋值
     #1: at Program  line 4 (a1 My_Subtype1;)

/


-- 通过INTO语句更新子类型数据
DECLARE
    SUBTYPE My_Subtype1 IS int;
    a1 My_Subtype1;
    a2 My_Subtype1;
    c VARCHAR(30);
BEGIN
    a1 :=1;
    select a1 into a2 from dual;
    select a2 into c from dual;
    DBMS_OUTPUT.PUT_LINE(c);
END;
/

-- 基类型更改，子类型值集随基类型更改
DROP PACKAGE pack1;
DROP PACKAGE pack2;

create or replace package PACK1 as
  SUBTYPE subt1 is char(4);
  tety1 subt1;
  function func(a1 int) return subt1;
end PACK1;
/
create or replace package body PACK1 as
  function func(a1 int) return subt1 AS
    BEGIN
     return null;
    END func;
end PACK1;
/

create or replace package PACK2 as
  tety2 PACK1.subt1;
  function func(a1 VARCHAR) return VARCHAR;
end PACK2;
/
create or replace package body PACK2 as
  function func(a1 VARCHAR) return VARCHAR AS
  c varchar;
    BEGIN
     tety2 := a1;
     select tety2 into c;
     return c;
    END func;
end PACK2;

/


select pack2.func(1) from dual;
FUNC(varchar)      |
-------------------
1                  |
总数目：1

select pack2.func('abc') from dual;
FUNC(varchar)      |
-------------------
abc                |
总数目：1


create or replace package PACK1 as
  SUBTYPE subt1 is int;
  tety1 subt1;
  function func(a1 int) return subt1;
end PACK1;
/
create or replace package body PACK1 as
  function func(a1 int) return subt1 AS
    BEGIN
     return null;
    END func;
end PACK1;

/

select pack2.func(1) from dual;
FUNC(varchar)      |
-------------------
1                  |
总数目：1
select pack2.func('abc') from dual;
ERROR, 错误的数值格式 'abc'


DROP PACKAGE pack1;
DROP PACKAGE pack2;