Oracle 嵌套表與可變數組（VARRAY）完整指南

嵌套表

定義與特性

嵌套表是「表中之表」，以集合形式儲存一對多關係，避免連接查詢。主表每筆記錄可關聯多行子表資料。

建立與使用範例

-- 1. 建立物件型別
CREATE TYPE animal_ty AS OBJECT (
  breed VARCHAR2(25),
  name  VARCHAR2(25),
  birthdate DATE
);

-- 2. 建立嵌套表型別
CREATE TYPE animals_nt AS TABLE OF animal_ty;

-- 3. 建立主表，指定嵌套表儲存段
CREATE TABLE breeder (
  breedername VARCHAR2(25),
  animals animals_nt
) NESTED TABLE animals STORE AS animals_nt_tab;

-- 4. 插入資料
INSERT INTO breeder VALUES (
  'mary',
  animals_nt(
    animal_ty('dog','butch','31-MAR-97'),
    animal_ty('dog','rover','31-MAR-97'),
    animal_ty('dog','julio','31-MAR-97')
  )
);

-- 5. 查詢嵌套表
SELECT name, birthdate FROM TABLE(SELECT animals FROM breeder);
SELECT name, birthdate FROM TABLE(SELECT animals FROM breeder WHERE breedername='mary') WHERE name='dog';

優勢

• 物件復用：面向對象設計提高代碼/數據庫對象復用率。
• 標準支援：標準化對象促進跨應用共享。
• 定義存取路徑：封裝方法與數據，統一存取邏輯。

可變數組

定義與特性

VARRAY 是有大小上限的有序集合，概念上是受限的嵌套表。適合儲存固定上限的重複屬性（如聯絡方式）。

建立與使用範例

-- 1. 建立元素型別
CREATE TYPE comm_info AS OBJECT (
  no       NUMBER(3),
  comm_type VARCHAR2(20),
  comm_no   VARCHAR2(30)
);

-- 2. 建立 VARRAY 型別（最多 50 個元素）
CREATE TYPE comm_info_list AS VARRAY(50) OF comm_info;

-- 3. 建立主表
CREATE TABLE user_info (
  user_id   NUMBER(6),
  user_name VARCHAR2(20),
  user_comm comm_info_list
);

-- 4. 插入資料
INSERT INTO user_info VALUES (
  1, 'mary',
  comm_info_list(
    comm_info(1,'手機','13651401919'),
    comm_info(2,'呼機','1281234567')
  )
);

-- 5. 查詢
SELECT user_comm FROM user_info WHERE user_id=1;
SELECT comm_type, comm_no FROM TABLE(SELECT user_comm FROM user_info WHERE user_id=1) WHERE no=1;

優勢

• 同嵌套表：物件復用、標準支援、定義存取路徑。
• 有序且有上限，適合固定結構的小集合。

嵌套表 vs VARRAY 選型建議