您现在的位置是：网站首页> 编程资料编程资料
Oracle递归树形结构查询功能_oracle_
2023-05-27
489人已围观
简介 Oracle递归树形结构查询功能_oracle_
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去解决，这也是我一直保持的想法并坚持着。
创建表：
create table SYS_DEPT ( dept_id VARCHAR2(32) not null, dept_name VARCHAR2(128), dept_code VARCHAR2(32), par_dept_id VARCHAR2(32), dept_leader VARCHAR2(32), dept_desc VARCHAR2(256), create_time CHAR(19), org_id VARCHAR2(32), dept_type VARCHAR2(1), order_id NUMBER, state CHAR(1) default '1', bqq_dept_id VARCHAR2(128), bqq_par_dept_id VARCHAR2(128) ) -- Add comments to the table comment on table SYS_DEPT is '部门信息，和单位多对一'; -- Add comments to the columns comment on column SYS_DEPT.dept_id is '主键'; comment on column SYS_DEPT.dept_name is '名称'; comment on column SYS_DEPT.dept_code is '编码，用于递归'; comment on column SYS_DEPT.par_dept_id is '父级部门ID'; comment on column SYS_DEPT.dept_leader is '部门领导ID'; comment on column SYS_DEPT.dept_desc is '部门描述'; comment on column SYS_DEPT.create_time is 'yyyy-mm-dd HHMMSS'; comment on column SYS_DEPT.org_id is '单位ID'; comment on column SYS_DEPT.dept_type is '1：正式部门；2：虚拟部门（用于通讯录展示）'; comment on column SYS_DEPT.order_id is '排序字段'; comment on column SYS_DEPT.state is '0：无效；1：有效'; comment on column SYS_DEPT.bqq_dept_id is '企业qqdeptid'; comment on column SYS_DEPT.bqq_par_dept_id is '企业qq父类deptid';
插入测试数据：
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) values ('40288ac45a3c1e8b015a3c28b4ae01d6', '客运部', '110', '-1', null, null, '2017-02-14 182625', '402881e54c40d74d014c40d8407a0016', '1', 29, '1', null, null); 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) values ('4028e4d35b5ca4ee015b60f98a1d59b3', '综合室', '110001', '40288ac45a3c1e8b015a3c28b4ae01d6', null, null, '2017-04-12 150338', '402881e54c40d74d014c40d8407a0016', '1', 63, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6134d9ff2946', '生产调度', '110001001', '4028e4d35b5ca4ee015b60f98a1d59b3', null, null, '2017-04-12 160825', '402881e54c40d74d014c40d8407a0016', '1', 135, '1', null, null); 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) values ('4028e4d35b5ca4ee015b60f9fae95a44', '站务中心', '110002', '40288ac45a3c1e8b015a3c28b4ae01d6', null, null, '2017-04-12 150407', '402881e54c40d74d014c40d8407a0016', '1', 64, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613562be2a08', '东岗站', '110002001', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 160900', '402881e54c40d74d014c40d8407a0016', '1', 136, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6135f9de2aca', '焦家湾站', '110002002', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 160939', '402881e54c40d74d014c40d8407a0016', '1', 137, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6136a3e22bb2', '拱星墩站', '110002003', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161022', '402881e54c40d74d014c40d8407a0016', '1', 138, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613723bb2c5f', '省气象局站', '110002004', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161055', '402881e54c40d74d014c40d8407a0016', '1', 139, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6137a5772d06', '五里铺站', '110002005', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161128', '402881e54c40d74d014c40d8407a0016', '1', 140, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6137e4e72d57', '兰州大学站', '110002006', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161144', '402881e54c40d74d014c40d8407a0016', '1', 141, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613840112dd0', '东方红广场站', '110002007', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161208', '402881e54c40d74d014c40d8407a0016', '1', 142, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6138765c2e12', '省政府站', '110002008', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161221', '402881e54c40d74d014c40d8407a0016', '1', 143, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6138b84b2e68', '西关站', '110002009', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161238', '402881e54c40d74d014c40d8407a0016', '1', 145, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6139390e2f06', '文化宫站', '110002010', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161311', '402881e54c40d74d014c40d8407a0016', '1', 146, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613980a82f61', '小西湖站', '110002011', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161330', '402881e54c40d74d014c40d8407a0016', '1', 147, '1', null, null); 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) values ('4028e4d35b5ca4ee015b6139c1dc2fb4', '七里河站', '110002012', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161346', '402881e54c40d74d014c40d8407a0016', '1', 148, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613a24853047', '西站十字站', '110002013', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161412', '402881e54c40d74d014c40d8407a0016', '1', 149, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613a81f030ce', '兰州西站北广场站', '110002014', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161436', '402881e54c40d74d014c40d8407a0016', '1', 150, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613ad627313d', '土门墩站', '110002015', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161457', '402881e54c40d74d014c40d8407a0016', '1', 151, '1', null, null); 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) values ('4028e4d35b5ca4ee015b613b394c31c6', '马滩站', '110002016', '4028e4d35b5ca4ee015b60f9fae95a44', null, null, '2017-04-12 161522'
相关内容
- Oracle连接配置解读_oracle_
- Oracle12c图形化&静默安装踩坑的方法步骤_oracle_
- windows本地安装配置oracle客户端完整流程（图文版）_oracle_
- win10系统安装oracle11g时遇到INS-13001环境不满足最低要求解决办法_oracle_
- PLSQL安装、汉化和激活的方法步骤实现_oracle_
- plsql配置tnsnames.ora的实现方法_oracle_
- plsql 连接oracle数据库详细配置的方法步骤_oracle_
- PLSQL Developer连接Oracle11g 64位数据库配置详解(图文)_oracle_
- PLSQL配置远程Oracle数据库连接的示例代码_oracle_
- Oracle如何获取系统当前时间等操作实例_oracle_
点击排行
本栏推荐
