CREATE TYPE_SQL命令_兼容Oracle数据库开发指南_云数据库PolarDB_阿里云技术文档

语法

对象类型

CREATE [ OR REPLACE ] TYPE name
  [ AUTHID { DEFINER | CURRENT_USER } ]
  { IS | AS } OBJECT
( { attribute { datatype | objtype | collecttype } }
    [, ...]
  [ method_spec ] [, ...]
) [ [ NOT ] { FINAL | INSTANTIABLE } ] ...

其中 method_spec 是：

  [ [ NOT ] { FINAL | INSTANTIABLE } ] ...
  [ OVERRIDING ]
    subprogram_spec

subprogram_spec 是：

  { MEMBER | STATIC }
  { PROCEDURE proc_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
  |
    FUNCTION func_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
    RETURN rettype
  }

嵌套表类型

CREATE [ OR REPLACE ] TYPE name { IS | AS } TABLE OF
  { datatype | objtype | collecttype }

varray 类型

CREATE [ OR REPLACE ] TYPE name { IS | AS }
  { VARRAY | VARYING ARRAY } (maxsize) OF { datatype | objtype }

复合类型

CREATE [ OR REPLACE ] TYPE name { IS | AS }
( [ attribute datatype ][, ...]
)

说明

CREATE TYPE 定义新的用户定义数据类型。可以创建的类型包括对象类型、嵌套表类型、varray 类型或复合类型。嵌套表类型和 varray 类型属于称为集合的类型类别。

复合类型与 Oracle 数据库不兼容。不过，复合类型可以通过 SPL 程序进行访问，与本节中所述的其他类型一样。

说明仅针对程序包，在程序包规格或程序包主体内，复合类型可以包含在采用 TYPE IS RECORD 语句声明的用户定义记录类型中。此类嵌套结构在其他 SPL 程序（如函数、存储过程、触发器等）中是不允许的。

在 CREATE TYPE 命令中，如果包括 schema 名称，则在指定的 schema 中创建类型，否则在当前 schema 中创建类型。新类型的名称不得与同一 schema 中的任何现有类型匹配，除非旨在更新现有类型的定义，在此情况下使用 CREATE OR REPLACE TYPE。

说明

OR REPLACE 选项当前不能用于添加、删除或修改现有对象类型的属性。可使用 DROP TYPE 命令首先删除现有对象类型。OR REPLACE 选项可用于添加、删除或修改现有对象类型中的方法。
ALTER TYPE ALTER ATTRIBUTE 命令的 PostgreSQL 形式可用于更改现有对象类型中属性的数据类型。不过，ALTER TYPE 命令不能添加或删除对象类型中的属性。

创建类型的用户成为该类型的所有者。

参数


