Oracle Connect By Prior用法 摘要： 1、connect by 中的條件就表示了父子之間的連接關系 ，比如 connect by id=prior pid。 2、 prior，表示在一表上，prior 所在列pid的某一A的記錄行的父親是列id上等于列pid上A的記錄行。 3、 LEVEL 偽列 表示樹的深度（

Oracle Connect By Prior用法

摘要：

1、connect by中的條件就表示了父子之間的連接關系，比如 connect by id=prior pid。

2、prior，表示在一表上，prior所在列pid的某一值A的記錄行的父親是列id上值等于列pid上值A的記錄行。

3、LEVEL偽列表示樹的深度（或叫高度）。

oracle中的select語句可以用START WITH...CONNECT BYPRIOR子句實現遞歸查詢（或叫樹狀查詢），connect by 是結構化查詢中用到的，

其基本語法是：

　　select ... from

　　where

　　start with

　　connect by

　　;

　　：過濾條件，用于對返回的所有記錄進行過濾。

　　：該限定條件，表示查詢結果以誰作為起始根結點的。當然可以放寬該限定條件，以取得多個根結點，實際就是多棵樹。

　　：連接條件，即表示不同節點間以該連接條件構成一個父子關系

或說是

1 2	[ START WITH condition ] CONNECT BY [ NOCYCLE ] condition

或說

其中 connect by 與 start with 語句擺放的先后順序不影響查詢的結果，[where 條件1]可以不需要，若是出現則要放在 connect by 與 start with 語句之前，否則出錯。

[where 條件1]、[條件2]、[條件3]各自作用的范圍都不相同：

[where 條件1]

是在根據“connect by [條件2] start with [條件3]”選擇出來的記錄中進行過濾，是針對單條記錄的過濾， 不會考慮樹的結構（最后的過濾）；

[ connect by 條件2]

指定構造樹的條件，以及對樹分支的過濾條件，在這里執行的過濾會把符合條件的記錄及其下的所有子節點都過濾掉；

[ start with 條件3]

限定作為搜索起始點的條件，如果是自上而下的搜索則是限定作為根節點的條件，如果是自下而上的搜索則是限定作為葉子節點的條件；

要根據connect by 從上到下還是從下到上，來確定起始節點，可能是葉節點，也可能是父節點，這些開始節點可以是多個，并且包含這些節點。

oracle 11g r2貌似不支持從下到上的遍歷？？

oracle 10g支持從下到上的遍歷？？

【oracle 的 start with ... connect by ...】

注釋：

0、

層級查詢語句（hierarchical query）中，where子句先被執行，再執行CONNECT BY子句以及其附屬子句。

LEVEL 偽列表示樹的深度（或叫高度）。

使用LEVEL偽列：

在具有樹結構的表中，每一行數據都是樹結構中的一個節點，由于節點所處的層次位置不同，所以每行記錄都可以有一個層號。層號根據節點與根節點的距離確定。不論從哪個節點開始，該起始根節點的層號始終為1，根節點的子節點為2， 依此類推。

1、這里說的節點指的是層級查詢語句（hierarchical query）中from子句里的表的每一數據行。

2、層級查詢語句（hierarchical query）中，CONNECT BY子句是必選的，而START WITH子句是可選的，START WITH子句是用來修飾CONNECT BY子句的。

3、prior關鍵字放在CONNECT BY子句中。其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR id = parent_id就是說上一條記錄的id 是本條記錄的parent_id，即本記錄的父親是上一條記錄。

例如，

connect by parent_id =prior id

表示的是一數據行中列名為parent_id的列上的值為該行的父節點的編號值，而

父節點的編號值都是來源于表中列名為id的列上的各個值。

總之，prior放在connect by子句連接條件里的哪一邊，哪一邊就是父節點的編號值的來源，而connect by子句連接條件里等號另一邊就是記錄一數據行其對應的父節點的編號值。

4、

