（轉載）令人迷糊的Oracle遞歸查詢(start with)

轉載地址：https://blog.csdn.net/weiwenhp/article/details/8218091

備註：如有侵權，請聯繫立即刪除。

寫代碼時碰到要弄清楚Oracle的role之間的傳遞關係,就是有role A的話,可以通過grant A to B,把A賦予給B,又通過grant B to C .那我想知道所有role中,有哪些role具有A的許可權.

上網一查發現有個遞歸查詢,不過都講的不是太詳細,而那Oracle整的那用法實在太怪異了,跟我們平時用的SQL差的太遠,所以琢磨了好一陣子腦子才轉過彎呢.

樹形結構

可能一看到遞歸查詢這樣太專業的名詞大家就迷糊了.實際上可以看成有一個樹形結構,然後我們要怎麼把所有樹的所有結點查找出來.學數據結構的時候我們知道要遍歷一個樹結構有啥前序遍歷,中序遍歷,後序遍歷.反正挺麻煩的.不像遍歷個數組那麼容易的.那實際上在Oracle的一個表中也可以保存樹形結構信息.你要查詢所有的樹節點,自己整個函數或存儲過程去整肯定是超級麻煩的.Oracle提供了一個簡單的機制幫助你.要用到start with ...connect by等關鍵字.先來假定有下麵這樣一個簡單的樹形結構存儲在表中.

create table Tree(son char(10), father char(10)); 然後插入些信息變成這樣的表

SON            FATHTER

孫子SB         兒子

孫子NB         兒子

兒子             爸爸

爸爸             爺爺

很顯然這是一個簡單的樹形結構

                           孫子SB

                             ^

                             |

爺爺 --> 爸爸 --> 兒子 -->孫子NB

遞歸查詢

假如要查詢出以爺爺為根的樹的所有節點值咋整呢 ?如果數據少多來幾個where嵌套就行.但要是樹層次有幾百那會搞死人了啊.於是我們就用Oracle提供的遞歸查詢.先看下SQL的寫法然後再講解

SELECT  son FROM tree

START WITH father = '爺爺'

CONNECT BY PRIOR son = father;

返回的結果為 爸爸 兒子 孫子NB 孫子SB

代碼看起來很短,但是極為怪異,腦子半天都不容易轉過彎呢.實際上我們不把這個SQL語句跟一般的SQL類比,而把它當作給一些函數指定一些參數的賦值語句才更容易理解.

那怎麼來理解上面的SQL呢?

首先把SELECT son FROM tree還是看成一般sql一樣,就是要查找son這一列的信息.而把以START WITH開頭的後面所有東東看成一個where限制條件.其中START WITH 是指定

樹的根,這裡指定的根是 '爺爺',實際上你還可以指定多個根的,比如 father in ('爺爺', '爸爸') .

而CONNECT BY PRIOR son = father相當於表明在遞歸的過程中,查找到的樹中其它節點接著又作為根結點.然後繼續遞歸. 反正看這sql語句前先想下樹形結構,然後想下一般編程語言中的遞歸函數.再來看時就容易理解點.實際上我覺得Oracle這樣設計不太好.如果用戶只是簡單的指定的一個根節點然後知道樹中其他節點信息.那麼就直接用START WITH指定根就行了.CONNECT BY PRIOR顯得有點多餘,可以不用用戶去指定.當作一個預設值,只有需要其他一些更複雜的操作時才讓用戶明確指定.這樣就不容易誤導人了.

為了便於理解可以可以CONNECT BY那一行當作多餘的,只記住要查詢的列名放前面,根列名放等號後面就行.這樣只要指定樹的根結點就比較好理解了.

start with ,connect by prior其他變形

上面講的用START WITH 指定樹的根,然後用CONNECT BY指定遞歸條件.是最簡單的也是最常用的形式.但實際上還有些變形.

1.START WITH 可以省略

比如

SELECT son FROM tree

CONNECT BY PRIOR son = father;

此時不指定樹的根的話,就預設把Tree整個表中的數據從頭到尾遍歷一次,每一個數據做一次根,然後遍歷樹中其他節點信息.

在這個例子中,上面的SQL等價於

SELECT son FROM tree

START WITH father IN (爺爺,爸爸,兒子,孫子NB,孫子SB)

CONNECT BY PRIOR son = father;

那查詢到的結果如下,有很多重覆信息的

爸爸,兒子,孫子NB,孫子SB    兒子,孫子NB,孫子SB    孫子NB,孫子SB

2.START WITH 與CONNECT BY PRIOR位置可互換

SELECT son FROM tree

CONNECT BY PRIOR son = father

START WITH father = '爺爺';

這語句與最開頭那個是等價的

3.nocycle關鍵字

我們知道標準的樹結構中是不會有環的,但表中的樹形結構不是標準的,有可能導致環的出現

比如

             ---------孫子SB

             |              ^

             |              |

爺爺 --> 爸爸 --> 兒子 -->孫子NB

哎在這裡想用線條整個箭頭出來真他媽麻煩啊.我又有點懶不想用其他畫圖工具啥的啊.反正假設兒子的兒子是孫子SB ,而孫子SB的兒子是爸爸.這樣就形成一個環了.

當然在Oracle中的role是禁止出現迴圈的.比如你grant A to B ,grant B to C .再來個grant C to A會出錯的.

假如有個上面的環,在再使用開始的遞歸查詢語言會出錯.得用nocycle關鍵字指定忽略環.

SELECT son FROM tree

START WITH father = '爺爺'

CONNECT BY NOCYCLE PRIOR son = father;

此時結果是

爸爸 兒子 孫子NB

你會註意到由於忽略了環,所以孫子SB的信息也被忽略掉了.

4. connect by prior 後麵條件順序的改變 (???)

SELECT son FROM tree

START WITH father = '爺爺'

CONNECT BY PRIOR son = father;

這是開頭的寫法,但實際上也可以寫成father = son倒過來寫.有人說沒倒過來是從上到下,從根往下.如果倒過來則是從下到上.哎不過我測試了下發現不是那麼回事.結果也有點亂七八糟的.沒想明白是啥規律.反正還有待研究啊

5.還可以加where條件

我上面說了可以把start with ,connect 假裝看成where 條件一樣.所以在這個sql語句其他地方還可以加其他where 語句,可以看成與遞歸查詢無關.只是對整個結果起過濾作用

比如

SELECT son FROM tree  WHERE son = '孫子SB'

START WITH father = '爺爺'

CONNECT BY PRIOR son = father;

當然你不能在最後部分加where,不能在connect by最後面再加.