Oracle start with connect by usage

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.