Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the connectbyprior recursive function in oracle. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Connectbyprior recursive algorithm in oracle. The use of the start with...connect by prior clause in Oracle connect by is used in structured queries, and its basic syntax is:

Select... From tablename start with condition 1

Connect by condition 2

Where condition 3

Example:

Select * from table

Start with org_id = 'HBHqfWGWPy'

Connect by prior org_id = parent_id

Simply put, store a tree structure in a table, for example, there are two fields in a table:

Org_id,parent_id can then form a tree structure by indicating who the parent of each record is.

The query using the above syntax can get all the records of the tree.

Where:

Condition 1 is the qualifying statement of the root node, of course, the qualification can be relaxed to obtain multiple root nodes, which is actually multiple trees.

Condition 2 is the connection condition, where the previous record is represented by PRIOR. For example, CONNECT BY PRIOR org_id = parent_id means that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.

Condition 3 is a filter condition, which is used to filter all records returned.

A brief introduction is as follows:

When scanning the tree structure table early, you need to access each node of the tree structure according to this. A node can only be accessed once. The steps to access are as follows:

Step 1: start from the root node

Step 2: access the node

Step 3: determine whether the node has any unaccessed child nodes, if so, turn to its leftmost unaccessed child section, and perform the second step, otherwise perform the fourth step

Step 4: if the node is the root node, the access is complete, otherwise perform step 5

Step 5: return to the parent of the node and perform the third step.

In short: the process of scanning the entire tree structure is the process of traversing the tree in the middle order.

1. Description of tree structure

The data of the tree structure is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, which is described by the relationship between the columns in the table, such as EMPNO and MGR in the EMP table. EMPNO represents the number of the employee, and MGR represents the number of the person leading the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. There is a MGR in each row of the table that represents the parent node (except the root node), and the entire tree structure can be determined by the parent node of each node.

Use the CONNECT BY nice START WITH clause in the SELECT command to query the tree structure relationships in the table. The format of the command is as follows:

SELECT .

CONNECT BY {PRIOR column name 1 = column name 2 | column name 1=PRIOR split name 2}

[START WITH]

Where the CONNECT BY clause states that each row of data will be retrieved in a hierarchical order and specifies that the data in the table will be connected to the tree structure. The PRIORY operator must be placed before either of the two columns of the join relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other, thus determining whether the search tree structure is in the order of top-down or bottom-up. In a join relationship, column expressions are allowed in addition to column names. The START WITH clause is optional and is used to identify which node is the root node of the lookup tree. If the clause is omitted, it means that all rows that meet the query criteria are the root node.

START WITH: you can specify not only one root node, but also multiple root nodes.

two。 About PRIOR

The operator PRIOR is placed before and after the equal sign, determining the order of retrieval when querying.

When PRIOR is placed in front of the equal sign in the CONNECT BY clause, it forces the sequential retrieval from the root node to the leaf node, that is, from the parent node to the child node through the tree structure, which we call the top-down way. Such as:

CONNECT BY PRIOR EMPNO=MGR

When the PIROR operator is placed after the equal sign in the CONNECT BY clause, it forces the sequential retrieval from the leaf node to the root node, that is, from the child node to the parent node through the tree structure, which we call the bottom-up approach. For example:

CONNECT BY EMPNO=PRIOR MGR

In this way, you should also specify a starting node.

3. Define the lookup start node

When querying the tree structure from top to bottom, you can start looking down not only from the root node, but also from any node as the starting node. The result of this search is a branch of the structure tree that starts with that node.

4. Use LEVEL

In a table with a tree structure, each row of data is a node in the tree structure, and each row of records can have a layer number because the nodes are in a different hierarchical location. The layer number is determined according to the distance between the node and the root node. No matter which node you start from, the layer number of the starting root node is always 1, the child node of the root node is 2, and so on.

5. Clipping of nodes and branches

When querying the tree structure, you can remove some rows in the table or a branch in the tree, and use the WHERE clause to define a single node in the tree structure to remove a single node in the tree, but it does not affect its descendant nodes (when retrieving from top to bottom) or their predecessors (when retrieving from bottom to top).

6. Sorted display

As in other queries, you can use the ORDER BY clause in a tree structure query to change the order in which the query results are displayed without having to traverse the tree structure

On how to use connectbyprior recursive function in oracle to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.