自定义数据类型

📄字数 8.3K

👁️阅读量加载中...

数据库自定义类型（User Defined Type，UDT）是数据库管理系统提供的高级功能，允许用户基于系统内置类型创建符合特定业务需求的专用数据类型。通过自定义类型，可以增强数据模型的表达能力和数据完整性。

XuguDB自定义类型分为：

结构类型（OBJECT）
数组类型（VARRAY）
嵌套表类型（TABLE）

OBJECT、VARRAY、TABLE三者创建或声明类型时的区别如下表所示:

类型	成员/元素	父类	构造函数	指定容量	方法
OBJECT	有	有	有	否	有
VARRAY	有	无	有	是	无
TABLE	有	无	有	否	无

自定义类型可支持以下功能：

比较：
1. 支持同个UDT对象的数据比较。
2. 支持UDT数据等值和不等值比较。
3. 支持UDT数据IS NULL或IS NOT NULL比较。
4. 若UDT定义类型的成员类型为基础类型时，则支持对成员值的排序。
赋值
1. SQL支持初始化赋值、构造函数赋值和复制赋值。
2. PL/SQL支持初始化赋值、构造函数赋值、成员赋值和复制赋值。
定义：
1. 表的字段类型、变量类型、参数类型、返回值类型等在其他对象中调用。
转换：
1. 支持UDT成员类型与其他基本数据类型之间的转换。

结构类型

数据库使用OBJECT表示结构类型，其设计类似于面向对象语言支持父类继承，包括结构类型规范和结构类型体两个部分，是在其他数据类型基础上建立的，可以包含多个属性（元素）和多种方法（成员函数），这些属性和方法构成了结构化的数据单元。对于模式级别创建的结构类型可以持久化存储在数据库中，通过系统表SYS_TYPES和SYS_OBJECTS查看。

成员函数的类型有:

构造函数（constructor）。
静态函数（static）。
普通成员函数（member）。

提示

重载：结构类型支持方法重载，即可以有多个同名但参数不同的方法。
继承：结构类型支持继承，但子类不能重写父类的成员函数（同名同参）。
使用点号.可访问对象的成员。

语法格式

创建OBJECT类型：

( ProcDef::=)

参数说明

Cre_Rep：创建模式。
- OR REPLACE：创建或替换。
- NOFORCE：不强制创建或替换，默认。
- FORCE：强制创建或替换。
sche_name：模式名，模式名可不写，默认当前模式。
type_name：创建的类型名。
UNDER：用于定义子类型继承关系。
parent_type_name：表示父类型。
MemberVarDecl：变量（或称为属性）定义，可定义多个成员，不同成员间用逗号分隔。
- VarName：普通变量名。
- CONSTANT：一个可选关键字，用来指定该变量是否为常量。
- TypeName：指定变量的数据类型，可以是基础类型或UDT数据类型。
- NOT NULL：可以对变量或常量的标量（或复合变量）施加 NOT NULL 约束。NOT NULL 约束可防止为对象分配空值。
  标量变量声明可以显式指定 NOT NULL，当指定了 NOT NULL 时必须为其分配初始值（因为标量变量的默认初始值为 NULL）。
- def_val：可指定变量的初始默认值，只能为常量。
MemberFuncDef：函数和过程声明。
- 函数可分为如下三类：
  - 构造函数：形式为CONSTRUCTOR udt_name(arguments)；其中函数名和Object名相同，参数的个数和类型要和本Object中的变量相对应，自定义的构造函数优先级高于默认构造函数。
  - 静态函数：形式为STATIC function_name([ arguments ])RETURN ret_type；其中arguments为函数的参数，可有一个、多个或无参数，每个参数可以用可选的关键词（IN | OUT | IN OUT）修饰；ret_type为函数的返回值。
  - 普通成员函数：形式为MEMBER function_name([ arguments ]) RETURN ret_type；其中arguments为函数的参数，可有一个、多个或无参数，每个参数可以用可选的关键词（IN | OUT | IN OUT）修饰；ret_type为函数的返回值。
- 过程可分为如下两类：
  - 静态过程：形式为STATIC procedure_name([ arguments ])；其中arguments为过程的参数，可有一个、多个或无参数，每个参数可以用可选的关键词（IN | OUT | IN OUT）修饰。
  - 普通成员过程：形式为MEMBER procedure_name([ arguments ])；其中arguments为过程的参数，可有一个、多个或无参数，每个参数可以用可选的关键词（IN | OUT | IN OUT）修饰。
