Oracle start with connect by usage

Start with connect by is used to create a tree structure for two fields with a parent-child relationship in a table. It searches for its subrecords in a tree structure starting from a field.

1. Create a table

Create Table test_strat_wth111
(
Oid varchar2 (20 ),
Parent_id varchar2 (20)
)

2. Insert data

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

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 ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('000000', '00 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20160301', '20160301 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Insert into test_strat_wth (OID, parent_id)
Values ('20140901', '01 ');

Commit;

3. query data

Select *
From test_strat_wth t
Start with T. parent_id is null
Connect by prior T. OID = T. parent_id

4. Data results:

00
00, 0000005
000000500001 0000005
000000500002 0000005
000000500003 0000005
000000500004 0000005
00, 0000003
00, 0000004
00, 0000006
000000600001 0000006
000000600002 0000006
00, 0000007
00, 0000008
00, 0000009
000000900001 0000009
000000900002 0000009
000000900003 0000009
000000900004 0000009
00, 0000011
00, 0000010
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