Oracle - 执行递归'WITH'查询时检测到周期

Question

我正在做一个使用 oracle sql 递归查询的基本示例。我正在计算 MON-YY 格式的未来月份。我设法有一个看似正确的查询，但我不理解 WITH 查询的中断条件。

我正在尝试突破年份值(例如，当您到达 2020 年时停止(，但它在这样做时会检测到一个周期。如果我打破月份值(例如 12 月(，它可以工作。

这是我基于一个月的休息时间的查询：

with
prochains_mois(mois, annee) as (
select 'sep' as mois, 19 as annee
from dual
union all
select 
case mois
when 'jan' then 'fev'
when 'fev' then 'mar'
when 'mar' then 'avr'
when 'avr' then 'mai'
when 'mai' then 'jun'
when 'jun' then 'jui'
when 'jui' then 'aou'
when 'aou' then 'sep'
when 'sep' then 'oct'
when 'oct' then 'nov'
when 'nov' then 'dec'
when 'dec' then 'jan'
end,
case mois
when 'dec' then annee + 1
else annee
end
from prochains_mois r
where mois <> 'dec'
)
select * from prochains_mois;

如果我这样做，它会返回一致的结果。

MOI      ANNEE
--- ----------
sep         19
oct         19
nov         19
dec         19

现在，如果我尝试中断年份的递归查询，比如说 2020 年，所以我将 with 子句中的 where 条件更改为：

where annee < 20

然后我得到：

ORA-32044: cycle detected while executing recursive WITH query

我试图打破一个月，看看我的年度添加是否正确，似乎是这样。如果我在三月休息，我得到正确的一月和二月：

where mois <> 'mar'

给

MOI      ANNEE
--- ----------
sep         19
oct         19
nov         19
dec         19
jan         20
fev         20
mar         20

Answer 1

使用DATEs：

with prochains_mois( value ) as (
select DATE '2019-09-01' from dual
union all
select ADD_MONTHS( value, 1 )
FROM   prochains_mois
WHERE  value < DATE '2020-12-01'
)
select SUBSTR( TO_CHAR( value, 'mon', 'NLS_DATE_LANGUAGE=FRENCH' ), 1, 3 ) AS mois,
TO_CHAR( value, 'RR' ) AS annee
from   prochains_mois;

输出：

莫伊斯 |安妮 :--- |:---- 9月 |19   10月 |19   11月 |19   十二月 |19   一月 |20   费夫 |20   3月 |20   AVR |20   舞 |20   瑞 |20   瑞 |20   奥乌 |20   9月 |20   10月 |20   11月 |20   十二月 |20

或使用查询并检查月份和年份是否匹配：

with
prochains_mois(mois, annee) as (
select 'sep' as mois, 19 as annee
from dual
union all
select 
case mois
when 'jan' then 'fev'
when 'fev' then 'mar'
when 'mar' then 'avr'
when 'avr' then 'mai'
when 'mai' then 'jun'
when 'jun' then 'jui'
when 'jui' then 'aou'
when 'aou' then 'sep'
when 'sep' then 'oct'
when 'oct' then 'nov'
when 'nov' then 'dec'
when 'dec' then 'jan'
end,
case mois
when 'dec' then annee + 1
else annee
end
from prochains_mois r
where ( mois, annee ) NOT IN ( ( 'dec', 20 ) )
)
select * from prochains_mois;

输出：

莫伊斯 |安妮 :--- |----: 9月 |   19 10月 |   19 11月 |   19 十二月 |   19 一月 |   20 费夫 |   20 3月 |   20 AVR |   20 舞 |   20 6月 |   20 瑞 |   20 奥乌 |   20 9月 |   20 10月 |   20 11月 |   20 十二月 |   20

db<>在这里小提琴

Answer 2

您的主要问题是您尝试使用字符串/数字操作日期。别这样;如果您正在使用日期，请使用日期！

例如，你可以像这样做你所追求的事情：

WITH prochains_mois (mnth_dt) AS (SELECT TRUNC(sysdate, 'mm') mnth_dt
FROM   dual
UNION ALL
SELECT add_months(mnth_dt, 1) mnth_dt
FROM   prochains_mois
WHERE add_months(mnth_dt, 1) < add_months(TRUNC(sysdate, 'yyyy'), 12))
SELECT mnth_dt,
to_char(mnth_dt, 'mon') mois,
to_char(mnth_dt, 'yy') annee
FROM   prochains_mois;
MNTH_DT     MOIS ANNEE
----------- ---- -----
01/09/2019  sep  19
01/10/2019  oct  19
01/11/2019  nov  19
01/12/2019  dec  19

注：注：您可以将递归子分解查询中的谓词简化为mnth_dt < add_months(TRUNC(SYSDATE, 'yyyy'), 11)。

这通过获取开始日期(在这里，我使用了sysdate(并找到每月的第一天(通过使用可选的第二个参数TRUNC来指定我们要截断它的级别(。

然后，我们只需在每个日期添加一个月，直到到达开始日期年份的最后一个月。

只有在获得日期后，才能使用to_char以所需的格式输出数据。