ALL_TYPES
📄字数 2.1K
👁️阅读量 加载中...
功能描述
ALL_TYPES系统视图用于存储和管理创建的自定义类型信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | SCHEMA_ID | INTEGER | 模式ID | √ | √ |
| 2 | USER_ID | INTEGER | 属主ID | √ | √ |
| 3 | TYPE_ID | INTEGER | UDT_ID | √ | √ |
| 4 | TYPE_NAME | VARCHAR | UDT名 | √ | √ |
| 5 | UDT_TYPE | INTEGER | 复合类型种类（1001:object 1004:varray 1005:table ） | √ | √ |
| 6 | SUPER_ID | INTEGER | 超类ID（对Object有效） | √ | √ |
| 7 | MEMBER_DT | VARCHAR | 成员类型名（对varray和table有效） | √ | √ |
| 8 | MEMBER_SCALE | INTEGER | 成员类型的scale（精度，标度） | √ | √ |
| 9 | MEMBER_NUM | INTEGER | 成员个数（对varray有效） | √ | √ |
| 10 | LANGUAGE | CHAR | 过程语言类别 | √ | √ |
| 11 | SPEC | CLOB | 头部定义 | √ | √ |
| 12 | BODY | CLOB | 成员过程体定义 | √ | √ |
| 13 | CREATE_TIME | DATETIME | 创建时间 | √ | √ |
| 14 | VALID | BOOLEAN | 是否有效 | √ | √ |
| 15 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 16 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 17 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 18 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
| 19 | RESERVED3 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 查询自定义类型的定义文本等信息
sql
-- 创建自定义类型
SQL> CREATE OR REPLACE TYPE udt_table IS TABLE OF INTEGER;
SQL> CREATE OR REPLACE TYPE udt_object IS OBJECT(id INT,name VARCHAR(10));
SQL> CREATE OR REPLACE TYPE udt_varry IS VARRAY(5) OF VARCHAR(10);
SQL> SELECT * FROM ALL_TYPES;
+-------+-----------+---------+---------+------------+----------+----------+-----------+--------------+------------+----------+--------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | TYPE_ID | TYPE_NAME | UDT_TYPE | SUPER_ID | MEMBER_DT | MEMBER_SCALE | MEMBER_NUM | LANGUAGE | SPEC | BODY | CREATE_TIME | VALID | COMMENTS | IS_SYS | RESERVED1 | RESERVED2 | RESERVED3 |
+-------+-----------+---------+---------+------------+----------+----------+-----------+--------------+------------+----------+--------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| 1 | 1 | 1 | 1048584 | UDT_TABLE | 1005 | 0 | INTEGER | -1 | <NULL> | PL/SQL | <NULL> | <NULL> | 2025-06-26 19:55:39.320 | T | <NULL> | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048585 | UDT_OBJECT | 1001 | 0 | <NULL> | <NULL> | <NULL> | PL/SQL | <CLOB> | <NULL> | 2025-06-26 19:55:39.325 | T | <NULL> | F | <NULL> | <NULL> | <NULL> |
| 1 | 1 | 1 | 1048587 | UDT_VARRY | 1004 | 0 | CHAR | 10 | 5 | PL/SQL | <NULL> | <NULL> | 2025-06-26 19:56:27.946 | T | <NULL> | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+------------+----------+----------+-----------+--------------+------------+----------+--------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+- SUPER_ID(超类ID)字段举例
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] |
+------------------------------------+
SQL> SELECT type_id, type_name, udt_type, super_id FROM ALL_TYPES ORDER BY create_time desc LIMIT 1;
+---------+--------------+----------+----------+
| TYPE_ID | TYPE_NAME | UDT_TYPE | SUPER_ID |
+---------+--------------+----------+----------+
| 1048592 | UDT_OBJ_TYPE | 1001 | 0 |
+---------+--------------+----------+----------+
-- 创建类型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,中国] |
+-----------------------------------------+
-- 查询系统表，under_udt_obj_type的super_id为udt_obj_type的id
SQL> SELECT type_id, type_name, udt_type, super_id FROM ALL_TYPES ORDER BY create_time desc LIMIT 2;
+---------+--------------------+----------+----------+
| TYPE_ID | TYPE_NAME | UDT_TYPE | SUPER_ID |
+---------+--------------------+----------+----------+
| 1048593 | UNDER_UDT_OBJ_TYPE | 1001 | 1048592 |
| 1048592 | UDT_OBJ_TYPE | 1001 | 0 |
+---------+--------------------+----------+----------+
-- 创建类型under_udt_obj_type_2继承under_udt_obj_type
SQL> CREATE OR REPLACE TYPE under_udt_obj_type_2 UNDER under_udt_obj_type(comments VARCHAR);
-- 查询系统表，under_udt_obj_type2的super_id为under_udt_obj_type的id
SQL> SELECT type_id, type_name, udt_type, super_id FROM ALL_TYPES ORDER BY create_time desc LIMIT 3;
+---------+----------------------+----------+----------+
| TYPE_ID | TYPE_NAME | UDT_TYPE | SUPER_ID |
+---------+----------------------+----------+----------+
| 1048594 | UNDER_UDT_OBJ_TYPE_2 | 1001 | 1048593 |
| 1048593 | UNDER_UDT_OBJ_TYPE | 1001 | 1048592 |
| 1048592 | UDT_OBJ_TYPE | 1001 | 0 |
+---------+----------------------+----------+----------+