Oracle中的層次查詢詳解

1 文法格式

select [level], column, expr... from table
　　[where condition]
　　start with condition
　　connect by [prior column1= column2 |
　　column1 = prior column2];      

2 文法解釋

層次查詢是通過start with和connect by子句辨別的：

1.其中level關鍵字是可選的，表示等級，1表示root,2表示root的child,其他相同的規則。

2.From之後可以是table,view但是隻能是一個table。

3.Where條件限制了查詢傳回的行，但是不影響層次關系，屬于将節點截斷，但是這個被截斷的節點的下層child不受影響。

4.Start with是表示開始節點，對于一個真實的層次關系，必須要有這個子句，但是不是必須的。

5.connect by prior是指定父子關系，其中prior的位置不一定要在connect by之後，對于一個真實的層次關系，這也是必須的。

對于from是視圖的，那麼這個view不能包含join。

3 層次查詢的限制

1.層次查詢from 之後如果是table，隻能是一個table，不能有join。

2.from之後如果是view，則view不能是帶join的。

3.使用order by子句，order子句是在等級層次做完之後開始的，是以對于層次查詢來說沒有什麼意義，除非特别關注level，獲得某行在層次中的深度，但是這兩種都會破壞層次。見增強特性中的使用siblings排序。

4.在start with中表達式可以有子查詢，但是connect by中不能有子查詢。

4 層次查詢的增強特性

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字元型或能自動轉換成字元型的列名。它的主要目的就是将父節點到目前節點的”path”按照指定的模式展現出現。這個函數隻能使用在層次查詢中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

    在oracle9i的時候，查找指定root下的葉子節點，是很複雜的，oracle10g引入了一個新的函數，connect_by_isleaf,如果行的值為0表示不是葉子節點，1表示是葉子節點。

3、CONNECT_BY_ISCYCLE和NOCYCLE關鍵字

    如果從root節點開始找其子孫，找到一行，結果發生和祖先互為子孫的情況，則發生循環，oracle會報ORA-01436: CONNECT BY loop in user data，在9i中隻能将發生死循環的不加入到樹中或删除，在10g中可以用nocycle關鍵字加在connect by之後，避免循環的參加查詢操作。并且通過connect_by_iscycle得到哪個節點發生循環。0表示未發生循環，1表示發生了循環。

4、CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根節點的相同列名的值。

5、使用SIBLINGS關鍵字排序

    對于層次查詢如果用order by排序，比如order by last_name則是先做完層次獲得level,然後按last_name排序，這樣破壞了層次，比如特别關注某行的深度，按level排序，也是會破壞層次的。

     在oracle10g中，增加了siblings關鍵字的排序。

文法：order  siblings  by <expre>

它會保護層次，并且在每個等級中按expre排序。

5 示範

1、 建構測試表與插入測試語句

create table tab_connect_by (child number,parent number);  
insert into tab_connect_by (CHILD, PARENT) values(2, 5);  
insert into tab_connect_by (CHILD, PARENT) values(3, 5);  
insert into tab_connect_by (CHILD, PARENT) values(10, 15);  
insert into tab_connect_by (CHILD, PARENT) values(5, 15);  
insert into tab_connect_by (CHILD, PARENT) values(9, 17);  
insert into tab_connect_by (CHILD, PARENT) values(8, 17);  
insert into tab_connect_by (CHILD, PARENT) values(15, 38);  
insert into tab_connect_by (CHILD, PARENT) values(17, 38);  
insert into tab_connect_by (CHILD, PARENT) values(6, 38);  
insert into tab_connect_by (CHILD, PARENT) values(13, 26);  
insert into tab_connect_by (CHILD, PARENT) values(1, 26);  
insert into tab_connect_by (CHILD, PARENT) values(12, 26);  
insert into tab_connect_by (CHILD, PARENT) values(11, 18);  
insert into tab_connect_by (CHILD, PARENT) values(7, 18);  
insert into tab_connect_by (CHILD, PARENT) values(38, null);  
insert into tab_connect_by (CHILD, PARENT) values(26, null);  
insert into tab_connect_by (CHILD, PARENT) values(18, null);  
commit;        

2 查詢語句1

select a.child,  
       a.parent,  
       level "層次",  
       sys_connect_by_path(child, '<-') "合并層次",  
       prior a.child "父節點",  
       connect_by_root a.child "根節點",  
       decode(connect_by_isleaf, 1, a.child, null) "子節點",  
       decode(connect_by_isleaf, 1, '是', '否') "是否子節點"  
  from tab_connect_by a  
 start with a.parent is null --從parent為空開始掃描  
connect by prior a.child = a.parent --以child為父列連接配接parent  
 order siblings by child desc --對層次排序  
;        

3 查詢語句2

Select level, connect_by_iscycle,connect_by_isleaf,parent, child   
　　From tab_connect_by   
　　Connect by nocycle prior child = parent   
　　Start with parent is null;        

轉載于:https://www.cnblogs.com/jepson6669/p/9437081.html