Oracle：在用户数据中按循环连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26464590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Connect By Loop in user data
提问by Manish
I understand when a loop can occur in Oracle. Theoritically it says if a record is both parent to another node and at the same time it is child to it, then it can enter into a loop.
我了解 Oracle 中何时会发生循环。从理论上讲，它表示如果一个记录既是另一个节点的父节点，又是另一个节点的子节点，则它可以进入循环。
But I can't catch why my this particular query is running into a loop.
但我不明白为什么我的这个特定查询会陷入循环。
SELECT Empno, Ename, Job
FROM Emp
START WITH Empno = 7839
CONNECT BY PRIOR
Job='PRESIDENT'
Can someone please explain me how this data can result into a loop. I made a CONNECT_BY_ISCYCLE check and found that the record looping is KING(President). But I still don't understand how can it be possible, as KING is President and I don't see any way in which it is becoming both child and parent to any record in the table.
有人可以解释一下这些数据如何导致循环。我做了一个 CONNECT_BY_ISCYCLE 检查，发现记录循环是 KING(President)。但我仍然不明白这怎么可能，因为 KING 是总统，我看不到它以任何方式成为表中任何记录的子项和父项。
Please explain me, why this is an error and where is the loop?
请解释一下，为什么这是一个错误，循环在哪里？
Thanks in advance.
提前致谢。
FYI, The table is the default EMP table in SCOTT user in Oracle.
仅供参考，该表是 Oracle SCOTT 用户的默认 EMP 表。
采纳答案by Multisync
To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query.
If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.
为了找到父行的子行，Oracle 计算父行的 CONNECT BY 条件的 PRIOR 表达式和表中每一行的另一个表达式。条件为真的行是父行的子行。CONNECT BY 条件可以包含其他条件以进一步过滤查询选择的行。
如果 CONNECT BY 条件导致层次结构中出现循环，则 Oracle 将返回错误。如果一行既是另一行的父行（或祖父或直接祖先）又是另一行的子行（或孙子或直接后代），则会发生循环。
if there are no rows which satisfy START WITH and have Job = 'PRESIDENT' the loop will never occur (Oracle retrieves only START WITH rows)
如果没有满足 START WITH 且 Job = 'PRESIDENT' 的行，则循环将永远不会发生（Oracle 仅检索 START WITH 行）
if there is a row in the table which satisfies START WITH and has Job = 'PRESIDENT' the loop occurs in any case because:
1. Oracle finds all the rows which satisfy the START WITH (root rows).
2. For each row from p.1 Oracle scans the whole table for descendants. All rows (including the row from p. 1) satisfy the condition in CONNECT BY (because prior Job = 'PRESIDENT' is always true)
3. Obvious ...
如果表中有一行满足 START WITH 并且 Job = 'PRESIDENT' 循环在任何情况下都会发生，因为：
1. Oracle 找到所有满足 START WITH 的行（根行）。
2. 对于 p.1 中的每一行，Oracle 扫描整个表以查找后代。所有行（包括第 1 页中的行）都满足 CONNECT BY 中的条件（因为prior Job = 'PRESIDENT' 始终为真）
3. 明显 ...
回答by charlan alves
Add the expression nocicle
connect by nocycle
添加表情nocicle
connect by nocycle
回答by Md Nazrul Islam
SELECT Empno, Ename, Job
FROM Emp
START WITH Empno = 7839
CONNECT BY nocycle PRIOR
Job='PRESIDENT'
