【函式】Oracle “CONNECT BY” 使用

散葉涔發表於2012-04-23
函式Oracle

Oracle “CONNECT BY”是層次查詢子句，一般用於樹狀或者層次結果集的查詢。其語法是：

?
1
2
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition

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).

 

說明： 
1. START WITH：告訴系統以哪個節點作為根結點開始查詢並構造結果集，該節點即為返回記錄中的最高節點。 
2. 當分層查詢中存在上下層互為父子節點的情況時，會返回ORA-01436錯誤。此時，需要在connect by後面加上NOCYCLE關鍵字。同時，可用connect_by_iscycle偽列定位出存在互為父子迴圈的具體節點。 connect_by_iscycle必須要跟關鍵字NOCYCLE結合起來使用

 

    接下來，用一些示例來說明“CONNECT BY”的用法。

 

[例1]

建立一個部門表，這個表有三個欄位，分別對應部門ID，部門名稱，以及上級部門ID

?
1
2
3
4
5
6
7
8
-- Create table
create table DEP
(
  DEPID      number(10) not null,
  DEPNAME    varchar2(256),
  UPPERDEPID number(10)
)
;

初始化一些資料

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '總經辦', null);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '開發部', 0);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '測試部', 0);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever開發部', 1);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client開發部', 1);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA測試部', 2);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '專案測試部', 2);
1 row inserted
 
SQL> commit;
Commit complete
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT * FROM DEP;
 
      DEPID DEPNAME                                                                           UPPERDEPID
----------- -------------------------------------------------------------------------------- -----------
          0 General Deparment                                                               
          1 Development                                                                                0
          2 QA                                                                                         0
          3 Server Development                                                                         1
          4 Client Development                                                                         1
          5 TA                                                                                         2
          6 Porject QA                                                                                 2
 
7 rows selected

現在我要根據“CONNECT BY”來實現樹狀查詢結果

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
 
DEPNAME                        ROOT                    ISLEAF      LEVEL PATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
General Deparment              General Deparment            0          1 /General Deparment
 -Development                  General Deparment            0          2 /General Deparment/Development
 ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development
 ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development
 -QA                           General Deparment            0          2 /General Deparment/QA
 ---TA                         General Deparment            1          3 /General Deparment/QA/TA
 ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA
                                                    
7 rows selected

說明： 
1. CONNECT_BY_ROOT 返回當前節點的最頂端節點 
2. CONNECT_BY_ISLEAF 判斷是否為葉子節點，如果這個節點下面有子節點，則不為葉子節點 
3. LEVEL 偽列表示節點深度 
4. SYS_CONNECT_BY_PATH函式顯示詳細路徑，並用“/”分隔

[例2]

透過CONNECT BY生成序列

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
 
    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected

[例3]

透過CONNECT BY用於十六進度轉換為十進位制

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 物件名稱: f_hex_to_dec
    -- 物件描述: 十六進位制轉換十進位制
    -- 輸入引數: p_str 十六進位制字串
    -- 返回結果: 十進位制字串
    -- 測試用例: SELECT f_hex_to_dec('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return  VARCHAR2(4000);
  BEGIN
    SELECT SUM(DATA) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                     WHEN 'A' THEN '10'
                     WHEN 'B' THEN '11'
                     WHEN 'C' THEN '12'
                     WHEN 'D' THEN '13'
                     WHEN 'E' THEN '14'
                     WHEN 'F' THEN '15'
                     ELSE substr(p_str, rownum, 1)
                   END) * power(16, length(p_str) - rownum) DATA
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

說明：

1. CONNECT BY rownum <= length(p_str))對輸入的字串進行逐個遍歷

2. 透過CASE語句，來解析十六進位制中的A-F對應的10進位制值

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

相關文章