VARRAY 基本特性
- **固定最大長度** 的一維陣列，宣告時指定上限。
- 元素型別可為純量型別 (VARCHAR2, NUMBER, DATE 等) 或物件型別。
- 儲存於資料庫物件 (需
CREATE TYPE) 或 PL/SQL 區塊區域變數。
- 索引從 **1 起始** (非 0)。
完整區塊範例：批次插入訂單與明細
DECLARE
-- 區域變數
v_id VARCHAR2(20) := '9020071025101010';
v_tempid VARCHAR2(20);
-- 1. 定義 VARRAY 型別 (最大 2 元素, VARCHAR2(30))
TYPE arr_syvar IS VARRAY(2) OF VARCHAR2(30);
-- 2. 宣告陣列變數
v_arraypar arr_syvar;
BEGIN
-- 3. 初始化陣列 (建構函數)
v_arraypar := arr_syvar('1234567', '2345678');
-- 4. 迴圈處理陣列元素 (索引 1..2)
FOR k IN 1 .. 2 LOOP
v_tempid := v_id || TO_CHAR(k);
-- 插入主訂單
INSERT INTO ti_order_cd (orderid, msisdn, switchid, priority, state, create_time)
VALUES (v_tempid, v_arraypar(k), 'PHS', '0', 'K', SYSDATE);
-- 插入服務明細
INSERT INTO ti_order_serv_cd (orderid, servid, servcode)
VALUES (v_tempid, '0', 'CG001');
-- 插入參數明細
INSERT INTO ti_order_var_cd (orderid, paracode, paravalue)
VALUES (v_tempid, 'X009', v_arraypar(k));
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
關鍵語法對照
| 操作 | 語法 | 說明 |
|---|
| 定義型別 (Schema 層級) | CREATE OR REPLACE TYPE arr_tel IS VARRAY(10) OF VARCHAR2(20);可跨 SP/函數/表欄位重用 |
| 定義型別 (PL/SQL 區塊) | TYPE arr_local IS VARRAY(5) OF NUMBER;僅區塊內可見 |
| 建構/初始化 | v_arr := arr_type(val1, val2, ...);元素數量不可超過上限 |
| 存取元素 | v_arr(index)索引 1 起始；超出範圍拋 SUBSCRIPT_OUTSIDE_LIMIT |
| 擴展陣列 | v_arr.EXTEND(n);動態增加 n 個 NULL 元素 (不可超過上限) |
| 修剪陣列 | v_arr.TRIM(n);移除末端 n 個元素 |
| 取得長度 | v_arr.COUNT目前元素數量 |
| 取得上限 | v_arr.LIMIT宣告最大長度 |
| 判斷是否為空 | v_arr IS NULL未初始化時為 NULL |
VARRAY vs Nested Table vs Associative Array
| 特性 | VARRAY | Nested Table | Associative Array (Index-by Table) |
|---|
| 最大長度 | 固定上限 | 無上限 (受儲存空間限制) | 無上限 |
| 儲存於資料庫 | 可 (Schema 型別) | 可 (Schema 型別) | 不可 (僅 PL/SQL 記憶體) |
| 索引類型 | 整數 1..N | 整數 1..N (稀疏) | 整數/字串 (負數可) |
| 序列化儲存 | 有序、緊湊 | 無序、需額外映射表 | N/A |
| 適用場景 | 小型固定集合、順序重要、需儲存表欄位 | 大型/不定大小集合、集合運算 (MULTISET) | 暫存查找表、快取、演算法中介資料 |
效能與最佳實務
- 批次處理：陣列配合
FORALL 陳述式可大幅減少 Context Switch，提升批次 DML 效能 (範例改用 FORALL 見下)。
- 上限設定：依業務最大可能筆數設定，避免過大浪費 PGA 記憶體。
- 例外處理：
FORALL ... SAVE EXCEPTIONS 捕捉個別 DML 錯誤並繼續處理。
進階：FORALL 批次綁定範例
DECLARE
TYPE arr_id IS VARRAY(1000) OF VARCHAR2(20);
v_ids arr_id := arr_id();
BEGIN
-- 假設從某處收集 1000 筆 ID
v_ids.EXTEND(1000);
FOR i IN 1..1000 LOOP v_ids(i) := 'ID_' || i; END LOOP;
-- 批次插入 (單一 SQL 執行 1000 筆)
FORALL i IN INDICES OF v_ids
INSERT INTO temp_table (col1) VALUES (v_ids(i));
COMMIT;
END;
/