START WITH 子句為可選項，用來標識哪個節點作為查找樹型結構的根節點。若該子句被省略，則表示所有滿足查詢條件（即where子句里的條件）的行作為根節點。具體例子見下文二大點里的注釋。

START WITH： 不但可以指定一個根節點，還可以指定多個根節點。

“START WITH 子句被省略，則表示所有滿足查詢條件（即where子句里的條件）的行作為根節點”這個特性

可以用來證明

”層級查詢語句（hierarchical query）中，where子句先被執行，再執行CONNECT BY子句以及其附屬子句“。

5、

START WITH 子句和CONNECT BY子句是兩個相互獨立的子句，即并沒有規定START WITH 子句出現的列就是要為CONNECT BY子句里那個帶有關鍵字prior的列，START WITH 子句出現的列可以來自表里的任何列，也就是說START WITH 子句出現的列可以沒有在START WITH 子句里出現，因為START WITH 子句的作用就是根據START WITH 子句的限定條件來篩選出哪些數據行作為根節點而已。例子，

select * from t2 start with id = 1 connect by prior id= root_id;

select * from t2 start with root_id = 0 connect by prior id = root_id;

6、

層級查詢語句（hierarchical query），

Oracle采用了自上而下的深度優先的算法。

下面以一個具體例子來說明層級查詢語句（hierarchical query）connect by 用法。

現在有一個表t2，

其上各個數據行間的父子關系的結構圖（即數據組織結構圖）如下：

表t2的表結構如下：

　　create table t2(

　　root_id number,

　　id number,

　　name varchar(5),

　　description varchar(10)

　　);

　　insert into t2(root_id,id,name,description)values(0,1,'a','aaa');

　　insert into t2(root_id,id,name,description)values(1,2,'a1','aaa1');

　　insert into t2(root_id,id,name,description)values(1,3,'a2','aaa2');

　　insert into t2(root_id,id,name,description)values(0,4,'b','bbb');

　　insert into t2(root_id,id,name,description)values(4,5,'b1','bbb1');

　　insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');

一、

獲取完整樹：

　　select * from t2 start withroot_id = 0connect by prior id = root_id;

注釋：

簡單說來是將一個樹狀結構存儲在一張表里，比如一個表中存在兩個字段: root_id,id。那么通過表示每一條記錄的父記錄是誰，就可以形成一個樹狀結構。
用上述語法的查詢可以取得這棵樹的所有記錄。

上述SQL語句具體執行過程分析：

1） 首先，執行的是該select語句中的start with子句部分，具體地說，就是執行該select語句的服務器進程先從表t2中找出所有 root_id字段的值為 0的數據行。在上述例子中，就是name字段分別為a和b的兩條數據行，它們兩構成了上圖中樹的第一層。

2）接著，執行的是該select語句中的connect by子句部分，具體地說，就是執行該select語句的服務器進程先從表t2剩下的數據行（即除去了已經被選中的那些數據行）中找出所有root_ id字段的值等于在上圖的樹中位于其上一層中各個數據行的id字段的值的數據行。在上述例子中，上圖中樹的第一層里name字段為a的數據行的id字段值為1，所以服務器進程就從表t2剩下的數據行（即出去了上圖中樹的第一層里的那兩條數據行）中找出所有root_ id字段的值等于1的數據行，即name字段分別為a1和a2的兩條數據行；上圖中樹的第一層里name字段為b的數據行的id字段值為4，所以服務器進程就從表t2剩下的數據行（即出去了上圖中樹的第一層里的那兩條數據行以及前面剛被選中的兩條數據行）中找出所有root_ id字段的值等于4的數據行，即name字段分別為b1和b2的兩條數據行。于是，name字段分別為a1和a2，b1和b2的四條數據行，構成了上圖中樹的第二層。

3)再接著，服務器進程還是要重復執行該select語句中的connect by子句部分，直到表中不存在符合prior id = root_id這個條件的數據行為止。

此時，服務器進程發現表t2中剩余的數據行為空了。

這樣，該select語句整個過程就執行完畢了。如果表t2中還是有剩余的數據行的，則服務器進程重復執行2）步驟。

注釋：

1）In a hierarchicalquery, one expression incondition must be qualifiedwith thePRIOR operator to refer to the parentrow. For example,

... PRIOR expr = expr

or

... expr = PRIOR expr

譯文：

在 層次查詢（或叫遞歸查詢）中，connect by子句的條件里的表達式必須要有prior運算符 放在一個字段的前面來表示該字段是父數據行（（即樹結構圖中位于子數據行所在層的直接上一層里的數據行））的該字段。例如，

... PRIOR expr = expr

or

... expr = PRIOR expr

也就是說，當connect by子句的條件里出現的字段是普通字段，不是偽列字段rownum或是level時，connect by子句的條件里的表達式必須要有prior運算符 放在一個字段的前面；當connect by子句的條件里出現的是偽列字段rownum或是level時，connect by子句的條件里的表達式不用出現prior運算符。

還有一點要說明的是，start with子句不是一個獨立的子句，即start with子句是一個不能單獨出現在SQL語句的子句，必須在SQL語句中出現了connect by子句后，才能出現。換句話說，start with子句是來配合connect by子句的。例如，

SQL> select * from t2 start with id = 1;

select * from t2 start withid = 1

*

第 1 行出現錯誤:

ORA-01788: 此查詢塊中要求 CONNECT BY 子句

附加：其他依賴于CONNECT BY 子句的偽列，如level、CONNECT_BY_ISCYCLE等也會有相同的提示，若是獨立出現的話。

當connect by子句沒有帶start with子句時，例如，select * from t2 connect by prior id = root_id，則因為帶prior的id字段是父數據行的字段，所以執行該select語句的服務器進程先從表t2中找出所有 id字段值非空的數據行（差不多就是表t2的所有數據行），它們構成了樹結構的第一層。接著，執行該select語句的服務器進程從表t2中找出所有root_ id字段的值等于在樹結構的第一層中各個數據行的id字段值的數據行，它們構成了樹結構的第二層。再接著，服務器進程從表t2中找出所有root_ id字段的值等于在樹結構的第二層中各個數據行的id字段值的數據行。就這樣，一直重復執行connect by prior id = root_id這一個子句，直到表中不存在符合prior id = root_id這個條件的數據行為止。

下面是該SQL語句的執行結果：

SQL> select t2.*,level from t2 connect by prior id = root_id;

LEVEL ID ROOT_ID NAME DESCRIPTIO

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

1 1 0 a aaa

1 2 1 a1 aaa1

1 3 1 a2 aaa2

1 4 0 b bbb

1 5 4 b1 bbb1

1 6 4 b2 bbb2

2 2 1 a1 aaa1

2 3 1 a2 aaa2

2 5 4 b1 bbb1

2 6 4 b2 bbb2

已選擇10行。

注釋：

ID表示樹狀結構圖中各個節點的編號值。

LEVEL偽列表示樹的深度（或叫高度）。

當connect by子句帶有start with子句時，例如，select * from t2 start with root_id = 0 connect by prior id = root_id，則執行該select語句的服務器進程首先執行的是該select語句中的start with子句部分。start with子句部分的功能就是讓服務器進程根據start with子句里的字段找到樹結構的第一層里所有的數據行。找完第一層的數據行后，服務器進程執行的操作和connect by子句沒帶有start with子句時的操作就是一樣的了，也是重復執行connect by prior id = root_id這一個子句，直到表中不存在符合prior id = root_id這個條件的數據行為止。

SQL>select * from t2 start with root_id = 0 connect by prior id = root_id;

LEVEL ID ROOT_ID NAME DESCRIPTIO

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

1 1 0 a aaa

1 4 0 b bbb

1 5 4 b1 bbb1

1 6 4 b2 bbb2

2 2 1 a1 aaa1

