Copyright 2014 Oracle andor its affiliates All rights reserved Oracle Database

Copyright 2014 oracle andor its affiliates all rights

This preview shows page 144 - 148 out of 224 pages.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle Database 12 c : : SQL SQL Worksh Workshop op II II F F - 2
Image of page 144
Sample Data from the Sample Data from the EMPLOYEES Table Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between the rows in a table. A relational database does not store records in a hierarchical way. However, where a hierarchical relationship exists between the rows of a single table, a Copyright © 2014, Oracle and/or its affiliates. All rights reserved. process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, with the branches of a tree in a specific order. Imagine a family tree with the eldest members of the family found close to the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on. A hierarchical query is possible when a relationship exists between rows in a table. For , , , , _ 100, which is King’s EMPLOYEE_ID . Note: Hierarchical trees are used in various fields such as human genealogy (family trees), livestock (breeding purposes), corporate management (management hierarchies), manufacturing (product assembly), evolutionary research (species development), and scientific research. Oracle Database 12 c : : SQL SQL Worksh Workshop op II II F F - 3
Image of page 145
Natural Tree Structure King EMPLOYEE_ID = 100 (Parent) MANAGER ID = 100 Child De Haan _ Kochhar Mourgos Zlotkey Zlotkey Hartstein Gietz Ernst Lorentz Fay Abel Taylor r Grant The EMPLOYEES table has a tree structure representing the management reporting line. The hierarchy can be created by looking at the relationship between equivalent values in the EMPLOYEE_ID and MANAGER_ID columns. This relationship can be exploited by joining the Copyright © 2014, Oracle and/or its affiliates. All rights reserved. table to itself. The MANAGER_ID column contains the employee number of the employee’s manager. The parent/child relationship of a tree structure enables you to control: The direction in which the hierarchy is walked The starting point inside the hierarchy Note: The slide dis la s an inverted tree structure of the mana ement hierarch of the employees in the EMPLOYEES table. Oracle Database 12 c : : SQL SQL Worksh Workshop op II II F F - 4
Image of page 146
Hierarchical Queries SELECT [LEVEL], column , expr ... FROM table condition : ex r com arison o erator ex r WHERE con t on s [START WITH condition(s) ] [CONNECT BY PRIOR condition(s) ] ; _ Keywords and Clauses Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH Copyright © 2014, Oracle and/or its affiliates. All rights reserved. . In the syntax: SELECT Is the standard SELECT clause LEVEL For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on.
Image of page 147
Image of page 148

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture