Oracle 存儲過程完整語法指南：從骨架到實戰範本

存儲過程是 Oracle PL/SQL 核心功能，封裝業務邏輯、提升效能、增強安全性。本文從最小骨架、參數模式、變數宣告、控制結構、異常處理到完整實戰範本，系統梳理存儲過程開發全流程。

最小骨架（可編譯通過的空過程）

CREATE OR REPLACE PROCEDURE skeleton IS
BEGIN
  NULL;  -- PL/SQL 區塊至少需一條語句
END;

• CREATE OR REPLACE：不存在則建立，存在則覆蓋。
• IS / AS：宣告區起始關鍵字（同義）。
• BEGIN ... END：執行區塊。

完整建立語法（含參數與區塊結構）

CREATE OR REPLACE PROCEDURE proc_name (
  param_in   IN  VARCHAR2,   -- 僅輸入
  param_out  OUT NUMBER,     -- 僅輸出
  param_io   IN OUT DATE     -- 雙向
) AS
  -- 區域變數宣告（需指定長度/精度，以分號結尾）
  v_msg      VARCHAR2(4000);
  v_count    NUMBER;
  v_ym_beg   CHAR(6);
BEGIN
  -- 1. 參數賦值給區域變數
  v_ym_beg := param_in;

  -- 2. 查詢結果存入變數（單筆必用 SELECT INTO）
  SELECT COUNT(*) INTO v_count FROM table_a WHERE col = v_ym_beg;

  -- 3. 條件控制
  IF v_count > 0 THEN
    SELECT col2 INTO v_msg FROM table_a WHERE col = v_ym_beg;
    DBMS_OUTPUT.PUT_LINE('Found: ' || v_msg);
  ELSIF v_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No data');
  ELSE
    RAISE NO_DATA_FOUND;  -- 主動拋出異常
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_msg := 'ERROR IN proc_name: ' || SUBSTR(SQLERRM, 1, 500);
    -- 記錄錯誤日誌
    INSERT INTO log_info(proc_name, error_info, op_date)
    VALUES ('proc_name', v_msg, SYSDATE);
    COMMIT;
    RETURN;  -- 或 RAISE 重拋
END;

關鍵語法重點

進階實戰範本：帶游標迴圈、動態 SQL、日誌記錄

CREATE OR REPLACE PROCEDURE batch_process (
  p_ym       IN  CHAR(12),   -- 起迄年月 如 '202301202312'
  p_count    OUT NUMBER
) AS
  -- 變數
  v_ym_beg     CHAR(6);
  v_ym_end     CHAR(6);
  v_ym_sn_beg  CHAR(6);
  v_ym_sn_end  CHAR(6);
  v_err_msg    VARCHAR2(4000);

  -- 遊標
  CURSOR c_data IS
    SELECT area_code, cmcode, SUM(rmb_amt)/10000 rmb_amt, SUM(usd_amt)/10000 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);
  v_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(v_ym_beg, 'YYYYMM'), -12), 'YYYYMM');
  v_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(v_ym_end, 'YYYYMM'), -12), 'YYYYMM');

  -- 刪除舊資料
  DELETE FROM target_t WHERE ym = p_ym;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows');

  -- 插入本期匯總
  INSERT INTO target_t (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;
  DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows');

  -- 遊標迴圈更新同期欄位
  FOR rec IN c_data LOOP
    UPDATE target_t
    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;

  COMMIT;
  p_count := SQL%ROWCOUNT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_err_msg := 'ERROR IN batch_process(' || p_ym || '): ' || SUBSTR(SQLERRM, 1, 500);
    INSERT INTO log_info(proc_name, error_info, op_date)
    VALUES ('batch_process', v_err_msg, SYSDATE);
    COMMIT;
    -- 視需求決定是否 RAISE 重拋
END;

開發檢查清單

• ☐ 參數模式正確（IN/OUT/IN OUT）
• ☐ 區域變數長度足夠（特別是 VARCHAR2）
• ☐ 單筆查詢用 SELECT INTO，多筆用遊標
• ☐ DML 後檢查 SQL%ROWCOUNT 確認影響筆數
• ☐ 異常區塊包含 ROLLBACK、錯誤日誌、必要時 RAISE
• ☐ 使用 DBMS_OUTPUT 僅供除錯，正式環境改寫日誌表