Start with...Connect By子句遞歸查詢一般用于一個表維護(hù)樹形結(jié)構(gòu)的應(yīng)用。
創(chuàng)建示例表：
CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME VARCHAR2(100 BYTE),
PID   NUMBER                                  DEFAULT 0
);

插入測試數(shù)據(jù)：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

從Root往樹末梢遞歸
select * from TBL_TEST
start with id=1
connect by prior id = pid

從末梢往樹ROOT遞歸
select * from TBL_TEST
start with id=5
connect by prior pid = id

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

有一張表   t  
字段：  
parent  
child  
兩個字段的關(guān)系是父子關(guān)系  
   
寫一個sql語句，查詢出指定父下面的所有的子  
   
比如  
   
a   b  
a   c    
a   e  
b   b1  
b   b2  
c   c1  
e   e1  
e   e3  
d   d1  
   
指定parent=a，選出  
a   b  
a   c    
a   e  
b   b1  
b   b2  
c   c1  
e   e1  
e   e3  
   
SQL語句：  
select   parent,child   from   test   start   with   parent='a'  
connect   by   prior   child=parent

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

connect by 是結(jié)構(gòu)化查詢中用到的，其基本語法是：
select ... from tablename start by cond1
connect by cond2
where cond3;
簡單說來是將一個樹狀結(jié)構(gòu)存儲在一張表里，比如一個表中存在兩個字段:
id,parentid那么通過表示每一條記錄的parent是誰，就可以形成一個樹狀結(jié)構(gòu)。
用上述語法的查詢可以取得這棵樹的所有記錄。
其中COND1是根結(jié)點(diǎn)的限定語句，當(dāng)然可以放寬限定條件，以取得多個根結(jié)點(diǎn)，實(shí)際就是多棵樹。
COND2是連接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。
COND3是過濾條件，用于對返回的所有記錄進(jìn)行過濾。

PRIOR和START WITH關(guān)鍵字是可選項(xiàng)
PRIORY運(yùn)算符必須放置在連接關(guān)系的兩列中某一個的前面。對于節(jié)點(diǎn)間的父子關(guān)系，PRIOR
運(yùn)算符在一側(cè)表示父節(jié)點(diǎn)，在另一側(cè)表示子節(jié)點(diǎn)，從而確定查找樹結(jié)構(gòu)是的順序是自頂向下還是
自底向上。在連接關(guān)系中，除了可以使用列名外，還允許使用列表達(dá)式。START WITH 子句為
可選項(xiàng)，用來標(biāo)識哪個節(jié)點(diǎn)作為查找樹型結(jié)構(gòu)的根節(jié)點(diǎn)。若該子句被省略，則表示所有滿足查詢
條件的行作為根節(jié)點(diǎn)。
完整的例子如SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START WITH PID =0 CONNECT BY PRIOR ID = PID

以上主要是針對上層對下層的順向遞歸查詢而使用start with ... connect by prior ...這種方式,但有時在需求需要的時候,可能會需要由下層向上層的逆向遞歸查詢,此是語句就有所變化:例如要實(shí)現(xiàn) select * from table where id in ('0','01','0101','0203','0304') ;現(xiàn)在想把0304的上一級03給遞歸出來,0203的上一級02給遞歸出來,而01現(xiàn)在已經(jīng)是存在的,最高層為0.而這張table不僅僅這些數(shù)據(jù),但我現(xiàn)在只需要('0','01','0101','0203','0304','02','03')這些數(shù)據(jù),此時語句可以這樣寫SELECT PID,ID,NAME FROM V_WF_WFKIND_TREE WHERE ID IN (SELECT DISTINCT(ID) ID FROM V_WF_WFKIND_TREE CONNECT BY PRIOR PID = ID START WITH ID IN ('0','01','0101','0203','0304') );

其中START WITH ID IN里面的值也可以替換SELECT 子查詢語句.

注意由上層向下層遞歸與下層向上層遞歸的區(qū)別在于START WITH...CONNECT BY PRIOR...的先后順序以及 ID = PID 和 PID = ID 的微小變化!

=============================================================
connect by prior start with 經(jīng)常會被用到一個表中存在遞歸關(guān)系的時候。比如我們經(jīng)常會將一個比較復(fù)雜的目錄樹存儲到一個表中。或者將一些部門存儲到一個表中，而這些部門互相有隸屬關(guān)系。這個時候你就會用到connect by prior start with。

典型的使用方法就是：
select * from table connect by prior cur_id=parent_id start with cur_id=???
例如：
a            b
1           0
2           1
3           1
4           2
5           3

