315/479

Hierarchical Queries

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:

hierarchical_query_clause::=

[画像:Description of hierarchical_query_clause.gif follows]
Description of the illustration hierarchical_query_clause.gif

START WITH specifies the root row(s) of the hierarchy.

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.
In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
```
... PRIOR expr = expr
or
... expr = PRIOR expr
```
If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
```
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and 
 PRIOR account_mgr_id = customer_id ...
```
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

Both the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, the PRIOR expression cannot refer to a sequence. That is, CURRVAL and NEXTVAL are not valid PRIOR expressions.

You can further refine a hierarchical query by using the CONNECT_BY_ROOT operator to qualify a column in the select list. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.

Hierarchical Query Examples

CONNECT BY Example The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
 FROM employees
 CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
 101 Kochhar 100
 108 Greenberg 101
 109 Faviet 108
 110 Chen 108
 111 Sciarra 108
 112 Urman 108
 113 Popp 108
 200 Whalen 101
...

LEVEL Example The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:

SELECT employee_id, last_name, manager_id, LEVEL
 FROM employees
 CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
 101 Kochhar 100 1
 108 Greenberg 101 2
 109 Faviet 108 3
 110 Chen 108 3
 111 Sciarra 108 3
 112 Urman 108 3
 113 Popp 108 3
...

START WITH Examples The next example adds a START WITH clause to specify a root row for the hierarchy and an ORDER BY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:

SELECT last_name, employee_id, manager_id, LEVEL
 FROM employees
 START WITH employee_id = 100
 CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY last_name;
LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
------------------------- ----------- ---------- ----------
King 100 1
Cambrault 148 100 2
Bates 172 148 3
Bloom 169 148 3
Fox 170 148 3
Kumar 173 148 3
Ozer 168 148 3
Smith 171 148 3
De Haan 102 100 2
Hunold 103 102 3
Austin 105 103 4
Ernst 104 103 4
Lorentz 107 103 4
Pataballa 106 103 4
Errazuriz 147 100 2
Ande 166 147 3
Banda 167 147 3
...

In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees table to set Russell as King's manager, we will create a loop in the data:

UPDATE employees SET manager_id = 145
 WHERE employee_id = 100;
SELECT last_name "Employee", 
 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE level <= 3 AND department_id = 80
 START WITH last_name = 'King'
 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
 2 3 4 5 6 7 ERROR:
ORA-01436: CONNECT BY loop in user data

The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE level <= 3 AND department_id = 80
 START WITH last_name = 'King'
 CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
Employee Cycle LEVEL Path
------------------------- ------ ------ -------------------------
Russell 1 2 /King/Russell
Tucker 0 3 /King/Russell/Tucker
Bernstein 0 3 /King/Russell/Bernstein
Hall 0 3 /King/Russell/Hall
Olsen 0 3 /King/Russell/Olsen
Cambrault 0 3 /King/Russell/Cambrault
Tuvault 0 3 /King/Russell/Tuvault
Partners 0 2 /King/Partners
King 0 3 /King/Partners/King
Sully 0 3 /King/Partners/Sully
McEwen 0 3 /King/Partners/McEwen
...

CONNECT_BY_ROOT Examples The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
 LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE LEVEL > 1 and department_id = 110
 CONNECT BY PRIOR employee_id = manager_id;
Employee Manager Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz King 3 /King/Kochhar/Higgins/Gietz

The following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:

SELECT name, SUM(salary) "Total_Salary" FROM (
 SELECT CONNECT_BY_ROOT last_name as name, Salary
 FROM employees
 WHERE department_id = 110
 CONNECT BY PRIOR employee_id = manager_id)
 GROUP BY name;
NAME Total_Salary
------------------------- ------------
Gietz 8300
Higgins 20300
King 20300
Kochhar 20300