参数	说明
name	要创建的类型的名称（可能是 schema 限定的）。
DEFINER \| CURRENT_USER	指定是采用对象类型所有者 (DEFINER) 的特权，还是采用执行对象类型中方法的当前用户 (CURRENT_USER) 的特权，来确定是否允许访问对象类型中引用的数据库对象。默认值为 DEFINER。
attribute	对象类型或复合类型中属性的名称。
datatype	定义对象类型或复合类型属性，或正在创建的集合类型元素的数据类型。
objtype	定义对象类型属性或正在创建的集合类型元素的对象类型的名称。
collecttype	定义对象类型属性或正在创建的集合类型元素的集合类型的名称。
FINALNOT FINAL	对于对象类型，指定是否可以从对象类型派生子类型。默认值为 FINAL（子类型不能从对象类型派生）。对于 method_spec，指定是否在子类型中覆盖方法。默认值为 NOT FINAL（可以在子类型中覆盖方法）。
INSTANTIABLENOT INSTANTIABLE	对于对象类型，指定是否创建该对象类型的对象实例。默认值为 INSTANTIABLE（可以创建该对象类型的实例）。如果指定 NOT INSTANTIABLE，则还必须指定 NOT FINAL。如果对象类型中任何方法的 method_spec 包含 NOT INSTANTIABLE 限定符，则必须在对象类型规格的右括号之后，采用 NOT INSTANTIABLE 和 NOT FINAL 定义对象类型自身。对于 method_spec，指定对象类型定义是否提供方法的实现。默认值为 INSTANTIABLE（对象类型的 CREATE TYPE BODY 命令提供方法的实现）。如果指定 NOT INSTANTIABLE，则对象类型的 CREATE TYPE BODY 命令不得包含方法的实现。
OVERRIDING	如果指定 OVERRIDING，则 method_spec 使用相同数量的同名方法参数覆盖同名方法，这些参数具有与超类型中的定义相同的数据类型、相同的顺序以及相同的返回类型（如果方法为函数）。
MEMBERSTATIC	如果子程序在对象实例上运行，请指定 MEMBER。如果子程序的运行独立于任何特定对象实例，请指定 STATIC。
proc_name	要创建的存储过程的名称。
SELF [ IN \| IN OUT ] name	对于成员方法，存在一个名为 SELF 的隐式内置参数，其数据类型就是正在定义的对象类型的数据类型。SELF 引用当前正在调用方法的对象实例。SELF 可以在参数列表中显式声明为 IN 或 IN OUT 参数。如果显式声明，则 SELF 必须为参数列表中的第一个参数。如果未明确声明 SELF，则其参数模式默认为 IN OUT（对于成员存储过程）和 IN（对于成员函数）。
argname	参数的名称。参数在方法主体中通过该名称进行引用。
argtype	方法参数的数据类型。参数类型可以是基本数据类型或用户定义类型，如嵌套表或对象类型。任何基本类型不得指定长度 - 例如，指定 VARCHAR2，而不是 VARCHAR2(10)。
DEFAULT value	如果方法调用中未提供默认值，则为输入参数提供一个默认值。对于模式为 IN OUT 或 OUT 的参数，可能无法指定 DEFAULT。
func_name	要创建的函数的名称。
rettype	返回数据类型，可以是为 argtype 列出的任意类型。对于 argtype，不得为 rettype 指定长度。
maxsize	varray 中允许的最大元素数量。

示例

创建对象类型

创建对象类型 addr_obj_typ。

CREATE OR REPLACE TYPE addr_obj_typ AS OBJECT (
    street          VARCHAR2(30),
    city            VARCHAR2(20),
    state           CHAR(2),
    zip             NUMBER(5)
);

创建包含成员方法 display_emp 的对象类型 emp_obj_typ。

CREATE OR REPLACE TYPE emp_obj_typ AS OBJECT (
    empno           NUMBER(4),
    ename           VARCHAR2(20),
    addr            ADDR_OBJ_TYP,
    MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
);

创建包含静态方法 get_dname 的对象类型 dept_obj_typ。

CREATE OR REPLACE TYPE dept_obj_typ AS OBJECT (
    deptno          NUMBER(2),
    STATIC FUNCTION get_dname (p_deptno IN NUMBER) RETURN VARCHAR2,
    MEMBER PROCEDURE display_dept
);

创建集合类型

创建数据类型 NUMBER(8,2) 的嵌套表类型 budget_tbl_typ。
```
CREATE OR REPLACE TYPE budget_tbl_typ IS TABLE OF NUMBER(8,2);
```

创建并使用复合类型

以下示例显示了从匿名块访问复合类型的用法。

复合类型的创建方法如下：

CREATE OR REPLACE TYPE emphist_typ AS (
    empno           NUMBER(4),
    ename           VARCHAR2(10),
    hiredate        DATE,
    job             VARCHAR2(9),
    sal             NUMBER(7,2)
);

以下是访问复合类型的匿名块：

DECLARE
    v_emphist       EMPHIST_TYP;