2 3 1 a2 aaa2

已選擇6行。

雖然這個SQL語句中start with子句里的字段和connect by子句里的帶prior的字段不一樣，但是不會相互影響的。

2）在connect by 子句里出現的兩個字段都是同一個表里的兩個字段。

3） “執行該select語句的服務器進程先從表t2剩下的數據行（即除去了已經被選中的那些數據行）中查找數據行“這樣的表述是不正確的。其實，每次服務器進程都是從表t2中的所有的數據行上查找的。例如，

SQL> select t2.*,level from t2 connect by prior id = root_id;

ROOT_ID ID NAME DESCRIPTIO LEVEL

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

0 1 a aaa 1

1 2 a1 aaa1 1

1 3 a2 aaa2 1

0 4 b bbb 1

4 5 b1 bbb1 1

4 6 b2 bbb2 1

1 2 a1 aaa1 2

1 3 a2 aaa2 2

4 5 b1 bbb1 2

4 6 b2 bbb2 2

已選擇10行。

從上面的例子可以看出，處于樹結構第二層（level=2）的數據行也在處于樹結構第一層（level=1）中出現。這就說明了，每次服務器進程都是從表t2中的所有的數據行上查找的。

4）當表中有重復行時，這些重復行不會合并在一起，而是單獨算的。例如，

SQL> insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');

已創建 1 行。

SQL> select t22.*,level from t2 connect by prior id = root_id order bylevel,i

d;

ROOT_ID ID NAME DESCRIPTIO LEVEL

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

0 1 a aaa 1

1 2 a1 aaa1 1

1 3 a2 aaa2 1

0 4 b bbb 1

4 5 b1 bbb1 1

4 6 b2 bbb2 1

4 6 b2 bbb2 1

1 2 a1 aaa1 2

1 3 a2 aaa2 2

4 5 b1 bbb1 2

4 6 b2 bbb2 2

4 6 b2 bbb2 2

已選擇12行。

二、

獲取特定子樹：

select * from t2 start with id = 1 connect by prior id= root_id;

select * from t2 start with id = 4 connect by prior id = root_id;

如果connect by prior中的prior被省略，則查詢將不進行深層遞歸。如：

select * from t2 start with root_id = 0 connect by id = root_id;//此時 start with子句還是有被執行的



select * from t2 start with id = 1 connect by id = root_id;如：

注釋：

上述兩個例子如果connect by prior中的關鍵字prior被省略，且在connect by 里的字段不是偽列rownum或是偽列level（偽列level只有在SQL語句中出現了connect by 子句時才有的），那么connect by子句會被忽略不被執行的（此時 start with子句還是有被執行的）。

參考：

http://www.360doc.com/content/11/0608/11/5287961_122421161.shtml

http://www.cnblogs.com/einyboy/archive/2012/08/01/2617939.html

http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm

Hierarchical Queries

http://www.cnblogs.com/zhf/archive/2008/09/10/1288101.html

另見：

深入理解connect by rownum

==================================================================

附加

Oracle “CONNECT BY” 使用

Oracle “CONNECT BY”是層次查詢子句，一般用于樹狀或者層次結果集的查詢。其語法是：

1 2	[ START WITHcondition ] CONNECT BY [ NOCYCLE ] condition