opt_comment：备注信息，可通过系统表SYS_TYPES字段COMMENTS查看内容。
- com_str：备注的描述内容。
示例：

sql

SQL> CREATE OR REPLACE TYPE udt_obj_type AS OBJECT(n NUMERIC,class VARCHAR2,type VARCHAR, dt DATE);

SQL> SELECT udt_obj_type(12,'重庆','成都','2025-6-14 00:00:00') as udt_object_type;
+------------------------------------+
|          UDT_OBJECT_TYPE           |
+------------------------------------+
| [12,重庆,成都,2025-06-14 00:00:00]  |
+------------------------------------+

-- 创建类型under_udt_obj_type继承udt_obj_type
SQL> CREATE OR REPLACE TYPE under_udt_obj_type UNDER udt_obj_type(region VARCHAR);

SQL> SELECT under_udt_obj_type(12,'重庆','成都','2025-6-14 00:00:00','中国') as under_udt_obj_type;
+-----------------------------------------+
|           UNDER_UDT_OBJ_TYPE            |
+-----------------------------------------+
| [12,重庆,成都,2025-06-14 00:00:00,中国] |
+-----------------------------------------+

示例：

sql

-- 创建一个表obj_tab，其中字段udt_obj由示例1创建的UDT_OBJ_TYPE类型定义
SQL> CREATE TABLE obj_tab(id INT,state VARCHAR2,type VARCHAR,udt_obj UDT_OBJ_TYPE);

SQL> INSERT INTO obj_tab(id,state,type,udt_obj) VALUES(1,'构造函数插入','udt_obj_type',udt_obj_type(1.0,'一层','udt_obj_type','2025-06-14 00:00:00'));

SQL> SELECT *FROM obj_tab;
+----+--------------------------+--------------+-------------------------------------------+
| ID |          STATE           |     TYPE     |                  UDT_OBJ                  |
+----+--------------------------+--------------+-------------------------------------------+
| 1  | 构造函数插入              | udt_obj_type | [1,一层,udt_obj_type,2025-06-14 00:00:00] |
+----+--------------------------+--------------+-------------------------------------------+

示例：

sql

-- 在PL/SQL块中声明一个变量，然后将该变量插入到表中
SQL> DECLARE
        udt_obj UDT_OBJ_TYPE;
     BEGIN
        udt_obj := UDT_OBJ_TYPE(2.0,'二层','udt_obj_type','2025-06-14 00:00:00');
        INSERT INTO obj_tab(id, state, type, udt_obj)VALUES(2, 'plsql通过udt变量插入数据', 'udt_obj_type', udt_obj);
     END;
     /

SQL> SELECT *FROM obj_tab WHERE id = 2;
+----+--------------------------+--------------+-------------------------------------------+
| ID |          STATE           |     TYPE     |                  UDT_OBJ                  |
+----+--------------------------+--------------+-------------------------------------------+
| 2  | plsql通过udt变量插入数据  | udt_obj_type | [2,二层,udt_obj_type,2025-06-14 00:00:00]  |
+----+--------------------------+--------------+-------------------------------------------+

示例：

sql

-- 创建OBJECT类型，自定义构造函数 
SQL> CREATE OR REPLACE TYPE udt_obj_type_2 AS OBJECT
     (
         street VARCHAR,
         city VARCHAR,
         CONSTRUCTOR PROCEDURE udt_obj_type_2(street_1 VARCHAR,city_2 VARCHAR)
     );
     /

SQL> CREATE OR REPLACE TYPE BODY udt_obj_type_2 IS CONSTRUCTOR PROCEDURE udt_obj_type_2(street_1 VARCHAR,city_2 VARCHAR) 
     AS 
        BEGIN
            street:= street_1;
            city:= city_2;
        END;
     END;
     /

SQL> CREATE TABLE tab_udt_test(id INT,name VARCHAR,address UDT_OBJ_TYPE_2);

SQL> INSERT INTO tab_udt_test VALUES(1,'成都',udt_obj_type_2('金石路','天府之国'));

SQL> SELECT *FROM tab_udt_test;
+----+------+-------------------+
| ID | NAME |      ADDRESS      |
+----+------+-------------------+
| 1  | 成都 | [金石路,天府之国]  |
+----+------+-------------------+

示例：

sql

