Oracle-OCP-SQL-19-Hierarchical-Retrieval

Copyright©OracleCorporation,2001.Allrightsreserved.HierarchicalRetrieval19-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Interprettheconceptofahierarchicalquery•Createatree-structuredreport•Formathierarchicaldata•Excludebranchesfromthetreestructure19-3Copyright©OracleCorporation,2001.Allrightsreserved.SampleDatafromtheEMPLOYEESTable19-4Copyright©OracleCorporation,2001.Allrightsreserved.NaturalTreeStructureDeHannKingHunoldEMPLOYEE_ID=100(Parent)MANAGER_ID=100(Child)WhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFayAbelTaylorGrantVargas19-5Copyright©OracleCorporation,2001.Allrightsreserved.HierarchicalQueriesWHEREcondition:exprcomparison_operatorexprSELECT[LEVEL],column,expr...FROMtable[WHEREcondition(s)][STARTWITHcondition(s)][CONNECTBYPRIORcondition(s)];19-6Copyright©OracleCorporation,2001.Allrightsreserved.WalkingtheTreeStartingPoint•Specifiestheconditionthatmustbemet•AcceptsanyvalidconditionUsingtheEMPLOYEEStable,startwiththeemployeewhoselastnameisKochhar....STARTWITHlast_name='Kochhar'STARTWITHcolumn1=value19-7Copyright©OracleCorporation,2001.Allrightsreserved.WalkingtheTreeDirectionTopdownColumn1=ParentKeyColumn2=ChildKeyBottomupColumn1=ChildKeyColumn2=ParentKeyWalkfromthetopdown,usingtheEMPLOYEEStable.CONNECTBYPRIORcolumn1=column2...CONNECTBYPRIORemployee_id=manager_id19-8Copyright©OracleCorporation,2001.Allrightsreserved.WalkingtheTree:FromtheBottomUpSELECTemployee_id,last_name,job_id,manager_idFROMemployeesSTARTWITHemployee_id=101CONNECTBYPRIORmanager_id=employee_id;19-9Copyright©OracleCorporation,2001.Allrightsreserved.WalkingtheTree:FromtheTopDownSELECTlast_name||'reportsto'||PRIORlast_nameWalkTopDownFROMemployeesSTARTWITHlast_name='King'CONNECTBYPRIORemployee_id=manager_id;…19-10Copyright©OracleCorporation,2001.Allrightsreserved.RankingRowswiththeLEVELPseudocolumnDeHannKingHunoldWhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFayAbelTaylorGrantVargasLevel1root/parentLevel2parent/childLevel3parent/child/leafLevel4leaf19-11Copyright©OracleCorporation,2001.Allrightsreserved.FormattingHierarchicalReportsUsingLEVELandLPADCreateareportdisplayingcompanymanagementlevels,beginningwiththehighestlevelandindentingeachofthefollowinglevels.COLUMNorg_chartFORMATA12SELECTLPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,'_')ASorg_chartFROMemployeesSTARTWITHlast_name='King'CONNECTBYPRIORemployee_id=manager_id19-13Copyright©OracleCorporation,2001.Allrightsreserved.PruningBranchesUsetheWHEREclausetoeliminateanode.UsetheCONNECTBYclausetoeliminateabranch.WHERElast_name!='Higgins'CONNECTBYPRIORemployee_id=manager_idANDlast_name!='Higgins'KochharHigginsGietzWhalenKochharHigginsWhalenGietz19-14Copyright©OracleCorporation,2001.Allrightsreserved.SummaryInthislesson,youshouldhavelearnedthefollowing:•Youcanusehierarchicalqueriestoviewahierarchicalrelationshipbetweenrowsinatable.•Youspecifythedirectionandstartingpointofthequery.•Youcaneliminatenodesorbranchesbypruning.19-15Copyright©OracleCorporation,2001.Allrightsreserved.Practice19OverviewThispracticecoversthefollowingtopics:•Distinguishinghierarchicalqueriesfromnonhierarchicalqueries•Walkingthroughatree•ProducinganindentedreportbyusingtheLEVELpseudocolumn•Pruningthetreestructure•Sortingtheoutput