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