Oracle 預存程序完整教學：語法結構、參數模式、變數宣告、游標、集合、異常處理與實戰範例

Oracle PL/SQL 預存程序從入門到實戰完整指南

1. 基本骨架

CREATE OR REPLACE PROCEDURE proc_name (
  p_in   IN  VARCHAR2,   -- 傳入，不帶長度
  p_out  OUT NUMBER,     -- 傳出
  p_io   IN OUT DATE     -- 雙向
) IS
  v_local VARCHAR2(100);  -- 區域變數，帶長度/精度
BEGIN
  NULL;  -- 至少一條敘述
END;

2. 參數模式對照

3. 變數宣告最佳實務

• 使用 %TYPE 錨定資料表欄位型別：v_ename emp.ename%TYPE;
• 使用 %ROWTYPE 錨定整列：v_emp emp%ROWTYPE;
• 常數：c_max CONSTANT NUMBER := 100;

4. 控制結構

CURSOR c_emp(p_deptno NUMBER) IS
  SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;

v_emp c_emp%ROWTYPE;
OPEN c_emp(10);
LOOP
  FETCH c_emp INTO v_emp;
  EXIT WHEN c_emp%NOTFOUND;
  -- 處理 v_emp
END LOOP;
CLOSE c_emp;

6. 參考游標 (SYS_REFCURSOR) — 可作 OUT 參數傳回客戶端

PROCEDURE get_emps(p_deptno IN NUMBER, p_rc OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;

7. 集合型別

BEGIN
  -- 業務邏輯
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_msg := '查無資料';
  WHEN DUP_VAL_ON_INDEX THEN
    v_msg := '主鍵重複';
  WHEN OTHERS THEN
    v_msg := '系統錯誤: ' || SQLERRM;
    ROLLBACK;
    INSERT INTO err_log(proc, msg, dt) VALUES ('proc_name', v_msg, SYSDATE);
    COMMIT;
END;

9. 實戰範例：薪資試算與跨表同步

CREATE OR REPLACE PROCEDURE calc_salary(p_ym IN VARCHAR2) IS
  v_beg   VARCHAR2(6) := SUBSTR(p_ym,1,6);
  v_end   VARCHAR2(6) := SUBSTR(p_ym,7,6);
  v_syn_beg VARCHAR2(6) := TO_CHAR(ADD_MONTHS(TO_DATE(v_beg,'YYYYMM'),-12),'YYYYMM');
  v_syn_end VARCHAR2(6) := TO_CHAR(ADD_MONTHS(TO_DATE(v_end,'YYYYMM'),-12),'YYYYMM');
  v_err   VARCHAR2(4000);
BEGIN
  DELETE FROM rpt_salary WHERE ym = p_ym;
  DBMS_OUTPUT.PUT_LINE('DEL '||SQL%ROWCOUNT);

  INSERT INTO rpt_salary(area, ym, cm, rmb, usd)
  SELECT area, p_ym, cm, SUM(rmb)/10000, SUM(usd)/10000
  FROM base_salary
  WHERE ym BETWEEN v_beg AND v_end
  GROUP BY area, cm;
  DBMS_OUTPUT.PUT_LINE('INS '||SQL%ROWCOUNT);

  FOR r IN (
    SELECT area, cm, SUM(rmb)/10000 rmb_syn, SUM(usd)/10000 usd_syn
    FROM base_salary
    WHERE ym BETWEEN v_syn_beg AND v_syn_end
    GROUP BY area, cm
  ) LOOP
    UPDATE rpt_salary
    SET rmb_syn = r.rmb_syn, usd_syn = r.usd_syn
    WHERE ym = p_ym AND area = r.area AND cm = r.cm;
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    v_err := 'calc_salary('||p_ym||'): '||SUBSTR(SQLERRM,1,500);
    ROLLBACK;
    INSERT INTO proc_log(name, err, dt) VALUES ('calc_salary', v_err, SYSDATE);
    COMMIT;
END;

10. 偵錯與效能提示

• 使用 DBMS_PROFILER 或 PL/SQL Developer Debugger 逐行偵錯。
• 大量 DML 使用 FORALL + BULK COLLECT 減少 Context Switch。
• 避免在迴圈中 COMMIT，造成 ORA-01555 Snapshot Too Old。
• 權限：GRANT EXECUTE ON proc_name TO role/user;。