ORACLE: OCA-047-question and experiment (5)-start with and connect

Recently busy, cool coin silver!

Question:

The core function of start with and connect by is to generate a tree result, and the meaning is to generate a tree result.

Here, we don't need the example in the question. We use the example database example provided by Oracle to correspond to the Employees table. It is estimated that the above exhibit is generated by selecting the four columns in the example. Query example:

Example 1:

SQL> select T. employee_id, T. manager_id, T. last_name from employees T 2 start with T. manager_id = 100 3 connect by prior T. employee_id = T. manager_id; employee_id manager_id last_name ---------------- --------------------------- 101 100 Kochhar the result is the first record with manager_id = 100, the parent node of the sub-node 101 is 100 108 101 the parent node of the Greenberg sub-node 108 is 101, it can be seen that the last 101 is used as the parent node to find the 109 108 faviet sub-node of the child node. Who is the 108 sub-node? Is there a subnode in 109 110 108 Chen 109? Apparently not. So I continued to find other sub-nodes of 108 and found 110 111 108 Sciarra 110 with sub-nodes? Apparently not. So I continued to find another sub-node of 108 and found the sub-node of 111 112 108 111 Urman? Apparently not. So I continued to find other sub-nodes of 108 and found 112 113 108 Popp 112 with sub-nodes? Apparently not, so I continued to find other sub-nodes of 108 and found 113 200 101 Whalen and continued to find other sub-nodes of 108. If no, I returned 108 parent node 101, find 101 other subnodes 200 203 101 mavris 200 have subnodes? Apparently not, so continue to find other sub-nodes of 101 and find the following sub-nodes of 203 204 101 Baer .... 205 101 Higgins... 206 205 gietz... 102 100 de Haan... 103 102 hunold

Can we summarize the rules here:

1. traversal is top-down (assuming that the parent node is in the upper layer). The traversal starts from the parent node to the child node. As long as there is a child node, the traversal continues with the child node as the parent node, if no child node exists, return to the previous layer to find another child node.

2. which value is used as the first parent node? Start with specifies that a random record is selected to start traversing. Remember, the core of this statement is to return a tree structure. The tree structure is composed of two column values, for example, manager_id and employee_id, which are physically divided into two columns. However, the correspondence is actually a node element in the tree. Although there is a complete tree, you can intercept the desired tree, and the root is specified by start. For example, if start with specifies the owner's ID, that is, the root of the complete tree, the returned result is the whole tree traversal result. Select a node and return the result of partial tree traversal.

3. What is Prior? See the following:

Example 2:

SQL> select T. employee_id, T. manager_id, T. last_name from employees T 2 start with T. manager_id = 100 3 connect by T. employee_id = prior T. manager_id; employee_id manager_id last_name ------------------ --------------------------- 101 100 Kochhar 101 parent node is 100, there is no saying that the 100 king 100 parent node is null ?? After finding the problem, the 101 parent node is found and the 100 parent node is found again. 102 100 de Haan because the previous record 100 has no parent node, and then returns to the lower layer, find the 102 that meets the statement (102 of the parent node is 100) 100 king but 102 of the parent node 100 still no parent node 114 100 raphaely had to find 114 100 king 120 100 Weiss 100 king 121 100 FRIPP 100 king 122 100 kaufling 100 king 123 vollman 100 king it's always so tragic

What is the rule? Prior is actually traversing up. After finding the node, use the parent node of the node as the child node and find its parent node. This is the bottom-up traversal. Other statement factors are the same as described above.

As described above, can you read the following content?

SQL> select t.employee_id,t.manager_id, t.last_name from employees t  2  start with t.manager_id = 108  3  connect by t.employee_id = prior t.manager_id; EMPLOYEE_ID MANAGER_ID LAST_NAME----------- ---------- -------------------------        109        108 Faviet        108        101 Greenberg        101        100 Kochhar        100            King        110        108 Chen        108        101 Greenberg        101        100 Kochhar        100            King

Let's talk about the role of where. Let's look at the next example.

Example 3:

SQL> select t.employee_id,t.manager_id, t.last_name from employees t  2  where t.last_name like '%an'  3  start with t.manager_id = 100  4  connect by prior t.employee_id =  t.manager_id; EMPLOYEE_ID MANAGER_ID LAST_NAME----------- ---------- -------------------------        112        108 Urman                            102        100 De Haan

Compared with example 1, we can know that the WHERE clause filters the traversed tree structure and finds all nodes that meet the WHERE clause. For example, an employee whose name ends with "an" is satisfied.