PL/SQL基础：阶层查询

　　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 PRIOR employee_id = manager_id ...
CONNECT BY PRIOR 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 PRIOR 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 PRIOR 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 Popp 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 PRIOR 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, '/') \"ath\"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = &#39;King&#39;
CONNECT BY PRIOR 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, &#39;/&#39;) \"ath\"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = &#39;King&#39;
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
Employee Cycle LEVEL Path
------------------------- ------ ------ -------------------------
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 \"athlen\", SYS_CONNECT_BY_PATH(last_name, &#39;/&#39;) \"ath\"
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 PRIOR employee_id = manager_id)
GROUP BY name;
NAME Total_Salary
------------------------- ------------
Gietz 8300
Higgins 20300
King 20300
Kochhar 20300

不错啊! 一个字牛啊!