PLSQL基础阶层查询.docVIP

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 通过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 通过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 更新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 1、查询110部门的职员，上司，职员和上司之间级别差及路径。 SELECT last_name Employee, CONNECT_BY_ROOT last_name Manager,