PL/SQL基础：阶层查询
作者: , 出处:IT专家网社区, 责任编辑: 叶江,
2007-05-10 13:11
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 |
- 本文关键词：
