Confusing Oracle recursive query (start)

Confusing Oracle recursive query (start with) when writing code, you need to understand the transfer relationship between Oracle's role. If role A exists, you can grant A to B, grant A to B and grant B to C. then I want to know which role has the permission in all role. I found a recursive query on the Internet, but I didn't talk about it in details. The usage of Oracle is so weird that it is too far away from the SQL statement we usually use, so after a while, my mind turned around. the tree structure may be confusing as soon as you see recursive queries, which are too professional. in fact, we can think of a tree structure, and how can we find all the nodes of all trees. when learning the data structure, we know what the pre-order traversal is for traversing a tree structure, middle-order traversal, and post-order traversal. it is quite troublesome. it is not as easy as traversing an array. in fact, a table in Oracle can also store tree structure information. if you want to query all the Tree nodes, it must be very troublesome to complete the entire function or stored procedure. orac Le provides a simple mechanism to help you. start... connect by and other keywords. assume that the following simple tree structure is stored in the table. create table Tree (son char (10), father char (10 )); then insert some information into this table. son fathter, grandson SB, SON, grandson, NB, SON, SON, Father, Father, and grandfather. Obviously, this is a simple tree structure. Grandson SB ^ | grandpa --> dad --> SON --> what is the sum of the node values of the tree rooted in grandpa in the recursive query of Sun Tzu NB? If there is less data, just a few where nesting rows. but if there are hundreds of tree layers, It would be dead. so we use recursive queries provided by Oracle. let's take a look at the SQL statement first, and then explain the SELECT son FROM tree START WITH father = 'Grandpa 'CONNECT BY PRIOR son = father. The returned result is that the SB code of father, son, and grandson NB looks very short, but it's weird. It's not easy to turn around for half a day. in fact, we do not compare this SQL statement with a general SQL statement, but think of it as a value assignment statement that specifies some parameters for some functions to make it easier to understand. so how can we understand the above SQL? First, we should look at the SELECT son FROM tree as a general SQL statement, that is, to find the information of the son column. consider all the things starting with start with as a where restriction. here, start with is the root of the specified tree. Here the root is 'grandcan'. in fact, you can also specify multiple roots, such as father in ('grandcan ', 'Daddy '). connect by prior son = father indicates that other nodes in the tree are used as the root node in the recursive process. then continue recursion. before reading this SQL statement, you should first consider the tree structure and then the recursive functions in general programming languages. it is easy to understand. in fact, I think Oracle is not well designed. if the user simply specifies a root node and then knows the information of other nodes in the tree. you can use start with to specify the root. connect by prior is a little redundant, and you do not need to specify it. as a default value, only other Users can specify more complex operations. this is not easy to mislead people. for ease of understanding, you can connect by that line as redundant. Just remember to put the column name to be queried first, and put the root column name after the equal sign. in this way, you only need to specify the root node of the tree. start with, connect by prior other deformation the above description uses start with to specify the root of the tree, and then uses connect by to specify the recursive condition. is the simplest and most commonly used form. but there are still some variants. 1. start with can be omitted, for example, SELECT son FROM tree connect by prior son = father. If the Tree root is not specified, data in the entire table of the tree is traversed from start to end BY default, each data is rooted once, and other node information in the tree is traversed. IN this example, the preceding SQL statement is equivalent to SELECT son FROM tree START WITH father IN (grandpa, Dad, son, Sun Tzu NB, Sun Tzu SB) connect by prior son = father; the query result is as follows: father, son, Sun Tzu NB, Sun Tzu SB son, Sun Tzu NB, sun Tzu SB grandson NB, Sun Tzu SB 2. the start with and connect by prior locations are interchangeable. SELECT son FROM tree connect by prior son = father start with father = 'Grandpa '; this statement is equivalent to the first one. the nocycle keyword we know that there is no ring in the standard tree structure, but the tree structure in the table is not standard, it may lead to the appearance of the ring, such as --------- Sun Tzu SB | ^ | grandpa --> dad --> son --> Sun Tzu NB Ah here I want to use the entire arrow to come out. It's really fucking troublesome. i'm a little lazy and don't want to use other drawing tools. assume that the son is the son of SB, and the son of SB is the father. this way A ring is formed. of course, the role in Oracle prohibits loops. for example, grant A to B, grant B to C. another grant C to A will have an error. if there is a loop above, an error will occur when you use the recursive query language that begins again. use the nocycle keyword to specify the ignore loop. SELECT son FROM tree start with father = 'Grandpa 'connect by nocycle prior son = father; at this time, the result is that the father and son of NB, you will notice that the ring is ignored, therefore, Sun Tzu's SB information is ignored. 4. change the conditional order after connect by prior (???) SELECT son FROM tree start with father = 'Grandpa 'connect by prior son = father; this is the first line, but it can also be written as father = son. some people say that it is from top to bottom, down from the root. if it is reversed, it is from bottom to top. I tested it and found that this is not the case. the results are also messy. I don't want to understand what the rule is. still waiting for research 5. you can also add the where condition. As mentioned above, start with and connect pretend to be the same as where condition. therefore, you can add other where statements elsewhere in this SQL statement, which may be considered unrelated to recursive queries. it only filters the entire result. For example, SELECT son FROM tree WHERE son = 'Sun Tzu SB 'start WITH father = 'Grandpa 'connect by prior son = father; of course, you cannot add a where clause to the last part or connect by clause.