Oracle 9i PL/SQL 程式設計經驗彙總：管道函數、定時、集合 vs 游標、Bulk Operations

1. 管道函數 加速平行查詢與資料載入

• 痛點：SQL 中呼叫 PL/SQL 函數會導致查詢序列化 (無法平行查詢)，資料倉庫大量資料處理效能受限。
• 解法：使用 Pipelined Table Function。
  • 輸入參數：`SYS_REFCURSOR` (引用游標)。
  • 回傳型別：Nested Table / VARRAY (集合)。
  • 函數宣告加 `PARALLEL_ENABLE` 關鍵字。
  • 函數體內用 `PIPE ROW(out_rec)` 逐行輸出，而非一次性回傳集合。
• 效果：啟用平行查詢 (PX)，多執行緒同時消費管道資料流，大幅縮短 ETL/報表查詢時間。

2. PL/SQL 精確暫停：DBMS_LOCK.SLEEP

-- 精確暫停 2.5 秒
DBMS_LOCK.SLEEP(2.5);

• 參數 `SECONDS IN NUMBER`，支援小數 (毫秒級)。
• 釋放 CPU，不佔用資源，優於忙等待迴圈。
• 需 `GRANT EXECUTE ON DBMS_LOCK TO user` (預設僅 DBA)。

3. 同一 Session 內定時觸發後續操作

DECLARE
  v_delay CONSTANT PLS_INTEGER := 100;  -- 1 秒 (單位：百毫秒)
  v_start PLS_INTEGER;
  v_end   PLS_INTEGER;
BEGIN
  v_start := DBMS_UTILITY.GET_TIME;  -- 百毫秒
  LOOP
    v_end := DBMS_UTILITY.GET_TIME;
    EXIT WHEN (v_end - v_start) >= v_delay;
    -- 可放簡單運算避免優化器移除迴圈
    NULL;
  END LOOP;
  -- 執行後續操作
  INSERT INTO log_table VALUES ('Delayed action executed');
END;

• `DBMS_UTILITY.GET_TIME` 回傳百毫秒 (1/100 秒) 整數。
• 跨 Session 定時/通訊需用 `DBMS_PIPE` (訊息佇列) 或 `DBMS_ALERT` (事件通知)。

4. 回傳資料集：集合 vs 遊標變數

5. 高效使用遊標的三大原則

1. 盡量使用 BULK COLLECT / FORALL：

   
       -- 批次提取 1000 列
       FETCH cur BULK COLLECT INTO v_array LIMIT 1000;
       -- 批次寫入
       FORALL i IN INDICES OF v_array
         INSERT INTO target_table VALUES v_array(i);
       

   • Oracle 9i R2 甚至支援 `BULK COLLECT INTO` 直接寫入記錄型別集合。
   • 減少 SQL/PL/SQL 引擎切換，典型提升 5-10 倍吞吐。
2. 優先使用顯式遊標：
   • 隱式遊標 (`SELECT INTO`) 適用單列查詢；多列/複雜邏輯顯式遊標 (`CURSOR c IS ...`) 可控制 `OPEN/FETCH/CLOSE`、支援參數化、可重用、除錯友善。
   • 顯式遊標在舊版本中執行速度略快 (避免隱式 `OPEN/FETCH/CLOSE` 額外檢查)。
3. 小型/靜態查找表快取至 Package Level 集合 (PGA Cache)：

   
       CREATE OR REPLACE PACKAGE pkg_cache AS
         TYPE t_dept IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER;
         g_depts t_dept;
         PROCEDURE load_depts;
         FUNCTION get_dept(p_deptno NUMBER) RETURN dept%ROWTYPE;
       END;
       /
       CREATE OR REPLACE PACKAGE BODY pkg_cache AS
         PROCEDURE load_depts IS
         BEGIN
           SELECT * BULK COLLECT INTO g_depts FROM dept;
         END;
         FUNCTION get_dept(p_deptno NUMBER) RETURN dept%ROWTYPE IS
         BEGIN
           RETURN g_depts(p_deptno);  -- 直接記憶體存取，零 SQL 開銷
         END;
       END;
       /
       -- 應用啟動時呼叫 pkg_cache.load_depts
       

   • 資料駐留於 PGA (Process Global Area)，跨 Session 不共享，適合 Session 級唯讀參考資料 (代碼表、配置表)。
   • 若需跨 Session 共享，改用 `DBMS_SHARED_POOL.KEEP` 釘住 Function 結果或使用 Result Cache (11g+)。

總結

掌握 管道函數解鎖平行查詢、DBMS_LOCK/UTILITY 精確控制流、依呼叫端選擇集合或遊標、BULK COLLECT/FORALL 批次化、熱點靜態資料 PGA 快取，能將 PL/SQL 程式效能提升數個數量級，是高階 PL/SQL 開發者必備技能組合。