Oracle 儲存程序實戰範例：GPRS 流量統計套件完整解析

案例背景

某電信增值服務平台需統計各服務商（SP）旗下終端設備（MDT）的 GPRS 上下行流量，定期彙總至統計表供計費與報表使用。本案例展示一個完整的 PL/SQL 套件實作，涵蓋游標巢狀迴圈、動態 SQL、大量資料處理與交易控制。

套件規格（Package Specification）

CREATE OR REPLACE PACKAGE psh_gprsstreamstat IS
  -- 公開介面：執行流量統計，回傳 1 表成功
  FUNCTION calcu_gprsstream RETURN NUMBER;
END psh_gprsstreamstat;
/

套件主體（Package Body）完整程式碼

CREATE OR REPLACE PACKAGE BODY psh_gprsstreamstat IS

  FUNCTION calcu_gprsstream RETURN NUMBER IS
    -- 常數定義
    c_ippackheadlen CONSTANT NUMBER := 40;  -- IP 封包頭長度

    -- 外層游標：所有啟用的服務商
    CURSOR c_spinfo IS
      SELECT DISTINCT spid FROM sh_spinfo WHERE isactive = '0';

    -- 內層游標變數：動態查詢某 SP 下的啟用終端
    c_mdtinfo  SYS_REFCURSOR;
    v_mdtinfo  NUMBER;

    -- 累計變數
    v_upcontentlens     NUMBER(20,0) := 0;
    vuptranscontentlens NUMBER(20,0) := 0;
    v_uptotallens       NUMBER(20,0) := 0;
    v_downcontentlens   NUMBER(20,0) := 0;
    v_downtotallens     NUMBER(20,0) := 0;

    -- 統計表主鍵
    v_newid NUMBER(20,0);
  BEGIN
    -- 初始化統計 ID：取現有最大值 +1
    SELECT NVL(MAX(statid), 0) + 1 INTO v_newid FROM sh_gprsstreamstat;

    -- 外層迴圈：遍歷每個服務商
    FOR v_spinfo IN c_spinfo LOOP
      -- 開啟內層游標：該 SP 下所有啟用的 MDT
      OPEN c_mdtinfo FOR
        SELECT DISTINCT mdtid
        FROM sh_mdtinfo
        WHERE isactive = '0' AND spid = v_spinfo.spid;

      LOOP
        FETCH c_mdtinfo INTO v_mdtinfo;
        EXIT WHEN c_mdtinfo%NOTFOUND;

        -- 重置單一 MDT 累計器
        v_upcontentlens := 0;
        vuptranscontentlens := 0;
        v_uptotallens := 0;
        v_downcontentlens := 0;
        v_downtotallens := 0;

        -- 1. 終端上傳原始內容長度 + IP 頭
        SELECT NVL(SUM(LENGTH(content) + c_ippackheadlen), 0)
        INTO v_upcontentlens
        FROM sh_gprsmdtupinfo
        WHERE mdtid = v_mdtinfo AND spid = v_spinfo.spid;

        -- 2. 終端轉發成功內容長度 + IP 頭
        SELECT NVL(SUM(LENGTH(content) + c_ippackheadlen), 0)
        INTO vuptranscontentlens
        FROM sh_gprsmdttransinfo
        WHERE issuccess = '1' AND mdtid = v_mdtinfo AND spid = v_spinfo.spid;

        v_uptotallens := v_upcontentlens + vuptranscontentlens;

        -- 3. 服務商下發內容長度 + IP 頭
        SELECT NVL(SUM(LENGTH(content) + c_ippackheadlen), 0)
        INTO v_downcontentlens
        FROM sh_gprsspdowninfo
        WHERE mdtid = v_mdtinfo AND spid = v_spinfo.spid;

        v_downtotallens := v_downcontentlens;

        -- 4. 有流量才寫入統計表
        IF v_uptotallens > 0 OR v_downtotallens > 0 THEN
          INSERT INTO sh_gprsstreamstat (
            statid, spid, mdtid, starttime, endtime,
            mdtupstream, spdownstream
          ) VALUES (
            v_newid, v_spinfo.spid, v_mdtinfo,
            SYSDATE, SYSDATE,  -- 實務上應為統計區間起訖時間
            v_uptotallens, v_downtotallens
          );
          v_newid := v_newid + 1;
        END IF;

      END LOOP;
      CLOSE c_mdtinfo;
      COMMIT;  -- 每個 SP 結束提交，避免長交易
    END LOOP;

    RETURN 1;
  END calcu_gprsstream;

  -- 套件初始化區塊（可選）
  BEGIN
    NULL;
  END psh_gprsstreamstat;
