Oracle PL/SQL 中修改 Collection 參數的關鍵技巧：IN OUT 模式詳解

問題背景

在 PL/SQL 中定義物件類型與 Collection（如 TABLE OF OBJECT_TYPE），若需在程序內部修改 Collection 元素屬性並反映回呼叫端，直接對 IN 模式參數賦值會拋出 PLS-00363: expression cannot be used as an assignment target。

類型定義

-- 1. 物件類型
CREATE OR REPLACE TYPE department_type AS OBJECT (
  dno      NUMBER(10),
  name     VARCHAR2(50),
  location VARCHAR2(50)
);

-- 2. Collection 類型
CREATE OR REPLACE TYPE dept_array AS TABLE OF department_type;

錯誤示範（IN 模式）

CREATE OR REPLACE PACKAGE BODY obj_pkg AS
  PROCEDURE modify_dept(p_depts IN dept_array) IS
  BEGIN
    FOR i IN p_depts.FIRST .. p_depts.LAST LOOP
      p_depts(i).location := 'New Loc ' || i;  -- 編譯錯誤 PLS-00363
    END LOOP;
  END;
END;

正確寫法：IN OUT 模式

CREATE OR REPLACE PACKAGE BODY obj_pkg AS
  PROCEDURE modify_dept(p_depts IN OUT NOCOPY dept_array) IS
  BEGIN
    FOR i IN 1 .. p_depts.COUNT LOOP
      p_depts(i).location := 'New Loc ' || i;  -- 合法
      -- 同步寫入資料表
      INSERT INTO department_test VALUES (p_depts(i).dno, p_depts(i).name, p_depts(i).location);
    END LOOP;
  END;
END;

關鍵點解析

完整可執行範例

-- 測試區塊
DECLARE
  v_depts dept_array := dept_array(
    department_type(10, 'HR', 'Taipei'),
    department_type(20, 'IT', 'Hsinchu')
  );
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before: ' || v_depts(1).location);  -- Taipei
  obj_pkg.modify_dept(v_depts);
  DBMS_OUTPUT.PUT_LINE('After:  ' || v_depts(1).location);  -- New Loc 1
END;
/

常見陷阱與最佳實務

1. NOCOPY 與例外處理：若程序拋出未捕捉例外，NOCOPY 參數可能處於部分修改狀態。建議在關鍵業務中使用 IN OUT（無 NOCOPY）確保原子性，或在 EXCEPTION 區塊回滾邏輯。
2. 集合未初始化：OUT/IN OUT 參數若未初始化（dept_array()），第一次存取會報 ORA-06531: Reference to uninitialized collection。
3. 稀疏集合：使用 FIRST/LAST/NEXT/PRIOR 迴圈而非 1..COUNT，以正確處理 DELETE(n) 後的稀疏索引。
4. 跨資料庫連結：透過 DB Link 傳遞 Collection 至遠端程序時，遠端必須擁有相同類型定義（CREATE TYPE ... AS OBJECT 在兩端同名同結構）。

結語

修改 Collection 參數的核心在於宣告為 IN OUT NOCOPY。理解四種參數模式的語意與效能差異，配合正確的初始化與迴圈寫法，即能安全、高效地在 PL/SQL 間傳遞並修改複雜結構資料。