Connect by prior recursive algorithm in oracle

Connect by prior recursive algorithm in oracle start... connect by prior clause usage connect by is used in structured queries. Its basic syntax is: select... from tablename start with condition 1 connect by condition 2 where Condition 3; example: select * from table start with org_id = 'hbh1_wgwpy' connect by prior org_id = parent_id; simply put, a tree structure is stored in a table. For example, if a table contains two fields: org_id and parent_id, a tree structure can be formed by indicating who the parent of each record is. You can use the preceding syntax to query all records of the tree. Where: Condition 1 is the limit statement of the root node. Of course, the limit conditions can be relaxed to obtain multiple root nodes, which are actually multiple trees. Condition 2 is the connection condition, where PRIOR is used to represent the previous record. For example, connect by prior org_id = parent_id indicates that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record. Condition 3 is a filtering condition used to filter all returned records. A brief introduction is as follows: When you scan a tree structure table early, you need to access each node in the tree structure. One node can only be accessed once. The steps are as follows: Step 1: Start from the root node; step 2: Access the node. Step 3: Determine whether the node has any child nodes that are not accessed. If yes, switch to the left-side unaccessed subsection of the node and perform step 2, otherwise, perform Step 4. Step 4: If the node is the root node, the access is completed. Otherwise, perform Step 5. Step 5: return to the parent node of the node and perform step 3. In short, the process of scanning the entire tree structure is also the process of traversing the tree in the middle order. 1. tree Structure Description tree data is stored in the table. The hierarchical relationship between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates the employee's number, MGR indicates the number of the person who leads the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. In each row of the table, there is a MGR representing the parent node (except the root node). The entire tree structure can be determined through the parent node of each node. You can use the connect by and start with clause in the SELECT command to query the tree structure of the table. The command format is as follows: SELECT... Connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR split name 2} [start with]; where: The connect by clause indicates that each row of data is retrieved in a hierarchical order, it also requires that the data in the table be connected to the tree structure. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use column expressions in the join relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node. Start with: You can specify not only one root node, but also multiple root nodes. 2. The position of the PRIOR operator PRIOR before and after the equal sign determines the query order. When PRIOR is placed before a moderate number in the connect by clause, it is forced to search from the root node to the leaf node in sequence, that is, the parent node is directed to the child node through the tree structure, we call it a top-down approach. For example, when the connect by prior empno = mgr piror operator is placed behind the medium number in the connect by clause, the sequential search from the leaf node to the root node is forced, that is, the child node goes to the parent node through the tree structure, which is called the bottom-up mode. For example, connect by empno = prior mgr should also specify a Start Node in this mode. 3. When you define a Search Start Node to query the tree structure from top to bottom, you can not only start from the root node, but also define any node as the Start Node to start searching down. In this way, the search result is a branch of the structure tree starting with this node. 4. Using LEVEL in a table with a tree structure, each row of data is a node in the tree structure. Because the nodes are in different levels, each row of records can have a layer number. The layer number is determined based on the distance between the node and the root node. No matter which node starts, the layer number of the starting root node is always 1, and the child node of the root node is 2. 5. when you query the tree structure, you can remove some rows from the table, or cut a branch in the tree, and use the WHERE clause to limit a single node in the tree structure, to remove a single node in the tree, but it does not affect its child nodes (top-down retrieval) or its predecessors (top-down retrieval ). 6. As shown in other queries, the order by clause can also be used in tree structure queries to change the display ORDER of query results without having to traverse the tree structure.