Oracle 递归遍历

1、场景递归到第几层，例如递归到第2层

select level, --层级
wdj.*
from wip_discrete_jobs_v wdj
where 1 = 1
start with wdj.wip_entity_name = '08363790' --递归开始
connect by wdj.attribute3 = prior wdj.wip_entity_name
and level < 3;

2、一行数据出现两行

效果图：

select level rn from dual connect by level < 2 ;

3、利用递归快速构造大量测试数据

create table TB_TEST2 (
c_id varchar2(1000),
c_date date,
c_string varchar2(1000),
c_amount number,
c_guid varchar2(1000),
c_type varchar2(1000)
);

批量插入数据：

insert into TB_TEST2
select to_char(rownum) c_id,
sysdate + rownum / 24 / 3600 c_date,
dbms_random.string('x', 20) c_string,
trunc(dbms_random.value(0, 10000000)) c_amount,
sys_guid() c_gruid,
to_char(trunc(dbms_random.value(0, 2))) c_type
from dual
connect by level <= 1000

额外话题字符串排序

select to_number(x.c_id) num_asc,
x.c_date,
x.c_string,
x.c_amount,
x.c_guid,
x.c_type
from tb_test2 x
order by to_number(x.c_id) asc

简单树查找

案例: 查找上下级关系

oracle树查询需要使用到start with ... connect by prior...子句

--采用oracle树查询实现

select empno, ename, mgr, (prior ename) as mgrname --上级名称
from scott.emp
start with empno = 7566 --以7566为起点向下递归查找
connect by (prior empno) = mgr --上一级的员工编码等于本级的上级领导编码

根节点、叶子节点、分支节点

案例：判别是否为根节点、叶子节点、分支节点

根节点：整个树的最上层(即level = 1)

叶子节点：在树中没有子级(即connect_by_isleaf = 1)

分支节点：排除根节点以及不是叶子节点的都是分支节点(即connect_by_isleaf = 0 and level > 1)

select e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname,
level as 层级,
case
when level = 1 then
'根节点'
else
null
end as 是否根节点,
case
when connect_by_isleaf = 1 then
'叶子节点(该员工不属于其他员工的上级)'
else
null
end as 是否叶子节点,
case
when connect_by_isleaf = 0 and level > 1 then --需要排除根节点(level = 1)
'分支节点'
else
null
end as 是否分支节点
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr

查看根节点到当前节点的路径

select t.empno, t.ename, t.mgr, t.mgrname, substr(t.enames, 2) as enames
from (select e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname,
sys_connect_by_path(e.ename, ',') as enames
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr) t

树查询中的排序不能直接使用order by,而应该使用order siblings by, 对于树形数据,我们只需要针对同一条路径下的分支进行排序,这样就不会影响到整棵树的数据

--直接加order by

select lpad('-', (level - 1) * 2, '-') || e.empno as empno,
e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr
order by e.empno desc;

--使用order siblings by

select lpad('-', (level - 1) * 2, '-') || e.empno as empno,
e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr
order siblings by e.empno desc

树查找中的where子句

在树形查询中,where子句过滤的对象是树查询的结果,所以如果要先过滤再进行树查询,那么需要嵌套一个子查询
select e.empno, e.ename, e.mgr from scott.emp e where e.deptno = 20;
如上图，20号部门没有mgr is null的数据。
--先查询树数据,再进行过滤

select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname
from scott.emp e
where e.deptno = 20
start with e.mgr is null
connect by (prior empno) = e.mgr;

--等价于

select *
from (select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname
from scott.emp e
start with e.mgr is null
connect by (prior empno) = e.mgr) t
where t.deptno = 20;

如果使用以上sql直接加where的话，数据过滤就不正确了，下面那个sql就是等价的，它是先进行树查询，然后再过滤整个树查询结果，因为20号部门没有mgr is null的数据，而看下面的查询结果却返回了数据，所以数据查询不正确

正确的做法如下：

--先过滤数据,再进行树查询

select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname
from (select * from scott.emp where deptno = 20) e
where e.deptno = 20
start with e.mgr is null
connect by (prior empno) = e.mgr;

查询树形的一个分支

查询树形的分支不能使用where,只需要通过start with xx指定开始遍历的节点即可

案例：查询员工编码为7698(BLAKE)及其下属员工

--案例：查询员工编码为7698(BLAKE)及其下属员工

select e.empno,
e.ename,
e.mgr,
(prior ename) as 所属上级,
level as 层次,
decode(connect_by_isleaf, 1, '是', '否') as 是否叶子节点
from scott.emp e
start with e.empno = 7698
connect by (prior empno) = e.mgr

去除树形的一个分支

同查询树形的分支不能使用where,如果要去除树形的一个分支, 因为树查询的过滤条件通过connect by (xx = yy)指定,所以可以直接在connect by 后面加条件过滤即可

案例：去除员工编码为7698(BLAKE)及其下属员工在树查询结果中分支

select e.empno,
e.ename,
e.mgr,
(prior ename) as 所属上级,
level as 层次,
decode(connect_by_isleaf, 1, '是', '否') as 是否叶子节点
from scott.emp e
start with e.mgr is null
connect by (prior empno) = e.mgr
and e.empno != 7698 --去除某个分支