SELECT: CONNECT BY

说明：

为层次查询指定父行和子行关系的条件

语法：

connect ::=

参数：

CONNECT BY有一个可选参数NOCYCLE：

如果层次数据存在环，系统会报错，想找出其中导致环出现的数据，可以使用该参数输出所有的形成环的地方，使用该参数的时候请加上CONNECT_BY_ISLEAF和CONNECT_BY_ISCYCLE参数。

若层次数据存在环时，神通数据库不会输出环上的重复元素，以及重复元素的兄弟及其兄弟的子孙。换句话，根据CONNECT BY条件计算节点的孩子集合中，如果存在某个孩子已位于层次链路中而形成环，则认为该节点不存在可连接的孩子，终止节点的输出。

注释：

CONNECT BY 子句是对FROM子句输出结果进行层次查询，输出根的儿子以及儿子的子孙。CONNECT BY后的条件不能含有CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH、CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE。

所有的WHERE后的条件都可以放在CONNECT BY之后，但当这些条件放在CONNECT BY之后时其意义发生了改变，所以CONNECT BY后的条件都不能优化到WHERE之后，WHERE后的条件也不能改变到CONNECT BY之后。CONNECT BY之后的所有条件都是对根的子孙起作用，对根结点是没有用的，所以无论CONNECT BY之后跟什么条件，根结点总是输出，并且这些条件作用以后子孙还得连续。但WHERE中的条件就不一样，它是作用在结果集上的，所以会对每个结点都起作用。

另外，值得注意的是，如果在CONNECT BY 后的条件中没有PRIOR关键字，那么不论是否有NOCYCLE关键字，层次查询都将不会对环进行判断。ORACLE可能因此进入死循环。而神通数据库由于有最大层次限制，会提示超过最大层次数。

示例：

DROP TABLE EMPLOYEES;

CREATE TABLE EMPLOYEES (employee_id int,last_name char(10), manager_id int);

insert into EMPLOYEES values(101,'Kochhar',100);
insert into EMPLOYEES values(108,'Greenberg',101);
insert into EMPLOYEES values(113,'Popp',108);
insert into EMPLOYEES values(200,'Whalen',101);
insert into EMPLOYEES values(109,'Faviet',108);
insert into EMPLOYEES values(110,'Chen',108);
insert into EMPLOYEES values(111,'Sciarra',108);
insert into EMPLOYEES values(112,'Urman',108);

使用CONNECT BY进行层次查询,查询语句如下：

--使用CONNECT BY进行层次查询,查询语句如下：
SELECT employee_id, last_name, manager_id from EMPLOYEES START WITH (employee_id = 108 or employee_id = 101) CONNECT BY PRIOR employee_id = manager_id;
 EMPLOYEE_ID | LAST_NAME  | MANAGER_ID
-------------+------------+------------
         101 | Kochhar    |        100
         108 | Greenberg  |        101
         113 | Popp       |        108
         109 | Faviet     |        108
         110 | Chen       |        108
         111 | Sciarra    |        108
         112 | Urman      |        108
         200 | Whalen     |        101
         108 | Greenberg  |        101
         113 | Popp       |        108
         109 | Faviet     |        108
         110 | Chen       |        108
         111 | Sciarra    |        108
         112 | Urman      |        108
(14 rows)

由于这里使用的测试数据不存在环，所以上面的查询有没有NOCYCLE都不会影响结果。

SELECT employee_id, last_name, manager_id from EMPLOYEES START WITH (employee_id = 108 or employee_id = 101) CONNECT BY NOCYCLE PRIOR employee_id = manager_id;
 EMPLOYEE_ID | LAST_NAME  | MANAGER_ID
-------------+------------+------------
         101 | Kochhar    |        100
         108 | Greenberg  |        101
         113 | Popp       |        108
         109 | Faviet     |        108
         110 | Chen       |        108
         111 | Sciarra    |        108
         112 | Urman      |        108
         200 | Whalen     |        101
         108 | Greenberg  |        101
         113 | Popp       |        108
         109 | Faviet     |        108
         110 | Chen       |        108
         111 | Sciarra    |        108
         112 | Urman      |        108
(14 rows)

构造存在环的SQL语句

SELECT * FROM EMPLOYEES CONNECT BY PRIOR manager_id START WITH manager_id=101;
ERROR, 结果有环存在, 用户数据中的CONNECT BY循环，但没有NOCYCLE参数！

使用参数输出所有形成环的地方

SELECT * FROM EMPLOYEES CONNECT BY NOCYCLE PRIOR manager_id START WITH manager_id=101;
 EMPLOYEE_ID | LAST_NAME  | MANAGER_ID
-------------+------------+------------
     108 | Greenberg  |        101
     200 | Whalen     |        101
(2 rows)