Oracle recursive query (startwith)

You can also store tree structure information in a table in Oracle. It is very troublesome to query all Tree nodes and complete the entire function or stored procedure. Oracle provides

You can also store tree structure information in a table in Oracle. It is very troublesome to query all Tree nodes and complete the entire function or stored procedure. Oracle provides

When writing code, if you need to understand the transfer relationship between Oracle's role, that is, if role A exists, you can grant A to B through 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.

Tree Structure

You may be confused when 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. oracle 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

Son of Sun Tzu SB

Son of Sun Tzu NB

Son and Dad

Grandpa Dad

Obviously, this is a simple tree structure.

Sun Tzu SB

^

|

Grandpa --> dad --> son --> grandson NB

Recursive query

What if I want to query all the node values of the tree rooted in grandpa? If there is less data than a few where nesting statements, but if there are hundreds of tree layers, it will be dead. So we will use the recursive query provided by Oracle. Let's take a look at the SQL statement and then explain it.

SELECT son FROM tree

Start with father = 'Grandpa'

Connect by prior son = father;

The returned results are Father, Son, and son, NB, grandson, SB.

The code looks very short, but it is very weird, and it is 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. the start with parameter specifies

The root of the tree. The root here is 'Grandpa '. in fact, you can specify multiple root types, such as father in ('Grandpa', 'Dad ').

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, users can specify only when other complex operations are required. 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.

,