BEGIN
    v_emphist.empno    := 9001;
    v_emphist.ename    := 'SMITH';
    v_emphist.hiredate := '01-AUG-17';
    v_emphist.job      := 'SALESMAN';
    v_emphist.sal      := 8000.00;
    DBMS_OUTPUT.PUT_LINE('   EMPNO: ' || v_emphist.empno);
    DBMS_OUTPUT.PUT_LINE('   ENAME: ' || v_emphist.ename);
    DBMS_OUTPUT.PUT_LINE('HIREDATE: ' || v_emphist.hiredate);
    DBMS_OUTPUT.PUT_LINE('     JOB: ' || v_emphist.job);
    DBMS_OUTPUT.PUT_LINE('     SAL: ' || v_emphist.sal);
END;

   EMPNO: 9001
   ENAME: SMITH
HIREDATE: 01-AUG-17 00:00:00
     JOB: SALESMAN
     SAL: 8000.00

以下示例显示了从在程序包主体内声明的用户定义记录类型访问复合类型的用法。

复合类型的创建方法如下：

CREATE OR REPLACE TYPE salhist_typ AS (
    startdate       DATE,
    job             VARCHAR2(9),
    sal             NUMBER(7,2)
);

程序包规格的定义方法如下：

CREATE OR REPLACE PACKAGE emp_salhist
IS
    PROCEDURE fetch_emp (
        p_empno     IN NUMBER
    );
END;

程序包主体的定义方法如下：

CREATE OR REPLACE PACKAGE BODY emp_salhist
IS
    TYPE emprec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR(10),
        salhist     SALHIST_TYP
    );
    TYPE emp_arr_typ IS TABLE OF emprec_typ INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;

    PROCEDURE fetch_emp (
        p_empno     IN NUMBER
    )
    IS
        CURSOR emp_cur IS SELECT e.empno, e.ename, h.startdate, h.job, h.sal
            FROM emp e, jobhist h
            WHERE e.empno = p_empno
              AND e.empno = h.empno;

        i           INTEGER := 0;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    STARTDATE  JOB         ' ||
        'SAL        ');
        DBMS_OUTPUT.PUT_LINE('-----  -------  ---------  ---------   ' ||
        '---------');

        FOR r_emp IN emp_cur LOOP
            i := i + 1;
            emp_arr(i) := (r_emp.empno, r_emp.ename,
                (r_emp.startdate, r_emp.job, r_emp.sal));
        END LOOP;

        FOR i IN 1 .. emp_arr.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(emp_arr(i).empno || '   ' ||
                RPAD(emp_arr(i).ename,8) || ' ' ||
                TO_CHAR(emp_arr(i).salhist.startdate,'DD-MON-YY') || '  ' ||
                RPAD(emp_arr(i).salhist.job,10) || ' ' ||
                TO_CHAR(emp_arr(i).salhist.sal,'99,999.99'));
        END LOOP;
    END;
END;

请注意，在程序包主体内 TYPE emprec_typ IS RECORD 数据结构的声明中，salhist 字段采用 CREATE TYPE salhist_typ 语句所创建的 SALHIST_TYP 复合类型定义。

关联的数组定义 TYPE emp_arr_typ IS TABLE OF emprec_typ 引用记录类型数据结构 emprec_typ，其中包含的字段 salhist 采用 SALHIST_TYP 复合类型定义。

以下显示了程序包存储过程的调用，从 emp 和 jobhist 表联合加载数组，然后显示数组内容：

EXEC emp_salhist.fetch_emp(7788);

EMPNO  ENAME    STARTDATE  JOB         SAL
-----  -------  ---------  ---------   ---------
7788   SCOTT    19-APR-87  CLERK        1,000.00
7788   SCOTT    13-APR-88  CLERK        1,040.00
7788   SCOTT    05-MAY-90  ANALYST      3,000.00

EDB-SPL Procedure successfully completed

CREATE TYPE_SQL命令_兼容Oracle数据库开发指南_云数据库PolarDB

CREATE TYPE

语法

说明

参数

示例

版权声明

评论