PL/SQL 中模擬陣列操作：關聯式陣列與記錄型別實戰

PL/SQL 無原生陣列，但有更強大的替代方案

PL/SQL 不支援傳統意義的 Array，但透過關聯式陣列與記錄型別的組合，可實現一維、多維陣列的全部功能，且更具類型安全與擴充性。

一維關聯式陣列

DECLARE
  TYPE emp_ssn_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  best_employees  emp_ssn_array;
  worst_employees emp_ssn_array;
BEGIN
  best_employees(1)  := 123456;
  best_employees(2)  := 888888;
  worst_employees(1) := 222222;
  worst_employees(2) := 666666;

  FOR i IN 1..best_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('i='||i||
      ', best='||best_employees(i)||
      ', worst='||worst_employees(i));
  END LOOP;
END;

關鍵點：

• INDEX BY BINARY_INTEGER 宣告為關聯式陣列（舊稱 PL/SQL Table），下標可為負數、非連續。
• .COUNT、.FIRST、.LAST、.NEXT、.PRIOR、.EXISTS、.DELETE 等方法豐富操作。
• 12c 起支援 INDEX BY PLS_INTEGER 或 VARCHAR2 做下標。

多維陣列：記錄型別 + 關聯式陣列

DECLARE
  TYPE emp_rec_type IS RECORD (
    emp_id   employee_table.emp_id%TYPE,
    emp_name employee_table.emp_name%TYPE,
    emp_gender employee_table.emp_gender%TYPE
  );
  TYPE emp_array_type IS TABLE OF emp_rec_type INDEX BY BINARY_INTEGER;
  emp_arr emp_array_type;
  v_rec   emp_rec_type;
BEGIN
  v_rec.emp_id   := 300000000;
  v_rec.emp_name := 'Barbara';
  v_rec.emp_gender := 'Female';
  emp_arr(1) := v_rec;

  v_rec.emp_id   := 300000008;
  v_rec.emp_name := 'Rick';
  v_rec.emp_gender := 'Male';
  emp_arr(2) := v_rec;

  FOR i IN 1..emp_arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('i='||i||
      ', id='||emp_arr(i).emp_id||
      ', name='||emp_arr(i).emp_name||
      ', gender='||emp_arr(i).emp_gender);
  END LOOP;
END;

輸出：

i=1, id=300000000, name=Barbara, gender=Female
i=2, id=300000008, name=Rick, gender=Male

進階用法提示

• 集合作為參數傳遞：可定義於套件規格中，供存儲過程/函數間傳遞批量資料，搭配 FORALL 批量 DML、BULK COLLECT 批量查詢，大幅降低 Context Switch 開銷。
• 巢狀表 vs 關聯式陣列：巢狀表可持久化存入資料表欄位，關聯式陣列僅存在於會話記憶體，效能更高。
• VARRAY：定長、有序、可持久化，適合固定大小的小型集合（如電話號碼清單）。
• 12c 新特性：支援在 SQL 中直接使用套件級定義的集合型別（需 CREATE TYPE），無需宣告於 PL/SQL 區塊內。

最佳實踐建議

1. 優先使用 %TYPE/%ROWTYPE 錨定資料型別，避免硬編碼。
2. 大量資料處理時，結合 BULK COLLECT LIMIT 1000 分批取數，避免 PGA 耗盡。
3. 集合刪除元素後下標不連續，迭代請用 .FIRST/.NEXT 而非數字迴圈。
4. 將集合型別定義於套件規格，實現跨程式單元複用。

掌握集合與記錄型別，是編寫高效能、可維護 PL/SQL 程式的關鍵基石。