Oracle:按用户数据循环连接 [英] Oracle: Connect By Loop in user data

问题描述

我了解Oracle中何时可能发生循环.从理论上说，如果一条记录既是另一个节点的父级，又是它的子级，那么它就可以进入循环.

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.

但是我无法理解为什么我的这个特定查询陷入循环.

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'

有人可以向我解释一下这些数据如何导致循环. 我进行了CONNECT_BY_ISCYCLE检查，发现记录循环为KING(总统). 但是我仍然不知道怎么可能，因为KING是总裁，并且我看不到它成为表中任何记录的孩子和父母的任何方式.

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.

请解释一下，为什么这是错误，循环在哪里?

Please explain me, why this is an error and where is the loop?

谢谢.

仅供参考，该表是Oracle SCOTT用户中的默认EMP表.

FYI, The table is the default EMP table in SCOTT user in Oracle.

推荐答案

要查找父行的子级，Oracle会评估PRIOR 父行和CONNECT BY条件的表达式 表中每一行的其他表达式.的行 条件是真实的是父母的孩子. CONNECT BY 条件可以包含其他条件以进一步过滤行 由查询选择.

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.

如果CONNECT BY条件导致层次结构中的循环，则 Oracle返回错误.如果一行是父行，则发生循环 (或祖父母或直接祖先)和孩子(或孙子或 另一行的直接后代.

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.

如果没有满足START WITH且Job ='PRESIDENT'的行，则永远不会发生循环(Oracle仅检索START WITH行)

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'的行，则在任何情况下都会发生循环，原因是:
1. Oracle查找满足START WITH的所有行(根行).
2.对于第1页中的每一行，Oracle都会扫描整个表以查找后代.所有行(包括第1页中的行)都满足CONNECT BY中的条件(因为之前的Job ='PRESIDENT'始终为true)
3.显而易见...

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 ...

这篇关于Oracle:按用户数据循环连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！