ORACLE中CONNECT BY...START WITH...的使用

標籤：

轉載：http://www.cnblogs.com/baiyixianzi/archive/2012/08/30/plsql12.html

一、文法 大致寫法：select * from some_table [where 條件1] connect by [條件2] start with [條件3]; 其中 connect by 與 start with 語句擺放的先後順序不影響查詢的結果，[where 條件1]可以不需要。 [where 條件1]、[條件2]、[條件3]各自作用的範圍都不相同： [where 條件1]是在根據“connect by [條件2] start with [條件3]”選擇出來的記錄中進行過濾，是針對單條記錄的過濾， 不會考慮樹的結構； [條件2]指定構造樹的條件，以及對樹分支的過濾條件，在這裡執行的過濾會把合格記錄及其下的所有子節點都過濾掉； [條件3]限定作為搜尋起始點的條件，如果是自上而下的搜尋則是限定作為根節點的條件，如果是自下而上的搜尋則是限定作為葉子節點的條件； 樣本： 假如有如下結構的表：some_table(id,p_id,name)，其中p_id儲存父記錄的id。 select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66; 對prior的說明：     prior存在於[條件2]中，可以不要，不要的時候只能尋找到符合“start with [條件3]”的記錄，不會在尋找這些記錄的子節點。要的時候有兩種寫法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一種寫法表示採用自上而下的搜尋方式（先找父節點然後找子節點），後一種寫法表示採用自下而上的搜尋方式（先找葉子節點然後找父節點）。 二、執行原理 connect by...start with...的執行原理可以用以下一段程式的執行以及對預存程序RECURSE()的調用來說明： /* 遍曆表中的每條記錄，對比是否滿足start with後的條件，如果不滿足則繼續下一條， 如果滿足則以該記錄為根節點，然後調用RECURSE()遞迴尋找該節點下的子節點， 如此迴圈直到遍曆完整個表的所有記錄 。*/ for rec in (select * from some_table) loop if FULLFILLS_START_WITH_CONDITION(rec) then     RECURSE(rec, rec.child); end if; end loop; /* 尋找子節點的預存程序*/ procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is begin APPEND_RESULT_LIST(rec); /*把記錄加入結果集合中*/ /*再次遍曆表中的所有記錄，對比是否滿足connect by後的條件，如果不滿足則繼續下一條， 如果滿足則再以該記錄為根節點，然後調用RECURSE()繼續遞迴尋找該節點下的子節點， 如此迴圈直到找至葉子節點。*/ for rec_recurse in (select * from some_table) loop     if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then       RECURSE(rec_recurse,rec_recurse.child);     end if; end loop; end procedure RECURSE; 三、使用探討     從上面的執行原理可以看到“connect by...start with...”構造樹的方式是：（1）如果是自上而下方式，則把表中的每一條記錄都作為根節點來產生樹，所以表中有多少條記錄就會構造出多少棵樹。（2）如果是自下而上的搜尋方式，則把表中的每一條記錄都作為葉子節點來產生分支，所以表中有多少條記錄就會產生多少條分支。     因此如果表中的記錄不是嚴格遵照每條記錄都只能有一個父記錄的原則，那麼就可能有部分記錄會存在於多棵樹中，那麼在尋找記錄的時候就可能會出現找到多條重複記錄的異常情況--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses --Hierarchical Queries--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_nameFROM employee_jhSTART WITH employee_id = 1CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME----------- ---------- ---------- ----------          1          0 James      Smith          2          1 Ron        Johnson          3          2 Fred       Hobbs          5          2 Rob        Green          4          1 Susan      Jones          6          4 Jane       Brown          9          6 Henry      Heyson          7          4 John       Grey          8          7 Jean       Blue         10          1 Kevin      Black         11         10 Keith      Long         12         10 Frank      Howard         13         10 Doreen     Penn13 rows selected.--2.Using a Subquery in a START WITH ClauseSELECT LEVEL,       LPAD(‘ ‘, 2 * LEVEL - 1) || first_name || ‘ ‘ || last_name AS employeeFROM employee_jhSTART WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = ‘Kevin‘ AND last_name = ‘Black‘)CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE---------- -------------------------         1  Kevin Black         2    Keith Long         2    Frank Howard         2    Doreen Penn--3.Including Other Conditions in a Hierarchical QuerySELECT LEVEL,       LPAD(‘ ‘, 2 * LEVEL - 1) || first_name || ‘ ‘ ||       last_name AS employee, salaryFROM employee_jhWHERE salary <= 50000START WITH employee_id = 1CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE                      SALARY---------- ------------------------- ----------         3      Rob Green                 40000         3      Jane Brown                45000         4        Henry Heyson            30000         3      John Grey                 30000         4        Jean Blue               29000         3      Keith Long                50000         3      Frank Howard              45000         3      Doreen Penn               470008 rows selected.



原文： http://zhidao.baidu.com/question/219362803.html

ORACLE中CONNECT BY...START WITH...的使用