The SELECT statement in Oracle can implement a recursive query with the start With...connect by prior clause, which is used in structured queries, and its basic syntax is:
Select ... from <TableName>
where <Conditional-1>
Start with <Conditional-2>
Connect by <Conditional-3>;
<CONDITIONAL-1>: Filter conditions that are used to filter all returned records.
<CONDITIONAL-2>: The criteria for the initial root node of the query result.
<conditional-3>: Join condition
The data organization structure is shown below:
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 (4,5, ' B1 ', ' bbb1 ');
Insert into T2 (root_id,id,name,description) VALUES (4,6, ' B2 ', ' bbb2 ');
Get the full tree:
SELECT * from t2 start and root_id = 0 Connect by Prior ID = root_id;
To get a specific subtree:
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 the prior in the connect by prior is omitted, the query does not perform deep recursion.
Such as:
SELECT * from t2 start and root_id = 0 Connect by id = root_id;
SELECT * FROM t2 start with id = 1 Connect by id = root_id;
Such as: