分级取回数据.pptVIP

分级取回数据 目标 完成本课后, 您应当能够执行下列操作： 解释分级查询的概念 创建一个树型结构的报告 格式化分级数据 从树型结构中去除分支 EMPLOYEES 表中的例子数据 自然树结构 分级查询 遍历树 指定必须满足的条件 接受有效的条件 使用 EMPLOYEES 表，从名字是 Kochhar 的雇员开始 遍历树 遍历树: 从底向上 遍历树: 从顶向下 用 LEVEL 伪列将行分等级 用 LEVEL 和 LPAD 格式化分级报告 创建一个报告显示公司的管理层，从最高级别开始，缩进下面跟随的级别 修剪分支 小结 在本课中, 您应该已经学会下面的内容： 能够用分级查询查看一个表中各行之间的分级关系 指定查询的起点 通过修剪除去结点或分支 练习 19 概览 本章练习包括下面的主题： 区别分级查询和非分级查询 遍历树 用 LEVEL 伪列生成一个缩进的报表 修剪树结构 输出排序 Pruning Branches You can use the WHERE and CONNECT BY clauses to prune the tree; that is, to control which nodes or rows are displayed. The predicate you use acts as a Boolean condition. Examples Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but process the child rows. SELECT department_id, employee_id,last_name, job_id, salary FROM employees WHERE last_name != Higgins START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; Starting at the root, walk from the top down, and eliminate employee Higgins and all child rows. SELECT department_id, employee_id,last_name, job_id, salary FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id AND last_name != Higgins; Instructor Note You may wish to add here that using a WHERE clause to restrict a node could result in the hierarchy not being reflected truly by the output. 19-* Copyright ? Oracle Corporation, 2001. All rights reserved. 19 Copyright ? Oracle Corporation, 2001. All rights reserved. De Hann King Hunold EMPLOYEE_ID = 100 (父) MANAGER_ID = 100 (子) Whalen Kochhar Higgins Mourgos Zlotkey Rajs Davies Matos Gietz Ernst Lorentz Hartstein Fay Abel Taylor Grant Vargas WHERE 条件: expr comparison_operator expr SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; 起点 ...START WITH last_name = Kochhar START WITH column1 = value 方向 从顶向下 Column1 = Parent Key Column2 = Child Key 从底向上 Column1 = C