PLSQL函数实现指定日期前入职员工入职日期查询及&符号使用问题

问题1：编写PL/SQL函数查找早于指定日期的员工入职日期 #

根据需求，提供两种常用实现方式，可按需选择：

方式1：返回早于指定日期的最新入职日期 #

适合仅需获取最晚符合条件入职日期的场景：

CREATE OR REPLACE FUNCTION get_earlier_hire_date(p_target_date DATE)
RETURN DATE
IS
    v_max_hire_date DATE;
BEGIN
    -- 筛选入职日期早于参数的记录，取最大的（即最新的）入职日期
    SELECT MAX(hire_date)
    INTO v_max_hire_date
    FROM employees
    WHERE hire_date < p_target_date;
    
    RETURN v_max_hire_date;
END;
/

方式2：返回所有符合条件的入职日期集合 #

如果需要获取全部早于指定日期的员工入职日期，用嵌套表类型返回：

-- 先定义存储日期集合的自定义类型
CREATE OR REPLACE TYPE hire_date_list IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION get_all_earlier_hire_dates(p_target_date DATE)
RETURN hire_date_list
IS
    v_hire_dates hire_date_list;
BEGIN
    -- 批量收集所有符合条件的入职日期
    SELECT hire_date
    BULK COLLECT INTO v_hire_dates
    FROM employees
    WHERE hire_date < p_target_date;
    
    RETURN v_hire_dates;
END;
/

问题2：修复含&符号的PL/SQL程序错误 #

你的原程序触发PLS-00103错误，核心问题有两个：

1. &符号误用：&v_hire_date是SQL*Plus这类客户端工具的替代变量，用于交互式输入值，但不能直接写在PL/SQL变量定义中——PL/SQL编译器不识别该语法，因此报错。
2. 逻辑错误：原代码中WHERE hire_date < v_hire_date的条件完全不合理，此时v_hire_date未赋值，相当于用空值做比较，无法得到有效结果。

正确写法分两种情况： #

情况1：让函数接收参数（推荐）

将输入日期作为函数参数传入，这是PL/SQL函数的标准写法：

CREATE OR REPLACE FUNCTION p_hire_date(p_input_date DATE)
RETURN DATE
IS
    v_hire_date employees.hire_date%TYPE;
BEGIN
    -- 用MAX确保仅返回一个值，避免SELECT INTO抛出多行异常
    SELECT MAX(hire_date)
    INTO v_hire_date
    FROM employees
    WHERE hire_date < p_input_date;
    
    RETURN v_hire_date;
-- 添加异常处理，避免无数据或多行数据时程序崩溃
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RETURN NULL;
END;
/

情况2：使用SQL*Plus的&变量调用函数（不推荐在函数定义中使用）

如果仅想通过客户端工具交互式输入日期调用函数，应在调用时使用&，而非函数定义中：

-- 调用上述函数时用&变量输入日期
SELECT p_hire_date(&target_date) FROM dual;

执行该语句时，工具会提示输入target_date的值，例如输入'01-JAN-2000'（注意日期格式需与数据库会话格式匹配）。

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