Oracle中可以透過START WITH . . . CONNECT BY . . .子句來實現SQL的層次查詢.
自從Oracle 9i開始，可以透過 SYS_CONNECT_BY_PATH 函式實現將父節點到當前行內容以“path”或者層次元素列表的形式顯示出來。

自從Oracle 10g 中，還有其他更多關於層次查詢的新特性 。例如，有的時候使用者更關心的是每個層次分支中等級最低的內容。
那麼你就可以利用偽列函式CONNECT_BY_ISLEAF來判斷當前行是不是葉子。如果是葉子就會在偽列中顯示“1”，
如果不是葉子而是一個分支（例如當前內容是其他行的父親）就顯示“0”。

在Oracle 10g 之前的版本中，如果在你的樹中出現了環狀迴圈（如一個孩子節點引用一個父親節點），
Oracle 就會報出一個錯誤提示：“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對父親的引用就無法執行查詢操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以進行任意的查詢操作。與這個關鍵字相關的還有一個偽列——CONNECT_BY_ISCYCLE，
如果在當前行中引用了某個父親節點的內容並在樹中出現了迴圈，那麼該行的偽列中就會顯示“1”，否則就顯示“0”。

The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts). 
It is also being used when an sql execution plan is explained. 

syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition

level
With level it is possible to show the level in the hierarchical relation of all the data.

--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.

--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

下面舉例說明：
[例1]建立一個部門表，這個表有4個欄位，分別對應部門ID，部門名稱，上級部門ID ，本部門直屬員工人數。
create table DEP
(  
DEPID      number(10) not null,  
DEPNAME    varchar2(32),  
UPPERDEPID number(10),
NUMOFEMP   number(10)
) ;
下面插入一些資料，結果如下：

[sql] view plaincopy

 

1 hr@MYTEST2> select * from dep;  

2   

3      DEPID DEPNAME         UPPERDEPID   NUMOFEMP  

4 ---------- --------------- ---------- ----------  

5          0 Dev Center                          2  

6          1 DevA                     0          3  

7          2 DevA Team1               1         10  

8          3 DevA Team2               1          8  

9          4 DevB                     0          2  

10          5 DevB Team1               4         12  

11          6 Test Center                         1  

12          7 Test Team1               6          5  

13          8 Test Team2               6          5  

14   

15 9 rows selected.  

現在根據“CONNECT BY”來實現樹狀查詢：

[sql] view plaincopy

 

16 column depname format a15;  

17 column rootdep format a15;  

18 column path format a30;  

19   

20 select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",  

21 connect_by_root depname "ROOTDEP",  

22 connect_by_isleaf "ISLEAF",  

23 level,  

24 sys_connect_by_path(depname, '/') "PATH"  

25 from dep  

26 start with upperdepid is null  

27 connect by prior depid = upperdepid  

28 /  

結果如下：

[sql] view plaincopy

 

29 DEPNAME         ROOTDEP             ISLEAF      LEVEL PATH  

30 --------------- --------------- ---------- ---------- ---------------------------  

31 Dev Center      Dev Center               0          1 /Dev Center  

32  -DevA          Dev Center               0          2 /Dev Center/DevA  

33  ---DevA Team1  Dev Center               1          3 /Dev Center/DevA/DevA Team1  

34  ---DevA Team2  Dev Center               1          3 /Dev Center/DevA/DevA Team2  

35  -DevB          Dev Center               0          2 /Dev Center/DevB  

36  ---DevB Team1  Dev Center               1          3 /Dev Center/DevB/DevB Team1  

37 Test Center     Test Center              0          1 /Test Center  

38  -Test Team1    Test Center              1          2 /Test Center/Test Team1  

39  -Test Team2    Test Center              1          2 /Test Center/Test Team2  

下面計算Dev Center和Test Center部門的總人數：

[sql] view plaincopy

 

40 select ROOTDEPID, sum(numofemp) "TOTALEMP"  

41 from (select connect_by_root depid "ROOTDEPID", numofemp from dep  

42 start with upperdepid is null  

43 connect by prior depid = upperdepid)  

44 group by ROOTDEPID  

45 /  

結果如下：

[plain] view plaincopy

 

46 ROOTDEPID   TOTALEMP  

47 --------- ----------  

