Oracle recursive query and Oracle recursive query

Oracle recursive query and Oracle recursive query
1. Create data

1.1 create a table and insert data

Create table district (id number (10) not null, PARENT_ID NUMBER (10), NAME VARCHAR2 (255 BYTE) not null ); alter table district add (CONSTRAINT DISTRICT_PK primary key (ID); alter table district add (CONSTRAINT DISTRICT_R01 foreign key (PARENT_ID) references district (ID); insert into DISTRICT (id, parent_id, name) values (1, null, 'sichuan province '); insert into DISTRICT (id, parent_id, name) values (2, 1, 'barzhong '); insert into DISTRICT (id, parent_id, name) values (3, 1, 'daobao'); insert into DISTRICT (id, parent_id, name) values (4, 2, 'bazhou DISTRICT '); insert into DISTRICT (id, parent_id, name) values (5, 2, 'tongjiang'); insert into DISTRICT (id, parent_id, name) values (6, 2, 'pingchang County '); insert into DISTRICT (id, parent_id, name) values (7, 3, 'tongchuan Region'); insert into DISTRICT (id, parent_id, name) values (8, 3, 'xuanhanxian '); insert into DISTRICT (id, parent_id, name) values (9, 8, 'River out'); insert into DISTRICT (id, parent_id, name) values (10, 8, 'sanhe township '); insert into DISTRICT (id, parent_id, name) values (11, 8, 'hujia town '); insert into DISTRICT (id, parent_id, name) values (12, 8, 'nanba town'); insert into DISTRICT (id, parent_id, name) values (13, 6, 'taizang output'); insert into DISTRICT (id, parent_id, name) values (14, 6, 'xiangtan town'); insert into DISTRICT (id, parent_id, name) values (15, 6, 'longgang town'); insert into DISTRICT (id, parent_id, name) values (16, 6, 'plaintown '); commit;

2. start with connect by prior Recursion

2.1 query all subnodes

 

SELECT * FROM districtSTART with name = 'barzhong' connect by prior id = parent_id

 

 

 

2.2 query all parent nodes

 

SELECT * FROM districtSTART with name = 'pingchang County 'connect by prior parent_id = ID

 

You only need to switch the location of the id and parent_id.

2.3 query the root node of a specified node.

SELECT d. *, connect_by_root (d. id), connect_by_root (NAME) FROM district dWHERE NAME = 'pyeongchang County 'start WITH d. parent_id = 1 -- d. parent_id is null and the result is Sichuan connect by prior d. ID = d. parent_id

2.4 query the recursive path of the administrative organization in Bazhong City

 

Select id, parent_id, NAME, sys_connect_by_path (NAME, '->') namepath, LEVELFROM district start with name = 'barzhong' connect by prior id = parent_id

3. with recursion

3.1. with recursive subclass

 

WITH t (ID, parent_id, NAME) -- The column name as (select id, parent_id, name from district where name = 'barzhong' union allselect d. ID, d. parent_id, d. name from t, district d -- specify the table and list, WHERE t. id = d. parent_id) SELECT * FROM t;

 

3.2 recursive parent class

WITH t (ID, parent_id, NAME) -- the table AS (select id, parent_id, name from district where name = 'tongjiang' union allselect d. ID, d. parent_id, d. name from t, district d -- specify the table and list, WHERE t. parent_id = d. id) SELECT * FROM t;