PL/SQL basics: hierarchical query (1)

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.