<p id="u7msl"><tr id="u7msl"></tr></p>^{<noscript id="u7msl"></noscript>}

oracle筆記（4）----層次查詢

2019-11-09 13:31:40

字體：大中小

來源：轉載

供稿：網友

層次查詢的概念

語法格式：

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

層次查詢是通過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。

層次查詢限制

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

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

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

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

層次查詢的增強特性

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排序。

示例：

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

[c-sharp] view plain copy print?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;