如何在Oracle PL/SQL中实现排除周末和节假日的日期减法语句

完全可以实现这类需求，以下提供两种实用方案，假设自定义节假日表名为HOLIDAYS，包含日期字段HOLIDAY_DATE。

方案1：纯SQL递归查询 #

适合一次性查询场景，通过生成日期范围并过滤非工作日，最终定位目标日期：

WITH date_range AS (
    SELECT :end_date - LEVEL + 1 AS calc_date
    FROM dual
    CONNECT BY LEVEL <= 100 -- 设定足够大的范围，确保覆盖所需工作日数量
),
valid_workdays AS (
    SELECT calc_date,
           ROW_NUMBER() OVER (ORDER BY calc_date DESC) AS day_rank
    FROM date_range
    -- 排除周六、周日
    WHERE TO_CHAR(calc_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
      -- 排除自定义节假日
      AND calc_date NOT IN (SELECT holiday_date FROM holidays)
)
SELECT calc_date AS result_date
FROM valid_workdays
WHERE day_rank = :days_to_subtract;

• 替换:end_date为目标结束日期（如TO_DATE('2023-03-01','YYYY-MM-DD')），:days_to_subtract为要减去的工作日天数（如7）。

方案2：PL/SQL函数（推荐） #

适合重复调用或嵌入业务逻辑，封装成可复用函数：

CREATE OR REPLACE FUNCTION get_adjusted_workday(p_end_date DATE, p_days_to_subtract NUMBER)
RETURN DATE
IS
    v_target_date DATE := p_end_date;
    v_remaining_days NUMBER := p_days_to_subtract;
BEGIN
    WHILE v_remaining_days > 0 LOOP
        v_target_date := v_target_date - 1;
        -- 检查当前日期是否为有效工作日
        IF TO_CHAR(v_target_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
           AND NOT EXISTS (SELECT 1 FROM holidays WHERE holiday_date = v_target_date) THEN
            v_remaining_days := v_remaining_days - 1;
        END IF;
    END LOOP;
    RETURN v_target_date;
END;
/

调用示例： #

SELECT get_adjusted_workday(TO_DATE('2023-03-01', 'YYYY-MM-DD'), 7) AS result_date FROM dual;

执行后将返回2023-02-20，与示例预期一致。

注意事项： #

• 确保HOLIDAYS表数据准确，日期字段类型与输入日期匹配。
• NLS_DATE_LANGUAGE=ENGLISH用于规避不同语言环境下星期缩写差异，若数据库默认语言为英文可省略。

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