-- 创建UDT对象的静态与成员函数
SQL> CREATE OR REPLACE TYPE udt_obj_type_3 as OBJECT
     (
        id int, 
        STATIC FUNCTION test_int(v1 int, v2 int) RETURN BIGINT,
        MEMBER FUNCTION test_int_2(v3 int, v4 int) RETURN NUMERIC(12,4)
     ); 
     /

-- 静态函数与成员函数实现
SQL> CREATE OR REPLACE TYPE BODY udt_obj_type_3 as
     STATIC FUNCTION test_int(v1 int, v2 int) RETURN BIGINT IS
        BEGIN
            RETURN v1 * v2;
        END;
     MEMBER FUNCTION test_int_2(v3 int, v4 int) RETURN NUMERIC(12,4) IS  
        BEGIN
            RETURN v3 + v4;
        END;
     END;
     /  

-- 执行函数输出结果
SQL> DECLARE
        ret_bigint BIGINT;
        udt_bigint udt_obj_type_3;
     BEGIN
        ret_bigint := udt_bigint.test_int(2,4);
        SEND_MSG(ret_bigint);
     END;
     /
-- 输出结果:
8

SQL> DECLARE
        ret_numeric NUMERIC(38,12);
        udt_numeric udt_obj_type_3;
     BEGIN
        ret_numeric := udt_numeric.test_int_2(200,400);
        SEND_MSG(ret_numeric);
     END;
     /
-- 输出结果:
600

数组类型

VARRAY可变数组类型是一个连续的相同数据类型元素的集合，成员最多包含65535个，成员类型可以是基础类型，也可以是用户自定义类型。数据库中使用VARRAY OF关键字创建或声明数组类型，数组内的每个元素均存在一个索引下标，其代表元素在数组中的位置，且可通过下标直接访问数组元素。

语法格式

创建VARRAY类型：

参数说明

Cre_Rep：创建模式。
- OR REPLACE：创建或替换。
- NOFORCE：不强制创建或替换，默认。
- FORCE：强制创建或替换。
sche_name：模式名，模式名可不写，默认当前模式。
type_name：创建的类型名。
ICONST：VARRAY数组的指定容量，创建时必须指定。
TypeName：元素的数据类型，可以是基本数据类型（如：INT、FLOAT、NUMBER、VARCHAR等）或其他用户自定义类型。
opt_comment：备注信息，可通过系统表SYS_TYPES字段COMMENTS查看内容。
- com_str：备注的描述内容。
示例：

sql

-- 创建基本数据类型 
SQL> CREATE OR REPLACE TYPE var_test IS VARRAY(3) OF VARCHAR;

SQL> SELECT var_test('四川','成都','金石路');
+--------------------+
|       EXPR1        |
+--------------------+
| [四川,成都,金石路]  |
+--------------------+

-- 创建一个表udt_varray_tab，其包含一个列udt_varray_type，为自定义VAR_TEST类型
SQL> CREATE TABLE udt_varray_tab(id int, udt_varray_type VAR_TEST);

SQL> INSERT INTO udt_varray_tab VALUES(1,VAR_TEST('重庆','酉阳','桃花源'));

SQL> SELECT *FROM udt_varray_tab;
+----+--------------------+
| ID |  UDT_VARRAY_TYPE   |
+----+--------------------+
| 1  | [重庆,酉阳,桃花源]  |
+----+--------------------+

SQL> DROP TABLE udt_varray_tab;

SQL> DROP TYPE var_test;

示例：

sql

-- 创建自定义类型 
SQL> CREATE OR REPLACE TYPE udt_obj_type AS OBJECT(n NUMERIC,city VARCHAR2,address VARCHAR,dt DATE);

SQL> CREATE OR REPLACE TYPE udt_obj_varray_type IS VARRAY(3) OF udt_obj_type;

-- 创建一个表varry_tab，其包含一个列udt_varry_type，为自定义UDT_OBJ_VARRAY_TYPE类型
SQL> CREATE TABLE varry_tab(id INT,state VARCHAR,udt_varry_type UDT_OBJ_VARRAY_TYPE);

