oracle oracle分层查询nocycle和root连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9448694/
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 hierarchical query nocycle and connect by root
提问by ratsy
Can somebody explain use of nocycle and connect by root clauses in hierarchical queries in oracle, also when we dont use 'start with' what is the order we get the rows, i mean when we don't use 'start with' we get lot many rows, can anybody explain nocycle and connect by root(how is different than start with?) using simple emp table, Thanks for the help
有人可以解释在oracle的分层查询中使用nocycle和connect by root子句吗，当我们不使用“开始于”时，我们获取行的顺序是什么，我的意思是当我们不使用“开始于”时，我们得到很多很多行，任何人都可以使用简单的 emp 表解释 nocycle 并通过 root 连接（与开始有什么不同？），谢谢您的帮助
回答by eaolson
If your data has a loop in it (A -> B -> A -> B ...), Oracle will throw an exception, ORA-01436: CONNECT BY loop in user dataif you do a hierarchical query. NOCYCLEinstructs Oracle to return rows even if such a loop exists.
如果您的数据中有一个循环（A -> B -> A -> B ...），ORA-01436: CONNECT BY loop in user data如果您执行分层查询，Oracle 将抛出异常。NOCYCLE即使存在这样的循环，也指示 Oracle 返回行。
CONNECT_BY_ROOTgives you access to the root element, even several layers down in the query. Using the HR schema:
CONNECT_BY_ROOT使您可以访问根元素，甚至是查询中的几个层。使用 HR 模式：
select level, employee_id, last_name, manager_id ,
connect_by_root employee_id as root_id
from employees
connect by prior employee_id = manager_id
start with employee_id = 100
LEVEL EMPLOYEE_ID LAST_NAME MANAGER_ID ROOT_ID
---------- ----------- ------------------------- ---------- ----------
1 100 King 100
2 101 Kochhar 100 100
3 108 Greenberg 101 100
4 109 Faviet 108 100
...
Here, you see I started with employee 100 and started finding his employees. The CONNECT_BY_ROOToperator gives me access to King's employee_id even four levels down. I was very confused at first by this operator, thinking it meant "connect by the root element" or something. Think of it more like "the root of the CONNECT BY clause."
在这里，您可以看到我从员工 100 开始并开始寻找他的员工。该CONNECT_BY_ROOT操作使我获得国王的雇员标识甚至四级下来。起初我对这个操作符很困惑，认为它的意思是“通过根元素连接”什么的。把它想象成“CONNECT BY 子句的根”。
回答by Sushmita Mitkar
Here is about nocycle use in query.
这里是关于在查询中使用 nocycle。
Suppose we have a simple table with r1 and r2 column names and the values for first row r1=a,r2=b and second row r1=b,r2=a Now we know arefers to band brefers back to a. Hence there is a loop and if we write a hierarchical query as
假设我们有一个简单的表，其中包含 r1 和 r2 列名以及第一行 r1=a,r2=b 和第二行 r1=b,r2=a 的值现在我们知道a指的是b并且b指回a。因此存在一个循环，如果我们将分层查询编写为
select r1 from table_name start with r1='a' connect by prior r2=r1;
select r1 from table_name start with r1='a' connect by prior r2=r1;
we get connect by loop error
我们通过循环错误获得连接
Hence use nocycleto alloworacle to give results even if loop exists.
因此，使用NOCYCLE，以允许甲骨文给出的结果，即使存在环路。
Hence the query select r1 from table_name start with r1='a' connect by nocycle prior r2=r1;
因此查询 select r1 from table_name start with r1='a' connect by nocycle before r2=r1;
