Confusing Oracle recursive query (start)

Document directory

• 1. Start with can be omitted
• 2. The start with and connect by prior locations are interchangeable.
• 3. nocycle keyword
• 4. Change the conditional order after connect by prior (???)
• 5. You can also add the where condition.

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. Let's 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.

 

Start with, connect by prior other Deformation

As mentioned above, using start with to specify the root of the tree and then using connect by to specify recursive conditions 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 tree table is traversed from start to end by default. Each data is used as the root, and other node information in the tree is traversed.

In this example, the preceding SQL statement is equivalent

Select son from tree

Start with Father in (grandpa, Dad, son, grandson Nb, grandson SB)

Connect by prior son = father;

The query results are as follows:

Father, Son, grandson Nb, grandson Sb son, grandson Nb, grandson Sb grandson Nb, grandson 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 statement at the beginning.

 

3. nocycle keyword

We know that there is no ring in the standard tree structure, but the tree structure in the table is not standard and may lead to the appearance of the ring.

For example

--------- Sun Tzu sb

| ^

|

Grandpa --> dad --> son --> grandson Nb

Ah, it's really fucking troublesome to use the entire arrow of the line here. 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. in this way, a ring is formed.

Of course, the role in Oracle prohibits loops. For example, if you grant A to B, grant B to C. Another grant C to a will cause an error.

If there is an above loop, an error will occur when you use the recursive query language that begins again. You must use the nocycle keyword to specify the ignore loop.

Select son from tree

Start with Father = 'Grandpa'

Connect by nocycle prior son = father;

The result is

Father, Son, and grandson Nb

You will notice that the information of Sun Tzu Sb is ignored because the ring 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 I mentioned above, start with and connect pretend to be the same as where conditions. therefore, you can add other where statements elsewhere in this SQL statement, which may be considered unrelated to recursive queries. 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.