ORACLE 10g新增了阶层查询操作符PRIOR，CONNECT_BY_ROOT

　　■PRIOR

　　阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，作为运算符，PRIOR和加(+)减(-)运算的优先级相同。

　　■阶层查询

　　语法：START WITH condition CONNECT BY NOCYCLE condition

• 　　START WITH 指定阶层的根
• 　　CONNECT BY 指定阶层的父/子关系
• 　　NOCYCLE 存在CONNECT BY LOOP的纪录时，也返回查询结果。
• 　　condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr

　　例：

CONNECT BY last_name != 'King' AND RIOR employee_id = manager_id ... 　　CONNECT BY RIOR employee_id = manager_id and 　　PRIOR account_mgr_id = customer_id ...

　　■CONNECT_BY_ROOT

　　查询指定根的阶层数据。

　　■CONNECT BY子句的例子

　　通过CONNECT BY子句定义职员和上司的关系。

SQL>SELECT employee_id, last_name, manager_id 　　FROM employees 　　CONNECT BY RIOR employee_id = manager_id; 　　EMPLOYEE_ID LAST_NAME MANAGER_ID 　　----------- ------------------------- ---------- 　　101 Kochhar 100 　　108 Greenberg 101 　　109 Faviet 108 　　110 Chen 108 　　111 Sciarra 108 　　112 Urman 108 　　113 Popp 108 　　200 Whalen 101

　　■LEVEL的例子

　　通过LEVEL虚拟列表示节点的关系。

SQL>SELECT employee_id, last_name, manager_id, LEVEL 　　FROM employees 　　CONNECT BY RIOR employee_id = manager_id; 　　EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL 　　----------- ------------------------- ---------- ---------- 　　101 Kochhar 100 1 　　108 Greenberg 101 2 　　109 Faviet 108 3 　　110 Chen 108 3 　　111 Sciarra 108 3 　　112 Urman 108 3 　　113 opp 108 3

　　■START WITH子句的例子

　　通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。

SQL>SELECT last_name, employee_id, manager_id, LEVEL 　　FROM employees 　　START WITH employee_id = 100 　　CONNECT BY RIOR employee_id = manager_id 　　ORDER SIBLINGS BY last_name; 　　LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL 　　------------------------- ----------- ---------- ---------- 　　King 100 1 　　Cambrault 148 100 2 　　Bates 172 148 3 　　Bloom 169 148 3 　　Fox 170 148 3 　　Kumar 173 148 3 　　Ozer 168 148 3 　　Smith 171 148 3 　　De Haan 102 100 2 　　Hunold 103 102 3 　　Austin 105 103 4 　　Ernst 104 103 4 　　Lorentz 107 103 4 　　Pataballa 106 103 4 　　Errazuriz 147 100 2 　　Ande 166 147 3 　　Banda 167 147 3

　　hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。

　　更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。

SQL>UPDATE employees SET manager_id = 145 　　WHERE employee_id = 100; 　　SQL>SELECT last_name "Employee", 　　LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" 　　FROM employees 　　WHERE level <= 3 AND department_id = 80 　　START WITH last_name = 'King' 　　CONNECT BY RIOR employee_id = manager_id AND LEVEL <= 4; 　　2 3 4 5 6 7 ERROR: 　　ORA-01436: CONNECT BY loop in user data 　　CONNECT BY NOCYCLE强制返回查询结果。CONNECT_BY_ISCYCLE显示是否存在LOOP。 　　SQL>SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", 　　LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" 　　FROM employees 　　WHERE level <= 3 AND department_id = 80 　　START WITH last_name = 'King' 　　CONNECT BY NOCYCLE RIOR employee_id = manager_id AND LEVEL <= 4; 　　Employee Cycle LEVEL ath 　　------------------------- ------ ------ ------------------------- 　　Russell 1 2 /King/Russell 　　Tucker 0 3 /King/Russell/Tucker 　　Bernstein 0 3 /King/Russell/Bernstein 　　Hall 0 3 /King/Russell/Hall 　　Olsen 0 3 /King/Russell/Olsen 　　Cambrault 0 3 /King/Russell/Cambrault 　　Tuvault 0 3 /King/Russell/Tuvault 　　Partners 0 2 /King/Partners 　　King 0 3 /King/Partners/King 　　Sully 0 3 /King/Partners/Sully 　　McEwen 0 3 /King/Partners/McEwen

　　■CONNECT_BY_ROOT的例子

　　1，查询110部门的职员，上司，职员和上司之间级别差及路径。

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", 　　LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" 　　FROM employees 　　WHERE LEVEL > 1 and department_id = 110 　　CONNECT BY PRIOR employee_id = manager_id; 　　Employee Manager Pathlen Path 　　--------------- ------------ ---------- ----------------------------------- 　　Higgins Kochhar 1 /Kochhar/Higgins 　　Gietz Kochhar 2 /Kochhar/Higgins/Gietz 　　Gietz Higgins 1 /Higgins/Gietz 　　Higgins King 2 /King/Kochhar/Higgins 　　Gietz King 3 /King/Kochhar/Higgins/Gietz

　　2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。

SELECT name, SUM(salary) "Total_Salary" FROM ( 　　SELECT CONNECT_BY_ROOT last_name as name, Salary 　　FROM employees 　　WHERE department_id = 110 　　CONNECT BY RIOR employee_id = manager_id) 　　GROUP BY name; 　　NAME Total_Salary 　　------------------------- ------------ 　　Gietz 8300 　　Higgins 20300 　　King 20300 　　Kochhar 20300