Oracle递归查询(一)

创建表和主外键

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 into A_DISTRICT (id, parent_id, name) values (1, null, '河南省');
insert into A_DISTRICT (id, parent_id, name) values (2, null, '山东省');
insert into A_DISTRICT (id, parent_id, name) values (3, 1, '郑州市');
insert into A_DISTRICT (id, parent_id, name) values (4, 1, '开封市');
insert into A_DISTRICT (id, parent_id, name) values (5, 1, '商丘市');
insert into A_DISTRICT (id, parent_id, name) values (6, 3, '中原区');
insert into A_DISTRICT (id, parent_id, name) values (7, 3, '金水区');
insert into A_DISTRICT (id, parent_id, name) values (8, 3, '二七区');
insert into A_DISTRICT (id, parent_id, name) values (9, 3, '城管回族区');
insert into A_DISTRICT (id, parent_id, name) values (10, 3, '上街区');
insert into A_DISTRICT (id, parent_id, name) values (11, 3, '惠济区');
insert into A_DISTRICT (id, parent_id, name) values (12, 4, '开封县');
insert into A_DISTRICT (id, parent_id, name) values (13, 4, '杞县');
insert into A_DISTRICT (id, parent_id, name) values (14, 4, '兰考县');
insert into A_DISTRICT (id, parent_id, name) values (15, 4, '通许县');
insert into A_DISTRICT (id, parent_id, name) values (16, 13, '城关镇');
insert into A_DISTRICT (id, parent_id, name) values (17, 13, '五里河镇');
insert into A_DISTRICT (id, parent_id, name) values (18, 13, '邢口镇');
insert into A_DISTRICT (id, parent_id, name) values (19, 13, '柿园乡');
insert into A_DISTRICT (id, parent_id, name) values (20, 13, '城郊乡');

递归语法和主要使用函数

select * from 表 start with 条件入口 connect by priZ??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vciBpZCA9ICBwYXJlbnRfaWSjqM/yz8KjqTwvcD4KPHA+ICAgICAgc2VsZWN0ICogZnJvbSCx7SBzdGFydCB3aXRoIMz1vP7I67/aIGNvbm5lY3QgYnkgcHJpb3IgcGFyZW50X2lkID0gaWSjqM/yyc+jqTwvcD4KPHA+yOe5+7K70LRwcmlvcqOsse3D98ew0PKx6cD6IM601rjD97HpwPq3vc/yo6zL+dLUsru9+NDQtd256aOsz7C538nPu+HU2tPvvuS688PmvNPJz29yZGVyIGJ5xcXQ8rrNZ3JvdXAgYnm31tfpPC9wPgo8cD4gICAgICBjb25uZWN0X2J5X3Jvb3QowdDD+ykgILLp0a+4+b3ateM8L3A+CjxwPiAgICAgIGNvbm5lY3RfYnlfaXNsZWFmICAgsunRr8rHt/HSttfTvdq146OsMMrHo6wxsrvKxzwvcD4KPHA+ICAgICAgc3lzX2Nvbm5lY3RfYnlfcGF0aCAgsunRr7XduenCt762PC9wPgo8cD48L3A+CjxoMj7KtdW9PC9oMj4KPGgzPrLp0a+4+b3ateM8L2gzPgo8cHJlIGNsYXNzPQ=="brush:sql;">select id, parent_id, name from a_district start with parent_id is null connect by id = parent_id order by id

递归查询 杞县 的所有上级节点（包含当前节点）

select id, parent_id, name from a_district start with name='杞县' connect by prior parent_id = id order by id

递归查询 杞县 的所有上级节点（包含当前节点和根节点）

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

递归查询 杞县 的所有子城镇乡（包含当前节点）

select id, parent_id, name from a_district start with name='杞县' connect by prior id = parent_id order by id

递归查询 开封市 的所有城镇乡（包含当前节点和根节点）

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 id = parent_id order by id

上行递归查询 城郊乡 深度、是否包含叶子节点

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '城郊乡