ORACLE DB 的學習者們

2017年12月23日 星期六

階層式查詢

參考連結： ORACLE 文件、 簡單範例一、 簡單範例二

語法架構

階層式查詢用於以下類型結構的資料：

資料彼此間存在從屬關係：比方說，員工資料表：員工編號、姓名、部門、主管編號。資料表內的員工可依照從屬關係畫出樹狀圖。

或是親屬資料表：姓名、性別、父母等

語法結構如下： 

select ... start with initial-condition connect by nocycle recurse-condition

select ... connect by recurse-condition

select ... start with initial-condition connect by nocycle recurse-condition

select ... connect by recurse-condition

範例：列出所有從屬關係

此語法先從滿足 initial-condition 的根節點開始，以DFS(Deep First Search) 或是 LDR(Left-Dump-Right)的順序，列印出根節點的子節點且符合recurse-condition的其餘子節點。

以下範例，從第一筆資料起，列出所有從屬關係 

      SELECT last_name, employee_id, manager_id, LEVEL
      FROM emp  
      CONNECT BY PRIOR employee_id =  manager_id
      ORDER SIBLINGS BY last_name;

UNARY OPERATOR PRIOR 的用法

CONNECT BY PRIOR employee_id = manager_id

PRIOR可以放在欄位 employee_id 前或是欄位 manager_id 之前，它是一個 UNARY OPERATOR ，放在EMPLOYEE_ID前，代表後續其他的子節點，需以根節點的這個EMPLOYEE_ID，當作子節點的MANAGER_ID，因此PRIOR放在EMPLOYEE_ID和放在MANAGER_ID所得的結果是不同的。

為根節點加上起始條件

根節點的起始條件用 START....WITH 語法來定義，可定義符合該條件的根節點，才會被查詢出來。

以下列出，從員工代碼 100 開始的員工，列出所有員工的從屬關係 

      SELECT last_name, employee_id, manager_id, LEVEL
      FROM emp  
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id =  manager_id
      ORDER SIBLINGS BY last_name;
其中 LEVEL 是用於此階層查詢中的虛擬欄位，表示由根節點開始的階層編號，而根節點為 1 。

CONNECT_BY_ROOT 與 SYS_CONNECT_BY_PATH

CONNECT_BY_ROOT 可以列出根節點的欄位，而 SYS_CONNECT_BY_PATH 則可列出由根節點到子節點的完整路徑。參看下列範例 

   SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM emp
   WHERE LEVEL > 1 and department_id = 100
   CONNECT BY PRIOR employee_id = manager_id;
上述範例列出所有子節點的管理者，並列出從根節點到子節點的完整階層。

張貼者： 於