Oracle VARRAY 變長數組實戰：創建、插入、查詢、更新、修改元素類型與擴容

什麼是 VARRAY？

• Variable-size Array：有序、固定最大長度、可變當前長度的集合類型。
• 元素通过下標（1-based）訪問，下標連續、無空洞。
• 存儲在同一表行內（行內 LOB 機制），適合小規模、固定上限的重複屬性（如電話號碼、郵箱列表）。
• 超過 4KB 會自動轉為行外存儲（LOB 段）。

完整實戰流程

1. 創建 VARRAY 類型

CREATE OR REPLACE TYPE varray_phone AS VARRAY(3) OF VARCHAR2(50);
-- 最大 3 個元素，每個最長 50 字符

2. 創建包含 VARRAY 列的表

CREATE TABLE person3 (
  id         INTEGER CONSTRAINT person3_pk PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name  VARCHAR2(20),
  phone      varray_phone
);

3. 插入數據（構造函數初始化）

-- 3 個電話
INSERT INTO person3 VALUES (1, 'yuan', 'weixiang', varray_phone('12345','34567','56789'));

-- 空數組
INSERT INTO person3 VALUES (2, 'hao', 'lihai', varray_phone());

-- 1 個電話
INSERT INTO person3 VALUES (3, 'li', 'ming', varray_phone('13800138000'));

COMMIT;

4. 查詢 VARRAY 內容

SELECT id, first_name, last_name, phone
FROM person3;

-- 結果示例
-- 1 yuan weixiang VARRAY_PHONE('12345', '34567', '56789')
-- 2 hao  lihai       VARRAY_PHONE()
-- 3 li   ming        VARRAY_PHONE('13800138000')

5. 更新整個 VARRAY（替換所有元素）

UPDATE person3
SET phone = varray_phone('12345','34567')
WHERE id = 2;

-- 查詢確認
SELECT * FROM person3 WHERE id = 2;
-- 2 hao lihai VARRAY_PHONE('12345', '34567')

6. 修改單個元素（需 PL/SQL 或 SQL 擴展）

-- 方法 1：PL/SQL 修改第 1 個元素
DECLARE
  v_phones varray_phone;
BEGIN
  SELECT phone INTO v_phones FROM person3 WHERE id = 1 FOR UPDATE;
  v_phones(1) := '99999';  -- 下標從 1 開始
  UPDATE person3 SET phone = v_phones WHERE id = 1;
  COMMIT;
END;
/

-- 方法 2：12c+ SQL 直接訪問元素（僅查詢）
SELECT p.id, p.phone(1) AS first_phone FROM person3 p;

7. 修改 VARRAY 定義（元素類型、最大長度）

-- 7.1 擴大元素長度：VARCHAR2(50) → VARCHAR2(60)
ALTER TYPE varray_phone MODIFY ELEMENT TYPE VARCHAR2(60) CASCADE;
-- CASCADE 將變更傳播給依賴表（person3）

-- 7.2 擴大最大元素數：3 → 5
ALTER TYPE varray_phone MODIFY LIMIT 5 CASCADE;

• CASCADE：自動更新依賴對象（表、視圖、PL/SQL 單元），否則需手動重新編譯。
• 修改類型會導致依賴對象 暫時失效，操作完成後自動重新編譯。

8. 數據字典查詢

-- 用戶自定義 VARRAY
SELECT type_name, limit, elem_type_name, elem_type_mod
FROM user_varrays;

-- 全庫 VARRAY
SELECT owner, type_name, limit, elem_type_name
FROM all_varrays;

VARRAY vs Nested Table 對比

最佳實踐建議

1. 評估上限：業務明確上限（如最多 3 個電話）用 VARRAY；無上限或超大用 Nested Table。
2. 索引支援：VARRAY 列無法直接建 B-tree 索引，需 CREATE INDEX ... ON (phone(1))（函數索引）或虛擬列索引。
3. PL/SQL 批量操作：FORALL i IN 1..v_arr.COUNT INSERT ... VALUES (v_arr(i)) 高效批量寫入。
4. JSON 替代方案：12c+ JSON 類型、JSON_ARRAY、JSON_TABLE 更靈活，建議新項目優先考慮。

結語

VARRAY 為「有序、定長上限、行內存儲」的集合類型，通過 CREATE TYPE ... AS VARRAY(n) OF datatype 定義，支持 ALTER TYPE ... MODIFY LIMIT/ELEMENT TYPE CASCADE 動態擴容。掌握構造函數初始化、PL/SQL 元素修改、數據字典查詢，即可在電話列表、標籤集合、固定規格屬性等場景輕鬆落地。新架構建議評估 JSON 類型作為更現代的替代方案。