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