PL/SQL基础：阶层查询

核心提示：ORACLE 10g新增了阶层查询操作符PRIOR，CONNECT_BY_ROOT ■PRIOR阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，PL/SQL基础：阶层查询，作为运算符，PRIOR和加(+)减(-)运算的优先级相同，condition ... PRIOR expr

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