oracle--(hierarchical Queries) level query

Content from: Oracle®database SQL Language Reference 11g Release 2 (11.2) e41084-03.

Empolyees table from the HR scheme, warehouses from the OE scheme.

If your table contains hierarchical data, you can use hierarchical query clauses to select rows of data in a hierarchical order, forming a hierarchical tree in the following form:

Here is the train diagram for the hierarchy query sentence:

START with: Specifies the root row of the hierarchy. That is, select rows based on criteria, make the top-level rows with these rows, and get the child rows.

CONNECT by: Specifies the parent row and child row relationships in the hierarchy.

An example of a hierarchical query:

SELECT  Level  from  with =  manager_id ORDER   by last_name;

Query Result:

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
...

Oracle handles hierarchical queries as follows:

• If there is a connection, the connection is calculated first, regardless of whether the connection is in the FROM clause or in the WHERE clause
• Calculate the Connect by condition
• Calculate any remaining WHERE clause

Next, Oracle uses the information obtained from the above calculations to form a hierarchy:

1. Oracle chooses the root record that meets the start with condition
2. Oracle Selects child rows per root record, each row satisfies the connect by condition
3. Oracle selects the generational child row of the previous row. That is, Oracle selects the child rows of the previous row first, and then selects the child rows of those child rows, one at a time. Oracle always calculates the connect by condition of the current parent row to select a child row. What do you mean? A table containing hierarchical data containing two columns with hierarchical relationships, such as: employee_id and manager_id,employee_id are the identities of the current row, and manager_id is the identity of the parent row (that is, the employee_id of the parent row). How do I specify this relationship? Oracle uses the prior operator, and Prior is a unary operator for the field name immediately following it, such as prior employee_id = manager_id, which is used for the current parent row. The prior operator causes Oracle to take the value of the employee_id column of the parent row and then compares the value of the manager_id column of the child row to the child row of the current parent row.
4. If the query contains a non-connected WHERE clause, Oracle removes rows from the hierarchy that do not satisfy the WHERE clause. Oracle calculates the condition for each row, instead of removing all its child rows when a row does not meet the criteria.
5. Oracle returns rows in a sequential manner. , the order of return records is 1,2,3,4,5,6,7,8,9,10,11,12. It appears that Oracle does some processing of each acquired enforcing, rather than simply appending.

Here are some examples of hierarchical queries:

1. CONNECT by

This example uses the Connect by clause to define the relationship between an employee and a manager

SELECT employee_id, last_name, manager_id  from  by= manager_id;

Query results (partial):

2. Level

This example uses the level of inferior display rows in tree relationships in query results.

SELECT  Level  from  by= manager_id;

Query results (partial):

3. START with

This example adds the start with clause to specify the starting root row, using the Siblings keyword to sort the sibling nodes (the child rows of the same parent row)

SELECT  Level  from  with =  manager_id ORDER   by last_name;

Query results (partial):

4. LOOP

In the previous example, King had no superior leadership, and in his staff there was a department called John Russell (code 80) leader, who now updates King's leadership for John Russell

UPDATE SET = 145 WHERE = ;

Execute statement:

SELECTlast_name "Employee", Level, Sys_connect_by_path (last_name,'/') "Path" fromEmployeesWHERE  Level <= 3  anddepartment_id=  theSTART withLast_Name= 'King'CONNECT byPRIOR employee_id=manager_id and  Level <= 4

Report Error:

ORA-01436: CONNECT by loop in user data

The reason for the mistake was that King, John Russell's leader and his staff, produced a loop in which Oracle reported errors. However, if the nocycle parameter is used in the Connect by clause, Oracle returns the result. Use the connect_by_iscycle pseudo-column to show which rows contain loops.

Execute statement:

SELECTlast_name "Employee", connect_by_iscycle "Cycle", Level, Sys_connect_by_path (last_name,'/') "Path" fromEmployeesWHEREdepartment_id=  theSTART withLast_Name= 'King'CONNECT byNocycle PRIOR employee_id=manager_id and  Level <= 3ORDER  by"Employee", "Cycle", Level, "Path";

Query Result:

As can be seen, Oracle removes the infinite loop.

5, Connect_by_isleaf

This is a pseudo-column that shows whether rows are leaf nodes in the hierarchy tree, 1 for leaf nodes, and not 1 for non-leaf nodes.

Execute statement:

SELECTlast_name "Employee", Level, Sys_connect_by_path (last_name,'/') "Path" fromEmployeesWHEREdepartment_id= the   andConnect_by_isleaf=1START withLast_Name= 'King'CONNECT byPRIOR employee_id=manager_id and  Level <= 3;

Query Result:

These 3-level nodes are all leaf nodes, and King appears in the query results because we specified the starting root row in start with.

An interesting example in the Oracle documentation shows how to use a hierarchical query to convert a column of data in a table to a comma-delimited list. Execute statement:

SELECT LTRIM(Sys_connect_by_path (warehouse_id,','),',') from(SELECTROWNUM R, warehouse_id fromwarehouses)WHEREConnect_by_isleaf= 1START withR= 1CONNECT byR=PRIOR R+ 1ORDER  bywarehouse_id;

Query Result:

CONNECT by R = PRIOR r + 1 clause Specifies the parent-child relationship of the row, as in the current parent row, the PRIOR operator takes the R value and then adds 1, and the query satisfies the value of R equal to the R+1 row, starting with the first row, The two adjacent lines form a parent-child relationship, the previous row is the parents row, and the latter behaves as a sub-line due to the particularity of R (one-way sequential table 1,2,3,4 ... ), the tree actually formed is a list. Using Sys_connect_by_path to display the path of each node, use Connect_by_isleaf = 1 to select only leaf nodes.

In this example, the warehouse_id of each line is exactly equal to rownum, and can be replaced by other columns to form a different list.

6, Connect_by_root

This example shows the last name of each employee in the Department 110, the number of senior managers for each employee, the level of employees and managers, and the path between the employee and the manager.

Execute statement:

SELECTlast_name "Employee", Connect_by_root last_name "Manager", Level-1"Pathlen", Sys_connect_by_path (last_name,'/') "Path" fromEmployeesWHERE  Level > 1  anddepartment_id=  theCONNECT byPRIOR employee_id=manager_idORDER  by"Employee", "Manager", "Pathlen", "Path";

Query Result:

oracle--(hierarchical Queries) level query