Oracle start with... connect by prior implements recursive query and oracle recursive query

Oracle start with... connect by prior implements recursive query and oracle recursive query

Basic Syntax:

<Span style = "font-size: 18px;"> <em> select... from <TableName> where <Conditional-1> start with <Conditional-2> connect by <Conditional-3>; <Conditional-1>: Filter condition, filters all returned records. <Conditional-2>: Conditions for restarting the root node in the query result. <Conditional-3>: Connection condition </em> </span>

The data table structure is as follows:

create table table_test(priorId number,id number,name varchar(5),description varchar(10));

Test data:

Insert into table_test (priorId, id, name, description) values (0, 1, 'F', 'f parent class'); insert into table_test (priorId, id, name, description) values (, 'f1 ', 'f subclass 1'); insert into table_test (priorId, id, name, description) values (, 'f2 ', 'F subclass 2'); insert into table_test (priorId, id, name, description) values (, 'F', 'f parent class'); insert into table_test (priorId, id, name, description) values (, 'f1 ', 'f subclass 1'); insert into table_test (priorId, id, name, description) values, 'F2', 'f subclass 2 ');

 

Obtain the complete tree:

select * from table_test start with priorId = 0 connect by prior id = priorId;

Get the child tree:

select * from table_test start with id = 1 connect by prior id = priorId;select * from table_test start with id = 4 connect by prior id = priorId;

If prior in connect by prior is omitted, the query will not undergo deep recursion:

select * from table_test start with priorId = 0 connect by id = priorId;select * from table_test start with id = 1 connect by id = priorId;