/

關鍵技術點深度解析

1. 參考游標（SYS_REFCURSOR）動態綁定

c_mdtinfo SYS_REFCURSOR;
OPEN c_mdtinfo FOR SELECT ... WHERE spid = v_spinfo.spid;

優勢：同一游標變數在迴圈中重複開啟不同查詢，避免硬編碼多個靜態游標。

2. NVL 處理 NULL 聚合結果

SELECT NVL(SUM(...), 0) INTO var FROM ...

若查無資料，SUM 回傳 NULL，直接指派給 NUMBER 變數會導致後續運算變 NULL，必須用 NVL 或 COALESCE 轉 0。

3. 批次提交策略

程式碼採用「每個 SP 提交一次」：

• 優點：縮短鎖定時間、減少 UNDO 空間壓力、失敗只回滾單一 SP
• 缺點：非原子性操作，中斷後需可重入設計
• 改進：加入「統計批次號」欄位，支援冪等重跑

4. 效能優化建議

優化版單一 SQL 範例

INSERT INTO sh_gprsstreamstat (statid, spid, mdtid, starttime, endtime, mdtupstream, spdownstream)
SELECT
  ROWNUM + (SELECT NVL(MAX(statid),0) FROM sh_gprsstreamstat),
  spid, mdtid, TRUNC(SYSDATE), TRUNC(SYSDATE),
  NVL(up_sum,0), NVL(down_sum,0)
FROM (
  SELECT
    m.spid, m.mdtid,
    SUM(LENGTH(u.content) + 40) AS up_sum,
    SUM(LENGTH(d.content) + 40) AS down_sum
  FROM sh_mdtinfo m
  LEFT JOIN sh_gprsmdtupinfo u ON u.mdtid = m.mdtid AND u.spid = m.spid
  LEFT JOIN sh_gprsmdttransinfo t ON t.mdtid = m.mdtid AND t.spid = m.spid AND t.issuccess = '1'
  LEFT JOIN sh_gprsspdowninfo d ON d.mdtid = m.mdtid AND d.spid = m.spid
  WHERE m.isactive = '0'
  GROUP BY m.spid, m.mdtid
) WHERE NVL(up_sum,0) + NVL(down_sum,0) > 0;

部署與排程整合

-- 建立排程作業（每日凌晨 02:00 執行）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_GPRS_DAILY_STAT',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PSH_GPRSSTREAMSTAT.CALCU_GPRSSTREAM',
    start_date      => TRUNC(SYSDATE+1) + 2/24,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE,
    comments        => 'GPRS 每日流量統計'
  );
END;
/

測試與驗證腳本

-- 1. 執行函數
SET SERVEROUTPUT ON
DECLARE
  v_result NUMBER;
BEGIN
  v_result := psh_gprsstreamstat.calcu_gprsstream;
  DBMS_OUTPUT.PUT_LINE('Return: ' || v_result);
END;
/

-- 2. 驗證統計結果
SELECT spid, mdtid, mdtupstream, spdownstream, starttime
FROM sh_gprsstreamstat
WHERE starttime >= TRUNC(SYSDATE)
ORDER BY statid;

-- 3. 檢查執行日誌（若有自訂日誌表）
SELECT * FROM job_run_log WHERE job_name = 'JOB_GPRS_DAILY_STAT' ORDER BY run_date DESC;

擴充建議

1. 分區表：sh_gprsstreamstat 依 starttime 區間分區，支援快速歸檔
2. 平行查詢：大量資料加入 PARALLEL Hint 或表屬性
3. 錯誤處理：套件加入 EXCEPTION WHEN OTHERS THEN LOG_ERROR; RAISE;
4. 參數化：統計區間、IP 頭長度移至設定表
5. 監控指標：執行耗時、處理筆數、流量總量寫入監控表