Sample Code for tree-like Query of Oracle Database

Oracle DatabaseOfTree QueryIs what we will introduce in this article, including the basic syntax of tree query, the construction environment,Root NodeTraversalSubnodeAnd trace the child nodes to the root node. Let's take a look at this part.

Basic Syntax:

 
 

  
  
select...from tabename start with cond1 connect by  prior cond2 where cond2 
 
 

Note:

Cond1 is a limit statement for the root node.

Cond2 is the connection condition, where prior indicates the previous record, indicating that the father of the record is the previous record.

Cond3 is a filter condition.

Build Environment:

 
 

  
  
create table Family(  
  
  
id integer,  
  
  
parentid integer,  
  
  
name varchar2(50)  
  
  
)  
  
  
insert into family values(0,0,'a')  
  
  
insert into family values(1,0,'b')  
  
  
insert into family values(2,1,'c')  
  
  
insert into family values(3,1,'d')  
  
  
insert into family values(4,1,'e')  
  
  
insert into family values(5,1,'f') 
 
 

Traverse sub-nodes through the root node

For example, query the information of all the children whose father is equal to 1.

 
 

  
  
select * from family start with parentid=1 connect by prior id=parentid 
 
 

Tracing to the root node through the sub-node

For example:

 
 

  
  
select * from family start with id=5 connect by prior parentid=id 
 
 

Note: If the ORA-01436 is reported: The coonect by loop in the user database, the parentid In the first data is changed to null, otherwise the loop find parentid will not find it!

Extended: query the level by level keyword

Select t. *, level from family t start with parentid = 1 connect by prior id = parentid

Note:The table must use an alias.

Here is an introduction to the tree-like Query of Oracle databases. If you want to learn more about Oracle databases, please read the article http://database.51cto.com/oracle/, which will surely help you reap the benefits!