令人迷糊的Oracle遞迴查詢(start with)

文章目錄

• 1.START WITH 可以省略
• 2.START WITH 與CONNECT BY PRIOR位置可互換
• 3.nocycle關鍵字
• 4. connect by prior 後麵條件順序的改變 (???)
• 5.還可以加where條件

寫代碼時碰到要弄清楚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最後面再加.