Oracle Handbook系列之結構化查詢(1)

一)準備測試資料

閑話少說，直入正題。建立一張簡單的職工表 t_hierarchical:

• Emp 職工編號
• Mgr 職工的直接上司(Mgr本身也是職工)
• Emp_name 職工姓名

插入一些測試資料，除了大老闆AA，其它的職工都各有自己的Manager。

 

  
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 
 

二)CONNECT BY

 

  
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 
 

解釋一下，CONNECT BY用於指定 父-子 記錄的關係(PRIOR我們在下例中解釋，更直觀一些)。舉emp 2為例，他隸屬於emp 1，如果我們以emp 1為根節點，顯然LEVEL=2;以emp 2自身為根節點，則LEVEL=1，這就是為什麼上述查詢結果中出現共色標識部分那兩行記錄，其它的類推。

三)START WITH

通常我們需要更直觀、更具有實用性的結果，這需要用到結構化查詢中的START WITH子句，用於指定根節點：

 

  
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 
 

這裡我們指定了根節點是emp 1，這樣的結果直觀了許多，例如，以emp 1為根節點，那麼emp 3位於第三級(emp 1—emp 2—emp 3)，這裡補充一下 PRIOR 關鍵字的說明，個人觀點：“PRIOR emp=mgr”表示前一條記錄的emp編號 = 目前記錄的mgr編號，從查詢結果中可以看出這一點。同時，從查詢結果中還能發現明顯的 遞迴 痕迹，參見不同顏色標識的數字。

四)SYS_CONNECT_BY_PATH()

不得不介紹一下非常牛波依的SYS_CONNECT_BY_PATH()函數，我們可以得到階層或者說樹狀結構的 路徑， 參見如下：

 

  
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 
 

五)CONNECT_BY_ISLEAF

非常好用的CONNECT_BY_ISLEAF虛列。何謂LEAF(葉子)，即沒有任何節點隸屬於該節點：

 

  
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