One of the Oracle Handbook Series: Hierarchical Queries)

1) Prepare test data

 

If you have less time to talk, go straight to the subject. Create a simple employee table t_hierarchical:

|-Emp employee ID

|-Direct superiors of Mgr employees (Mgr itself is also an employee)

|-Emp_name: employee name

 

Insert some test data. Except for the big boss AA, other employees have their own managers.

 

Select emp, mgr, emp_name from t_hierarchical t;
1 AA

2 1 BB

3 2 CC

4 3 DD

5 2 EE

6 3 FF

 

2) CONNECT

 

Select emp, mgr, LEVEL from t_hierarchical t
Connect by prior emp = mgr
Order by emp;
1 1

2 1 2

2 1 1

3 2 1

3 2 3

3 2 2

4 3 4

4 3 1

4 3 2

4 3 3

5 2 3

5 2 2

5 2 1

6 3 2

6 3 3

6 3 4

6 3 1

To explain, connect by is used to specify the relationship between the parent and sub-records (PRIOR is explained more intuitively in the following example ). Take emp 2 as an example. It belongs to emp 1. If we use emp 1 as the root node, apparently LEVEL = 2. If we use emp 2 as the root node, LEVEL = 1, this is why the two rows of records in the co-colored logo section appear in the above query results, and so on.

 

3) START

Generally, we need more intuitive and practical results. This requires the start with clause in the structured query to specify the root node:

 

Select emp, mgr, LEVEL from t_hierarchical t
Start with emp = 1
Connect by prior emp = mgr;
1 1

2 1 2

3 2 3

4 3 4

6 3 4

5 2 3

Here we specify the root node as emp 1. This result is intuitive. For example, if emp 1 is the root node, emp 3 is in the third level (emp 1-emp 2-emp 3 ), here, I will add a description of the PRIOR keyword. My personal opinion: "PRIOR emp = mgr" indicates the emp number of the previous record = The mgr Number of the current record, which can be seen from the query results. In addition, obvious recursive traces can be found in the query results. For details, see numbers with different colors.

 

4) SYS_CONNECT_BY_PATH ()

I have to introduce very niubo's SYS_CONNECT_BY_PATH () function. We can get the hierarchy or tree structure path, as shown below:

 

Select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH (emp, '/') path from t_hierarchical t
Start with emp = 1
Connect by prior emp = mgr;
1 1/1

2 1 2/1/2

3 2 3/1/2/3

4 3 4/1/2/3/4

6 3 4/1/2/3/6

5 2 3/1/2/5

 

5) CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF is a very useful virtual column. What is LEAF (LEAF), that is, no node belongs to this node:

 

Select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH (emp, '/') path from t_hierarchical t
Where CONNECT_BY_ISLEAF = 1
Start with emp = 1
Connect by prior emp = mgr;
4 3 4/1/2/3/4

6 3 4/1/2/3/6

5 2 3/1/2/5

 

6) connect by and WHERE clauses

The SQL statement execution sequence after structured query is introduced is described as follows:

1) JOIN: Whether using the join on statement or the JOIN in WHERE statement

2) CONNECT

3) Other WHERE conditions

 

Let's take a look at an example. Assume that the preceding employees need to save some comments. At the same time, the comments are divided into two different versions based on Chinese and English. We can simply design this comment table:

|-Emp employee ID

|-Lang language (Chinese or English)

|-Emp_desc employee description

 

Select emp, lang, emp_desc from t_desc;
1 chinese this is a comment

1 english this is comment

2 chinese this is a comment

2 english this is comment

3 chinese. This is a comment.

3 english this is comment

4 chinese. This is a comment.

4 english this is comment

5 chinese this is a comment

5 english this is comment

6 chinese. This is a comment.

6 english this is comment

 

Now we need to include the Chinese comments of each employee in the original employee structured query. Let's look at the following query:

 

Select t. emp, t. mgr, td. emp_desc, LEVEL
From t_hierarchical t, t_desc td
Where t. emp = td. emp and td. lang = 'China'
Start with t. emp = 1
Connect by prior t. emp = t. mgr;
1 chinese this is comment 1

2 1 chinese this is comment 2

3 2 chinese this is comment 3

4 3 chinese this is comment 4

6 3 chinese this is comment 4

4 3 chinese this is comment 4

6 3 chinese this is comment 4

5 2 chinese this is comment 3

3 2 chinese this is comment 3

4 3 chinese this is comment 4

6 3 chinese this is comment 4

4 3 chinese this is comment 4

6 3 chinese this is comment 4

5 2 chinese this is comment 3

2 1 chinese this is comment 2

3 2 chinese this is comment 3

4 3 chinese this is comment 4

6 3 chinese this is comment 4

4 3 chinese this is comment 4

6 3 chinese this is comment 4

5 2 chinese this is comment 3

3 2 chinese this is comment 3

4 3 chinese this is comment 4

6 3 chinese this is comment 4

4 3 chinese this is comment 4

6 3 chinese this is comment 4

5 2 chinese this is comment 3

 

Let's look at this query again. It looks the same as the former:

 

Select t. emp, t. mgr, td. emp_desc, LEVEL
From t_hierarchical t join t_desc td
On (t. emp = td. emp and td. lang = 'China ')
Start with t. emp = 1
Connect by prior t. emp = t. mgr;
1. Note 1.

2 1 This is comment 2

3 2. Note 3.

4 3. Note 4.

6 3. Note 4.

5 2. This is comment 3.
 

The second is the expected result, and the second is far from each other. The reason is because the second condition td in the previous example. lang = 'China' is not considered a JOIN condition, so it is executed after connect by. In the next example, the second condition is explicitly written in the join on clause, therefore, it is executed before connect.

Because JOIN with the second condition is missing (that is, the first example in this section), each employee may appear twice. For example, if there is less data, let's see how connect by processes such duplicate data.

 

Select emp, mgr, lang from t2;
1 chinese

1 english

2 chinese

2 1 english

 

After connect:

 

Select emp, mgr, lang from t2
Start with emp = 1
Connect by prior emp = mgr;
1 chinese

2 chinese

2 1 english

1 english

2 chinese

2 1 english

 

Lang = 'China' after filtering:

1 chinese

2 chinese

2 chinese

Repeated rows are obviously not the expected results.

 

7) CONNECT BY LEVEL

Next, let's take a look at a special usage of connect by level. This is a headache, but it is also very useful in some situations:

 

Select LEVEL from dual connect by level <= 6;
1

2

3

4

5

6

If you have never used it before, but unfortunately you guessed the result, I deeply admire it. I have not figured it out yet. In fact, it is not even in line with the CONNECT BY syntax for structured queries, according to the Oracle documentation, the connect by condition must contain at least one expression that uses the PRIOR keyword. So some people think that connect by level is a BUG and suspect that Oracle may correct it in subsequent versions.

In any case, connect by level runs well in Oracle 10g/11g. If you don't want to figure out the reason, you can simply think that it is a loop, therefore, if you write connect by 1 = 1, the number from 1 to infinity will be output.

Author Snowtoday MSN: MyYe110w@hotmail.com