oracle中connect by prior實現遞迴查詢

收集的幾條在oracle中通過connect by prior來實現遞迴查詢

Start with...Connect By子句遞迴查詢一般用於一個表維護樹形結構的應用。

建立示例表：

CREATE TABLE TBL_TEST

(

ID    NUMBER,

NAME VARCHAR2(100 BYTE),

PID   NUMBER                                  DEFAULT 0

);

插入測試資料：

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 

兩個欄位的關係是父子關係 

  

寫一個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 是結構化查詢中用到的，其基本語法是：

select ... from tablename start by cond1

connect by cond2

where cond3;

簡單說來是將一個樹狀結構儲存在一張表裡，比如一個表中存在兩個欄位:

id,parentid那麼通過表示每一條記錄的parent是誰，就可以形成一個樹狀結構。

用上述語法的查詢可以取得這棵樹的所有記錄。

其中COND1是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。

COND2是連線條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。

COND3是過濾條件，用於對返回的所有記錄進行過濾。

PRIOR和START WITH關鍵字是可選項

PRIORY運算子必須放置在連線關係的兩列中某一個的前面。對於節點間的父子關係，PRIOR

運算子在一側表示父節點，在另一側表示子節點，從而確定查詢樹結構是的順序是自頂向下還是

自底向上。在連線關係中，除了可以使用列名外，還允許使用列表示式。START WITH 子句為

可選項，用來標識哪個節點作為查詢樹型結構的根節點。若該子句被省略，則表示所有滿足查詢

條件的行作為根節點。

完整的例子如SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START WITH PID =0 CONNECT BY PRIOR ID = PID

以上主要是針對上層對下層的順向遞迴查詢而使用start with ... connect by prior ...這種方式,但有時在需求需要的時候,可能會需要由下層向上層的逆向遞迴查詢,此是語句就有所變化:例如要實現 select * from table where id in ('0','01','0101','0203','0304') ;現在想把0304的上一級03給遞迴出來,0203的上一級02給遞迴出來,而01現在已經是存在的,最高層為0.而這張table不僅僅這些資料,但我現在只需要('0','01','0101','0203','0304','02','03')這些資料,此時語句可以這樣寫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 子查詢語句.

注意由上層向下層遞迴與下層向上層遞迴的區別在於START WITH...CONNECT BY PRIOR...的先後順序以及 ID = PID 和 PID = ID 的微小變化!

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

connect by prior start with 經常會被用到一個表中存在遞迴關係的時候。比如我們經常會將一個比較複雜的目錄樹儲存到一個表中。或者將一些部門儲存到一個表中，而這些部門互相有隸屬關係。這個時候你就會用到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及其下面的所有資料，則：

select * from table connect by prior a=b start with a=2

a           b

2           1

4           2

這些只是基礎，皮毛。其實只要你靈活的構造查詢語句。可以得出意想不到的結果。比如生成樹每一個路徑。

但是這些記錄組成的樹必須正常才可以。如果有互為父子的情況，就會出現迴圈錯誤！

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

注意：在用這個函式的時候，statement的引數要用 ResultSet.TYPE_SCROLL_INSENSITIVE   而不能用 ResultSet.TYPE_SCROLL_SENSITIVE,在這裡再把這兩個之間的區別講講：

1.TYPE_FORWORD_ONLY,只可向前滾動； 

  

2.TYPE_SCROLL_INSENSITIVE,雙向滾動，但不及時更新，就是如果資料庫裡的資料修改過，並不在ResultSet中反應出來。 

  

3.TYPE_SCROLL_SENSITIVE，雙向滾動，並及時跟蹤資料庫的更新,以便更改ResultSet中的資料

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

10g樹形查詢特性CONNECT_BY_ISCYCLE

     在10g中Oracle提供了新的偽列：CONNECT_BY_ISCYCLE，通過這個偽列，可以判斷是否在樹形查詢的過程中構成了迴圈，這個偽列只是在CONNECT BY NOCYCLE方式下有效。

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

　　CONNECT_BY_ISCYCLE的實現和前面兩篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的實現完全不同。

　　因為要實現CONNECT_BY_ISCYCLE，就必須先實現CONNECT BY NOCYCLE，而在9i中是沒有方法實現這個功能的。

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

　　所以總的來說，這個功能的實現比前面兩個功能要複雜得多。由於樹形查詢的LEVEL是不固定的，所以採用連結的方式實現，基本上是不現實的。換句話說，用純SQL的方式來實現樹形查詢的功能基本上不可行。而為了解決這個功能，只能通過PL/SQL配合SQL來實現。

　　仍然是首先構造一個例子：

SQL> CREATE TABLE T_TREE (ID NUMBER, FATHER_ID NUMBER, NAME VARCHAR2(30));

　　表已建立。

　　SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (0, 0, 'ROOT');

　　已建立 1 行。

　　SQL> INSERT INTO T_TREE VALUES (4, 7, 'FG');

　　已建立 1 行。

　　SQL> COMMIT;

　　提交完成。

　　SQL> SELECT * FROM T_TREE;

　　ID FATHER_ID NAME

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

　　1 0 A

　　2 1 BC

　　3 1 DE

　　4 1

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-622022/，如需轉載，請註明出處，否則將追究法律責任。