Les19_chinese

1、19Copyright Oracle Corporation, 2001. All rights reserved.分级查询分级查询19-2Copyright Oracle Corporation, 2001. All rights reserved.目标目标通过本章学习，您将可以通过本章学习，您将可以:分级查询的概念分级查询的概念创建树形的报表创建树形的报表格式划分级数据格式划分级数据在树形结构中删除分支和节点在树形结构中删除分支和节点19-3Copyright Oracle Corporation, 2001. All rights reserved.EMPLOYEES 表中的数据表中的数

2、据19-4Copyright Oracle Corporation, 2001. All rights reserved.树形结构树形结构EMPLOYEE_ID = 100 (双亲双亲)MANAGER_ID = 100 (孩子孩子)19-5Copyright Oracle Corporation, 2001. All rights reserved.分级查询分级查询 expr comparison_operator exprSELECT LEVEL, column, expr.FROM tableWHERE condition(s)START WITH condition(s)CONNECT

3、BY PRIOR condition(s) ;19-6Copyright Oracle Corporation, 2001. All rights reserved.遍历树遍历树始始 点点指定需要满足的条件指定需要满足的条件接受有效的条件接受有效的条件遍历遍历 EMPLOYEES 表表, 以姓名为以姓名为 Kochhar的员工作为始点的员工作为始点.START WITH last_name = KochharSTART WITH column1 = value19-7Copyright Oracle Corporation, 2001. All rights reserved.遍历树遍历树方向

4、方向CONNECT BY PRIOR column1 = column2. CONNECT BY PRIOR employee_id = manager_id19-8Copyright Oracle Corporation, 2001. All rights reserved.遍历树遍历树: 从底到顶从底到顶SELECT employee_id, last_name, job_id, manager_idFROM employeesSTART WITH employee_id = 101CONNECT BY PRIOR manager_id = employee_id ;19-9Copyrig

5、ht Oracle Corporation, 2001. All rights reserved.遍历树遍历树: 从顶到底从顶到底SELECT last_name| reports to | PRIOR last_name Walk Top DownFROM employeesSTART WITH last_name = KingCONNECT BY PRIOR employee_id = manager_id ;19-10Copyright Oracle Corporation, 2001. All rights reserved.使用使用 LEVEL 伪列标记层次伪列标记层次 层次层次1根

6、根/双亲双亲层次层次2双亲双亲/孩子孩子层次层次3双亲双亲/孩子孩子/叶子叶子层次层次4叶子叶子19-11Copyright Oracle Corporation, 2001. All rights reserved.使用使用 LEVEL 和和 LPAD格式化分层查询格式化分层查询COLUMN org_chart FORMAT A12SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,_) AS org_chartFROM employees START WITH last_name=King CONNECT BY PRIOR employ

7、ee_id=manager_id 19-12Copyright Oracle Corporation, 2001. All rights reserved.修剪树枝修剪树枝19-13Copyright Oracle Corporation, 2001. All rights reserved.总结总结通过本章学习通过本章学习,您已经可以您已经可以: 对具有层次关系的数据创建树形报表对具有层次关系的数据创建树形报表指定遍历的始点和方向指定遍历的始点和方向删除节点和树枝删除节点和树枝19-14Copyright Oracle Corporation, 2001. All rights reserved.Hidden Slide19-15Copyright Oracle Corporation