深入理解start with ... connect by (二）

 

前面提到oracle中start with ...connect by 除了用来进行层次查询和得到序列之外，还看到了在Oracle中利用深度优先法则进行connect by 运算。9I之后还增加了sys_connect_by_path 函数。说实话，我一直对这两个用法不得要领，只好期待哪天脑袋被门夹了，说不定能豁然开朗……….

玩笑归玩笑，动手实践才是最重要。

ORACLE 10G，PLSQL DEVELOP，创建测试表，插入测试数据

create table test(parent_id varchar2(20),lower_id varchar2(20));
insert into test values('0','1');
insert into test values('0','2');
insert into test values('1','11');
insert into test values('1','12');
insert into test values('2','21');
insert into test values('2','22');
insert into test values('11','111');
insert into test values('11','112');
insert into test values('12','121');
insert into test values('12','122');
insert into test values('21','211');
insert into test values('21','212');
insert into test values('22','221');
insert into test values('22','222');
commit;

 

CONNECT BY ：

SQL> select test.*,level from test start with parent_id='0' connect by prior lower_id=parent_id;

PARENT_ID            LOWER_ID                  LEVEL

-------------------- -------------------- ----------

0                    1                             1

1                    11                            2

11                   111                           3

11                   112                           3

1                    12                            2

12                   121                           3

12                   122                           3

0                    2                             1

2                    21                            2

21                   211                           3

21                   212                           3

2                    22                            2

22                   221                           3

22                   222                           3

14 rows selected.

SQL>

SQL> select test.* from test start with lower_id='222' connect by prior parent_id=lower_id;

PARENT_ID            LOWER_ID

-------------------- --------------------

22                   222

2                    22

0                    2

SQL>

从这两种写法可以看出，分别从根向叶子节点遍历，和从叶子节点向根遍历，得到树的结果。LEVEL是connect by 树中的伪列，标识这个叶子是在树当中的第几层。

SYS_CONNECT_BY_PATH

用法：SYS_CONNECT_BY_PATH(colum,char)

SQL> col aa format a10

SQL> select sys_connect_by_path(lower_id,' ') aa,level from test start with parent_id='0' connect by prior lower_id=parent_id;

AA              LEVEL

---------- ----------

 1                  1

 1 11               2

 1 11 111           3

 1 11 112           3

 1 12               2

 1 12 121           3

 1 12 122           3

 2                  1

 2 21               2

 2 21 211           3

 2 21 212           3

 2 22               2

 2 22 221           3

 2 22 222           3

14 rows selected.

Elapsed: 00:00:00.01

SQL>