Oracle start ...... Connect by prior ...... Clause for recursive query

The start with... connect by clause recursive query is generally used for a table to maintain a tree structure. The connect by clause is used in structured queries. Its basic syntax is as follows:

Select... from <tablename>
Where <conditional-1>
Start with <conditional-2>
Connect by <conditional-3>;

<Conditional-1>: Filter condition used to filter all returned records.
<Conditional-2>: Conditions for restarting the root node in the query result.
<Conditional-3>: Connection Condition

The data organization structure is as follows:


 

 

 

The database table structure is as follows:

Create Table T2 (
Root_id number,
ID number,
Name varchar (5 ),
Description varchar (10)
);

Insert into T2 (root_id, ID, name, description) values (0, 1, 'A', 'aaa ');
Insert into T2 (root_id, ID, name, description) values (1, 2, 'a1', 'aaa1 ');
Insert into t2 (root_id, id, name, description) values (1, 3, 'a2 ', 'aaa2 ');
Insert into t2 (root_id, id, name, description) values (0, 4, 'B', 'bbb ');
Insert into t2 (root_id, id, name, description) values (, 'b1 ', 'bbb1 ');
Insert into t2 (root_id, id, name, description) values (, 'b2', 'bbb ');

Obtain the complete tree:

Select * from t2 start with root_id = 0 connect by prior id = root_id;

 

 

 

Get the child tree:

Select * from t2 start with id = 1 connect by prior id = root_id;

 

 

 

Select * from t2 start with id = 4 connect by prior id = root_id;

 

 

 

If prior in connect by prior is omitted, deep recursion is not performed for the query.
For example:

Select * from t2 start with root_id = 0 connect by id = root_id;

 

 

 

Select * from t2 start with id = 1 connect by id = root_id;
For example:

 

Create table TBL_TEST (id number, NAME VARCHAR2 (100 BYTE), pid number default 0); INSERT test data: insert into TBL_TEST (ID, NAME, PID) VALUES ('1 ', '10', '0'); insert into TBL_TEST (ID, NAME, PID) VALUES ('2', '11', '1'); insert into TBL_TEST (ID, NAME, PID) VALUES ('3', '20', '0'); insert into TBL_TEST (ID, NAME, PID) VALUES ('4', '12 ', '1'); insert into TBL_TEST (ID, NAME, PID) VALUES ('5', '123', '2 '); recursive select * from Root to tree end from TBL_TEST start with id = 1 connect by prior id = pid recursively select * from TBL_TEST start with id = 5 connect by prior pid = id