oracle - "type"和 "subtype"关键字有什么区别？

Question

在一些 PL/SQL 示例代码中，我注意到每次都使用 type 和 subtype 关键字来声明自定义类型(类似于 typedef例如，C 中的 关键字)。

在我看来，它们的用法是可以互换的:它们有什么区别？

Answer 1

如果您尝试声明具有更具体精度/小数位数(或将其限制为 NOT NULL)的基本数据类型，如下所示:

DECLARE
  TYPE int IS NUMBER(38,0);
BEGIN
  NULL;
END;
/

然后它就不起作用并且你会得到异常:

ORA-06550: line 2, column 15: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: ( array new range record varray char_base number_base decimal date_base clob_base blob_base bfile_base table ref fixed varying sparse The symbol "range" was substituted for "NUMBER" to continue.

相反，您想使用SUBTYPE关键字:

DECLARE
  SUBTYPE int IS NUMBER(38,0);
  SUBTYPE intn IS NUMBER(38,0) NOT NULL;
BEGIN
  NULL;
END;
/

例如 - A previous question where an answer was to use a SUBTYPE to constrain the data to specific precision and scale.

<小时/>

另请考虑以下声明:

1. TYPE intlist IS TABLE OF NUMBER(38,0);
   

2. TYPE intlist IS TABLE OF NUMBER(38,0) NOT NULL;
   

3. SUBTYPE integern IS NUMBER(38,0) NOT NULL;
   TYPE intlist IS TABLE OF integern;
   

4. TYPE intlist IS TABLE OF NUMBER(38,0);
   SUBTYPE intlistn IS intlist NOT NULL;
   

对于(1)，列表可以为NULL，列表的元素也可以为NULL:

DECLARE
  TYPE intlist IS TABLE OF NUMBER(38,0);
  list intlist := NULL;
BEGIN
  list := intlist( 1, 2, NULL, 4 );
END;
/

(2) 和 (3) 是等效的 - 列表可以为 NULL，但列表中包含的任何元素都必须为 NOT NULL。

DECLARE
  TYPE intlist IS TABLE OF NUMBER(38,0) NOT NULL;

  -- This works:
  list intlist := NULL;
BEGIN
  -- This also works:
  list := intlist( 1, 2, 3, 4 );

  -- But this will raise an exception
  -- list := intlist( 1, 2, NULL, 4 );
END;
/

要强制列表不能为 NULL，您需要声明 SUBTYPE 并按照代码段中的 intlistn 强制执行约束( 4).

DECLARE
  TYPE intlist IS TABLE OF NUMBER(38,0);
  SUBTYPE intlistn IS intlist NOT NULL;

  -- This works as the list is NOT NULL (even though an element of the list is)
  list intlistn := intlist( 1, 2, NULL, 4 );
BEGIN
  -- This does not works as the SUBTYPE constraint prevents it:
  -- list := NULL;
END;
/