Oracle tree SQL statement, sys_connect_by_path Function

Sys_connect_by_path Function

Since Oracle 9i, you can use the sys_connect_by_path function to display the content from the parent node to the current row in the form of a "path" or a list of hierarchical elements. For example:
Column path format A50
Select level, sys_connect_by_path (child, "/") path
From hier
Start with parent is null
Connect by prior child = parent;

Level path
----------------------------------------------------
1/Asia
2/Asia/China
3/Asia/China/Beijing
2/Asia/Japan
3/Asia/Japan/Osaka
3/Asia/Japan/Tokyo
1/Australia
2/Australia/New South Wales
3/Australia/New South Wales/sysydney
1/Europe
2/Europe/United Kingdom
3/Europe/United Kingdom/England
4/Europe/United Kingdom/England/London
1/North America
2/North America/Canada
3/North America/Canada/Ontario
4/North America/Canada/Ontario/Ottawa
4/North America/Canada/Ontario/Toronto
2/North America/USA
3/North America/USA/California
4/North America/USA/California/Redwood Shores

Connect_by_isleaf pseudo Column

In Oracle 10g, there are other new features about hierarchical queries. For example, sometimes users are more concerned with the content with the lowest level in each layer branch. Then you can use the pseudo-column function connect_by_isleaf to determine whether the current row is a leaf. If it is a leaf, "1" is displayed in the pseudo column, and "0" is displayed if it is not a leaf but a branch (for example, the current content is the father of another row ". The following example shows how to use this function:

Select connect_by_isleaf, sys_connect_by_path (child, "/") path
From hier
Start with parent is null
Connect by prior child = parent;

Connect_by_isleaf path
----------------------------------------------
0/Asia
0/Asia/China
1/Asia/China/Beijing
0/Asia/Japan
1/Asia/Japan/Osaka
1/Asia/Japan/Tokyo
0/Australia
0/Australia/New South Wales
1/Australia/New South Wales/sysydney
0/Europe
0/Europe/United Kingdom
0/Europe/United Kingdom/England
1/Europe/United Kingdom/England/London
0/North America
0/North America/Canada
0/North America/Canada/Ontario
1/North America/Canada/Ontario/Ottawa
1/North America/Canada/Ontario/Toronto
0/North America/USA
0/North America/USA/California
1/North America/USA/California/Redwood Shores

Connect_by_root pseudo Column

In Oracle 10g, there is a new operation-connect_by_root. It is used before the column name to return the root node of the current layer. The following example shows the content of the highest-level node corresponding to the current row data in the hierarchy table.

Select connect_by_root, sys_connect_by_path (child, "/") path
From hier
Start with parent is null
Connect by prior child = parent;

Connect_by_root path
--------------------------------------
Asia/Asia
Asia/China
Asia/China/Beijing
Asia/Japan
Asia/Japan/Osaka
Asia/Japan/Tokyo
Australia/Australia
Australia/New South Wales
Australia/New South Wales/sysydney
Europe/Europe
Europe/United Kingdom
Europe/United Kingdom/England
Europe/United Kingdom/England/London
North America/North America
North America/Canada
North America/Canada/Ontario
North America/Canada/Ontario/Ottawa
North America/Canada/Ontario/Toronto
North America/USA
North America/USA/California
North America/USA/California/Redwood Shores

Connect_by_iscycle pseudo Column

In Oracle versions earlier than 10 Gb, if a ring loop occurs in your tree (for example, a child node references a Father's Day node), Oracle Reports an error message: ORA-01436: connect by loop in user data ". If the reference to the father is not deleted, the query operation cannot be performed. In Oracle 10 Gb, you only need to specify "nocycle" to perform any query operation. There is also a pseudo column related to this keyword-connect_by_iscycle. If the content of a Father's Day point is referenced in the current row and a loop occurs in the tree, "1" is displayed in the pseudo column of the row, otherwise "0" is displayed ". For example:

Create Table hier2
(
Parent number,
Child number
);

Insert into hier2 values (null, 1 );
Insert into hier2 values (1, 2 );
Insert into hier2 values (2, 3 );
Insert into hier2 values (3, 1 );

Select connect_by_iscycle, sys_connect_by_path (child, "/") path
From hier2
Start with parent is null
Connect by nocycle prior child = parent;

Connect_by_iscycle path
-------------------------
0/1
0/1/2
1/1/2/3