19分级查询

Copyright©OracleCorporation,2001.Allrightsreserved.分级查询19-2Copyright©OracleCorporation,2001.Allrightsreserved.目标通过本章学习，您将可以:•分级查询的概念•创建树形的报表•格式划分级数据•在树形结构中删除分支和节点19-3Copyright©OracleCorporation,2001.Allrightsreserved.EMPLOYEES表中的数据19-4Copyright©OracleCorporation,2001.Allrightsreserved.树形结构DeHannKingHunoldEMPLOYEE_ID=100(双亲)MANAGER_ID=100(孩子)WhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFayAbelTaylorGrantVargas19-5Copyright©OracleCorporation,2001.Allrightsreserved.分级查询WHERE条件:exprcomparison_operatorexprSELECT[LEVEL],column,expr...FROMtable[WHEREcondition(s)][STARTWITHcondition(s)][CONNECTBYPRIORcondition(s)];19-6Copyright©OracleCorporation,2001.Allrightsreserved.遍历树始点•指定需要满足的条件•接受有效的条件遍历EMPLOYEES表,以姓名为Kochhar的员工作为始点...STARTWITHlast_name='Kochhar'STARTWITHcolumn1=value19-7Copyright©OracleCorporation,2001.Allrightsreserved.遍历树方向从顶到底Column1=ParentKeyColumn2=ChildKey从底到顶Column1=ChildKeyColumn2=ParentKey从顶到底遍历EMPLOYEES表CONNECTBYPRIORcolumn1=column2...CONNECTBYPRIORemployee_id=manager_id19-8Copyright©OracleCorporation,2001.Allrightsreserved.遍历树:从底到顶SELECTemployee_id,last_name,job_id,manager_idFROMemployeesSTARTWITHemployee_id=101CONNECTBYPRIORmanager_id=employee_id;19-9Copyright©OracleCorporation,2001.Allrightsreserved.遍历树:从顶到底SELECTlast_name||'reportsto'||PRIORlast_nameWalkTopDownFROMemployeesSTARTWITHlast_name='King'CONNECTBYPRIORemployee_id=manager_id;…19-10Copyright©OracleCorporation,2001.Allrightsreserved.使用LEVEL伪列标记层次DeHannKingHunoldWhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFayAbelTaylorGrantVargas层次1根/双亲层次2双亲/孩子层次3双亲/孩子/叶子层次4叶子19-11Copyright©OracleCorporation,2001.Allrightsreserved.使用LEVEL和LPAD格式化分层查询COLUMNorg_chartFORMATA12SELECTLPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,'_')ASorg_chartFROMemployeesSTARTWITHlast_name='King'CONNECTBYPRIORemployee_id=manager_id19-12Copyright©OracleCorporation,2001.Allrightsreserved.修剪树枝使用WHERE子句删除节点使用CONNECTBY子句删除树枝WHERElast_name!='Higgins'CONNECTBYPRIORemployee_id=manager_idANDlast_name!='Higgins'KochharHigginsGietzWhalenKochharHigginsWhalenGietz19-13Copyright©OracleCorporation,2001.Allrightsreserved.总结通过本章学习,您已经可以:•对具有层次关系的数据创建树形报表•指定遍历的始点和方向•删除节点和树枝