尝试使用递归WITH子句生成Fibonacci系列，得到错误：'执行时检测到循环'

I am trying to generate Fibonacci series using below query (recursive WITH clause).

我试图使用下面的查询(递归WITH子句)生成斐波那契数列。

WITH X(Pnbr,Cnbr) AS
(SELECT 0, 1 FROM dual
 UNION ALL
 SELECT X.Cnbr, X.Pnbr+X.Cnbr FROM X
 WHERE X.Cnbr<50
)
 SELECT * FROM X

But I am getting this error

但是我收到了这个错误

ORA-32044: cycle detected while executing recursive WITH query

Why?

2 个解决方案

#1

Your data at first iteration would be

您在第一次迭代时的数据将是

PNBR  CNBR
0   , 1
1   , 1 + 0

So, CNBR is 1 is first two rows.. A Cycle is detected!

所以,CNBR是1前两行..检测到循环!

The CONNECTING condition has to be unique!

CONNECTING条件必须是唯一的!

So probably you would need to maintain an iterator.

所以你可能需要维护一个迭代器。

ROWNUM is what I used here for it.

ROWNUM就是我在这里使用的。

WITH X(iter,Pnbr,Cnbr) AS
(SELECT 1,0, 1 FROM dual
 UNION ALL
 SELECT iter + rownum, X.Cnbr, X.Pnbr+X.Cnbr FROM X
 WHERE iter < 50
)
 SELECT iter,Pnbr FROM X;

DEMO

#2

I agree with the diagnosis in M. Ravisankar's Answer (from 2015), but not with the remedy.

我同意M. Ravisankar的答案中的诊断(从2015年开始),但不同意补救措施。

To handle precisely the situation presented in the original post, recursive CTE offers the CYCLE clause. In this problem, while there will be repeated values in the Pnbr column as well as in the Cnbr column, when considered separately, there are no repeated values (duplicates) in the composite (Pnbr, Cnbr).

为了准确处理原始帖子中提供的情况,递归CTE提供了CYCLE子句。在这个问题中,虽然在Pnbr列和Cnbr列中都会有重复的值,但是当单独考虑时,在复合(Pnbr,Cnbr)中没有重复值(重复)。

So, the query can be written like this:

所以,查询可以这样写:

WITH X(Pnbr,Cnbr) AS
(SELECT 0, 1 FROM dual
 UNION ALL
 SELECT X.Cnbr, X.Pnbr+X.Cnbr FROM X
 WHERE X.Cnbr<50
)
cycle Pnbr, Cnbr set cycle to 'Y' default 'N'    ----
SELECT Pnbr, Cnbr FROM X

Notice the cycle clause (second to last line), and also SELECT Pnbr, Cnbr as opposed to SELECT * (if we selected * here, we would also get the cycle column, which we don't need).

注意循环子句(倒数第二行),还有SELECT Pnbr,Cnbr而不是SELECT *(如果我们在这里选择*,我们也会得到循环列,我们不需要)。

#1