Oracle 10g FORALL增強：INDICES OF與VALUES OF處理非連續數組

FORALL演進歷程

10g前：FORALL i IN lower..upper 僅支持連續索引，數組不可有空洞。
10g新增：INDICES OF 和 VALUES OF 子句，完美解決稀疏數組和自定義順序處理。

INDICES OF：處理稀疏數組

DECLARE
    TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    sparse_arr num_tab;
BEGIN
    sparse_arr(1) := 100;
    sparse_arr(3) := 300;  -- 索引2缺失
    sparse_arr(5) := 500;  -- 索引4缺失
    
    FORALL i IN INDICES OF sparse_arr
        UPDATE emp SET salary = salary * 1.1 WHERE id = sparse_arr(i);
END;

自動跳過不存在的索引，僅處理1,3,5三個有效元素。

VALUES OF：自定義處理順序

DECLARE
    TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    main_arr num_tab;
    idx_arr  num_tab;  -- 索引數組
BEGIN
    main_arr(1) := 10; main_arr(2) := 20; main_arr(3) := 30;
    
    -- 按特定順序處理：先3，後1，最後2
    idx_arr(1) := 3; idx_arr(2) := 1; idx_arr(3) := 2;
    
    FORALL i IN VALUES OF idx_arr
        INSERT INTO log_table VALUES (main_arr(i), SYSDATE);
END;

HR模式實戰案例

需求：查找部門名含"IT"的部門，將其部門ID按特定順序插入日志表。

DECLARE
    TYPE dept_tab IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER;
    depts dept_tab;
    TYPE idx_tab IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
    it_indices idx_tab;
    cnt PLS_INTEGER := 0;
BEGIN
    -- 加載所有部門到內存
    SELECT * BULK COLLECT INTO depts FROM departments;
    
    -- 搜索IT相關部門，記錄索引
    FOR i IN depts.FIRST..depts.LAST LOOP
        IF depts(i).department_name LIKE '%IT%' THEN
            cnt := cnt + 1;
            it_indices(cnt) := i;
        END IF;
    END LOOP;
    
    -- 按索引數組順序批量插入
    FORALL i IN VALUES OF it_indices
        INSERT INTO it_dept_log VALUES (depts(i).department_id, SYSDATE);
END;

性能對比

最佳實踐

• 大批量DML優先考慮FORALL系列
• 集合類型首選TABLE OF ... INDEX BY PLS_INTEGER（關聯數組）
• 異常處理用SAVE EXCEPTIONS配合SQL%BULK_EXCEPTIONS
• 注意FORALL不支持SELECT，查詢用BULK COLLECT