Les19oracle数据库教程.ppt

分级查询,目标,通过本章学习，您将可以:分级查询的概念创建树形的报表格式划分级数据在树形结构中删除分支和节点,EMPLOYEES 表中的数据,树形结构,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 tableWHERE condition(s)START WITH condition(s)CONNECT BY PRIOR condition(s);,遍历树,始 点,指定需要满足的条件接受有效的条件遍历 EMPLOYEES 表,以姓名为 Kochhar的员工作为始点,.START WITH last_name=Kochhar,START WITH column1=value,遍历树,方向,从顶到底,Column1=Parent KeyColumn2=Child Key,从底到顶,Column1=Child KeyColumn2=Parent Key,从顶到底遍历 EMPLOYEES 表,CONNECT BY PRIOR column1=column2,.CONNECT BY PRIOR employee_id=manager_id,遍历树:从底到顶,SELECT employee_id,last_name,job_id,manager_idFROM employeesSTART WITH employee_id=101CONNECT BY PRIOR manager_id=employee_id;,遍历树:从顶到底,SELECT last_name|reports to|PRIOR last_name Walk Top DownFROM employeesSTART WITH last_name=KingCONNECT BY PRIOR employee_id=manager_id;,使用 LEVEL 伪列标记层次,De Hann,King,Hunold,Whalen,Kochhar,Higgins,Mourgos,Zlotkey,Rajs,Davies,Matos,Gietz,Ernst,Lorentz,Hartstein,Fay,Abel,Taylor,Grant,Vargas,层次1根/双亲,层次2双亲/孩子,层次3双亲/孩子/叶子,层次4叶子,使用 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 employee_id=manager_id,修剪树枝,使用 WHERE 子句删除节点,使用CONNECT BY 子句删除树枝,WHERE last_name!=Higgins,CONNECT BY PRIOR employee_id=manager_id AND last_name!=Higgins,Kochhar,Higgins,Gietz,Whalen,Kochhar,Higgins,Whalen,Gietz,总结,通过本章学习,您已经可以:对具有层次关系的数据创建树形报表指定遍历的始点和方向删除节点和树枝,Hidden Slide,Hidden Slide,Hidden Slide,