Structured Query of the Oracle Handbook Series (1)

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 superior 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