在Oracle中使用WITH语句创建工作日历表函数的语法疑问

问题背景 #

熟悉SQL Server环境，对Oracle语法不熟悉，需要创建一个表函数，接收基准日期、工作日数量、趋势值（正负表示日期前后方向）作为参数，生成指定数量的工作日。已有可运行的查询语句，但转换为函数时出错。

原查询语句（可正常运行） #

VAR TREND = 1;
VAR OBS_DATE = 20221109;
VAR N_DAYS = 21;

WITH CAL AS
(
    SELECT
        TO_DATE(:OBS_DATE, 'YYYYMMDD') + (LEVEL - 1 * :TREND) DT, ROW_NUMBER() OVER(ORDER BY NULL) - 1 IX
    FROM 
        DUAL
    WHERE 
        TO_CHAR(TO_DATE(:OBS_DATE, 'YYYYMMDD') + (LEVEL - 1 * :TREND) , 'D') NOT IN (1,7)
    CONNECT BY LEVEL <= :N_DAYS + :N_DAYS/5*2+1
)
SELECT DT 
FROM CAL 
WHERE IX <= :N_DAYS;

错误代码的问题点 #

你编写的函数存在多个语法和逻辑错误：

• 返回类型错误：表函数不能直接返回DATE类型，必须返回集合类型（如TABLE OF DATE）
• 参数引用错误：Oracle函数中直接使用参数名即可，无需加:前缀
• 冗余转换：OBS_DATE参数已经是DATE类型，不需要再用TO_DATE转换
• 语法格式错误：RETURN子句定义错误，数字后多余的.（如5.*2.+1.）会导致语法报错
• 排序逻辑问题：ORDER BY NULL无法保证日期顺序，应该按生成的日期排序

正确的表函数实现 #

步骤1：创建日期集合类型 #

首先需要定义一个存储日期的表类型（如果没有内置类型可用）：

CREATE OR REPLACE TYPE DATE_TABLE_TYPE AS TABLE OF DATE;
/

步骤2：实现返回集合的函数 #

CREATE OR REPLACE FUNCTION FUN_BUS_CALENDAR(
    OBS_DATE IN DATE := SYSDATE,  -- 基准日期，默认当前日期
    N_DAYS IN NUMBER,             -- 需要生成的工作日数量
    TREND IN NUMBER := 1          -- 趋势值，1表示往后，-1表示往前，默认1
) RETURN DATE_TABLE_TYPE
IS
    v_result DATE_TABLE_TYPE := DATE_TABLE_TYPE();
BEGIN
    WITH CAL AS (
        SELECT
            OBS_DATE + (LEVEL - 1) * TREND AS DT,
            ROW_NUMBER() OVER(ORDER BY OBS_DATE + (LEVEL - 1) * TREND) - 1 AS IX
        FROM DUAL
        -- 用英文缩写判断周末，避免语言环境影响
        WHERE TO_CHAR(OBS_DATE + (LEVEL - 1) * TREND, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
        -- 生成足够多的日期，确保能筛选出N_DAYS个工作日
        CONNECT BY LEVEL <= N_DAYS + CEIL(N_DAYS/5)*2 + 2
    )
    -- 批量收集结果到集合变量
    SELECT DT
    BULK COLLECT INTO v_result
    FROM CAL
    WHERE IX < N_DAYS;  -- IX从0开始，取前N_DAYS个工作日

    RETURN v_result;
END;
/

步骤3：实现管道化表函数（可选，适合大数据量） #

管道化函数可以逐行返回结果，无需一次性收集所有数据：

CREATE OR REPLACE FUNCTION FUN_BUS_CALENDAR(
    OBS_DATE IN DATE := SYSDATE,
    N_DAYS IN NUMBER,
    TREND IN NUMBER := 1
) RETURN DATE_TABLE_TYPE PIPELINED
IS
BEGIN
    FOR rec IN (
        WITH CAL AS (
            SELECT
                OBS_DATE + (LEVEL - 1) * TREND AS DT,
                ROW_NUMBER() OVER(ORDER BY OBS_DATE + (LEVEL - 1) * TREND) - 1 AS IX
            FROM DUAL
            WHERE TO_CHAR(OBS_DATE + (LEVEL - 1) * TREND, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
            CONNECT BY LEVEL <= N_DAYS + CEIL(N_DAYS/5)*2 + 2
        )
        SELECT DT
        FROM CAL
        WHERE IX < N_DAYS
    ) LOOP
        PIPE ROW(rec.DT);
    END LOOP;
    RETURN;
END;
/

函数使用方法 #

通过TABLE()函数将集合转换为查询结果：

-- 生成2022-11-09往后的21个工作日
SELECT COLUMN_VALUE AS WORK_DATE
FROM TABLE(FUN_BUS_CALENDAR(TO_DATE('20221109','YYYYMMDD'), 21, 1));

-- 生成当前日期往前的10个工作日
SELECT COLUMN_VALUE AS WORK_DATE
FROM TABLE(FUN_BUS_CALENDAR(SYSDATE, 10, -1));

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