Oracle “CONNECT BY” 使用

標籤：blog   http   io   使用   ar   sp   art   on   log   

  Oracle “CONNECT BY”是層次查詢子句，一般用於樹狀或者層次結果集的查詢。其文法是：

[ START WITH condition ]CONNECT BY [ NOCYCLE ] condition

 

說明：
1. START WITH：告訴系統以哪個節點作為根結點開始尋找並構造結果集，該節點即為返回記錄中的最高節點。
2. 當分層查詢中存在上下層互為父子節點的情況時，會返回ORA-01436錯誤。此時，需要在connect by後面加上NOCYCLE關鍵字。同時，可用connect_by_iscycle偽列定位出存在互為父子迴圈的具體節點。 connect_by_iscycle必須要跟關鍵字NOCYCLE結合起來使用

 

[例1]

建立一個部門表，這個表有三個欄位，分別對應部門ID，部門名稱，以及上層業務ID

   DEPID DEPNAME                                                                           UPPERDEPID----------- -------------------------------------------------------------------------------- -----------          0 General Deparment                                                                          1 Development                                                                                0          2 QA                                                                                         0          3 Server Development                                                                         1          4 Client Development                                                                         1          5 TA                                                                                         2          6 Porject QA                                                                                 2

 

 

現在我要根據“CONNECT BY”來實現樹狀查詢結果

SQL> SELECT RPAD( ‘ ‘, 2*(LEVEL-1), ‘-‘ ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, ‘/‘) "PATH"FROM DEPSTART WITH UPPERDEPID IS NULLCONNECT BY PRIOR DEPID = UPPERDEPID;

DEPNAME                        ROOT                    ISLEAF      LEVEL PATH------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------General Deparment              General Deparment            0          1 /General Deparment -Development                  General Deparment            0          2 /General Deparment/Development ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development -QA                           General Deparment            0          2 /General Deparment/QA ---TA                         General Deparment            1          3 /General Deparment/QA/TA ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA

說明：
1. CONNECT_BY_ROOT 返回當前節點的最頂端節點
2. CONNECT_BY_ISLEAF 判斷是否為葉子節點，如果這個節點下面有子節點，則不為葉子節點
3. LEVEL 偽列表示節點深度
4. SYS_CONNECT_BY_PATH函數顯示詳細路徑，並用“/”分隔

 

總結：

1.根據樹形結構查子類

select * from zhfg_soar.SP_ORG_GROUP where levelpermission = 0 and validflag = 1start with  id = ‘20130718091552GRP60000001‘ connect by prior id=PARENTID 

2. 根據屬性結構查詢父類

select * from zhfg_soar.SP_ORG_GROUP where levelpermission = 0 and validflag = 1start with  id = ‘40288181455dbe1701455ee92b9d001d‘ connect by prior PARENTID=id 

 

部分轉載：http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

Oracle “CONNECT BY” 使用