Oracle recursive query and Oracle recursive query

Oracle recursive query and Oracle recursive query
Create a table and a primary foreign key

CREATE TABLE SC_DISTRICT(  ID         NUMBER(10)                  NOT NULL,  PARENT_ID  NUMBER(10),  NAME       VARCHAR2(255 BYTE)          NOT NULL);ALTER TABLE SC_DISTRICT ADD (  CONSTRAINT SC_DISTRICT_PK PRIMARY KEY (ID));ALTER TABLE SC_DISTRICT ADD (  CONSTRAINT SC_DISTRICT_R01  FOREIGN KEY (PARENT_ID)  REFERENCES SC_DISTRICT (ID));

Insert data

Insert into A_DISTRICT (id, parent_id, name) values (1, null, 'henan province '); insert into A_DISTRICT (id, parent_id, name) values (2, null, 'shandong province '); insert into A_DISTRICT (id, parent_id, name) values (3, 1, 'zhengzhou City'); insert into A_DISTRICT (id, parent_id, name) values (4, 1, 'kaifeng City'); insert into A_DISTRICT (id, parent_id, name) values (5, 1, 'shangqiu City'); insert into A_DISTRICT (id, parent_id, name) values (6, 3, 'zhongyuan Region'); insert into A_DISTRICT (id, parent_id, name) values (7, 3, 'jinshui'); insert into A_DISTRICT (id, parent_id, name) values (8, 3, 'er7'); insert into A_DISTRICT (id, parent_id, name) values (9, 3, 'urban management Hui Region '); insert into A_DISTRICT (id, parent_id, name) values (10, 3, 'block up'); insert into A_DISTRICT (id, parent_id, name) values (11, 3, 'huiji Region'); insert into A_DISTRICT (id, parent_id, name) values (12, 4, 'kaifeng Region'); insert into A_DISTRICT (id, parent_id, name) values (13, 4, 'jinxian '); insert into A_DISTRICT (id, parent_id, name) values (14, 4, 'lankao '); insert into A_DISTRICT (id, parent_id, name) values (15, 4, 'tongxu County '); insert into A_DISTRICT (id, parent_id, name) values (16, 13, 'chengguan town'); insert into A_DISTRICT (id, parent_id, name) values (17, 13, 'wulihezhen '); insert into A_DISTRICT (id, parent_id, name) values (18, 13, 'hangzhou '); insert into A_DISTRICT (id, parent_id, name) values (19, 13, 'shiyuanxiang '); insert into A_DISTRICT (id, parent_id, name) values (20, 13, 'suburban towns ');

The generated data is as follows:

Recursive syntax and main functions

Select * from Table start with condition entry connect by prior id = parent_id (down)

Select * from Table start with condition entry connect by prior parent_id = id (up)

If prior is not written, it indicates that the traversal direction is not specified in the forward order traversal, so recursion is not performed. Traditionally, order by sorting and group by grouping are added to the end of the statement.

Connect_by_root (column name) query the root node

Connect_by_isleaf: Check whether the node is a leaf node. 0 is. 1 is not.

Sys_connect_by_path

Query the root node

select id, parent_id, name from a_district start with parent_id is null connect by id = parent_id order by id

Recursively query all upper-level nodes (including the current node) in PiXian County)

Select id, parent_id, name from a_district start with name = 'pianxia' connect by prior parent_id = id order by id

Recursively query all upper-level nodes (including the current node and root node) in PiXian County)

Select id, parent_id, name, connect_by_root (id) city_id, connect_by_root (name) city_name from a_district start with name = ' 'connect by prior parent_id = id order by id

Recursively query all sub-towns and towns in PiXian (including the current node)

Select id, parent_id, name from a_district start with name = 'taobao' connect by prior id = parent_id order by id

Recursively query all towns and towns in Kaifeng City (including the current node and root node)

Select id, parent_id, name, connect_by_root (id) city_id, connect_by_root (name) city_name from a_district start with name = 'kaifeng 'connect by prior id = parent_id order by id

Upstream recursive query of the depth of suburban towns and whether the depth contains leaf nodes

Select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = 'suburban country' connect by prior parent_id = id order by id

Downstream recursive query of Henan Province depth, whether it contains leaf nodes

Select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = 'henan province 'connect by prior id = parent_id order by id

Upstream recursive query of PiXian path

Select id, name, parent_id, substr (sys_connect_by_path (name, '->'), 3) name_path from a_district start with name = 'pianxia' connect by prior parent_id = id order by id

Downstream recursive query of Henan Province paths

Select id, name, parent_id, substr (sys_connect_by_path (name, '->'), 3) name_path from a_district start with name = 'henan province 'connect by prior id = parent_id order by id

Comprehensive use

Select id, name, parent_id, level, connect_by_isleaf isleaf, substr (sys_connect_by_path (name, '->'), 3) name_path from a_district start with name = 'henan province 'connect by prior id = parent_id order by id