如果想查找a=2及其下面的所有數(shù)據(jù)，則：
select * from table connect by prior a=b start with a=2
a           b
2           1
4           2

這些只是基礎(chǔ)，皮毛。其實(shí)只要你靈活的構(gòu)造查詢語句。可以得出意想不到的結(jié)果。比如生成樹每一個路徑。
但是這些記錄組成的樹必須正常才可以。如果有互為父子的情況，就會出現(xiàn)循環(huán)錯誤！

select * from tb_cus_area_cde

--子取父
select * from tb_cus_area_cde a   
CONNECT BY PRIOR     a.c_snr_area=a.c_area_cde START WITH a.c_area_cde='1040101'

--父取子
select * from tb_cus_area_cde a   
CONNECT BY PRIOR     a.c_area_cde=a.c_snr_area START WITH a.c_snr_area is null

注意：在用這個函數(shù)的時候，statement的參數(shù)要用 ResultSet.TYPE_SCROLL_INSENSITIVE   而不能用 ResultSet.TYPE_SCROLL_SENSITIVE,在這里再把這兩個之間的區(qū)別講講：

1.TYPE_FORWORD_ONLY,只可向前滾動；  
   
2.TYPE_SCROLL_INSENSITIVE,雙向滾動，但不及時更新，就是如果數(shù)據(jù)庫里的數(shù)據(jù)修改過，并不在ResultSet中反應(yīng)出來。  
   
3.TYPE_SCROLL_SENSITIVE，雙向滾動，并及時跟蹤數(shù)據(jù)庫的更新,以便更改ResultSet中的數(shù)據(jù)

======================================
10g樹形查詢特性CONNECT_BY_ISCYCLE

     在10g中Oracle提供了新的偽列：CONNECT_BY_ISCYCLE，通過這個偽列，可以判斷是否在樹形查詢的過程中構(gòu)成了循環(huán)，這個偽列只是在CONNECT BY NOCYCLE方式下有效。

　　這一篇描述一下解決問題的思路。

　　CONNECT_BY_ISCYCLE的實(shí)現(xiàn)和前面兩篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的實(shí)現(xiàn)完全不同。

　　因?yàn)橐獙?shí)現(xiàn)CONNECT_BY_ISCYCLE，就必須先實(shí)現(xiàn)CONNECT BY NOCYCLE，而在9i中是沒有方法實(shí)現(xiàn)這個功能的。

　　也就是說，首先要實(shí)現(xiàn)自己的樹形查詢的功能，而僅這第一點(diǎn)，就是一個異常困難的問題，何況后面還要實(shí)現(xiàn)NOCYCLE，最后再加上一個ISCYCLE的判斷。

　　所以總的來說，這個功能的實(shí)現(xiàn)比前面兩個功能要復(fù)雜得多。由于樹形查詢的LEVEL是不固定的，所以采用鏈接的方式實(shí)現(xiàn)，基本上是不現(xiàn)實(shí)的。換句話說，用純SQL的方式來實(shí)現(xiàn)樹形查詢的功能基本上不可行。而為了解決這個功能，只能通過PL/SQL配合SQL來實(shí)現(xiàn)。

　　仍然是首先構(gòu)造一個例子：

SQL> CREATE TABLE T_TREE (ID NUMBER, FATHER_ID NUMBER, NAME VARCHAR2(30));
　　表已創(chuàng)建。
　　SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (0, 0, 'ROOT');
　　已創(chuàng)建 1 行。
　　SQL> INSERT INTO T_TREE VALUES (4, 7, 'FG');
　　已創(chuàng)建 1 行。
　　SQL> COMMIT;
　　提交完成。
　　SQL> SELECT * FROM T_TREE;
　　ID FATHER_ID NAME
　　---------- ---------- ------------------------------
　　1 0 A
　　2 1 BC
　　3 1 DE
　　4 1 FG
　　5 2 HIJ
　　6 4 KLM
　　7 6 NOPQ
　　0 0 ROOT
　　4 7 FG
　　已選擇9行。

　　上面構(gòu)造了兩種樹形查詢循環(huán)的情況，一種是當(dāng)前記錄的自循環(huán)，另一種是樹形查詢的某個子節(jié)點(diǎn)是當(dāng)前節(jié)點(diǎn)的祖先節(jié)點(diǎn)，從而構(gòu)成了循環(huán)。在這個例子中，記錄ID為0和ID為4且FATHER_ID等于7的兩條記錄分別構(gòu)成了上述的兩種循環(huán)的情況。

　　下面就來看看CONNECT_BY_ISCYCLE和CONNECT BY NOCYCLE的功能：

