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, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
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, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
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 "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 PRIOR employee_id = manager_id)
GROUP BY name;
NAME Total_Salary
------------------------- ------------
GIEtz 8300
Higgins 20300
King 20300
Kochhar 20300
