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

This article introduces how to achieve the recursive tree structure query function in Oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Oracle tree structure query, that is, hierarchical recursive query, is often used in sql statements, and the implementation of organizational structure and its hierarchical functions are also often encountered in actual development.

Summary: the tree structure is usually composed of root node, parent node, child node and leaf node. To put it simply, there are two fields in a table, dept_id,par_dept_id, so you can form a tree structure by finding the parent id of each record, that is, par_dept_id (child) = dept_id (parent). Generally speaking, the par_dept_id of this record is another record, that is, the parent dept_id. The basic syntax of its tree structure hierarchical query is:

SELECT [LEVEL], * FEOM table_name START WITH condition 1 CONNECT BY PRIOR condition 2 WHERE condition 3 ORDER BY sort field

Description: LEVEL--- pseudo-column, used to represent the hierarchy of the tree

Condition 1: the qualification of the root node, of course, the permission can be relaxed to obtain multiple root nodes, that is, to get multiple trees.

The purpose of condition 2 is to give what the relationship between father and son is, and make a recursive query according to this relationship.

Condition 3-filter condition, which filters all returned records.

Sort field-sort all returned records

Description to prior: there are two ways to write it: connect by prior dept_id=par_dept_id or connect by dept_id=prior par_dept_id, the former means top-down search (first find the parent node and then the child node), and the latter means the bottom-up search method (find the leaf node and then the parent node).

The hierarchical query of the tree structure requires access to each node of the tree structure and cannot be repeated. The access steps are:

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.