Oracle Hierarchy Query statement

Oracle Hierarchical query Syntax:

Select ...

[Level | Connect_by_root |connect_by_isleaf |connect_by_iscycle]

From table_name

[WHERE]

Connect by {prior Col1=col2 | Col1=prior col2}

[Start with]

[Order [siblings] by];

Connect by: Indicates that each row of data is retrieved in a hierarchical order, and that the data in the table is linked into a tree-structured relationship. The prior operator must precede one of the two columns in the join relationship, and for a parent-child relationship between subnodes, the prior operator is the parent node, and the other side is a child node, which determines whether the order of the Find tree structure is top-down or bottom-up.

Start with: Used to identify which node is the root node of the find tree structure, the hierarchical query needs to determine the starting point, through start with, and after the condition. Start with determines which row to use as root, and if start with is not specified, each row is treated as root, and then its descendants are found. The start with can be followed by a subquery, and if the Where condition, it truncates the nodes in the hierarchy that meet the relevant conditions, but does not affect the entire hierarchical query.

Level: is a pseudo-column that represents the hierarchy of the current node and, for the root node, levels returns 1, with the pseudo-column combined with other Oracle functions to display the data in a formatted format.

Connect_by_root:connect_by_root must be used in conjunction with a field to obtain the field information for the root node record.

Connect_by_isleaf: Determine whether the current node is a leaf nodes, 0 means non-leaf nodes, 1 is a leaf node.

Connect_by_cycle: You can check whether a loop is formed during a tree query, and this pseudo-column is only valid in Connect_by_cycle mode.

Order siblings by: Defines the order in which each sibling node under the parent node is returned.

Example:

--Tree-shaped structure table emp;select * from emp; emp_id              emp_name               UP_EMP_ID    ----- ----            ---------------          ---------------  1               USER1               0         2               USER2               13               user3              1 4               user4              25               USER5               26               USER6               3--starts from the root node emp_id=1 and queries out all child nodes of the root node from top to bottom Select  emp_id,emp_name,up_emp_id,levelfrom empstart  with emp_id=1connect by prior emp_id=up_emp_idorder siblings by emp_ Id Emp_id                emp_name            UP_EMP_ID          &nbSp;              level---------               ---------------       ---------------                     --------1                USER1             0                        12                user2            1                         24               user4             2                        35                USER5             2                        33                USER3             1                        26                user6            3                         3--from emp_id=6, bottom-up queries all parent nodes of Emp_id=6 select emp_id,emp_name,up_emp_id from   Empstart with emp_id=6connect by emp_id = prior up_emp_idorder by   emp_id;EMP_ID               emp_name             up_emp_id     ---------            ---------------         ---------------  1               USER1              03              user3              16               USER6              3

This article is from the "Dark Shun" blog, please make sure to keep this source http://mjal01.blog.51cto.com/12140495/1975584

Oracle Hierarchy Query statement