Oracle树形结构查询（递归）
程序员文章站
2022-12-22 11:51:36
引用：https://blog.csdn.net/u012615705/article/details/78321022 文章转自上述地址，内部有稍许改动，如有需要请查看原文。 oracle树状结构查询即层次递归查询，是sql语句经常用到的，在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。... ......
引用： 文章转自上述地址，内部有稍许改动，如有需要请查看原文。
oracle树状结构查询即层次递归查询，是sql语句经常用到的，在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。
概要：树状结构通常由根节点、父节点、子节点和叶节点组成，简单来说，一张表中存在两个字段，dept_id,par_dept_id,那么通过找到每一条记录的父级id即可形成一个树状结构，也就是par_dept_id（子）=dept_id（父），通俗的说就是这条记录的par_dept_id是另外一条记录也就是父级的dept_id，其树状结构层级查询的基本语法是：
select [level],*
feom table_name
start with 条件1
connect by prior 条件2
where 条件3
order by 排序字段
说明：level---伪列，用于表示树的层次
条件1---根节点的限定条件，当然也可以放宽权限，以获得多个根节点，也就是获取多个树
条件2---连接条件，目的就是给出父子之间的关系是什么，根据这个关系进行递归查询
条件3---过滤条件，对所有返回的记录进行过滤。
排序字段---对所有返回记录进行排序
对prior说明：要的时候有两种写法：connect by prior dept_id=par_dept_id 或 connect by dept_id=prior par_dept_id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。
树状结构层次化查询需要对树结构的每一个节点进行访问并且不能重复，其访问步骤为：
大致意思就是扫描整个树结构的过程即遍历树的过程，其用语言描述就是：
步骤一：从根节点开始；
步骤二：访问该节点；
步骤三：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
步骤四：若该节点为根节点，则访问完毕，否则执行第五步；
步骤五：返回到该节点的父节点，并执行第三步骤。
除此之外，sys_connect_by_path函数是和connect by 一起使用的，在实战中具体带目的具体介绍！
实战：最近做项目的组织结构，对于部门的各级层次显示，由于这部分掌握不牢固，用最笨的like模糊查询解决了，虽然功能实现了，但是问题很多，如扩展性不好，稍微改下需求就要进行大改，不满意最后对其进行了优化。在开发中能用数据库解决的就不要用java去解决，这也是我一直保持的想法并坚持着。
创建表：
1: create table sys_dept
2: (
3: dept_id varchar2(32) not null,
4: dept_name varchar2(128),
5: dept_code varchar2(32),
6: par_dept_id varchar2(32),
7: dept_leader varchar2(32),
8: dept_desc varchar2(256),
9: create_time char(19),
10: org_id varchar2(32),
11: dept_type varchar2(1),
12: order_id number,
13: state char(1) default '1',
14: bqq_dept_id varchar2(128),
15: bqq_par_dept_id varchar2(128)
16: )
17:
18: -- add comments to the table
19: comment on table sys_dept
20: is '部门信息，和单位多对一';
21: -- add comments to the columns
22: comment on column sys_dept.dept_id
23: is '主键';
24: comment on column sys_dept.dept_name
25: is '名称';
26: comment on column sys_dept.dept_code
27: is '编码，用于递归';
28: comment on column sys_dept.par_dept_id
29: is '父级部门id';
30: comment on column sys_dept.dept_leader
31: is '部门领导id';
32: comment on column sys_dept.dept_desc
33: is '部门描述';
34: comment on column sys_dept.create_time
35: is 'yyyy-mm-dd hh:mm:ss';
36: comment on column sys_dept.org_id
37: is '单位id';
38: comment on column sys_dept.dept_type
39: is '1：正式部门；2：虚拟部门（用于通讯录展示）';
40: comment on column sys_dept.order_id
41: is '排序字段';
42: comment on column sys_dept.state
43: is '0：无效；1：有效';
44: comment on column sys_dept.bqq_dept_id
45: is '企业qqdeptid';
46: comment on column sys_dept.bqq_par_dept_id
47: is '企业qq父类deptid';
插入测试数据：
1: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
2: values ('40288ac45a3c1e8b015a3c28b4ae01d6', '客运部', '110', '-1', null, null, '2017-02-14 18:26:25', '402881e54c40d74d014c40d8407a0016', '1', 29, '1', null, null);
3:
4: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
5: values ('4028e4d35b5ca4ee015b60f98a1d59b3', '综合室', '110001', '40288ac45a3c1e8b015a3c28b4ae01d6', null, null, '2017-04-12 15:03:38', '402881e54c40d74d014c40d8407a0016', '1', 63, '1', null, null);
6:
7: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
8: values ('4028e4d35b5ca4ee015b6134d9ff2946', '生产调度', '110001001', '4028e4d35b5ca4ee015b60f98a1d59b3', null, null, '2017-04-12 16:08:25', '402881e54c40d74d014c40d8407a0016', '1', 135, '1', null, null);
9:
10: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
11: values ('4028e4d35b5ca4ee015b60f9fae95a44', '站务中心', '110002', '40288ac45a3c1e8b015a3c28b4ae01d6', null, null, '2017-04-12 15:04:07', '402881e54c40d74d014c40d8407a0016', '1', 64, '1', null, null);
12:
13: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
14: values ('4028e4d35b5ca4ee015b613562be2a08', '东岗站', '110002001', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:09:00', '402881e54c40d74d014c40d8407a0016', '1', 136, '1', null, null);
15:
16: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
17: values ('4028e4d35b5ca4ee015b6135f9de2aca', '焦家湾站', '110002002', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:09:39', '402881e54c40d74d014c40d8407a0016', '1', 137, '1', null, null);
18:
19: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
20: values ('4028e4d35b5ca4ee015b6136a3e22bb2', '拱星墩站', '110002003', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:10:22', '402881e54c40d74d014c40d8407a0016', '1', 138, '1', null, null);
21:
22: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
23: values ('4028e4d35b5ca4ee015b613723bb2c5f', '省气象局站', '110002004', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:10:55', '402881e54c40d74d014c40d8407a0016', '1', 139, '1', null, null);
24:
25: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
26: values ('4028e4d35b5ca4ee015b6137a5772d06', '五里铺站', '110002005', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:11:28', '402881e54c40d74d014c40d8407a0016', '1', 140, '1', null, null);
27:
28: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
29: values ('4028e4d35b5ca4ee015b6137e4e72d57', '兰州大学站', '110002006', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:11:44', '402881e54c40d74d014c40d8407a0016', '1', 141, '1', null, null);
30:
31: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
32: values ('4028e4d35b5ca4ee015b613840112dd0', '东方红广场站', '110002007', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:12:08', '402881e54c40d74d014c40d8407a0016', '1', 142, '1', null, null);
33:
34: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
35: values ('4028e4d35b5ca4ee015b6138765c2e12', '省*站', '110002008', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:12:21', '402881e54c40d74d014c40d8407a0016', '1', 143, '1', null, null);
36:
37: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
38: values ('4028e4d35b5ca4ee015b6138b84b2e68', '西关站', '110002009', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:12:38', '402881e54c40d74d014c40d8407a0016', '1', 145, '1', null, null);
39:
40: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
41: values ('4028e4d35b5ca4ee015b6139390e2f06', '文化宫站', '110002010', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:13:11', '402881e54c40d74d014c40d8407a0016', '1', 146, '1', null, null);
42:
43: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
44: values ('4028e4d35b5ca4ee015b613980a82f61', '小西湖站', '110002011', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:13:30', '402881e54c40d74d014c40d8407a0016', '1', 147, '1', null, null);
45:
46: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
47: values ('4028e4d35b5ca4ee015b6139c1dc2fb4', '七里河站', '110002012', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:13:46', '402881e54c40d74d014c40d8407a0016', '1', 148, '1', null, null);
48:
49: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
50: values ('4028e4d35b5ca4ee015b613a24853047', '西站十字站', '110002013', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:14:12', '402881e54c40d74d014c40d8407a0016', '1', 149, '1', null, null);
51:
52: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
53: values ('4028e4d35b5ca4ee015b613a81f030ce', '兰州西站北广场站', '110002014', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:14:36', '402881e54c40d74d014c40d8407a0016', '1', 150, '1', null, null);
54:
55: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
56: values ('4028e4d35b5ca4ee015b613ad627313d', '土门墩站', '110002015', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:14:57', '402881e54c40d74d014c40d8407a0016', '1', 151, '1', null, null);
57:
58: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
59: values ('4028e4d35b5ca4ee015b613b394c31c6', '马滩站', '110002016', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:15:22', '402881e54c40d74d014c40d8407a0016', '1', 152, '1', null, null);
60:
61: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
62: values ('4028e4d35b5ca4ee015b613b9051325e', '兰州海关站', '110002017', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:15:45', '402881e54c40d74d014c40d8407a0016', '1', 153, '1', null, null);
63:
64: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
65: values ('4028e4d35b5ca4ee015b613c286b332e', '兰州城市学院（省科技馆）站', '110002018', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:16:24', '402881e54c40d74d014c40d8407a0016', '1', 154, '1', null, null);
66:
67: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
68: values ('4028e4d35b5ca4ee015b613c806933a3', '深安大桥南站', '110002019', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:16:46', '402881e54c40d74d014c40d8407a0016', '1', 155, '1', null, null);
69:
70: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
71: values ('4028e4d35b5ca4ee015b613cdf98342c', '陈官营站', '110002020', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 16:17:11', '402881e54c40d74d014c40d8407a0016', '1', 157, '1', null, null);
72:
73: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
74: values ('4028e4d35b5ca4ee015b60fa3e2f5a94', '乘务中心', '110003', '40288ac45a3c1e8b015a3c28b4ae01d6', null, null, '2017-04-12 15:04:24', '402881e54c40d74d014c40d8407a0016', '1', 65, '1', null, null);
75:
76: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
77: values ('4028e4d35b5ca4ee015b613d738d34f4', '陈官营车场组', '110003001', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:17:48', '402881e54c40d74d014c40d8407a0016', '1', 158, '1', null, null);
78:
79: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
80: values ('4028e4d35b5ca4ee015b613defed359e', '东岗车场组', '110003002', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:18:20', '402881e54c40d74d014c40d8407a0016', '1', 159, '1', null, null);
81:
82: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
83: values ('4028e4d35b5ca4ee015b613e42ae3612', '第一车队', '110003003', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:18:41', '402881e54c40d74d014c40d8407a0016', '1', 161, '1', null, null);
84:
85: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
86: values ('4028e4d35b5ca4ee015b613e7a50366c', '第二车队', '110003004', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:18:56', '402881e54c40d74d014c40d8407a0016', '1', 162, '1', null, null);
87:
88: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
89: values ('4028e4d35b5ca4ee015b613ebc8e36c1', '第三车队', '110003005', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:19:13', '402881e54c40d74d014c40d8407a0016', '1', 163, '1', null, null);
90:
91: insert into sys_dept (dept_id, dept_name, dept_code, par_dept_id, dept_leader, dept_desc, create_time, org_id, dept_type, order_id, state, bqq_dept_id, bqq_par_dept_id)
92: values ('4028e4d35b5ca4ee015b613eff483729', '第四车队', '110003006', '4028e4d35b5ca4ee015b60fa3e2f5a94', null, null, '2017-04-12 16:19:30', '402881e54c40d74d014c40d8407a0016', '1', 164, '1', null, null);
在这张表中有三个字段：dept_id 部门主键id;dept_name 部门名称;dept_code 部门编码；par_dept_id 父级部门id(首级部门为 -1);
- 当前节点遍历子节点（遍历当前部门下所有子部门包括本身）
1: select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
2: from sys_dept t
3: start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
4: connect by prior t.dept_id = t.par_dept_id
5: order by level, t.dept_code
结果：
dept_id=40288ac45a3c1e8b015a3c28b4ae01d6 是客运部主键，对其下的所有子部门进行遍历，同时用 order by level,dept_code 进行排序 以便达到实际生活中想要的数据；共31条数据，部分数据如图所示：
但是：
有问题啊，如果你想在上面的数据中获取层级在2也就是level=2的所有部门，发现刚开始的时候介绍的语言不起作用？并且会报ora-00933：sql命令未正确结束，why?
这个我暂时也没有得到研究出理论知识，但是改变下where level='2'的位置发现才会可以的。错误的和正确的sql我们对比一下，以后会用就行，要是路过的大神知道为什么，还请告知下，万分感谢！
错误sql:
正确sql:1: select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
2: from sys_dept t
3: start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
4: connect by prior t.dept_id = t.par_dept_id
5: where level = '2'
6: order by level, t.dept_code
7:
1: select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
2: from sys_dept t
3: where level = '2'
4: start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
5: connect by prior t.dept_id = t.par_dept_id
6: order by level, t.dept_code
当然了，这个对其他形式的where过滤所有返回记录没有影响的，这个只是一个例外！ -
sys_connect_by_path函数求父节点到子节点路径
简单介绍下，在oracle中sys_connect_by_path与connect by 一起使用，也就是先要有或建立一棵树，否则无用还会报错。它的主要作用体现在path上即路径，是可以吧一个父节点下的所有节点通过某个字符区分，然后链接在一个列中显示。
sys_connect_by_path(column,clear),其中column是字符型或能自动转换成字符型的列名，它的主要目的就是将父节点到当前节点的“path”按照指定的模式出现，char可以是单字符也可以是多字符，但不能使用列值中包含的字符，而且这个参数必须是常量，且不允许使用绑定变量，clear不要用逗号。
文字容易让人疲劳，放图和代码吧！1: select sys_connect_by_path(t.dept_name,'-->'),t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
2: from sys_dept t
3: start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
4: connect by prior t.dept_id = t.par_dept_id
5: order by level, t.dept_code
结果：
下面以最简单的情况进行示例说明：
select t.f_id, sys_connect_by_path(t.f_id, '\') as con_code,
sys_connect_by_path(t.f_name, '\') as con_name
from 表名 t
start with t.f_pid is null
connect by prior t.f_id = t.f_pid;
说明：其中的f_id为标识码，f_pid为父节点标识码，f_name为名称