您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 巢狀表完整入門指南

編輯：Oracle 資料庫基礎

前言

Oracle 集合型別包含三種：Varrays (變長陣列)、Nested Tables (巢狀表)、Associative Arrays (關聯陣列/索引表)。巢狀表是無固定大小上限的有序集合，元素型別相同，可儲存於資料表欄位中，支援 SQL 操作 (SELECT/INSERT/UPDATE/DELETE)，是實現「表中表」關係的強大工具。本文從型別建立、資料表定義、CRUD 操作完整示範。

一、建立物件型別

CREATE OR REPLACE TYPE name_typ AS OBJECT (
    first_name  VARCHAR2(30),
    second_name VARCHAR2(30),
    short_name  VARCHAR2(10)
);
/
-- Type created

二、建立巢狀表型別

CREATE OR REPLACE TYPE nested_table_name AS TABLE OF name_typ;
/
-- Type created

關鍵字： 使用 TABLE OF 而非 AS TABLE OF，後者會報 ORA-00928。

三、在資料表中使用巢狀表型別

CREATE TABLE test_family (
    family_id NUMBER(6) PRIMARY KEY,
    members   nested_table_name
)
NESTED TABLE members STORE AS nested_members;
/
-- Table created

NESTED TABLE members STORE AS nested_members：指定巢狀表資料的實體儲存段名稱 (存儲表)，必須指定，否則報 ORA-22913。
儲存表 nested_members 為系統自動維護的獨立段，不可直接 DML，需透過父表操作。

四、查詢巢狀表結構資訊

DESC nested_table_name;
-- NESTED_TABLE_NAME TABLE OF NAME_TYP
-- FIRST_NAME, SECOND_NAME, SHORT_NAME

DESC test_family;
-- FAMILY_ID NUMBER(6)
-- MEMBERS   NESTED_TABLE_NAME

SET DESCRIBE DEPTH 2
DESC test_family;
-- 顯示巢狀表內屬性細節

SELECT * FROM user_nested_tables;
-- 查看所有巢狀表欄位對應的儲存表名稱

五、巢狀表 CRUD 操作完整範例

1. INSERT (新增家庭與成員)

-- 家庭 1：兩位成員
INSERT INTO test_family VALUES (
    1,
    nested_table_name(
        name_typ('張', '金花婆婆', '婆婆'),
        name_typ('楊', '00', '楊帥帥')
    )
);

-- 家庭 2：四位成員
INSERT INTO test_family VALUES (
    2,
    nested_table_name(
        name_typ('曹', '妙妙草', '苗苗'),
        name_typ('徐', '0', '海豹'),
        name_typ('狄', '狄狄兔', '海洋一餅'),
        name_typ('於', '00', '小魚')
    )
);

2. SELECT (查詢)

-- 查詢整個家庭 (巢狀表以集合形式顯示)
SELECT * FROM test_family WHERE family_id = 1;

-- 展開巢狀表查詢 (使用 TABLE() 函數)
SELECT f.family_id, m.*
FROM test_family f, TABLE(f.members) m
WHERE f.family_id = 1;

-- 統計每個家庭成員數
SELECT family_id, CARDINALITY(members) AS member_cnt
FROM test_family;

3. UPDATE / INSERT INTO 巢狀表 (新增成員)

-- 向家庭 1 新增一位成員
INSERT INTO TABLE(SELECT members FROM test_family WHERE family_id = 1)
VALUES (name_typ('蘇', '小可', '可可豆'));

-- 修改家庭 1 中暱稱 '可可豆' 的成員姓名
UPDATE TABLE(SELECT members FROM test_family WHERE family_id = 1) m
SET m = name_typ('莫', '小米', '可可豆')
WHERE m.short_name = '可可豆';

語法重點： TABLE(SELECT nested_col FROM parent_table WHERE ...) 將巢狀表轉為可 DML 的虛擬表。

4. DELETE FROM 巢狀表 (移除成員)

-- 刪除家庭 1 中暱稱 '可可豆' 的成員
DELETE FROM TABLE(SELECT members FROM test_family WHERE family_id = 1) m
WHERE m.short_name = '可可豆';

5. 完整驗證流程

-- 檢查家庭 1 最終成員
SELECT f.family_id, m.*
FROM test_family f, TABLE(f.members) m
WHERE f.family_id = 1;
-- 結果：張/金花婆婆/婆婆、楊/00/楊帥帥 (蘇/莫已被刪除/修改)

六、進階操作與注意事項

1. 巢狀表與 Varrays 差異

特性	巢狀表	Varrays
大小上限	無 (受限於表空間)	建立時指定最大元素數
儲存方式	獨立儲存段 (Out-of-line)	內嵌父表 (Inline，小於 4KB 時)
索引支援	可建立索引於儲存表	不支援
稀疏性	支援 (可刪除中間元素)	密集 (不可有空洞)
適用場景	大量、不固定、需獨立查詢的子資料	少量、固定、常整體讀寫的屬性

2. 索引優化

-- 在儲存表上建立索引加速巢狀表查詢
CREATE INDEX idx_nested_short_name ON nested_members (short_name);

3. PL/SQL 中操作巢狀表變數

DECLARE
    v_members nested_table_name := nested_table_name();
BEGIN
    v_members.EXTEND(3);
    v_members(1) := name_typ('A', 'B', 'C');
    -- 可使用 FIRST, LAST, NEXT, PRIOR, EXISTS, COUNT, LIMIT, EXTEND, TRIM, DELETE 等集合方法
    FOR i IN v_members.FIRST .. v_members.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_members(i).short_name);
    END LOOP;
END;
/