The class query operator PRIOR and CONNECT_BY_ROOT are added to ORACLE 10 Gb.
PRIOR
In the conditional expression of Cy BY condition of a hierarchical query, PRIOR is used to specify the parent node. As an operator, PRIOR and the plus (+) minus (-) operations have the same priority.
Hierarchical Query
Syntax: start with condition connect by nocycle condition
◆ Start with specifies the root of the class
◆ Connect by parent/child relationship of the specified class
◆ When NOCYCLE has a connect by loop record, the query result is also returned.
◆ Condition... PRIOR expr = expr or... expr = PRIOR expr
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 ... |
CONNECT_BY_ROOT
Queries the class data of the specified root.
Connect by clause example
Use the connect by clause to define the relationship between employees and superiors.
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 |
LEVEL example
The LEVEL virtual column is used to represent the node relationship.
SQL>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
|
Example of the start with clause
Use start with to specify the root node and order siblings by to maintain the class ORDER.
SQL>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 |