HIERARCHICAL QUERIES AND SYS_CONNECT_BY_PATH

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

SYS_CONNECT_BY_PATH

Syntax

Description of the illustration sys_connect_by_path.gif

Purpose

SYS_CONNECT_BY_PATH

 is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by 

char

 for each row returned by 

CONNECT

BY

 condition.

Both 

column

 and 

char

 can be any of the datatypes 

CHAR

, 

VARCHAR2

, 

NCHAR

, or 

NVARCHAR2

. The string returned is of 

VARCHAR2

 datatype and is in the same character set as 

column

.

See Also:

"Hierarchical Queries" for more information about hierarchical queries and 

CONNECT

BY

 conditions

Examples

The following example returns the path of employee names from employee 

Kochhar

 to all employees of 

Kochhar

 (and their employees):

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   START WITH last_name = 'Kochhar'
   CONNECT BY PRIOR employee_id = manager_id;

Path
---------------------------------------------------------------
 /Kochhar
   /Kochhar/Greenberg
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Chen
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Greenberg/Popp
   /Kochhar/Whalen
   /Kochhar/Mavris
   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz
      

实验：

SELECT T.EMPNO, ENAME, SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, ','), 2), T.MGR

  FROM EMP T

 START WITH T.MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

SELECT LPAD(' ', LEVEL * 2 - 1) || ENAME AS TREE, F.EMPNO,ENAME,JOB,MGR, LEVEL

  FROM EMP F

 START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;