请求协助开发带异常处理的PLSQL员工薪资校验调整函数

需求说明 #

• 检查指定员工的薪资是否低于全体员工平均薪资，若低于则将其薪资上调5%
• 输出格式要求：

  O/P: Old salary is: $510
       Avg salary is: $957.05
       New salary is: $765
       1-Huguette Sandrine: $765
  

• 需处理所有可能的异常情况

完整实现代码 #

CREATE OR REPLACE FUNCTION checkEMPsal(p_emp_id IN NUMBER)
RETURN NUMBER
IS
    avg_sal NUMBER;
    v_old_sal NUMBER;
    v_new_sal NUMBER;
    v_emp_name VARCHAR2(100);
BEGIN
    -- 获取全体员工平均薪资
    SELECT AVG(emp_sal) INTO avg_sal FROM employee;

    -- 获取目标员工的当前薪资和姓名
    SELECT emp_sal, emp_name INTO v_old_sal, v_emp_name
    FROM employee
    WHERE emp_id = p_emp_id;

    -- 检查薪资是否低于平均
    IF v_old_sal < avg_sal THEN
        -- 计算上调5%后的新薪资
        v_new_sal := v_old_sal * 1.05;
        -- 更新员工薪资
        UPDATE employee
        SET emp_sal = v_new_sal
        WHERE emp_id = p_emp_id;
        COMMIT;

        -- 按要求格式输出信息
        DBMS_OUTPUT.PUT_LINE('O/P: Old salary is: $' || v_old_sal);
        DBMS_OUTPUT.PUT_LINE('     Avg salary is: $' || TO_CHAR(avg_sal, '9999.99'));
        DBMS_OUTPUT.PUT_LINE('     New salary is: $' || v_new_sal);
        DBMS_OUTPUT.PUT_LINE('     ' || p_emp_id || '-' || v_emp_name || ': $' || v_new_sal);

        RETURN 1; -- 返回1表示调整成功
    ELSE
        -- 薪资不低于平均时的提示
        DBMS_OUTPUT.PUT_LINE('O/P: Employee ' || p_emp_id || '-' || v_emp_name || ' salary is not below average.');
        RETURN 0; -- 返回0表示未调整
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee ID ' || p_emp_id || ' does not exist.');
        RETURN -1;
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Cannot calculate average salary (no employees in table).');
        RETURN -2;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
        RETURN -3;
END;
/

代码关键点说明 #

• 函数参数：新增p_emp_id参数，明确指定要检查的员工，解决原代码无目标对象的问题
• 变量声明：补充员工薪资、姓名变量，用于存储查询结果和计算值
• 数据校验：先查询目标员工信息，避免后续操作出现无数据异常
• 薪资计算：使用v_old_sal * 1.05计算5%的涨幅，确保精度
• 输出格式：严格匹配要求的缩进和内容格式，用TO_CHAR格式化平均薪资为两位小数
• 异常处理：覆盖员工不存在、无员工数据计算平均失败、其他未知异常，确保函数健壮性

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