PL/SQL basics: hierarchical query (1)

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