如何处理CONNECT BY LEVEL中的NULL值以避免查询无限循环？

我有一张租赁表（leases），存储了每份租赁的开始日期（Start_Date）和结束日期（End_Date），部分租赁无结束日期，该字段为NULL。我需要生成租赁期间的付款日期/金额列表。

对于有结束日期的租赁，我用以下逻辑实现：

CONNECT BY LEVEL <= TRUNC(MONTHS_BETWEEN(pl.END_DATE, pl.START_DATE)+1,0)

但处理End_Date为NULL的情况时，查询会无限循环。

示例数据：

其中租赁2能正常生成13行数据，租赁4会触发无限循环。

我尝试过用NVL把NULL替换为当前日期，写法如下但未解决问题：

SELECT  CASE WHEN rownum = 1 THEN pl.START_DATE ELSE ADD_MONTHS(trunc(pl.START_DATE, 'mon') + pl.PAYMENT_DAY -1, rownum -1) END AS PAYMENT_DATE
         , pl.*
         , rownum AS pm_num
FROM    PM_LEASES pl  
WHERE   pl.LEASE_ID = 4
CONNECT BY LEVEL <= TRUNC(MONTHS_BETWEEN(nvl(pl.END_DATE, trunc(sysdate)), pl.START_DATE)+1,0)

也查阅过Oracle文档，但文档主要聚焦层级结构，没找到对应解决办法。目前用了个临时方案，仅支持付款次数不超过100次的租赁：

SELECT  pl.lease_id
        , CASE WHEN lvl = 1 THEN pl.first_payment_amt ELSE pl.lease_amt END AS lease_amt
        , CASE WHEN lvl = 1 THEN pl.start_date ELSE ADD_MONTHS(trunc(pl.START_DATE, 'mon') + pl.PAYMENT_DAY -1, lvl-1) END AS payment_date
FROM    pm_leases pl
LEFT JOIN (select level as lvl from dual connect by level <= 100) ON 1=1
WHERE   add_months(pl.start_date, lvl) <= nvl(pl.end_date, add_months(trunc(sysdate), 1))

方法1：修复CONNECT BY逻辑，避免无限循环 #

当End_Date为NULL时，MONTHS_BETWEEN返回NULL，导致LEVEL <= NULL的条件永远为真，触发无限循环。可以通过明确终止条件+防循环约束解决：

SELECT
    pl.lease_id,
    CASE 
        WHEN LEVEL = 1 THEN pl.first_payment_amt 
        ELSE pl.lease_amt 
    END AS lease_amt,
    CASE 
        WHEN LEVEL = 1 THEN pl.start_date 
        ELSE ADD_MONTHS(TRUNC(pl.start_date, 'MON') + pl.payment_day - 1, LEVEL - 1) 
    END AS payment_date
FROM pm_leases pl
CONNECT BY 
    -- 动态计算最大层级：有结束日期则按租期，无则按到当前日期的月份数
    LEVEL <= CASE 
                WHEN pl.end_date IS NOT NULL THEN TRUNC(MONTHS_BETWEEN(pl.end_date, pl.start_date) + 1, 0)
                ELSE TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), pl.start_date) + 1, 0)
             END
    -- 确保只针对当前租赁生成层级，避免跨行循环
    AND PRIOR pl.lease_id = pl.lease_id
    -- Oracle专属防无限循环约束
    AND PRIOR SYS_GUID() IS NOT NULL
WHERE pl.lease_id IN (2, 4); -- 可指定单个或多个租赁ID

方法2：使用递归CTE（更直观，Oracle 11gR2+支持） #

递归CTE的终止条件更清晰，完全避免无限循环风险：

WITH recursive_payments AS (
    -- 初始行：第一笔付款
    SELECT
        pl.lease_id,
        pl.start_date AS payment_date,
        pl.first_payment_amt AS lease_amt,
        1 AS lvl,
        pl.end_date,
        pl.start_date AS original_start,
        pl.payment_day
    FROM pm_leases pl
    WHERE pl.lease_id IN (2, 4) -- 筛选目标租赁
    UNION ALL
    -- 递归生成后续付款
    SELECT
        rp.lease_id,
        ADD_MONTHS(TRUNC(rp.original_start, 'MON') + rp.payment_day - 1, rp.lvl),
        pl.lease_amt,
        rp.lvl + 1,
        rp.end_date,
        rp.original_start,
        rp.payment_day
    FROM recursive_payments rp
    JOIN pm_leases pl ON rp.lease_id = pl.lease_id
    WHERE 
        -- 终止条件：有结束日期则不超结束日，无则不超当前日期
        (rp.end_date IS NOT NULL AND ADD_MONTHS(TRUNC(rp.original_start, 'MON') + rp.payment_day - 1, rp.lvl) <= rp.end_date)
        OR (rp.end_date IS NULL AND ADD_MONTHS(TRUNC(rp.original_start, 'MON') + rp.payment_day - 1, rp.lvl) <= TRUNC(SYSDATE))
)
SELECT lease_id, lease_amt, payment_date
FROM recursive_payments
ORDER BY lease_id, lvl;

临时方案的不足与优化 #

你当前的临时方案限制了最多100次付款，若要保留类似逻辑，可把固定的100替换为动态计算的最大月份数（比如TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), pl.start_date) + 1, 0)），避免次数限制。

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