-- 使用构造函数插入
SQL> INSERT INTO varry_tab (id, state, udt_varry_type) VALUES (1, '构造函数插入', 
            UDT_OBJ_VARRAY_TYPE(
            udt_obj_type(1.0, '四川', 'udt_obj_type', TO_DATE('2025-06-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
            udt_obj_type(2.0, '成都', 'udt_obj_type', TO_DATE('2025-06-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
          )
        );
        /

SQL> SELECT *FROM varry_tab WHERE id = 1;
+----+--------------+---------------------------------------------------------------------------------------+
| ID |    STATE     |                                    UDT_VARRY_TYPE                                     |
+----+--------------+---------------------------------------------------------------------------------------+
| 1  | 构造函数插入 | [[1,四川,udt_obj_type,2025-06-16 00:00:00],[2,成都,udt_obj_type,2025-06-17 00:00:00]]   |
+----+--------------+---------------------------------------------------------------------------------------+

-- 在PL/SQL块中声明一个变量，然后将该变量插入到表中
SQL> DECLARE
        udt_varry_type UDT_OBJ_VARRAY_TYPE;
     BEGIN
        udt_varry_type := UDT_OBJ_VARRAY_TYPE(
                        udt_obj_type(1.0,'四川','成都','2025-06-14 00:00:00'),
                        udt_obj_type(2.0,'金石路','佳霖','2025-06-15 00:00:00'),
                        udt_obj_type(3.0,'重庆','桃花源','2025-06-16 00:00:00')
                        );
        INSERT INTO varry_tab (id, state, udt_varry_type) VALUES (2, 'plsql通过udt变量插入数据', udt_varry_type);
     END;
      /

SQL> SELECT *FROM varry_tab WHERE id = 2;
+----+--------------------------+-------------------------------------------------------------------------------------------------------------+
| ID |          STATE           |                                               UDT_VARRY_TYPE                                                |
+----+--------------------------+-------------------------------------------------------------------------------------------------------------+
| 2  | plsql通过udt变量插入数据  | [[1,四川,成都,2025-06-14 00:00:00],[2,金石路,佳霖,2025-06-15 00:00:00],[3,重庆,桃花源,2025-06-16 00:00:00]]    |
+----+--------------------------+-------------------------------------------------------------------------------------------------------------

-- 通过索引下标方式查询部分属性
SQL> SELECT udt_varry_type(1), udt_varry_type(2) FROM varry_tab;
+-------------------------------------------+-------------------------------------------+
|                   EXPR1                   |                   EXPR2                   |
+-------------------------------------------+-------------------------------------------+
| [1,四川,udt_obj_type,2025-06-16 00:00:00] | [2,成都,udt_obj_type,2025-06-17 00:00:00] |
| [1,四川,成都,2025-06-14 00:00:00]         | [2,金石路,佳霖,2025-06-15 00:00:00]        |
+-------------------------------------------+-------------------------------------------+

嵌套表类型

嵌套表类型在SQL中称为多重集合类型，表示无序的相同类型数组元素的集合。数据库中使用TABLE OF关键字可以创建或声明嵌套表类型，嵌套表内的每个元素均有一个索引下标，代表元素在数组中的位置，且可以根据下标直接访问嵌套表元素。不同于数组的是，嵌套表未经删减，元素数据位置是连续的，若删除中间位置的数据后，数据变成了离散的。

语法格式

创建TABLE类型：

参数说明

Cre_Rep：创建模式。
- OR REPLACE：创建或替换。
- NOFORCE：不强制创建或替换，默认。
- FORCE：强制创建或替换。
sche_name：模式名，模式名可不写，默认当前模式。
type_name：创建的类型名。
TypeName：元素的数据类型，可以是基本数据类型（如：INT、FLOAT、NUMBER、VARCHAR等）或其他用户自定义类型。
opt_comment：备注信息，可通过系统表SYS_TYPES字段COMMENTS查看内容。
- com_str：备注的描述内容。
示例：

sql

-- 创建基本数据类型 
SQL> CREATE OR REPLACE TYPE udt_tab_type IS TABLE OF BIGINT;

SQL> SELECT udt_tab_type(1,2,3,4,5);
+-------------+
|    EXPR1    |
+-------------+
| [1,2,3,4,5] |
+-------------+

-- 创建一个表tab_type，其包含一个列udt_type，为自定义UDT_TAB_TYPE类型
SQL> CREATE TABLE tab_type(id int, type_tab UDT_TAB_TYPE);

SQL> INSERT INTO tab_type VALUES(1,UDT_TAB_TYPE(1,2,3,4,5));

SQL> SELECT *FROM tab_type;
+----+-------------+
| ID |  TYPE_TAB   |
+----+-------------+
| 1  | [1,2,3,4,5] |
+----+-------------+

示例：

sql

-- 创建自定义类型 
SQL> CREATE OR REPLACE TYPE udt_obj_type AS OBJECT(n NUMERIC,city VARCHAR2,address VARCHAR,dt DATE);

SQL> CREATE OR REPLACE TYPE udt_obj_tab_type IS TABLE OF udt_obj_type;

--创建一个表udt_tab_type，其包含一个列udt_type，为自定义UDT_OBJ_VARRAY_TYPE类型
SQL> CREATE TABLE udt_tab(id INT,state VARCHAR,udt_type UDT_OBJ_TAB_TYPE);

--构造函数插入数据
SQL> INSERT INTO udt_tab(id, state, udt_type) VALUES (1,'构造函数插入', 
          UDT_OBJ_TAB_TYPE(
          udt_obj_type(1, '2层', 'udt_obj_type', '2025-08-24 00:00:00'),
          udt_obj_type(2, '2层', 'udt_obj_type', '2025-08-24 00:00:00')
          )
        );

SQL> SELECT *FROM udt_tab;
+----+--------------+-------------------------------------------------------------------------------------+
| ID |    STATE     |                                      UDT_TYPE                                       |
+----+--------------+-------------------------------------------------------------------------------------+
| 1  | 构造函数插入  | [[1,2层,udt_obj_type,2025-08-24 00:00:00],[2,2层,udt_obj_type,2025-08-24 00:00:00]]  |
+----+--------------+-------------------------------------------------------------------------------------+

-- 在PL/SQL块中声明一个变量，然后将该变量插入到表中
SQL> DECLARE
        udt_type UDT_OBJ_TAB_TYPE;
     BEGIN
        udt_type := UDT_OBJ_TAB_TYPE(
                        udt_obj_type(1.0,'四川','成都','2025-06-14 00:00:00'),
                        udt_obj_type(2.0,'金石路','佳霖','2025-06-15 00:00:00'),
                        udt_obj_type(3.0,'重庆','桃花源','2025-06-16 00:00:00')
                        );
        INSERT INTO udt_tab (id, state, udt_type) VALUES (2, 'plsql通过udt变量插入数据', udt_type);
     END;
      /

SQL> SELECT *FROM udt_tab WHERE id = 2;
+----+--------------------------+-------------------------------------------------------------------------------------------------------------+
| ID |          STATE           |                                                  UDT_TYPE                                                   |
+----+--------------------------+-------------------------------------------------------------------------------------------------------------+
| 2  | plsql通过udt变量插入数据 | [[1,四川,成都,2025-06-14 00:00:00],[2,金石路,佳霖,2025-06-15 00:00:00],[3,重庆,桃花源,2025-06-16 00:00:00]]     |
+----+--------------------------+-------------------------------------------------------------------------------------------------------------+

-- 通过索引下标方式查询部分属性
SQL> SELECT udt_type(1), udt_type(2) FROM udt_tab;
+------------------------------------------+------------------------------------------+
|                  EXPR1                   |                  EXPR2                   |
+------------------------------------------+------------------------------------------+
| [1,2层,udt_obj_type,2025-08-24 00:00:00] | [2,2层,udt_obj_type,2025-08-24 00:00:00] |
| [1,四川,成都,2025-06-14 00:00:00]         | [2,金石路,佳霖,2025-06-15 00:00:00]      |
+------------------------------------------+------------------------------------------+

删除类型

语法格式

参数说明

name_space: 自定义类型名称。
CASCADE | RESTRICT | CASCADE CONSTRAINTS：存在依赖自定义类型的对象时，自定义类型不允许强制删除，需修改或者删除依赖对象后，方可删除自定义类型。
示例：

sql

SQL> CREATE TYPE person AS OBJECT( name VARCHAR, age INT, height NUMERIC(3,2), gender CHAR(1));

SQL> DROP TYPE person;

自定义数据类型 ​

结构类型 ​

语法格式 ​

参数说明 ​

数组类型 ​

语法格式 ​

参数说明 ​

嵌套表类型 ​

语法格式 ​

参数说明 ​

删除类型 ​

语法格式 ​

参数说明 ​

自定义数据类型

结构类型

语法格式

参数说明

数组类型

语法格式

参数说明

嵌套表类型

语法格式

参数说明

删除类型

语法格式

参数说明