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 plain copy

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 plain copy

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 plain copy

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 plain copy

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 plain copy

46 ROOTDEPID TOTALEMP

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

48 6 11

49 0 37

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

[sql] view plain copy

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 plain copy

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 plain copy

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

Oracle “CONNECT BY” 使用

相關文章