Use SQL recursive statements in Oracle

Example:

PID ID
A B
A c
A E
B B1
B B2
C C1
E E1
E E3
D D1

Specify pid = A and select
A B
A c
A E
B B1
B B2
C C1
E E1
E E3
SQL statement: Select parent, child from Test start with PID = 'A'
Connect by prior id = PID

 

 

OracleSQLRecursive query:
1. Table Structure
SQL> DESC comm_org_subjection
Name null? Type
-----------------------------------------------------------------------------
Org_subjection_id not null varchar2 (32) subkey
Org_id not null varchar2 (32)
Father_org_id not null varchar2 (32) parent key
Locked_if not null varchar2 (1)
Start_date not null date
End_date date
Edition_nameplate number (8)
Code_afford_if varchar2 (1)
Code_afford_org_id not null varchar2 (32)
Coding_show_id number (8)
Bsflag varchar2 (1)
Modifi_date date
Creator_id varchar2 (32)
Create_date date
Creator varchar2 (35)

2. Recursively search for all child nodes with the parent node org_id c6000000000001:
Select * From comm_org_subjection
Start with a.org _ id = 'c6000000000001'
Connect by prior a.org _ subjection_id = A. father_org_id

3. Recursively search for all the parent nodes under the subnode org_id c6000000000001:
Select org_id from comm_org_subjection
Start with a.org _ id = 'c6000000000001'
Connect by prior A. father_org_id = a.org _ subjection_id