以下转自http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html  作者：lettooOracle “CONNECT BY” 使用

    Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：

1
2
[ STARTWITHcondition ]
CONNECTBY[ 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
createtableDEP
(
DEPID      number(10)notnull,
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>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(0,'总经办',null);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(1,'开发部', 0);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(2,'测试部', 0);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(3,'Sever开发部', 1);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(4,'Client开发部', 1);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(5,'TA测试部', 2);
1 row inserted
SQL>INSERTINTODEP(DEPID, DEPNAME, UPPERDEPID)VALUES(6,'项目测试部', 2);
1 row inserted
SQL>commit;
Commitcomplete
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>SELECT*FROMDEP;
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
7rowsselected

现在我要根据“CONNECT BY”来实现树状查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>SELECTRPAD(' ', 2*(LEVEL-1),'-') || DEPNAME"DEPNAME",
CONNECT_BY_ROOT DEPNAME"ROOT",
CONNECT_BY_ISLEAF"ISLEAF",
LEVEL,
SYS_CONNECT_BY_PATH(DEPNAME,'/')"PATH"
FROMDEP
STARTWITHUPPERDEPIDISNULL
CONNECTBYPRIORDEPID = UPPERDEPID;
DEPNAME                        ROOT                    ISLEAF     LEVELPATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
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
7rowsselected

说明： 
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>SELECTROWNUMFROMDUALCONNECTBYROWNUM <= 10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10rowsselected

[例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
CREATEORREPLACEFUNCTIONf_hex_to_dec(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_hex_to_dec
-- 对象描述: 十六进制转换十进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 十进制字符串
-- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return  VARCHAR2(4000);
BEGIN
SELECTSUM(DATA)INTOv_return
FROM(SELECT(CASEupper(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'
ELSEsubstr(p_str, rownum, 1)
END) * power(16, length(p_str) - rownum) DATA
FROMdual
CONNECTBYrownum <= length(p_str));
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
END;

说明：

1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值