Shulou(Shulou.com)06/01 Report--

Hierarchical query syntax in Oracle:

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 will be retrieved in a hierarchical order, and specifies that the data in the table will be connected to the tree structure. The prior operator must be placed in front of one of the two columns of the join relationship, and for parent-child relationships between child nodes, the prior operator is located on one side of the parent node and the other side of the child node, thus determining whether to find the tree structure in top-down or bottom-up order.

Start with: used to identify which node is used as the root node to find the tree structure. The hierarchical query needs to determine the starting point, through start with, followed by conditions. Start with will determine which line to use as root, and if start with is not specified, each row will be treated as root, and then look for its descendants. After start with, you can follow the subquery. If you where the condition, the nodes in the hierarchy that meet the relevant conditions will be truncated, but the whole hierarchical query will not be affected.

Level: is a pseudo column that represents the current level of this node. For the root node, level returns 1, through which the data is formatted and displayed in combination with other oracle functions.

Connect_by_root:connect_by_root must be used with a field in order to get the field information of the root node record.

Connect_by_isleaf: determines whether the current node is a leaf node. 0 indicates a non-leaf node and 1 indicates 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 between the sibling nodes under the parent node on return.

Example:

-- Tree 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 USER6-start with the root node emp_id=1 Query all the child nodes of the root node 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 from the top down EMP_ID EMP_NAME UP_EMP_ID LEVEL--1 USER1 0 12 USER2 1 24 USER4 2 35 USER5 2 33 USER3 1 26 USER6 3 3muri-from emp_id=6 Query all parent node select emp_id,emp_name,up_emp_id from empstart with emp_id=6connect by emp_id= prior up_emp_idorder by emp_id of emp_id=6 from the bottom up EMP_ID EMP_NAME UP_EMP_ID-1 USER1 03 USER3 16 USER6 3

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.