PL/SQL 進階開發實戰技巧：管道函數、定時、集合 vs 游標、BULK COLLECT 最佳實務

技巧一：管道函數 - 讓 PL/SQL 函數支援並行查詢

問題

一般 PL/SQL 函數在 SQL 中被呼叫時會導致查詢序列化 (Serial Execution)，無法使用 Parallel Query。

解決：PIPELINED + PARALLEL_ENABLE

CREATE TYPE num_arr IS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION gen_numbers(p_cnt NUMBER) RETURN num_arr PIPELINED PARALLEL_ENABLE IS
BEGIN
  FOR i IN 1..p_cnt LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;
/
-- 使用
SELECT * FROM TABLE(gen_numbers(1000000)); -- 可並行執行

關鍵規則

• 輸入參數必須為 REF CURSOR (資料來源)
• 回傳型別為 Nested Table 或 VARRAY
• 函數規格需宣告 PARALLEL_ENABLE
• 適用場景：ETL 轉換、大量資料產生器、並行載入

技巧二：精確定時等待 - DBMS_LOCK.SLEEP

-- 等待 2.5 秒
EXEC DBMS_LOCK.SLEEP(2.5);

• 參數為 NUMBER，支援小數秒 (毫秒級)
• 釋放 CPU，不佔用資源
• 需 GRANT EXECUTE ON DBMS_LOCK TO user;

技巧三：Session 間定時通訊 - DBMS_PIPE

單一 Session 內用 DBMS_UTILITY.GET_TIME 迴圈等待即可；跨 Session 需用 Pipe 傳遞訊號：

-- Sender
DBMS_PIPE.PACK_MESSAGE('GO');
DBMS_PIPE.SEND_MESSAGE('sync_pipe');

-- Receiver
DBMS_PIPE.RECEIVE_MESSAGE('sync_pipe', timeout_seconds);
DBMS_PIPE.UNPACK_MESSAGE(msg);

技巧四：回傳資料集 - 集合 vs 游標變數

技巧五：高效使用游標 - 5 大黃金法則

1. 優先使用 BULK COLLECT：FETCH c BULK COLLECT INTO coll LIMIT 1000; 批量提取，9i R2 起支援 FORALL 批量 DML。
2. 顯式游標優於隱式：CURSOR c IS ... 可控制 OPEN/FETCH/CLOSE、重用、傳參，效能略優於 SELECT INTO。
3. 小型/靜態表 → Package Level Collection Cache：

   CREATE OR REPLACE PACKAGE pkg_cache IS
     TYPE code_tab IS TABLE OF code_table%ROWTYPE INDEX BY PLS_INTEGER;
     g_codes code_tab;
     PROCEDURE load_codes;
   END;
   /
   CREATE OR REPLACE PACKAGE BODY pkg_cache IS
     PROCEDURE load_codes IS
     BEGIN
       SELECT * BULK COLLECT INTO g_codes FROM code_table;
     END;
     -- 查詢直接讀 g_codes(i).col，避免 SGA 存取
   END;

   資料駐留於 PGA (Process Global Area)，存取速度比 SGA 快數倍。

4. 使用 CURSOR FOR LOOP 簡化程式碼：隱含 OPEN/FETCH/CLOSE，讀碼更清晰。
5. 避免在迴圈內 COMMIT：導致 ORA-01555、游標失效，改用批量處理分批 Commit。

技巧六：BULK COLLECT 進階 - LIMIT 與 SAVE EXCEPTIONS

DECLARE
  CURSOR c IS SELECT * FROM large_table;
  TYPE t_tab IS TABLE OF large_table%ROWTYPE;
  v_tab t_tab;
  v_err_cnt PLS_INTEGER;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_tab LIMIT 5000;
    EXIT WHEN v_tab.COUNT = 0;
    
    BEGIN
      FORALL i IN 1..v_tab.COUNT SAVE EXCEPTIONS
        INSERT INTO target_table VALUES v_tab(i);
    EXCEPTION
      WHEN OTHERS THEN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
          v_err_cnt := v_err_cnt + 1;
          DBMS_OUTPUT.PUT_LINE('Error at index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
        END LOOP;
    END;
    
    COMMIT;
  END LOOP;
  CLOSE c;
END;

結語

掌握管道函數並行化、精確定時、跨 Session 通訊、集合/游標選型、游標五大法則與 BULK COLLECT 進階錯誤處理，能將 PL/SQL 開發效能與代碼品質推向專業級水準。