Oracle “CONNECT BY” 使用

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

 

1	[ START WITHcondition ]

2	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	-- Create table

2	create table DEP

3

(

4	DEPID      number(10)not null,

5	DEPNAME    varchar2(256),

6	UPPERDEPID number(10)

7

)

8

;

初始化一些数据

 

01	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (0, '总经办', null);

02	1 row inserted

03

04	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (1, '开发部', 0);

05	1 row inserted

06

07	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (2, '测试部', 0);

08	1 row inserted

09

10	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (3, 'Sever开发部', 1);

11	1 row inserted

12

13	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (4, 'Client开发部', 1);

14	1 row inserted

15

16	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (5, 'TA测试部', 2);

17	1 row inserted

18

19	SQL> INSERTINTO DEP(DEPID, DEPNAME, UPPERDEPID)VALUES (6, '项目测试部', 2);

20	1 row inserted

21

22	SQL> commit;

23	Commit complete

 

01	SQL> SELECT* FROM DEP;

02

03	DEPID DEPNAME                                                                           UPPERDEPID

04	----------- -------------------------------------------------------------------------------- -----------

05	0 General Deparment

06	1 Development                                                                                0

07	2 QA                                                                                         0

08	3 Server Development                                                                         1

09	4 Client Development                                                                         1

10	5 TA                                                                                         2

11	6 Porject QA                                                                                 2

12

13	7 rowsselected

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

 

01	SQL> SELECTRPAD( ' ', 2*(LEVEL-1),'-' ) || DEPNAME "DEPNAME",

02	CONNECT_BY_ROOT DEPNAME "ROOT",

03	CONNECT_BY_ISLEAF "ISLEAF",

04

LEVEL ,

05	SYS_CONNECT_BY_PATH(DEPNAME, '/')"PATH"

06

FROM DEP

07	START WITHUPPERDEPID IS NULL

08	CONNECT BY PRIOR DEPID = UPPERDEPID;

09

10	DEPNAME                        ROOT                    ISLEAF     LEVEL PATH

11	------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------

12	General Deparment              General Deparment            0          1 /General Deparment

13	-Development                  General Deparment            0          2 /General Deparment/Development

14	---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development

15	---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development

16	-QA                           General Deparment            0          2 /General Deparment/QA

17	---TA                         General Deparment            1          3 /General Deparment/QA/TA

18	---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA

19

20	7 rowsselected

说明：
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

[例2]

通过CONNECT BY生成序列

 

01	SQL> SELECTROWNUM FROM DUAL CONNECT BYROWNUM <= 10;

02

03

ROWNUM

04	----------

05

1

06

2

07

3

08

4

09

5

10

6

11

7

12

8

13

9

14

10

15

16	10 rowsselected

[例3]

通过CONNECT BY用于十六进度转换为十进制

 

01	CREATE OR REPLACE FUNCTIONf_hex_to_dec(p_str INVARCHAR2) RETURN VARCHAR2 IS

02	----------------------------------------------------------------------------------------------------------------------

03	-- 对象名称: f_hex_to_dec

04	-- 对象描述: 十六进制转换十进制

05	-- 输入参数: p_str 十六进制字符串

06	-- 返回结果: 十进制字符串

07	-- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;

08	----------------------------------------------------------------------------------------------------------------------

09	v_return  VARCHAR2(4000);

10

BEGIN

11	SELECTSUM(DATA) INTOv_return

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

13	WHEN'A' THEN '10'

14	WHEN'B' THEN '11'

15	WHEN'C' THEN '12'

16	WHEN'D' THEN '13'

17	WHEN'E' THEN '14'

18	WHEN'F' THEN '15'

19	ELSEsubstr(p_str, rownum, 1)

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

21

FROMdual

22	CONNECTBY rownum <= length(p_str));

23	RETURNv_return;

24

EXCEPTION

25	WHENOTHERS THEN

26	RETURNNULL;

27

END;

说明：

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

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