Pl/sql Foundation: Stratum Inquiry

ORACLE 10g New Hierarchy query operator Prior,connect_by_root

PRIOR

The connecy by condition of a hierarchical query needs to use prior to specify the parent node, and as an operator, the prior and add (+) minus (-) operations have the same precedence.

Class Query

Syntax: START with condition CONNECT by nocycle condition

START with the root of the specified hierarchy

CONNECT by the parent/child relationship of the specified hierarchy

Nocycle also returns the results of the query when a record of Connect by loop exists.

Condition ... PRIOR expr = expr or ... expr = PRIOR expr

Cases:

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

Connect_by_root

Queries the hierarchical data for the specified root.

Example of CONNECT by clause

Define the relationship between the employee and the boss by using the Connect by clause.

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