Teacher Fu class: Oracle advanced query Connect by__oracle

to facilitate learning and testing, all examples are created under Oracle's own user Scott.

The SELECT statement in Oracle can use the start with ... connect by prior ... clause to implement recursive query, connect by is used in structured queries, and its basic syntax is: Select ... from
where < filter conditions, for filtering all returned records >
start with < root node qualification statements , of course, you can relax the qualifying conditions to obtain multiple root nodes, in fact, multiple trees >
Connect by [prior] < join conditions, where prior represents the previous record, for example: Connect by prior t.id = T.parent_ ID means that the ID of the previous record is the parent_id of this record, that is, the father of this record is the previous record > 
below we look directly at the example and query all subordinates of ' KING '. The SQL statement is as follows: [SQL] view plain copy select *     from scott.emp e     start with e.ename =  ' KING '    connect by prior  e.empno = e.mgr;   let's look at another example and turn to all the bosses of ' SMITH '. The SQL statement is as follows: [SQL] view plain copy select *     from scott.emp e     start with e.ename =  ' SMITH '    connect by e.empno  = prior e.mgr;   through the above two examples, it is estimated that we should understand almost, and then introduce the connect by a few fixed partners.

1. Sys_connect_by_path function
Syntax: Sys_connect_by_path (column name, ' delimiter ').
Action: Starts with the start with the place traversal, will traverse to the path according to the function the separator, composes a new string.
[SQL]View plain Copy Select Sys_connect_by_path (ename, '/') Ename_tree from scott.emp to start with ename = ' KING ' Connec   T by Mgr = prior empno; To insert a digression, introduce a trick used by the Sys_connect_by_path function to convert the query rows into columns, and all the columns of the EMP in the table with ' | ' Separate output (hint: You can take the following statement apart to analyze one by one), the SQL statement is as follows:[SQL]View plain Copy Select Max (LTrim sys_connect_by_path (column_name, ' | '), ' | ') Column_names from (select column_name, R  Ownum rnum from user_tab_columns where table_name = ' EMP ') start with rnum = 1 Connect by rnum = RowNum;

2, Level: In the Structured query results, each row is a node in the structure, levels means that the node in the structure of the hierarchy, the root node is 1, the root node of the child node is 2, and so on.
The following SQL statement shows an intuitive effect: [SQL] view plain copy select Ename, Sys_connect_by_path (ename, '/') Ename_tree, level from S Cott.emp start with ename = ' KING ' connect by mgr = prior empno;

3, Connect_by_root: Returns the value of the column corresponding to the root node of the current node before using the column name. Connect_by_isleaf: Returns whether the current node is a leaf node, "yes" returns 1, "No" returns 0.
The following SQL statement is intuitive to show effects: [SQL] view plain copy select sys_connect_by_path (ename,  '/')   ename_tree,          connect_by_root ename as root,           connect_by_isleaf as isleaf      from scott.emp e    start with e.ename =  ' KING '    connect by prior e.empno = e.mgr;