1 / 36

Accessing Hierarchical Data in Oracle

Accessing Hierarchical Data in Oracle. Carol-Lee Erikson Tutch ctutch@gmail.com. Hierarchically Structured Data. Items at different levels are similar Arbitrary number of levels Examples Personnel Officer Manager Contributor Manufacturing Product Assembly Part.

bryson
Download Presentation

Accessing Hierarchical Data in Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Accessing Hierarchical Data in Oracle Carol-Lee Erikson Tutch ctutch@gmail.com

  2. Hierarchically Structured Data • Items at different levels are similar • Arbitrary number of levels • Examples • Personnel • Officer • Manager • Contributor • Manufacturing • Product • Assembly • Part

  3. Representing a Hierarchy • Items have a Parent-Child relationship • Hierarchy can be visualized as a Tree • Item is called a Node • Connections btw nodes are Links • Node has 0 or 1 parent • Node w/o parent is a Root • Node has 0 to N children • Node w/o children is a Leaf

  4. Hierarchy Tree 1 Level 1 2 9 15 Level 2 3 4 7 10 11 Level 3 5 6 8 12 13 Level 4 14 Level 5

  5. ER Diagram

  6. Structure for Sample Data CREATETABLEemp( emp_idNUMBER(5)NOTNULL, emp_nameVARCHAR2(30)NOTNULL, mgr_emp_idNUMBER(5), dept_cdVARCHAR2(6), salNUMBER(9,2), hire_dtDATENOTNULL); CREATEUNIQUEINDEXemp_pkONemp(emp_id); ALTERTABLEempADDCONSTRAINTemp_pk PRIMARYKEY( emp_id)USINGINDEXemp_pk; CREATEINDEX emp_f1 ONemp(mgr_emp_idASC); ALTERTABLEempADDCONSTRAINT emp_f1 FOREIGNKEY(mgr_emp_id) REFERENCESemp(emp_id);

  7. Sample Data SELECT * FROMemp ORDERBYemp_id;

  8. Sample Data Graph 10 Barbara 16 George 33 Mary 24 John 12 Edward 11 John 14 Beth 17 Barby 13 Josh 15 Rich 21 Michelle 28 Sam 22 Tom 32 Jane 31 Robin

  9. Employees & Supervisors SELECTe.emp_id,e.emp_name, m.emp_idASmgr_id,m.emp_nameASmgr_name FROMempeJOINempm ONe.mgr_emp_id=m.emp_id ORDERBYe.emp_id;

  10. … Add Top-Level Supervisors SELECTe.emp_id,e.emp_name, m.emp_idASmgr_id,m.emp_nameASmgr_name FROMempeLEFTJOINempm ONe.mgr_emp_id=m.emp_id ORDERBYe.emp_id;

  11. … Add Supervisor’s Supervisors SELECTe.emp_id,e.emp_name, m1.emp_id AS mgr_id1, m1.emp_name AS gr_name1, m2.emp_id AS mgr_id2, m2.emp_name AS mgr_name2 FROMempe LEFTJOINemp m1 ONe.mgr_emp_id= m1.emp_id LEFTJOINemp m2 ON m1.mgr_emp_id = m2.emp_id ORDERBYe.emp_id;

  12. All Root Nodes SELECTe.emp_id,e.emp_name FROMempe WHEREe.mgr_emp_idISNULL;

  13. All Leaf Nodes SELECTe.emp_id,e.emp_name FROMempe WHEREe.emp_idNOTIN (SELECTDISTINCTm.mgr_emp_id FROMempm WHEREm.mgr_emp_idISNOTNULL);

  14. Oracle Extensions for Hierarchies • CONNECT BY clause • Allows linking w/n same range variable (table alias) • Occurs after WHERE clause • Executes before WHERE clause • PRIOR keyword • Indicates how to connect w/ last record retrieved • Can occur on either side of the join • Can be used multiple times • START WITH clause • Indicates record(s) to initially retrieve • Optional: w/o, initially starts w/ all records

  15. Basic Hierarchy Query SELECTe.emp_id,e.emp_name,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  16. Adding the Manager Name SELECTe.emp_idAS"E ID",e.emp_nameAS"Employee", m.emp_idAS"M ID",m.emp_nameAS"Manager" FROM(SELECTe.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id) h JOINempeONh.emp_id=e.emp_id JOINempmONh.mgr_emp_id=m.emp_id ORDERBYe.emp_id;

  17. Start w/ any Record Set SELECTe.emp_id,e.emp_name,e.mgr_emp_id FROMempe STARTWITHe.emp_name='Beth‘ CONNECTBYe.mgr_emp_id=PRIORe.emp_id;

  18. Indicating LEVEL SELECTLEVEL,e.emp_id,e.emp_name,e.mgr_emp_id FROMempe STARTWITHe.emp_name='Beth‘ CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  19. Use Functions on LEVEL SELECTMAX(level)ASmaxlevel FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  20. Aggregate on LEVEL SELECTlevel,COUNT ( emp_id) FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id GROUPBYlevel;

  21. Indenting Dependents SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  22. Filter a Hierarchy SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe WHEREe.hire_dt<'1-Jan-2004' STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  23. Top N Levels of Hierarchy SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe WHERElevel<=2 STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  24. Sorting, the Wrong Way SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id ORDERBYemp_name;

  25. Sorting: The SIBLINGS Clause SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id ORDERSIBLINGSBYemp_name;

  26. Display a Path SELECTSUBSTR(SYS_CONNECT_BY_PATH( e.emp_name,' - '),4) AS"Employee Path" FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id ORDERSIBLINGSBYemp_name;

  27. The Root for Each Node SELECTe.emp_id,e.emp_nameAS"Employee", CONNECT_BY_ROOTe.emp_nameAS"Top Dog" FROMempe STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  28. Has a Connection to SELECT * FROMempe WHEREe.emp_name='Beth' STARTWITHe.emp_name='Robin‘ CONNECTBYe.emp_id=PRIORe.mgr_emp_id;

  29. Summng Dependent Info SELECT t2.emp_id, t2.emp_name, t2.sal, (SELECTSUM(t1.sal ) FROMempt1 STARTWITHt1.emp_id = t2.emp_id CONNECTBYt1.mgr_emp_id =PRIORt1.emp_id ) ASsum_salary FROMemp t2;

  30. Cycles in a Hierarchy UPDATEempe SETe.mgr_emp_id=22 WHEREe.emp_id=10; SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_id=10 CONNECTBYPRIORe.emp_id=e.mgr_emp_id; 17:08:52 Error: ORA-01436: CONNECT BY loop in user data

  31. Running w/ Errors SELECTLPAD(' ',3 * (level-1)) || e.emp_nameAS"Employee", level,e.emp_id,e.mgr_emp_id FROMempe STARTWITHe.mgr_emp_id=10 CONNECTBYNOCYCLEPRIORe.emp_id =e.mgr_emp_id;

  32. Trapping Cycle Errors SELECTe.emp_id,e.emp_name,CONNECT_BY_ISCYCLE FROMempe STARTWITHe.mgr_emp_id=10 CONNECTBYNOCYCLEPRIORe.emp_id=e.mgr_emp_id; ROLLBACK;

  33. Finding Leaf Nodes SELECTe.emp_id,e.emp_name FROMempe WHERECONNECT_BY_ISLEAF=1 STARTWITHe.mgr_emp_idISNULL CONNECTBYPRIORe.emp_id=e.mgr_emp_id;

  34. Source of Information Mastering Oracle SQL, 2nd Edition Sanjay Mishra & Alan Beaulieu O'Reilly Media, Inc., 2004 ISBN-13: 978-0-596-00632-7 496 pages

  35. Counter SELECTlevel x FROM dual CONNECTBYlevel<=100;

  36. Additional Tricks w/ CONNECT BY • Finding potential flights using an INTERSECT of 2 CONNECT BY queries. • @ http://gennick.com/flight.html SQL Pocket Guide, 2nd Edition By: Jonathan Gennick Publisher: O'Reilly Media, Inc. Pub. Date: April 17, 2006 Print ISBN-13: 978-0-596-52688-7 Pages in Print Edition: 192

More Related