oracle的递归函数

### connect by

语法:

select * from tableName

start with  条件A   -- 开始递归的根节点,可多个条件

connect  by prior  条件B  -- prior  决定查询的索引顺序

where 条件 C

## 开始:创建数据库

```

create table TEST_DG
(
  id        VARCHAR2(36),
  name      VARCHAR2(36),
  father_id VARCHAR2(36),
  self_id   VARCHAR2(36)
);

```

## 插入数据

```

insert into TEST_DG (id, name, father_id, self_id)
values ('C59384CDE9F7473EA7B4444B0B2B6E5A', '管理员', '000', '0001');
insert into TEST_DG (id, name, father_id, self_id)
values ('2D2F9BCC15334C7AB56301A2A0790FDA', '老板', '0001', '000101');
insert into TEST_DG (id, name, father_id, self_id)
values ('64976492F7E34BFA8286F0323B129A20', '经理', '0001', '000102');
insert into TEST_DG (id, name, father_id, self_id)
values ('33684C36FA5644DCBBF59E254BD21BD9', '主管', '0001', '000103');
insert into TEST_DG (id, name, father_id, self_id)
values ('A96E1F32B565485684767725C4FC81A5', '员工', '0001', '000104');
insert into TEST_DG (id, name, father_id, self_id)
values ('4129C371879F4B848CEDBB2F940D47DA', '厨师长', '0002', '000201');
insert into TEST_DG (id, name, father_id, self_id)
values ('CC7C2634231C4252996C45F81DCD5D25', '一般 管理员', '000', '0002');
insert into TEST_DG (id, name, father_id, self_id)
values ('2655913C8B2B416E867A38F22AEC3551', '超级管理员', '0', '000');
insert into TEST_DG (id, name, father_id, self_id)
values ('AF4F33642D70407DA25472792D61E430', '厨师', '0002', '000202');
insert into TEST_DG (id, name, father_id, self_id)
values ('D5D2D630F8FC43E3B7FB58C9AB212191', '打荷', '0002', '000203');
commit;

```

## 实例代码

```

select td.*,level from test_dg td --level 表示该数据的层号
       start with  td.self_id = '000'
       connect by prior td.self_id = td.father_id --prior后面的条件顺序很重要

```

### 总结

## 1.该方法一般用于树形数据查找,比如权限,其中的查询原理类似与ztree的配置

## 2.start with 跟的条件就是开始递归的地方

## 3.prior 关键字所放的位置决定着递归的方向

## 4.若需要对子节点剪切,则可在where后面加上判断

## 5.引用,prior 放在 connect by 后面 则表示,从跟节点想子节点递归

若prior 放在connect by 的 等号(=)  后面,则表示从子节点到根节点递归  如:  connect by prior td.self_id = prior td.father_id

## 6.start with 可以省略

##7. 实现序列,select level from dual connect by level<=100;