如何让Oracle存储过程返回日期集合？现有实现问题求助

嘿，我看了你的代码，问题根源很清楚：你尝试用VARRAY和SYS_REFCURSOR返回日期，但两者完全没关联上，而且打印逻辑也有顺序问题。咱们一步步来解决：

先说说你原代码的问题 #

1. 游标没被赋值：你声明了输出参数alldate SYS_REFCURSOR，但整个过程里既没打开它，也没往里面塞数据，最后返回的是空游标。
2. 打印顺序错误：你在给alldates(i)赋值前就打印它，这时候元素还没初始化，自然是空；递增i后又打印下一个未赋值的元素，结果还是空，这就是你看不到第二次输出的原因。
3. VARRAY是多余的局部变量：你定义了局部的alldates集合，但没把它的数据传递到输出游标里，等于白忙活。

推荐方案：用SYS_REFCURSOR返回日期序列（最通用） #

这种方式不需要额外的集合类型，直接用Oracle的CONNECT BY生成日期序列，然后通过游标返回，代码简洁又高效：

CREATE OR REPLACE PROCEDURE LeaveDates2 (
    STDATE IN DATE,
    ENDDATE IN DATE,
    alldate OUT SYS_REFCURSOR
) AS
BEGIN
    -- 打开游标，生成从STDATE到ENDDATE的所有日期
    OPEN alldate FOR
        SELECT STDATE + LEVEL - 1 AS leave_date
        FROM DUAL
        CONNECT BY STDATE + LEVEL - 1 <= ENDDATE;
END LeaveDates2;

调用测试代码 #

你可以用这段PL/SQL块调用存储过程，验证返回的日期：

DECLARE
    v_start_date DATE := TO_DATE('01-JAN-2016', 'DD-MON-YYYY');
    v_end_date DATE := TO_DATE('05-JAN-2016', 'DD-MON-YYYY');
    v_date_cursor SYS_REFCURSOR;
    v_current_date DATE;
BEGIN
    LeaveDates2(v_start_date, v_end_date, v_date_cursor);
    
    -- 遍历游标获取所有日期
    LOOP
        FETCH v_date_cursor INTO v_current_date;
        EXIT WHEN v_date_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_current_date);
    END LOOP;
    
    CLOSE v_date_cursor;
END;
/

备选方案：用自定义集合类型返回数组 #

如果你确实需要返回数组形式的日期集合，可以先定义一个全局集合类型，再在存储过程中填充：

-- 先创建全局的日期数组类型（需要有创建类型的权限）
CREATE OR REPLACE TYPE DATE_ARRAY AS VARRAY(30) OF DATE;
/

CREATE OR REPLACE PROCEDURE LeaveDates3 (
    STDATE IN DATE,
    ENDDATE IN DATE,
    alldates OUT DATE_ARRAY
) AS
    v_current_date DATE := STDATE;
    v_index NUMBER := 1;
BEGIN
    alldates := DATE_ARRAY(); -- 初始化集合
    WHILE v_current_date <= ENDDATE LOOP
        alldates.EXTEND; -- 扩展集合容量
        alldates(v_index) := v_current_date;
        v_current_date := v_current_date + 1;
        v_index := v_index + 1;
    END LOOP;
END LeaveDates3;

调用测试代码 #

DECLARE
    v_start_date DATE := TO_DATE('01-JAN-2016', 'DD-MON-YYYY');
    v_end_date DATE := TO_DATE('05-JAN-2016', 'DD-MON-YYYY');
    v_date_list DATE_ARRAY;
BEGIN
    LeaveDates3(v_start_date, v_end_date, v_date_list);
    
    -- 遍历集合打印日期
    IF v_date_list IS NOT NULL THEN
        FOR i IN 1..v_date_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(v_date_list(i));
        END LOOP;
    END IF;
END;
/

总结 #

优先用SYS_REFCURSOR方案，因为它不需要预先定义类型，而且不管是PL/SQL还是Java、Python等外部语言，处理游标都非常方便。如果一定要用数组，再考虑备选方案。

内容的提问来源于stack exchange，提问作者Shilpa M B