PolarDB-O 云原生数据库 CREATE TYPE

定义新的用户定义类型，可以是对象类型、集合类型（嵌套表类型或 varray 类型）或复合类型。

语法

对象类型

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	定义对象类型属性或正在创建的集合类型元素的集合类型的名称。
FINAL \| NOT 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使用相同数量的同名方法参数覆盖同名方法，这些参数具有与超类型中的定义相同的数据类型、相同的顺序以及相同的返回类型（如果方法为函数）。
MEMBER \| STATIC	如果子程序在对象实例上运行，请指定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

polar-spl Procedure successfully completed

语法

说明

参数

示例

发表回复 取消回复

发表回复取消回复