获取错误为“ORA-32044:执行递归 WITH 查询时检测到循环"； [英] Getting error as "ORA-32044: cycle detected while executing recursive WITH query"

问题描述

我收到错误

ORA-32044:在执行递归 WITH 查询时检测到循环

ORA-32044: cycle detected while executing recursive WITH query

在 Oracle 中执行以下查询时.

while executing the following query in Oracle.

WITH EmpsCTE (affiliation_id, from_customer_id,to_customer_id, to_name, level1)
AS
(
SELECT affiliation_id, from_customer_id,to_customer_id, to_name, 0
 FROM affiliation aff
 WHERE to_customer_id != from_customer_id
 and to_customer_id = 1000022560394
UNION ALL
SELECT aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1
 FROM affiliation aff
 INNER JOIN EmpsCTE  m
 ON aff.to_customer_id = m.from_customer_id
)
SELECT * FROM EmpsCTE;

推荐答案

您的代码将正常工作，除了只有一个数据条件，即您的 to_customer (1000022560394) 自己首先开始交易并经过某个级别的交易仅退还给他.

Your code will working fine except only for one data condition that is when your to_customer (1000022560394) himself have started the transaction in the first place and after some level of transaction its being returned to him only.

例如-样本数据集

对于这种情况，即使在事务结束时，您的递归查询部分也会发现其所有条件都为真，因为数据将同时存在于您的普通表和增量数据集中.

For this case, your Recursive part of query will find all its conditions true even at the end of the transaction, for the data will be there both in your normal table and incremental dataset.

一种解决方案是创建一个匹配标志来确定其遇到的次数并避免无限循环:

WITH EmpsCTE (affiliation_id, from_customer_id,to_customer_id, to_name,level1,match_count)  
AS  
(  
SELECT affiliation_id, from_customer_id,to_customer_id, to_name, 0, 0 match_count  
 FROM affiliation aff  
 WHERE to_customer_id != from_customer_id  
 and to_customer_id = 1000022560394  
UNION ALL  
SELECT aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1,1 match_count  
 FROM affiliation aff  
 INNER JOIN EmpsCTE  m  
 ON aff.to_customer_id = m.from_customer_id  
 where m.match_count=0  
)  
SELECT * FROM EmpsCTE;  

这篇关于获取错误为“ORA-32044:执行递归 WITH 查询时检测到循环"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！