【原】Oracle開發專題之：級聯查詢(Hierarchical Queries)

一、級聯數據的表示：

首先我們來看一張關于組織架構的結構圖

這張圖是一個典型的“樹型結構圖”，只有一個根節點(King)，其下有若干個分支節點，每個分支節點下又有若干個子節點或樹葉節點。假如我們要把這些關系信息映射到數據庫中(此處以Oracle9i數據庫為例)，表結構應當如何表示呢？

CREATE TABLE EMPLOYEE (

EMP_ID NUMBER (4) CONSTRAINT EMP_PK PRIMARY KEY,

FNAME VARCHAR2 (15)NOT NULL,

LNAME VARCHAR2 (15)NOT NULL,

DEPT_ID NUMBER (2)NOT NULL,

MANAGER_EMP_ID NUMBER (4) CONSTRAINT EMP_FK REFERENCES EMPLOYEE(EMP_ID),

SALARY NUMBER (7,2)NOT NULL,

HIRE_DATE DATENOT NULL,

JOB_ID NUMBER (3));

請注意這里紅色粗體部分，字段MANAGER_EMP_ID的值引用了字段EMP_ID的值，我們稱這種引用為“自引用”。它規定了經理人員的ID必須是來自表中存在的員工ID。

二、Oracle 9i中的start with...connect by:

[[START WITH condition1] CONNECT BY condition2]

START WITH condition1
指定級聯數據的根記錄(一條或多條)，所有滿足條件1的記錄都將被當成是根紀錄，假如我們不給定START WITH子句，所有的紀錄都會被當成是根紀錄，通常這不是我們想要的結果。condition1可以是一個子查詢。

CONNECT BY condition2
指定級聯數據中父紀錄和子紀錄之間的關系，這里的關系被表示成一個表達式，當前紀錄的字段會和對應的父紀錄的某個字段進行比較。condition2必須跟著一個PRIOR操作符，該操作符用于標明父紀錄的字段。condtion2不能包含子查詢

PRIOR是Oracle的一個內建操作符，僅用于級聯查詢。當我們在級聯查詢的CONNECT BY條件中使用了PRIOR操作符時，位于其后的表達式被當成是當前紀錄的父紀錄進行比較。

三、實例比較：

下面我們通過2條SQL語句來演示如何進行級聯查詢，以及PRIOR在不同位置時帶來的不同結果。

SQL> select * from employee;

ID EMP_NAME MANAGER_ID

---------- -------------------- ----------

1 king

2 mark 1

3 bob 1

4 tom 2

5 paul 2

6 jack 3

7 ben 4

7 rows selected.

需求：我們要找出員工ID為2的人及其所有下屬(包括直接和間接下屬)

SQL> select * from employee start with id = 2 connect by prior id = manager_id order by id;

ID EMP_NAME MANAGER_ID

---------- -------------------- ----------

2 mark 1

4 tom 2

5 paul 2

7 ben 4

請注意PRIOR操作符被放置在字段ID前面。查詢結果中ID為7的員工ben，雖然其對應的經理ID為4，但是因為員工號為4的tom，其對應的經理ID為2，所以ben是屬于mark的間接下屬而符合查詢條件。

我們已經知道PRIOR放在那一側，那一側的字段就會被當成父記錄的字段而被用于和當前記錄的字段(另一側的表達式)進行比較，那么假如我們把PRIOR放在manager_id一側，結果會有什么不同嗎？請看下面的SQL執行結果。

SQL> select * from employee start with id = 2 connect by id = prior manager_id order by id;

ID EMP_NAME MANAGER_ID

---------- -------------------- ----------

1 king

2 mark 1

很明顯結果完全不同，那么是什么造成了兩次查詢的結果完全不同呢？說到這里我們還要再回到SQL語言本身，我用一種比較直白的方式來講解不同位置的PRIROR所帶來的不同意義。

【1】第一個查詢：connect by prior id = manager_id，意思是從當前根記錄開始，查找所有符合條件的記錄：他們的manager_id必須等于當前記錄的id。也就是說查找所有manager_id=2的記錄及其子記錄，很明顯manager_id=2的記錄只有tom和paul，但是由于ben的直接領導tom是mark的下屬，所以ben也是mark的下屬，只不過是間接關系而已。

【2】第二個查詢：connect by id = prior manager_id，意思是從當前根記錄開始，查找所有符合條件的記錄：他們的id必須等于當前記錄的manager_id。也就是說查找所以id=1的記錄，那么很明顯id=1的記錄只有king。

總結：Prior放在那里，那一側就是被比較的一方(父方)，另一側就是發起比較的一方(子方)。語義上可以這樣翻譯：xxx字段的值必須等于當前記錄XXX字段的值(prior一方)

參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)

-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。

posted on 2008-06-11 17:55 Paul Lin 閱讀(4349) 評論(6) 編輯收藏所屬分類: Oracle 開發

FeedBack:

# re: Oracle開發專題之：級聯查詢(Hierarchical Queries)

2008-06-15 14:43 | 藍劍

明白了，謝謝！回復更多評論