oracle 層次化查詢(產生菜單樹等)

標籤：表達   pid   返回   images   path   包含   connect   acl   number   

1、簡介:Oracle層次化查詢是Oracle特有的功能實現,主要用於返回一個資料集,這個資料集存在樹的關係(資料集中存在一個Pid記錄著當前資料集某一條記錄的Id)。

 

2、層次化查詢主要包含兩個子句,一個start with另一個是connect by。

start with:這個子句一般用於指定層次化查詢的開始節點(也就是樹的最頂級節點),找到最頂級節點,然後按照一定的規則開始尋找其剩餘的子節點

connect by:這個子句就是上面所說的規則,用於尋找剩餘子節點的規則

CREATE TABLE MENU(    "ID" NUMBER,     "DATA" VARCHAR2(100),     "PID" NUMBER) insert into MENU (id, data, pid)values (7, ‘g‘, 3);insert into MENU (id, data, pid)values (1, ‘a‘, null);insert into MENU (id, data, pid)values (2, ‘b‘, null);insert into MENU (id, data, pid)values (3, ‘c‘, 2);insert into MENU (id, data, pid)values (4, ‘d‘, 2);insert into MENU (id, data, pid)values (5, ‘e‘, 4);insert into MENU (id, data, pid)values (6, ‘f‘, 1);

 

下面開始執行層次化查詢,從PId為null的節點(該節點為根節點)開始遞迴尋找,尋找出所有的更節點下的子節點,構建出一個完整的樹

select ID,DATA,nvl(TO_CHAR(PID),‘NULL‘) from menu start with PID is NULL connect by prior ID=pid

代碼解析:

(1)、start with PID is NULL  指定層次化查詢的根節點,

紅框內的兩個節點為根節點,並開始遍曆其餘的節點。

(2)、connect by prior ID=pid  當前節點的PID等於上一層節點的ID，如果滿足條件,就加入到樹結果集中

指定遍曆尋找子節點的規則----->  這一過程是遞迴尋找,會一層一層找下去,直到不符合這一規則,則尋找停止。

 

3、實現上面結果集的另一種Sql實現

select ID,DATA,nvl(TO_CHAR(PID),‘NULL‘) from menu start with (data=‘a‘ or data=‘b‘) connect by prior ID=PID

結論:根節點的定義比較靈活,但是(connect by)遍曆子節點的規則,比較固定基本都是判斷父節點和子節點的ID的,如果理解了這句話,層次化查詢,差不多也就理解了!

 

4、  Oracle SQL 中的層次化查詢會檢測資料中是否存在迴環(死迴圈)，如果存在迴環，則會拋出 ORA-01436: CONNECT BY loop in user data . 的錯誤。如果在 connect by 後面加上 nocycle 則 產生迴環的最後一層的節點會被刪除。

 

 

如果資料中出現這種情況,產生了迴環,那麼在connect by 後面 加nocycle,節能

select ID,DATA,nvl(TO_CHAR(PID),‘NULL‘) from menu start with (data=‘a‘ or data=‘b‘) connect by nocycle prior ID=PID 

 

just沒有報錯,有點鬱悶,並沒有刪除,不知道哪裡出了問題,但是至少不抱錯了!!!

 

5、Oracle 還為層次化查詢提供了一些偽列（ Pseudo Column ）。

(1)、CONNECT_BY_ISCYCLE 當這一行有一個子節點同時也是它的祖先節點時返回 1 ，否則返回 0 。

(2)、CONNECT_BY_ISLEAF 當這一行是分葉節點時返回 1 ，否則返回 0 。偽列 LEVEL 返回這一行在樹中的層次，根為第一層。

(3)、CONNECT_BY_ROOT 查詢操作符可以加在 connect by 之後的某個欄位之前，表示獲得這一行的根節點的該欄位的值。

 

6、層次化查詢還支援一個特殊的函數 SYS_CONNECT_BY_PATH ， SYS_CONNECT_BY_PATH （ exp ， char ），這個函數返回從根節點到這一行計算其中每個exp 運算式的值，並把它們串連成字串，每個節點之間用 char 字元來分割。下面是一個例子。

 

這個函數很棒,可以考慮其他的資料庫也實現這個方法,這樣我們處理一個樹結構就很方便了！！！

 

oracle 層次化查詢(產生菜單樹等)