The start with .. connect by clause can be used toselect data that has a hierarchical relationship (usually some sort ofparent->child (boss->employee or thing->parts).

說明：
1. START WITH：告訴系統以哪個節點作為根結點開始查找并構造結果集，該節點即為返回記錄中的最高節點。
2. 當分層查詢中存在上下層互為父子節點的情況時，會返回ORA-01436錯誤。此時，需要在connect by后面加上NOCYCLE關鍵字。同時，可用connect_by_iscycle偽列定位出存在互為父子循環的具體節點。 connect_by_iscycle必須要跟關鍵字NOCYCLE結合起來使用，也就說，connect_by_iscycle偽列是一個不能單獨出現在SQL語句的偽列，必須在SQL語句中出現了關鍵字NOCYCLE后，才能出現。例如，

select connect_by_iscycle from t2 start with root_id = 0 connect byNOCYCLE prior id = root_id;

接下來，用一些示例來說明“CONNECT BY”的用法。

[例1]

創建一個部門表，這個表有三個字段，分別對應部門ID，部門名稱，以及上級部門ID

1 2 3 4 5 6 7 8

-- Create table create table DEP ( DEPID number(10)notnull, DEPNAME varchar2(256), UPPERDEPID number(10) ) ;

初始化一些數據

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(0, '總經辦',null); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(1, '開發部', 0); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(2, '測試部', 0); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(3, 'Sever開發部', 1); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(4, 'Client開發部', 1); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(5, 'TA測試部', 2); 1 row inserted SQL> INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(6, '項目測試部', 2); 1 row inserted SQL> commit; Commit complete

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> SELECT*FROM DEP; DEPID DEPNAME UPPERDEPID ----------- -------------------------------------------------------------------------------- ----------- 0 General Deparment 1 Development 0 2 QA 0 3 Server Development 1 4 Client Development 1 5 TA 2 6 Porject QA 2 7 rowsselected

現在我要根據“CONNECT BY”來實現樹狀查詢結果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> SELECTRPAD(' ', 2*(LEVEL-1),'-') || DEPNAME "DEPNAME", CONNECT_BY_ROOT DEPNAME"ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL , SYS_CONNECT_BY_PATH(DEPNAME,'/')"PATH" FROM DEP START WITHUPPERDEPIDIS NULL CONNECT BY PRIOR DEPID = UPPERDEPID; DEPNAME ROOT ISLEAF LEVELPATH ------------------------------ ------------------- ---------- ---------- -------------------------------------------------------------------------------- General Deparment General Deparment 0 1 /General Deparment -Development General Deparment 0 2 /General Deparment/Development ---Server Development General Deparment 1 3 /General Deparment/Development/Server Development ---Client Development General Deparment 1 3 /General Deparment/Development/Client Development -QA General Deparment 0 2 /General Deparment/QA ---TA General Deparment 1 3 /General Deparment/QA/TA ---Porject QA General Deparment 1 3 /General Deparment/QA/Porject QA 7 rowsselected

說明：
1. CONNECT_BY_ROOT （偽列）返回當前節點的最頂端節點
2. CONNECT_BY_ISLEAF （偽列）判斷是否為葉子節點，如果這個節點下面有子節點，則不為葉子節點
3. LEVEL 偽列表示樹的深度（或叫高度）
4. SYS_CONNECT_BY_PATH函數顯示詳細路徑，并用“/”分隔

CONNECT BY的應用例子

[例2]

通過CONNECT BY用于十六進度轉換為十進制

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS ---------------------------------------------------------------------------------------------------------------------- -- 對象名稱: f_hex_to_dec -- 對象描述: 十六進制轉換十進制 -- 輸入參數: p_str 十六進制字符串 -- 返回結果: 十進制字符串 -- 測試用例: SELECT f_hex_to_dec('78A') FROM dual; ---------------------------------------------------------------------------------------------------------------------- v_return VARCHAR2(4000); BEGIN SELECT SUM(DATA) INTO v_return FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE substr(p_str, rownum, 1) END) * power(16, length(p_str) - rownum) DATA FROM dual CONNECT BY rownum <= length(p_str)); RETURN v_return; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;

說明：

1. CONNECT BY rownum <= length(p_str))對輸入的字符串進行逐個遍歷

2. 通過CASE語句，來解析十六進制中的A-F對應的10進制值

[例3]

通過CONNECT BY生成序列

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10; ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected

本例子的具體詳解見：《深入理解connect byrownum

參考：

http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

http://www.blogjava.net/freeman1984/archive/2011/05/06/349668.html

connect by prior

百度 connect by prior

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com