Oracle Start With Connect By

Start With Connect By  是用来实现在一个表中存在父子关系的两个字段的树形结构，通过一个从一个字段开始，以树形结构去查找其子记录。

1、建表

create table TEST_STRAT_WTH111
(
  OID       VARCHAR2(20),
  PARENT_ID VARCHAR2(20)
)

2、插入数据

insert into test_strat_wth (OID, PARENT_ID)
values ('0000005', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000003', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000004', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500001', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000006', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000600001', '0000006');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000600002', '0000006');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000007', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000008', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000009', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900001', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900002', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900003', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900004', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('00', '');

insert into test_strat_wth (OID, PARENT_ID)
values ('01', '');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500002', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500003', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500004', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000011', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100004', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100005', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000500001', '0100005');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000500002', '0100005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100006', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000600001', '0100006');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000600002', '0100006');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000010', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000001', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000005', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000003', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000004', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100007', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100008', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100010', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100009', '01');

COMMIT;

3、查询数据

SELECT *
  FROM TEST_STRAT_WTH T
START WITH T.PARENT_ID IS NULL
CONNECT BY PRIOR T.OID = T.PARENT_ID

4、数据结果：

00
0000005 00
000000500001 0000005
000000500002 0000005
000000500003 0000005
000000500004 0000005
0000003 00
0000004 00
0000006 00
000000600001 0000006
000000600002 0000006
0000007 00
0000008 00
0000009 00
000000900001 0000009
000000900002 0000009
000000900003 0000009
000000900004 0000009
0000011 00
0000010 00
000001000001 0000010
000001000005 0000010
000001000003 0000010
000001000004 0000010
01
0100004 01
0100005 01
010000500001 0100005
010000500002 0100005
0100006 01
010000600001 0100006
010000600002 0100006
0100007 01
0100008 01
0100010 01

评论

1 楼 yvonxiao 2009-03-28  

这个的确好用，我记得我以前是自己写了个存储过程来解决这种递归问题的