Oracle PL/SQL 异常处理

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

PL/SQL 异常处理机制[1]：

类型：

• 预定义异常
• 非预定义异常
• 用户定义异常

详细见：Oracle PL/SQL 异常处理。

---

2. 预定义异常

DECLARE
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = 100;
  DBMS_OUTPUT.PUT_LINE(v_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Too many rows');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

---

3. 常见预定义

异常	错误码	描述
NO_DATA_FOUND	ORA-01403	无数据
TOO_MANY_ROWS	ORA-01422	多行
ZERO_DIVIDE	ORA-01476	除零
INVALID_CURSOR	ORA-01001	无效游标
DUP_VAL_ON_INDEX	ORA-00001	唯一约束
VALUE_ERROR	ORA-06502	值错误
INVALID_NUMBER	ORA-01722	数字无效

---

4. 非预定义异常

4.1 关联

DECLARE
  e_fk_violation EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_fk_violation, -02292);
BEGIN
  DELETE FROM departments WHERE id = 10;
EXCEPTION
  WHEN e_fk_violation THEN
    DBMS_OUTPUT.PUT_LINE('FK violation');
END;
/

---

5. 用户定义异常

5.1 声明与抛出

DECLARE
  e_low_salary EXCEPTION;
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = 100;
  IF v_salary < 5000 THEN
    RAISE e_low_salary;
  END IF;
EXCEPTION
  WHEN e_low_salary THEN
    DBMS_OUTPUT.PUT_LINE('Salary too low');
END;
/

5.2 RAISE_APPLICATION_ERROR

CREATE OR REPLACE PROCEDURE check_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  IF v_salary < 5000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary too low: ' || v_salary);
  END IF;
END;
/

---

6. SQLCODE 与 SQLERRM

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

---

7. 异常传播

7.1 嵌套块

BEGIN
  BEGIN
    -- 内部块
    RAISE NO_DATA_FOUND;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Inner caught');
      -- 不再抛出
  END;
  
  -- 继续执行
  DBMS_OUTPUT.PUT_LINE('Continue');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Outer');
END;
/

7.2 RAISE 重新抛出

EXCEPTION
  WHEN OTHERS THEN
    -- 记录日志
    log_error(...);
    -- 重新抛出
    RAISE;
END;

---

8. 异常与事务

BEGIN
  INSERT INTO log VALUES (...);
  
  BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO SAVEPOINT before_transfer;
      RAISE;
  END;
  
  COMMIT;
END;
/

---

9. SQL%BULK_EXCEPTIONS

9.1 FORALL

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array := id_array(1, 2, 3, 4, 5);
  errors PLS_INTEGER;
BEGIN
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    DELETE FROM employees WHERE id = v_ids(i);
EXCEPTION
  WHEN OTHERS THEN
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.PUT_LINE('Errors: ' || errors);
    FOR j IN 1..errors LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 
        ' Code ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
      );
    END LOOP;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

10. 自定义异常处理包

CREATE OR REPLACE PACKAGE error_handler AS
  PROCEDURE log_error(p_proc VARCHAR2, p_err VARCHAR2);
  PROCEDURE raise_custom(p_code NUMBER, p_msg VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY error_handler AS
  PROCEDURE log_error(p_proc VARCHAR2, p_err VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO error_log (proc_name, error_msg, error_time)
    VALUES (p_proc, p_err, SYSTIMESTAMP);
    COMMIT;
  END;
  
  PROCEDURE raise_custom(p_code NUMBER, p_msg VARCHAR2) IS
  BEGIN
    log_error('unknown', p_msg);
    RAISE_APPLICATION_ERROR(p_code, p_msg);
  END;
END;
/

---

11. 错误日志

11.1 创建表

CREATE TABLE error_log (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  proc_name VARCHAR2(100),
  error_msg VARCHAR2(4000),
  error_backtrace VARCHAR2(4000),
  error_time TIMESTAMP,
  username VARCHAR2(50)
);

11.2 使用

EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log (proc_name, error_msg, error_backtrace, error_time, username)
    VALUES ('my_proc', SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, SYSTIMESTAMP, USER);
    RAISE;

---

12. DBMS_ERRLOG

12.1 创建

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('EMPLOYEES', 'ERR_EMPLOYEES');

12.2 使用

INSERT INTO employees (id, name, email) 
SELECT id, name, email FROM new_employees
LOG ERRORS INTO ERR_EMPLOYEES ('INSERT') REJECT LIMIT UNLIMITED;

---

13. 异常处理最佳实践

13.1 WHEN OTHERS 谨慎

-- 不推荐
EXCEPTION
  WHEN OTHERS THEN NULL;  -- 吞掉异常

-- 推荐
EXCEPTION
  WHEN OTHERS THEN
    log_error(...);
    RAISE;

13.2 具体异常优先

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...
  WHEN OTHERS THEN ...

13.3 RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR(-20001, 'Custom error', TRUE);
-- TRUE：保留原错误栈

---

14. 常见坑与排错

14.1 异常吞掉

-- 避免
WHEN OTHERS THEN NULL;
-- 应记录并 RAISE

14.2 异常顺序

- 具体在前
- OTHERS 在后

14.3 自治事务

-- 日志独立提交
PRAGMA AUTONOMOUS_TRANSACTION;

---

15. 最佳实践

1. 具体异常优先：清晰
2. WHEN OTHERS 谨慎：记录 + RAISE
3. RAISE_APPLICATION_ERROR：业务
4. PRAGMA EXCEPTION_INIT：关联
5. SQLCODE/SQLERRM：信息
6. FORMAT_ERROR_BACKTRACE：栈
7. SAVE EXCEPTIONS：批量
8. 错误日志表：审计
9. PRAGMA AUTONOMOUS：独立
10. 文档化：错误码

---

16. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Exceptions” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/exceptions.html