48         6         11  

49         0         37  

[例2]透過CONNECT BY用於十六進度轉換為十進位制

[sql] view plaincopy

 

50 CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS  

51     ----------------------------------------------------------------------------------------------------------------------  

52     -- 物件名稱: f_hex_to_dec  

53     -- 物件描述: 十六進位制轉換十進位制  

54     -- 輸入引數: p_str 十六進位制字串  

55     -- 返回結果: 十進位制字串  

56     -- 測試用例: SELECT f_hex_to_dec('78A') FROM dual;  

57     ----------------------------------------------------------------------------------------------------------------------  

58     v_return  VARCHAR2(4000);  

59   BEGIN  

60     SELECT SUM(DATA) INTO v_return  

61       FROM (SELECT (CASE upper(substr(p_str, rownum, 1))  

62                      WHEN 'A' THEN '10'  

63                      WHEN 'B' THEN '11'  

64                      WHEN 'C' THEN '12'  

65                      WHEN 'D' THEN '13'  

66                      WHEN 'E' THEN '14'  

67                      WHEN 'F' THEN '15'  

68                      ELSE substr(p_str, rownum, 1)  

69                    END) * power(16, length(p_str) - rownum) DATA  

70               FROM dual  

71             CONNECT BY rownum <= length(p_str));  

72     RETURN v_return;  

73   EXCEPTION  

74     WHEN OTHERS THEN  

75       RETURN NULL;  

76   END;  

說明：
1. CONNECT BY rownum <= length(p_str))對輸入的字串進行逐個遍歷
2. 透過CASE語句，來解析十六進位制中的A-F對應的10進位制值

測試結果如下：

[plain] view plaincopy

 

77 hr@MYTEST2> variable dec varchar2(32);  

78 hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');  

79   

80 PL/SQL procedure successfully completed.  

81   

82 hr@MYTEST2> print dec  

83   

84 DEC  

85 ----------  

86 511  

[例3]透過CONNECT BY生成序列
對於connect by，現在大多數人已經很熟悉了，connect by中的條件就表示了父子之間的連線關係，比如 connect by id=prior pid。

但如果connect by中的條件沒有表示記錄之間的父子關係
那會出現什麼情況？
常見的，connect by會在構造序列的時候使用
用select rownum from dual connect by rownum代替早期版本的 select rownum from all_objects where rownum 

我們注意到，dual是一個只有一條記錄的表，如果表有多條記錄，將會怎樣？
下面開始實驗
CREATE TABLE T(ID VARCHAR2(1 BYTE));

INSERT INTO T ( ID ) VALUES ( 'A'); 
INSERT INTO T ( ID ) VALUES ( 'B'); 
INSERT INTO T ( ID ) VALUES ( 'C'); 
COMMIT;
然後執行以下查詢：

[plain] view plaincopy

 

87 hr@MYTEST2> column id format a2;  

88 hr@MYTEST2> select id,level from t connect by level<2;  

89   

90 ID      LEVEL  

91 -- ----------  

92 A           1  

93 B           1  

94 C           1  

95   

96 hr@MYTEST2> select id,level from t connect by level<3;  

97   

98   

99 ID      LEVEL  

100 -- ----------  

101 A           1  

102 A           2  

103 B           2  

104 C           2  

105 B           1  

106 A           2  

107 B           2  

108 C           2  

109 C           1  

110 A           2  

111 B           2  

112 C           2  

113   

114 12 rows selected.  

無需多說，我們很快可以找到其中的規律，假設表中有N條記錄
則記F(N,l)為 select id,level from t connect by level的結果集數目
那麼，
F(N,1)=N
F(N,l) = F(N,l-1)*N+N

於是可以總結出
F(N,l)=∑power(N,p), p取值為[1,l)

要解釋，也很容易。
當連線條件不能限制記錄之間的關係時
每一條記錄都可以作為自己或者其他記錄的葉子
如下所示：
A          1A          2A          3
B          3
C          3
B          2A          3
B          3
C          3
C          2
A          3
B          3
C          3

在這裡，我們看到的是
Oracle採用了深度優先的演算法

轉載自：
http://www.blogjava.net/wxqxs/archive/2008/08/15/222338.html
http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
http://www.itpub.net/thread-994465-1-1.html