Oracle中如何用CONNECT BY生成日期区间内的多行日期？

问题背景 #

现有表date_period，建表语句如下：

create table date_period(dt_from date, dt_to date);

需求是将每行记录的dt_from到dt_to日期区间，展开为单列的所有日期。

原查询仅在表中只有一行数据时生效，多行时结果异常：

select (dt_from - 1 + ROWNUM) from date_period connect by ROWNUM <= (trunc(dt_to) - trunc(dt_from) + 1)

示例数据 #

表中存在两行记录：

13-FEB-2023, 15-FEB-2023
20-JUN-2023, 22-JUN-2023

期望输出 #

13-FEB-2023
14-FEB-2023
15-FEB-2023
20-JUN-2023
21-JUN-2023
22-JUN-2023

实际错误输出 #

13-FEB-23
14-FEB-23
15-FEB-23
23-JUN-23

修正后的CONNECT BY解决方案 #

原查询出错的核心原因是：多行数据时，CONNECT BY没有限制递归仅在当前行内部进行，导致不同行的递归逻辑互相干扰。以下是两种可行的修正方案：

方案一：基于ROWID的递归限制 #

利用ROWID作为每行的唯一标识，确保递归仅在当前行内执行，同时用LEVEL替代ROWNUM生成日期偏移：

select trunc(dt_from) + LEVEL - 1 as dt
from date_period
connect by LEVEL <= trunc(dt_to) - trunc(dt_from) + 1
       and PRIOR ROWID = ROWID
       and PRIOR dbms_random.value is not null;

• LEVEL：递归层级从1开始递增，刚好对应日期区间的天数偏移，trunc(dt_from) + LEVEL -1能准确生成区间内的每一天。
• PRIOR ROWID = ROWID：强制递归仅关联当前行，避免跨行列的无效关联。
• PRIOR dbms_random.value is not null：避免Oracle抛出"ORA-01436: CONNECT BY loop in user data"错误，若表有主键，也可以用PRIOR 主键字段 != 主键字段替代。

方案二：结合multiset与table函数 #

这种写法逻辑更直观，将每行的日期区间单独展开，无需处理递归关联问题：

select dt.column_value as dt
from date_period,
     table(cast(multiset(
       select trunc(dt_from) + LEVEL - 1
       from dual
       connect by LEVEL <= trunc(dt_to) - trunc(dt_from) + 1
     ) as sys.OdciDateList)) dt;

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