Oracle 遊標與觸發器結合實戰：維修中心不合格率計算存儲過程解析

業務背景

某維修服務管理系統需定期計算各維修中心（Service Center）近 3/6 個月的不合格率，作為考核依據。數據涉及：維修工單（fact_repair_sheet）、備件驗收（fact_vefpart）、電話回訪（fact_verify）、維修中心維表（dim_service_center）。

存儲過程核心邏輯（節選重構）

CREATE OR REPLACE PROCEDURE pro_rate_calculate AS
  -- 1. 聲明遊標：遍歷所有維修中心
  CURSOR cur_sc_id IS
    SELECT sc_id FROM dim_service_center;

  v_sc_id       dim_service_center.sc_id%TYPE;
  v_count3      NUMBER;   -- 單表 3 月不合格數
  v_allcount3   NUMBER;   -- 3 月總不合格數
  v_count6      NUMBER;
  v_allcount6   NUMBER;
  v_datediff    NUMBER;
  v_rate        NUMBER;
BEGIN
  -- 2. 打開遊標並循環
  OPEN cur_sc_id;
  LOOP
    FETCH cur_sc_id INTO v_sc_id;
    EXIT WHEN cur_sc_id%NOTFOUND;

    -- 3. 計算【備件驗收】近 3 月不合格數
    SELECT COUNT(*)
    INTO v_count3
    FROM dim_service_center dsc
    JOIN fact_repair_sheet frs ON dsc.sc_id = frs.sc_id
    JOIN fact_vefpart fvp ON fvp.repid = frs.repair_sheet_id
    WHERE (fvp.vef_result1 = '2' AND fvp.major_flag = '2')  -- 初核不合格且無需復核
       OR fvp.vef_result2 = '2'                              -- 或復核不合格
      AND fvp.ismajor = '1'                                  -- 主要備件
      AND frs.close_date > ADD_MONTHS(SYSDATE, -3)           -- 近 3 月
      AND dsc.sc_id = v_sc_id;

    v_allcount3 := v_count3;

    -- 4. 計算【電話回訪】近 3 月不合格數
    SELECT COUNT(*)
    INTO v_count3
    FROM fact_verify fv
    JOIN fact_repair_sheet frs ON fv.repid = frs.repair_sheet_id
    JOIN dim_service_center dsc ON dsc.sc_id = frs.sc_id
    WHERE fv.callstatus = '不合格'
      AND frs.close_date > ADD_MONTHS(SYSDATE, -3)
      AND dsc.sc_id = v_sc_id;

    v_allcount3 := v_allcount3 + v_count3;  -- 合併兩源不合格總數

    -- 5. 同理計算近 6 月指標（v_allcount6）...
    -- 6. 計算不合格率 v_rate := v_allcount3 / v_total * 100 ...
    -- 7. 寫入結果表或更新維表指標列
    -- UPDATE dim_service_center SET rate_3m = v_rate WHERE sc_id = v_sc_id;

  END LOOP;
  CLOSE cur_sc_id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;  -- 或記錄日誌表
END pro_rate_calculate;
/

關鍵技術點解析

性能優化建議

1. 批量綁定：若中心數千個，改用 BULK COLLECT + FORALL 減少上下文切換。
   SELECT sc_id BULK COLLECT INTO v_sc_ids FROM dim_service_center; → FORALL i IN 1..v_sc_ids.COUNT UPDATE ...
2. 單語句聚合：將循環邏輯改寫為單條 MERGE 或 INSERT ... SELECT，利用 SQL 引擎並行聚合：
   MERGE INTO dim_service_center dsc USING (SELECT sc_id, COUNT(*) cnt FROM ... GROUP BY sc_id) src ON (dsc.sc_id = src.sc_id) WHEN MATCHED THEN UPDATE SET rate_3m = src.cnt / dsc.total * 100;
3. 索引支援：fact_repair_sheet(sc_id, close_date)、fact_vefpart(repid, vef_result1, vef_result2, ismajor)、fact_verify(repid, callstatus) 組合索引。
4. 分區表：按 close_date 範圍分區 fact_repair_sheet，查詢自動分區剪枝。
5. 物化視圖：每日刷新 mv_sc_rate_3m，報表直接查詢，避免即時計算壓力。

觸發器自動觸發場景（擴展）

-- 工單關閉時自動更新中心不合格統計（簡化示例）
CREATE OR REPLACE TRIGGER trg_repair_sheet_after_close
AFTER UPDATE OF close_date ON fact_repair_sheet
FOR EACH ROW
WHEN (NEW.close_date IS NOT NULL AND OLD.close_date IS NULL)
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 避免觸發器中 COMMIT 影響主事務
BEGIN
  -- 增量更新統計表
  UPDATE sc_stats_daily
  SET total_cnt = total_cnt + 1,
      defect_cnt = defect_cnt + CASE WHEN :NEW.overall_result = '不合格' THEN 1 ELSE 0 END
  WHERE sc_id = :NEW.sc_id AND stat_date = TRUNC(:NEW.close_date);

  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO sc_stats_daily (sc_id, stat_date, total_cnt, defect_cnt)
    VALUES (:NEW.sc_id, TRUNC(:NEW.close_date), 1, CASE WHEN :NEW.overall_result = '不合格' THEN 1 ELSE 0 END);
  END IF;
  COMMIT;
END;
/

結語

遊標循環處理多維度聚合是 PL/SQL 經典模式，但大數據量下必須向集合操作（SQL 聚合、MERGE、物化視圖）轉型。觸發器實現增量維護需謹慎使用 AUTONOMOUS_TRANSACTION 避免死鎖與主事務污染。建議採用「日增量觸發器維護統計表 + 定時過程全量校準」雙軌制，兼顧實時性與準確性。