Oracle 存儲過程開發規範與進階範本：參數模式、游標、集合、異常處理、動態 SQL、套件化實戰

Oracle 存儲過程開發規範與進階範本：參數模式、游標、集合、異常處理、動態 SQL、套件化實戰

本文整理企業級 PL/SQL 開發規範與可直接落地的進階範本，涵蓋參數傳遞陷阱、游標變數、集合類型選擇、異常處理模式、動態 SQL、套件化設計、學生成績計算綜合案例，助您寫出可維護、高效能、安全的存儲過程。

1. 參數模式與寬度鐵律

• IN：傳值，唯讀，可設 DEFAULT，寬度由呼叫端決定。
• OUT：傳址，進入時為 NULL，寬度由程序內部指派決定 (呼叫端定義寬度不足不會報錯但資料會被截斷)。
• IN OUT：傳址，雙向，寬度由程序內部決定。
• 最佳實踐：參數型別盡量用 table.column%TYPE，確保雙端一致。

2. 進階範本：帶游標、集合、異常日誌的標準過程

CREATE OR REPLACE PROCEDURE proc_monthly_summary(
  p_ym        IN  CHAR,           -- 格式 YYYYMM
  p_rowcount  OUT NUMBER
) AS
  v_err_msg   VARCHAR2(4000);
  v_ym_beg    CHAR(6);
  v_ym_end    CHAR(6);
  -- 遊標宣告
  CURSOR c_summary IS
    SELECT area_code, cmcode, SUM(rmb_amt)/10000 AS rmb_amt, SUM(usd_amt)/10000 AS usd_amt
    FROM bgd_area_cm_m_base_t
    WHERE ym BETWEEN v_ym_beg AND v_ym_end
    GROUP BY area_code, cmcode;
BEGIN
  -- 參數解析與轉換
  v_ym_beg := SUBSTR(p_ym, 1, 6);
  v_ym_end := SUBSTR(p_ym, 7, 6);

  -- 清理舊資料
  DELETE FROM summary_table WHERE ym = p_ym;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows');

  -- 彙總插入
  INSERT INTO summary_table (area_code, ym, cmcode, rmb_amt, usd_amt)
  SELECT area_code, p_ym, cmcode, SUM(rmb_amt)/10000, SUM(usd_amt)/10000
  FROM bgd_area_cm_m_base_t
  WHERE ym BETWEEN v_ym_beg AND v_ym_end
  GROUP BY area_code, cmcode;

  -- 遊標遍歷更新同期欄位
  FOR rec IN c_summary LOOP
    UPDATE summary_table
    SET rmb_amt_sn = rec.rmb_amt, usd_amt_sn = rec.usd_amt
    WHERE area_code = rec.area_code AND cmcode = rec.cmcode AND ym = p_ym;
  END LOOP;

  p_rowcount := SQL%ROWCOUNT;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    v_err_msg := 'ERROR IN proc_monthly_summary('||p_ym||'): ' || SUBSTR(SQLERRM,1,500);
    ROLLBACK;
    INSERT INTO proc_log(proc_name, error_info, op_date) VALUES ('proc_monthly_summary', v_err_msg, SYSDATE);
    COMMIT;
    RAISE;  -- 重拋異常讓呼叫端感知
END;
/
-- 呼叫
VAR v_cnt NUMBER;
EXEC proc_monthly_summary('202401202412', :v_cnt);
PRINT v_cnt;

3. 集合類型選擇決策表

4. SYS_REFCURSOR 遊標變數傳遞模式

-- 回傳遊標給呼叫端 (Java/.NET/報表工具常用)
CREATE OR REPLACE PROCEDURE get_emp_cursor(p_deptno IN NUMBER, p_rc OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_rc FOR SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;
END;
/
-- 呼叫端需 FETCH 直到 %NOTFOUND

5. 異常處理進階模式

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 特定業務異常
    LOG_ERROR('QUERY_EMPTY', SQLERRM);
  WHEN DUP_VAL_ON_INDEX THEN
    -- 唯一鍵衝突
    LOG_ERROR('DUP_KEY', SQLERRM);
  WHEN OTHERS THEN
    -- 兜底：記錄完整堆疊
    LOG_ERROR('UNEXPECTED', DBMS_UTILITY.FORMAT_ERROR_STACK || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;  -- 重拋
END;

6. 動態 SQL (EXECUTE IMMEDIATE) 安全範本

-- 參數化綁定防注入
v_sql := 'SELECT COUNT(*) FROM ' || v_table_name || ' WHERE created_date > :dt';
EXECUTE IMMEDIATE v_sql INTO v_cnt USING v_date;

-- DDL 動態執行
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_table_name;

7. 套件化設計：公開介面與私有實作分離

-- 規格
CREATE OR REPLACE PACKAGE pkg_emp_mgmt AS
  PROCEDURE adjust_salary(p_ename VARCHAR2, p_pct NUMBER);
  FUNCTION annual_income(p_ename VARCHAR2) RETURN NUMBER;
  -- 私有程序不在此宣告
END pkg_emp_mgmt;
/

-- 主體
CREATE OR REPLACE PACKAGE BODY pkg_emp_mgmt AS
  -- 私有輔助函數
  FUNCTION calc_tax(p_sal NUMBER) RETURN NUMBER IS
  BEGIN RETURN p_sal * 0.03; END;

  PROCEDURE adjust_salary(p_ename VARCHAR2, p_pct NUMBER) AS
  BEGIN
    UPDATE emp SET sal = sal * (1 + p_pct/100) WHERE ename = p_ename;
  END;

  FUNCTION annual_income(p_ename VARCHAR2) RETURN NUMBER AS
    v_sal NUMBER; v_comm NUMBER;
  BEGIN
    SELECT sal, NVL(comm,0) INTO v_sal, v_comm FROM emp WHERE ename = p_ename;
    RETURN (v_sal + v_comm) * 12;
  END;
END pkg_emp_mgmt;
/
-- 呼叫
EXEC pkg_emp_mgmt.adjust_salary('SCOTT', 10);

8. 綜合案例：學生成績自動計算 (含課外加分、遊標、集合、GOTO)

CREATE OR REPLACE PROCEDURE auto_compute_score(p_step IN NUMBER) AS
  TYPE rec_comment IS RECORD (std_id VARCHAR2(30), comment VARCHAR2(1));
  TYPE arr_comment IS TABLE OF rec_comment INDEX BY BINARY_INTEGER;
  v_comments arr_comment;

  CURSOR c_students IS
    SELECT std_id, math, article, language, music, sport
    FROM student WHERE step = p_step;

  v_total NUMBER; v_avg NUMBER;
BEGIN
  -- 1. 取得課外評語集合
  SELECT std_id, comment BULK COLLECT INTO v_comments
  FROM out_school;

  -- 2. 遍歷學生計算
  FOR rec IN c_students LOOP
    v_total := rec.math + rec.article + rec.language + rec.music + rec.sport;

    -- 查找課外評語 (關聯陣列遍歷)
    FOR i IN 1..v_comments.COUNT LOOP
      IF rec.std_id = v_comments(i).std_id THEN
        IF v_comments(i).comment = 'A' THEN
          v_total := v_total + 20;
          GOTO calc_avg;  -- 找到即跳出
        END IF;
      END IF;
    END LOOP;

    <>
    v_avg := v_total / 5;
    UPDATE student SET total = v_total, average = v_avg WHERE std_id = rec.std_id;
  END LOOP;
  COMMIT;
END;
/
-- 注意：GOTO 在 PL/SQL 中合法但慎用，此處為演示標籤跳出雙層迴圈

9. 命名慣例建議