SQL> SELECT *
　　2 FROM T_TREE
　　3 START WITH ID = 0
　　4 CONNECT BY PRIOR ID = FATHER_ID;
　　ERROR:
　　ORA-01436: 用戶數(shù)據(jù)中的 CONNECT BY 循環(huán)
　　未選定行
　　SQL> SELECT *
　　2 FROM T_TREE
　　3 START WITH ID = 1
　　4 CONNECT BY PRIOR ID = FATHER_ID;
　　ERROR:
　　ORA-01436: 用戶數(shù)據(jù)中的 CONNECT BY 循環(huán)
　　未選定行

　　這就是不使用CONNECT BY NOCYCLE的情況，查詢會報(bào)錯，指出樹形查詢中出現(xiàn)循環(huán)，在10g中可以使用CONNECT BY NOCYCLE的方式來避免錯誤的產(chǎn)生：

SQL> SELECT *
　　2 FROM T_TREE
　　3 START WITH ID = 0
　　4 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;
　　ID FATHER_ID NAME
　　---------- ---------- ------------------------------
　　0 0 ROOT
　　1 0 A
　　2 1 BC
　　5 2 HIJ
　　3 1 DE
　　4 1 FG
　　6 4 KLM
　　7 6 NOPQ
　　已選擇8行。

　　使用CONNECT BY NOCYCLE，Oracle自動避免循環(huán)的產(chǎn)生，將不產(chǎn)生循環(huán)的數(shù)據(jù)查詢出來，下面看看CONNECT_BY_ISCYCLE的功能：

SQL> SELECT ID,
　　2 FATHER_ID,
　　3 NAME,
　　4 CONNECT_BY_ISCYCLE CYCLED
　　5 FROM T_TREE
　　6 START WITH ID = 0
　　7 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;
　　ID FATHER_ID NAME CYCLED
　　---------- ---------- ------------------------------ ----------
　　0 0 ROOT 1
　　1 0 A 0
　　2 1 BC 0
　　5 2 HIJ 0
　　3 1 DE 0
　　4 1 FG 0
　　6 4 KLM 0
　　7 6 NOPQ 1
　　已選擇8行。

　　可以看到，CONNECT_BY_ISCYCLE偽列指出循環(huán)在樹形查詢中發(fā)生的位置。

　　為了實(shí)現(xiàn)CONNECT_BY_ISCYCLE就必須先實(shí)現(xiàn)CONNECT BY NOCYCLE方式，而這在9i中是沒有現(xiàn)成的辦法的，所以這里嘗試使用PL/SQL來自己實(shí)現(xiàn)樹形查詢的功能。

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 AS
　　2 V_STR VARCHAR2(32767) := '/' || P_VALUE;
　　3
　　4 PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2, P_STR IN OUT VARCHAR2) AS
　　5 BEGIN
　　6 FOR I IN (SELECT ID FROM T_TREE WHERE FATHER_ID = P_FATHER AND FATHER_ID != ID) LOOP
　　7 IF INSTR(P_STR || '/', '/' || I.ID || '/') = 0 THEN
　　8 P_STR := P_STR || '/' || I.ID;
　　9 P_GET_CHILD_STR(I.ID, P_STR);
　　10 END IF;
　　11 END LOOP;
　　12 END;
　　13 BEGIN
　　14 P_GET_CHILD_STR(P_VALUE, V_STR);
　　15 RETURN V_STR;
　　16 END;
　　17 /

　　函數(shù)已創(chuàng)建。

　　構(gòu)造一個函數(shù)，在函數(shù)中遞歸調(diào)用過程來實(shí)現(xiàn)樹形查詢的功能。

　　下面看看調(diào)用這個函數(shù)的結(jié)果：　

SQL> SELECT F_FIND_CHILD(0) FROM DUAL;
　　F_FIND_CHILD(0)
　　------------------------------------------------
　　/0/1/2/5/3/4/6/7
　　SQL> SELECT F_FIND_CHILD(2) FROM DUAL;
　　F_FIND_CHILD(2)
　　------------------------------------------------
　　/2/5
　　SQL> SELECT F_FIND_CHILD(4) FROM DUAL;
　　F_FIND_CHILD(4)
　　------------------------------------------------
　　/4/6/7

　　雖然目前存在的問題還有很多，但是已經(jīng)基本上實(shí)現(xiàn)了一個最簡單的NOCYCLE的SYS_CONNECT_BY_PATH的功能。

　　有了這個函數(shù)作為基礎(chǔ)，就可以逐步的實(shí)現(xiàn)最終的目標(biāo)了。