SQL statements in the Oracle Tree Structure

Oracle provides a tree structure for hierarchical queries:

Start with: specifies that the record is a condition for the root node. You can have multiple root nodes.

Connect by: Specifies the condition for the relationship between the parent node and the child node of the record. Find the condition of the parent node containing the child node

Prior: Specifies the reference of the parent node record.

Siblings: Specify the sort of the tree. The sequence of sibling nodes under the same parent node.

The storage structure of the tree in the database table is as follows:

 
Create Table tree (ID number not null, // primary key PID Number not null, // parent node ID order_id number not null, // sorting ID node_name varchar2 (100) not null, // node name del varchar2 (5), // Delete Id 1: Delete constraint pk_tree primary key (ID ));
 

The instance SQL statement is as follows:

Select * from tree where del <> 1 start with PID = 0 and del <> 1 connect by prior id = PID and del <> 1 order siblings by order_id
 

The query result is a set of records sorted by tree.

If logical deletion (false deletion) is used, del <> 1 in the preceding SQL statement has different meanings. The first step is to filter all records in the table that are not marked to be deleted (shorten the calculation range of the Spanning Tree ), the second is to filter the records of the root node that are not marked to be deleted, and the third is to filter records of the parent node that contains the child node that is not marked to be deleted.