项目上遇到了一张表，通过pl/sql developer查看其表结构，发现列显示空，一番探究之后发现它是一张嵌套表的子表，这种表确实少见，今天来说说oracle的嵌套表。
一、什么是嵌套表？
面对需要存储一对多数据的场景，你是选择创建子表，还是忍受字段冗余？Oracle 提供了一种独特的解决方案——嵌套表，却为何在市面上鲜有踪影？今天，我们将全面剖析这一特性。
简单来说，嵌套表允许你将一个完整的表作为另一个表的列。
想象一下经典的「员工-地址」场景。在传统数据库设计中，你至少需要两张表：employees 和 addresses，并通过外键关联。而使用嵌套表，你可以在 employees 表中直接创建一个 address_list 列，这个列的类型就是一个可以存储多条地址记录的「表」。
嵌套表的本质是：在父表的单行记录内，嵌入一个完整的子表数据集合。
二、实战演练：从创建到查询
理论说得再多，不如动手一试。让我们通过一个完整的案例，感受嵌套表的运作方式。
1. 定义类型：构建数据模型的基石
首先，我们需要定义要“嵌套”的数据结构。
-- 1. 创建对象类型：定义单条地址的结构
CREATE TYPE address_type AS OBJECT (
street VARCHAR2(100),
city VARCHAR2(50),
province VARCHAR2(50),
postal_code VARCHAR2(10)
);
/
-- 2. 创建嵌套表类型：基于对象类型定义一个“表类型”
CREATE TYPE address_list_type AS TABLE OF address_type;
/
2. 创建表：嵌入式的数据模型
接下来，创建包含嵌套表列的主表。
-- 3. 创建主表，其中一列是嵌套表类型
CREATE TABLE employees_nested (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
-- 关键在此：列的数据类型是一个表
address_list address_list_type
) NESTED TABLE address_list STORE AS employee_addresses_nt;
关键说明：
-
NESTED TABLE ... STORE AS语句是必须的，它告诉 Oracle 为嵌套表数据分配物理存储空间。 -
employee_addresses_nt是一个由系统维护的存储表，对用户基本透明。
3. 插入数据：使用构造函数
向嵌套表插入数据需要使用特定的构造函数语法。
-- 插入数据：使用构造函数初始化嵌套表
INSERT INTO employees_nested VALUES (
1,
'张三',
address_list_type( -- 初始化嵌套表
address_type('科技园路1号', '深圳市', '广东省', '518000'), -- 第一条记录
address_type('中山大道100号', '广州市', '广东省', '510000') -- 第二条记录
)
);
INSERT INTO employees_nested VALUES (
2,
'李四',
address_list_type(
address_type('南京路200号', '上海市', '上海市', '200000')
)
);
COMMIT;
4. 查询数据：解锁嵌套数据的正确姿势
子表是无法单独查询数据的，强行查询会返回错误：ORA-22812:无法参考嵌套表列的存储表；
szr@orcl(2524)> SELECT * FROM EMPLOYEE_ADDRESSES_NT;
SELECT * FROM EMPLOYEE_ADDRESSES_NT
*
第 1 行出现错误:
ORA-22812: 无法参考嵌套表列的存储表
只能通过主表查看数据，直接 SELECT * 无法直观显示嵌套表内容，可使用 TABLE() 函数进行展平。
szr@orcl(2524)> col address_list for a100;
szr@orcl(2524)> select * from employees_nested;
EMPLOYEE_ID NAME ADDRESS_LIST(STREET, CITY, PROVINCE, POSTAL_CODE)
----------- -------------------- ----------------------------------------------------------------------------------------------------
1 张三 ADDRESS_LIST_TYPE(ADDRESS_TYPE('科技园路1号', '深圳市', '广东省', '518000'), ADDRESS_TYPE('中山大道1
00号', '广州市', '广东省', '510000'))
2 李四 ADDRESS_LIST_TYPE(ADDRESS_TYPE('南京路200号', '上海市', '上海市', '200000'))
查询所有员工及其所有地址：
SELECT e.employee_id,
e.name,
a.street,
a.city,
a.province,
a.postal_code
FROM employees_nested e,
TABLE(e.address_list) a; -- 关键：将嵌套表展平为关系行
查询结果：
|
|
|
|
|
|
|
|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
查询特定城市的员工：
SELECT e.employee_id, e.name
FROM employees_nested e,
TABLE(e.address_list) a
WHERE a.city = '广州市';
EMPLOYEE_ID NAME
----------- --------------------
1 张三
嵌套表的特性
查看嵌套表的相关信息（查看dba_nested_tables视图）：
select owner,table_name,table_type_owner,table_type_name,parent_table_name,parent_table_column from dba_nested_tables where owner='SZR';
OWNER TABLE_NAME TABLE_TYPE_OWNER TABLE_TYPE_NAME PARENT_TABLE_NAME PARENT_TABLE_COLUMN
--------------- -------------------- -------------------- -------------------- -------------------- --------------------
SZR EMPLOYEE_ADDRESSES_N SZR ADDRESS_LIST_TYPE EMPLOYEES_NESTED ADDRESS_LIST
嵌套表的列数据无法在user_tab_columns中查到：
szr@orcl(2524)> select owner,table_name,column_name,data_type from dba_tab_columns where OWNER='SZR' AND TABLE_NAME IN ('EMPLOYEE_ADDRESSES_NT','EMPLOYEES_NESTED');
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
--------------- -------------------- -------------------- --------------------
SZR EMPLOYEES_NESTED EMPLOYEE_ID NUMBER
SZR EMPLOYEES_NESTED NAME VARCHAR2
SZR EMPLOYEES_NESTED ADDRESS_LIST ADDRESS_LIST_TYPE
总结
嵌套表作为早期解决方案，其应用场景在很大程度上已经被更灵活、更通用的 JSON 特性所覆盖。Oracle 在 12c（12.1.0.2）版本中引入了对JSON的支持，如果您正在使用 Oracle 19c，那么您已经拥有了一个非常强大且成熟的 JSON 功能集，完全足以应对绝大多数业务场景。