Implementation of oracle tree Query

Oracle tree query, also known as recursive query, is one of the most common query methods. The following describes the oracle tree query in detail, hoping to give you a deeper understanding of oracle tree query.

The select statement in oracle can use the start with... connect by prior clause to implement recursive queries. The connect by clause is used in structured queries. Its basic syntax is as follows:

Select * from tablename start with cond1
Connect by cond2
Where cond3;

Simply put, a tree structure is stored in a table. For example, a table has two fields:
The id and parentid indicate the parent of each record to form a tree structure.

You can use the preceding syntax to query all records of the tree.

COND1 is a limit statement for the root node. Of course, you can relax the limit conditions to obtain multiple root nodes. Actually, it is multiple trees.

COND2 is the connection condition, where PRIOR represents the previous record. For example, connect by prior id = PRAENTID indicates that the ID of the previous record is the PRAENTID of this record, that is, the father of this record is the previous record.

COND3 is a filter condition used to filter all returned records.

Perform a simple oracle tree query (recursive query) for oracle)
Deptid number department id
Paredeptid number parent department id (Department id)
Name char (40 Byte) department NAME

Tracing data to the root node through a subnode.

Ql code select * from persons. dept start with deptid = 76 connect by prior paredeptid = deptid <SPAN style = "FONT-SIZE: small"> select * from persons. dept start with deptid = 76 connect by prior paredeptid = deptid </SPAN>
Select * from persons. dept start with deptid = 76 connect by prior paredeptid = deptid

Traverse sub-nodes through the root node.

Ql code select * from persons. dept start with paredeptid = 0 connect by prior deptid = paredeptid
<SPAN style = "FONT-SIZE: small"> select * from persons. dept start with paredeptid = 0 connect by prior deptid = paredeptid </SPAN>

Select * from persons. dept start with paredeptid = 0 connect by prior deptid = paredeptid

You can use the level keyword to query the level.

Ql code select a. *, level from persons. dept a start with paredeptid = 0 connect by prior deptid = paredeptid
<SPAN style = "FONT-SIZE: small"> select a. *, level from persons. dept a start with paredeptid = 0 connect by prior deptid = paredeptid </SPAN>
 

How to delete repeated Oracle records

Learn more about Oracle hierarchical Query

Provides you with an in-depth understanding of Oracle temporary tables

Oracle statements used to query all tables of a user

Oracle with statement usage