层次查询中connect by nocycle与connect_by_iscycle伪列连用时未按预期输出结果

chengccy2010

newkid 发表于 2017-6-30 21:36
我看你说得头头是道，给你出个题：

update family1 set fatherid=0 where fatherid is null;

原理母鸡...
Oracle官网查了下,这么说的:
If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
           PRIOR account_mgr_id = customer_id ...
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

http://docs.oracle.com/database/ ... ries.htm#SQLRF52332

newkid

这是我的观察结果：Oracle会收集所有 PRIOR 后面出现过的表达式，座位判断是否循环的依据。像我上面加上and prior fatherid is not null 之后，(childid,fatherid) 联合起来才能作为循环的依据。
这也是为什么PRIOR SYS_GUID() IS NOT NULL 会使得循环判断无效。
我的例子中两个PRIOR是简单AND的关系，不知道OR或者更复杂的AND OR 混合关系是否也还成立。你如果脑子好使又有空可以做做实验。

oliseh

学习了，谢两位大师