PL/SQL基本的：阶层查询(1)

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

共2页: 1 [2] 下一页