Oracle APEX PL/SQL自定义异常失效：触发器未阻止非法数据插入

核心问题 #

你编写的存储过程中，自定义异常sal_error被本地EXCEPTION块捕获后仅做了打印输出处理，没有将异常向上传递到调用它的触发器中。触发器调用存储过程时，无法感知到异常发生，因此会继续执行插入/更新操作，最终导致非法薪资数据被写入EMPLOYEES表。

代码问题分析 #

在CHECK_SALARY存储过程的EXCEPTION块中，你只通过DBMS_OUTPUT.PUT_LINE打印了错误信息，但没有重新抛出异常。Oracle中，异常被捕获后如果不主动重新抛出，调用者（此处为触发器）会认为存储过程执行成功，不会中断后续的DML操作。

修复方案 #

方案1：存储过程捕获异常后重新抛出 #

修改存储过程，在打印错误信息后添加RAISE;语句，将异常传递给触发器，从而终止插入/更新操作：

CREATE OR REPLACE PROCEDURE CHECK_SALARY(
    t_job_id IN EMPLOYEES.JOB_ID%TYPE,
    t_sal IN EMPLOYEES.SALARY%TYPE
)
IS
    min_sal JOBS.MIN_SALARY%TYPE;
    max_sal JOBS.MAX_SALARY%TYPE;
    sal_error EXCEPTION;
BEGIN
    SELECT MIN_SALARY, MAX_SALARY
    INTO min_sal, max_sal
    FROM JOBS
    WHERE JOB_ID = t_job_id;
    IF t_sal > max_sal OR t_sal < min_sal THEN
        RAISE sal_error;
    END IF;
EXCEPTION
    WHEN sal_error THEN 
        DBMS_OUTPUT.PUT_LINE ('Invalid Salary. Salaries for ' || t_job_id || ' must be between ' || min_sal || ' and ' || max_sal || '.');
        -- 重新抛出异常，让触发器感知并终止DML操作
        RAISE;
END;

方案2：直接使用RAISE_APPLICATION_ERROR抛出应用级错误 #

这种方式不需要自定义异常，直接抛出Oracle预定义的应用错误（错误码范围-20000到-20999），可以直接被触发器捕获并终止操作，这也是你之前测试有效的方式：

CREATE OR REPLACE PROCEDURE CHECK_SALARY(
    t_job_id IN EMPLOYEES.JOB_ID%TYPE,
    t_sal IN EMPLOYEES.SALARY%TYPE
)
IS
    min_sal JOBS.MIN_SALARY%TYPE;
    max_sal JOBS.MAX_SALARY%TYPE;
BEGIN
    SELECT MIN_SALARY, MAX_SALARY
    INTO min_sal, max_sal
    FROM JOBS
    WHERE JOB_ID = t_job_id;
    IF t_sal > max_sal OR t_sal < min_sal THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid Salary. Salaries for ' || t_job_id || ' must be between ' || min_sal || ' and ' || max_sal || '.');
    END IF;
END;

方案3：存储过程不捕获异常，由触发器处理 #

去掉存储过程中的EXCEPTION块，让自定义异常直接传递到触发器，再由触发器捕获并处理：

修改后的存储过程

CREATE OR REPLACE PROCEDURE CHECK_SALARY(
    t_job_id IN EMPLOYEES.JOB_ID%TYPE,
    t_sal IN EMPLOYEES.SALARY%TYPE
)
IS
    min_sal JOBS.MIN_SALARY%TYPE;
    max_sal JOBS.MAX_SALARY%TYPE;
    sal_error EXCEPTION;
BEGIN
    SELECT MIN_SALARY, MAX_SALARY
    INTO min_sal, max_sal
    FROM JOBS
    WHERE JOB_ID = t_job_id;
    IF t_sal > max_sal OR t_sal < min_sal THEN
        RAISE sal_error;
    END IF;
END;

修改后的触发器

CREATE OR REPLACE TRIGGER CHECK_SALARY_TRG 
BEFORE INSERT OR UPDATE ON EMPLOYEES 
FOR EACH ROW
DECLARE
    t_job_id EMPLOYEES.JOB_ID%TYPE;
    t_sal EMPLOYEES.SALARY%TYPE;
    sal_error EXCEPTION;
    -- 绑定自定义异常到错误码（可选，便于捕获）
    PRAGMA EXCEPTION_INIT(sal_error, -20000);
BEGIN
    t_job_id := :NEW.JOB_ID;
    t_sal := :NEW.SALARY;
    CHECK_SALARY(t_job_id, t_sal);
EXCEPTION
    WHEN sal_error THEN
        DBMS_OUTPUT.PUT_LINE('薪资校验失败，操作已终止');
        -- 重新抛出异常，终止插入/更新
        RAISE;
END;

总结 #

自定义异常本身功能正常，问题出在异常的传递逻辑上。只要确保存储过程中的异常能够被调用它的触发器感知到，就能实现阻止非法数据插入的需求。其中，使用RAISE_APPLICATION_ERROR是最直接的方式，而自定义异常则需要通过重新抛出的方式向上传递。

内容的提问来源于stack exchange，提问作者user22155495