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

業務背景

需定時計算各維修服務中心近 3/6 個月的 **不合格率**，依據初核/複核結果、主要備件標識、回訪狀態等多維條件聚合，結果寫入統計表供報表查詢。

核心邏輯拆解

1. 游標遍歷所有維修中心 (`DIM_SERVICE_CENTER.SC_ID`)。
2. 對每個中心，分別統計：
   • 近 3 個月 **工單備件不合格數** (初核不合格免複核 OR 複核不合格，且為主要備件)。
   • 近 3 個月 **回訪不合格數** (`CALLSTATUS='不合格'`)。
   • 近 6 個月同維度統計 (判斷 `CLOSE_DATE` 時間差)。
3. 彙總計算不合格率，寫入目標表。

完整存儲過程代碼 (重構優化版)

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

  v_sc_id       dim_service_center.sc_id%TYPE;
  v_count3      NUMBER;      -- 單表單月不合格計數
  v_allcount3   NUMBER := 0; -- 彙總 3 月不合格
  v_count6      NUMBER;
  v_allcount6   NUMBER := 0;
  v_datediff    NUMBER;      -- 時間差，>0 表示 6 個月前
  v_rate        NUMBER;
BEGIN
  OPEN cur_sc_id;
  LOOP
    FETCH cur_sc_id INTO v_sc_id;
    EXIT WHEN cur_sc_id%NOTFOUND;

    -- 重置單中心計數器
    v_allcount3 := 0;
    v_allcount6 := 0;

    /* ---------- 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 dsc.sc_id = v_sc_id
      AND frs.close_date > ADD_MONTHS(SYSDATE, -3)
      AND (
           (fvp.vef_result1 = '2' AND fvp.major_flag = '2')  -- 初核不合格且免複核
        OR (fvp.vef_result2 = '2' AND fvp.ismajor = '1')    -- 或複核不合格且主要備件
      );
    v_allcount3 := v_allcount3 + v_count3;

    /* ---------- 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 dsc.sc_id = v_sc_id
      AND fv.callstatus = '不合格'
      AND frs.close_date > ADD_MONTHS(SYSDATE, -3);
    v_allcount3 := v_allcount3 + v_count3;

    /* ---------- 6 個月同邏輯 (略) ---------- */
    -- 同理計算 v_allcount6

    /* ---------- 計算不合格率並寫入統計表 ---------- */
    -- v_rate := CASE WHEN total > 0 THEN v_allcount3 / total * 100 ELSE 0 END;
    -- INSERT INTO stat_center_rate ...;
  END LOOP;
  CLOSE cur_sc_id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END pro_rate_calculate;
/
-- 排程每日執行
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_RATE_CALC_DAILY',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PRO_RATE_CALCULATE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',  -- 凌晨 2 點
    enabled         => TRUE);
END;

效能優化關鍵點

重構建議：單 SQL 集合化寫法 (11g+)

-- 一次性算出所有中心 3/6 月不合格數，避免循環
INSERT INTO stat_center_rate (sc_id, rate_3m, rate_6m, calc_date)
SELECT
  dsc.sc_id,
  ROUND(NVL(q3.cnt,0) / NULLIF(q3.total,0) * 100, 2),
  ROUND(NVL(q6.cnt,0) / NULLIF(q6.total,0) * 100, 2),
  TRUNC(SYSDATE)
FROM dim_service_center dsc
LEFT JOIN (
  SELECT frs.sc_id,
         COUNT(*) AS cnt,
         COUNT(*) OVER (PARTITION BY frs.sc_id) AS total  -- 示意，實際需分母邏輯
  FROM fact_repair_sheet frs
  JOIN fact_vefpart fvp ON fvp.repid = frs.repair_sheet_id
  WHERE frs.close_date > ADD_MONTHS(SYSDATE, -3)
    AND ((fvp.vef_result1='2' AND fvp.major_flag='2') OR (fvp.vef_result2='2' AND fvp.ismajor='1'))
  GROUP BY frs.sc_id
) q3 ON dsc.sc_id = q3.sc_id
LEFT JOIN ( ... 6 月同邏輯 ... ) q6 ON dsc.sc_id = q6.sc_id;

結語

游標循環雖直觀，但在大數據量、高頻排程場景下 **效能與擴展性均不足**。掌握 **集合化 SQL 思維、分區表、統計資訊、批次綁定**，將行級循環重構為集合操作，是 PL/SQL 開發者進階為資料庫架構師的關鍵轉折點。