但是我无法理解为什么我的这个特定查询会进入循环.
SELECT Empno,Ename,Job FROM Emp START WITH Empno = 7839 CONNECT BY PRIOR Job='PRESIDENT'
有人可以解释一下这个数据如何导致循环.
我做了一个CONNECT_BY_ISCYCLE检查,发现记录循环是KING(总统).
但我仍然不明白怎么可能,因为KING是总统,而且我认为它没有任何方式成为表中任何记录的孩子和父母.
请解释一下,为什么这是一个错误,循环在哪里？
提前致谢.
仅供参考,该表是Oracle中SCOTT用户的默认EMP表.
解决方法
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.
如果没有满足START WITH且具有Job =’PRESIDENT’的行,则循环将永远不会发生(Oracle仅检索START WITH行)
如果表中有一行满足START WITH且有Job =’PRESIDENT’,则无论如何都会发生循环,因为：1. Oracle查找满足START WITH(根行)的所有行.2.对于p.1中的每一行,Oracle会扫描整个表以查找后代.所有行(包括第1行中的行)都满足CONNECT BY中的条件(因为之前的Job =’PRESIDENT’始终为true)明显……
