Oracle HR库PL/SQL薪酬函数查不存在ID时异常未生效问题咨询

问题根因 #

• 第一个问题：外层SELECT查询逻辑优先级高于函数调用
  你写的外层查询是直接从employees表过滤指定employee_id，如果输入的ID不存在，外层查询首先就没有匹配的行，根本不会执行get_annual_emp函数，自然触发不到函数内的异常块，直接返回no rows selected。
• 第二个问题：自定义函数本身存在逻辑缺陷
  函数的返回类型是NUMBER，要求所有执行分支必须有返回值。你当前的异常块仅执行了DBMS_OUTPUT.PUT_LINE打印，没有写RETURN语句，就算触发了异常，也会抛出函数无返回值的报错。

修复方案 #

1. 先修正自定义函数逻辑 #

补全异常块的返回值，这里返回-1作为员工不存在的标识：

CREATE OR REPLACE FUNCTION get_annual_emp(v_empid NUMBER) 
RETURN NUMBER IS
v_sal employees.salary%TYPE;
v_comm employees.commission_pct%TYPE;
BEGIN
SELECT salary,commission_pct INTO v_sal,v_comm
FROM employees
WHERE employee_id=v_empid;
RETURN (NVL(v_sal,0) * 12 + (NVL(v_comm,0) * NVL(v_sal,0) * 12));

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee with that ID');
RETURN -1; -- 补全返回值
END get_annual_emp;
/

2. 调整查询调用方式 #

不要直接从employees表过滤ID，改用PL/SQL块执行逻辑，或者用DUAL表触发函数调用：

方案A：PL/SQL块执行（更符合需求，能同时返回ID、姓名、薪酬）

DECLARE
    v_input_empid NUMBER := &v_empid;
    v_last_name employees.last_name%TYPE;
    v_annual_comp NUMBER;
BEGIN
    -- 先查询员工基础信息，不存在直接触发块内异常
    SELECT last_name INTO v_last_name 
    FROM employees 
    WHERE employee_id = v_input_empid;
    -- 调用函数计算薪酬
    v_annual_comp := get_annual_emp(v_input_empid);
    -- 打印结果
    DBMS_OUTPUT.PUT_LINE('员工ID：'||v_input_empid||'，姓名：'||v_last_name||'，年度薪酬：'||v_annual_comp);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee with that ID');
END;
/

方案B：仅验证函数调用

如果只需要验证函数的异常逻辑是否生效，直接从DUAL表调用即可：

SELECT get_annual_emp(&v_empid) "Annual Compensation" FROM dual;

这时候输入不存在的ID，就会触发函数内的打印逻辑，同时返回-1。

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