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
