Oracle日期区间生成代码改造：实现参数化的管道函数/存储过程

原递归CTE的核心是生成指定时间范围内、按固定间隔递增的时间序列，下面提供三种参数化实现方案，适配不同场景需求：

一、管道函数（推荐用于SQL查询直接调用） #

管道函数可直接作为SQL数据源使用，最适配QA团队日常查询场景。

步骤1：创建自定义集合类型 #

先定义用于返回时间序列的表类型：

CREATE OR REPLACE TYPE timestamp_table_type AS TABLE OF TIMESTAMP;
/

步骤2：实现管道函数 #

CREATE OR REPLACE FUNCTION generate_time_series(
    p_date1 TIMESTAMP,
    p_date2 TIMESTAMP,
    p_interval_num NUMBER,
    p_interval_unit VARCHAR2
) RETURN timestamp_table_type PIPELINED
IS
    v_start_time TIMESTAMP := LEAST(p_date1, p_date2);
    v_end_time TIMESTAMP := GREATEST(p_date1, p_date2);
    v_interval INTERVAL DAY TO SECOND;
    v_current_time TIMESTAMP;
BEGIN
    -- 转换输入单位为标准间隔类型
    CASE UPPER(p_interval_unit)
        WHEN 'S' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'SECOND');
        WHEN 'M' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'MINUTE');
        WHEN 'H' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'HOUR');
        WHEN 'D' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'DAY');
        ELSE RAISE_APPLICATION_ERROR(-20001, '无效的间隔单位：仅支持S(秒)/M(分钟)/H(小时)/D(天)');
    END CASE;

    v_current_time := v_start_time;
    LOOP
        EXIT WHEN v_current_time >= v_end_time;
        PIPE ROW(v_current_time);
        v_current_time := v_current_time + v_interval;
    END LOOP;

    RETURN;
END;
/

使用示例 #

-- 生成2022-11-01 02:37:11到2022-11-01 05:00:00之间，每5分钟一个的时间点
SELECT column_value AS dt FROM TABLE(generate_time_series(
    TIMESTAMP '2022-11-01 02:37:11',
    TIMESTAMP '2022-11-01 05:00:00',
    5,
    'M'
));

二、带输出游标的存储过程 #

适合在PL/SQL块中调用并批量处理结果：

CREATE OR REPLACE PROCEDURE generate_time_series_proc(
    p_date1 TIMESTAMP,
    p_date2 TIMESTAMP,
    p_interval_num NUMBER,
    p_interval_unit VARCHAR2,
    p_result_cursor OUT SYS_REFCURSOR
)
IS
    v_start_time TIMESTAMP := LEAST(p_date1, p_date2);
    v_end_time TIMESTAMP := GREATEST(p_date1, p_date2);
    v_interval INTERVAL DAY TO SECOND;
BEGIN
    CASE UPPER(p_interval_unit)
        WHEN 'S' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'SECOND');
        WHEN 'M' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'MINUTE');
        WHEN 'H' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'HOUR');
        WHEN 'D' THEN v_interval := NUMTODSINTERVAL(p_interval_num, 'DAY');
        ELSE RAISE_APPLICATION_ERROR(-20001, '无效的间隔单位：仅支持S(秒)/M(分钟)/H(小时)/D(天)');
    END CASE;

    OPEN p_result_cursor FOR
        WITH dt (dt, interv) AS (
            SELECT v_start_time, v_interval FROM dual
            UNION ALL
            SELECT dt.dt + interv, interv FROM dt
            WHERE dt.dt + interv < v_end_time
        )
        SELECT dt FROM dt;
END;
/

使用示例 #

DECLARE
    v_cursor SYS_REFCURSOR;
    v_dt TIMESTAMP;
BEGIN
    generate_time_series_proc(
        TIMESTAMP '2022-11-01 02:37:11',
        TIMESTAMP '2022-11-01 05:00:00',
        5,
        'M',
        v_cursor
    );
    FETCH v_cursor INTO v_dt;
    WHILE v_cursor%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_dt);
        FETCH v_cursor INTO v_dt;
    END LOOP;
    CLOSE v_cursor;
END;
/

三、SQL宏（Oracle 19c+支持） #

Oracle 19c及以上版本可使用SQL宏，直接在SQL中展开逻辑，无需创建集合类型，性能更优：

CREATE OR REPLACE FUNCTION generate_time_series_macro(
    p_date1 TIMESTAMP,
    p_date2 TIMESTAMP,
    p_interval_num NUMBER,
    p_interval_unit VARCHAR2
) RETURN VARCHAR2 SQL_MACRO
IS
    v_start_time TIMESTAMP := LEAST(p_date1, p_date2);
    v_end_time TIMESTAMP := GREATEST(p_date1, p_date2);
    v_interval_str VARCHAR2(100);
BEGIN
    CASE UPPER(p_interval_unit)
        WHEN 'S' THEN v_interval_str := 'NUMTODSINTERVAL(' || p_interval_num || ', ''SECOND'')';
        WHEN 'M' THEN v_interval_str := 'NUMTODSINTERVAL(' || p_interval_num || ', ''MINUTE'')';
        WHEN 'H' THEN v_interval_str := 'NUMTODSINTERVAL(' || p_interval_num || ', ''HOUR'')';
        WHEN 'D' THEN v_interval_str := 'NUMTODSINTERVAL(' || p_interval_num || ', ''DAY'')';
        ELSE RAISE_APPLICATION_ERROR(-20001, '无效的间隔单位：仅支持S(秒)/M(分钟)/H(小时)/D(天)');
    END CASE;

    RETURN q'[
        WITH dt (dt, interv) AS (
            SELECT TIMESTAMP ']' || v_start_time || q'[', ]' || v_interval_str || q'[ FROM dual
            UNION ALL
            SELECT dt.dt + interv, interv FROM dt
            WHERE dt.dt + interv < TIMESTAMP ']' || v_end_time || q'['
        )
        SELECT dt FROM dt
    ]';
END;
/

使用示例 #

SELECT dt FROM generate_time_series_macro(
    TIMESTAMP '2022-11-01 02:37:11',
    TIMESTAMP '2022-11-01 05:00:00',
    5,
    'M'
);

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