oracle sql 高级编程学习笔记（二十六)

• 一、RSF实例演示
• 二、RSF的限制条件
• 三、与connect by 不同点
• 四、复制connect by功能

• 1、level伪列
• 2、sys_connect_by_path函数
• 3、connect_by_root
• 4、connect_by_iscycle
• 5、connect_by_isleaf

递归子查询因子化　recursive subquery factoring RSF

在上篇文章中我们用connect by 演示了递归功能，本文将用RSF进行演示

一、RSF实例演示

实现connect by同样功能

with emp  as
 (select e.last_name, e.first_name, e.employee_id, e.manager_id
    from employees e
  ) ,
  emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl -- lvl只是别名
    from emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set order1
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
       er.first_name,
       er.employee_id,
       er.manager_id
  from emp_recurse er;

结果:

同样也能得到相同的结果，只是RSF方法看着有点冗长，但它的工作原理理解更简单，并且支持更复杂的查询,递归的with语句 需要有两个查询块：定位点成员和递归成员。这两个子查询块必须通过集合运算union all 结合到一起。前面那个就是定位点，后面那个就是递归成员。递归子查询必须引用定义子查询–这样做了，就是进行了递归。

二、RSF的限制条件

下面元素不能出现在RSF的递归成员中使用：
1、DISTINCT 关键字或GROUP BY 子句
2、MODEL 子句
3、聚合函数、但在select 列表中可以使用分析函数
4、query_name 的子查询
5、引用query_name作为右表的联结

三、与connect by 不同点

1、level伪列被lvl 取代
2、rsf查询返回列必须像这样 emp_recurse(last_name,first_name,employee_id,manager_id,lvl) 声明。
3、search depth first by last_name set order1 相当于order by siblings 一样的层级输出
而默认 是breadth first 。当然这并不是层级输出
breadth 演示

with emp  as
     (select e.last_name, e.first_name, e.employee_id, e.manager_id
        from employees e
      
      ) ,
      emp_recurse(last_name,
    first_name,
    employee_id,
    manager_id,
    lvl) as
     (select emp.last_name,
             emp.first_name,
             emp.employee_id,
             emp.manager_id,
             1 as lvl
        from emp
       where emp.manager_id is null
      union all
      select emp.last_name,
             emp.first_name,
             emp.employee_id,
             emp.manager_id,
             empr.lvl + 1 as lvl
        from emp
        join emp_recurse empr
          on empr.employee_id = emp.manager_id) 
    --改为 breadth 输出 不会按照层级排序一样
          search breadth first by last_name set order1
    select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name  last_name,
           er.first_name,
           er.employee_id,
           er.manager_id
      from emp_recurse er;
      结果并不是我们要的：

search 子句中指定了按照last_name 进行搜索，也可以指定first_name ,manager_id进行搜索，以控制每个层级中各行 的顺序。

四、复制connect by功能

connect by 函数，运算符，伪列如下

类型	名称	用途
函数	sys_connect_by_path	返回当前数据行的所有祖先
运算符	connect_by_root	返回根数据行的值
运算符	prior	用来表明层级型查询，在递归子查询中不需要
伪列	connect_by_iscycle	在层级中检测循环
参数	nocycle	connect by的参数，与connect_by_iscycle一起使用
伪列	connect_by_isleaf	标识叶子数据行
伪列	level	用来表明层级中的深度

1、level伪列

lvl 实现了connect by中的level作用

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.lvl
  from emp_recurse er
  order by  last_name_order;

2、sys_connect_by_path函数

本函数用于返回组成层级的直到当前行的值。

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id is null
  order    siblings by emp.last_name;

可以看到 sys_connect_by_path函数建立了一个从根到当前节点之间层级。这个函数不能在RSF查询中使用，同样可以使用与产生level相同的办法实现。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl,
         ':'||to_char(emp.last_name) path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path||':'||emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.path
  from emp_recurse er
  order by  last_name_order;

大家会发现通过下面的改动可以实现根元素之前没有分隔符，并且分隔符还能修改为’,’，

—修改分隔符为’,'演示

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl,
         emp.last_name  path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path||','||emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.path
  from emp_recurse er
  order by  last_name_order;

但是sys_connect_by_path函数中根节点的分隔符是无法去掉：

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
--把分隔符改为','
  sys_connect_by_path(emp.last_name,',') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id is null
  order    siblings by emp.last_name;

3、connect_by_root

connect_by_root 强化了connect by 的语法，使得它可以返回当前行的根节点。

select level,LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_root last_name root,
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  where connect_by_root last_name='Cambrault'
  connect by  prior emp.employee_id=emp.manager_id
  order    siblings by emp.last_name;

可以看到 sys_connect_by_path可以获取到节点的根节点

同样也可以在RSF中复制该功能。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1               as lvl,
         emp.last_name   path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path || ':' || emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set last_name_order,
emps as
 (select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
--Cambrault 属于第二层级，所以截取路径中的第二个名字即可
         substr(er.path, instr(er.path, ':', 1,1)+1, instr(er.path, ':', 1,2)-instr(er.path, ':', 1,1)-1) root,
         substr(er.path, instr(er.path, ':', 1,1)+1) path
    from emp_recurse er
     )
select last_name, root, path from emps where emps.root = 'Cambrault';

显而易见，connect_by_root功能更加灵活，对于获取第三个层级，同样不需要改代码，而
RSF中还需修改截取部分的代码。
–获取递归层次是3 指定根节点’Hunold’的员工

select level,LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_root last_name root,
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  where connect_by_root last_name='Hunold'
  connect by  prior emp.employee_id=emp.manager_id
  order    siblings by emp.last_name;

而对于RSF还需修改代码

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1               as lvl,
         emp.last_name   path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path || ':' || emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set last_name_order,
emps as
 (select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
-- 递归层次是3，截取第三个冒号和第二个冒号之间
         substr(er.path, instr(er.path, ':', 1,2)+1, instr(er.path, ':', 1,3)-instr(er.path, ':', 1,2)-1) root,
          substr(er.path, instr(er.path, ':', 1,2)+1) path
    from emp_recurse er
     )
select last_name, root, path from emps where emps.root = 'Hunold';

4、connect_by_iscycle

connect_by_iscycle 伪列使得在层级中检测循环变得很容易。

我们通过修改表中employees 表中King的上级为Hunold来演示

UPDATE employees E SET E.MANAGER_ID='103' WHERE E.EMPLOYEE_ID='100';

再来使用connect by 查询

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,',') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id ='100'
  order    siblings by emp.last_name;

提示ora-1436错误

接下来就是
nocycle和connect_by_iscycle大展拳脚的时候了

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,',') path,
  connect_by_iscycle
  from employees emp
  connect by  nocycle prior emp.employee_id=emp.manager_id
  start with emp.manager_id ='100'
  order    siblings by emp.last_name;

connect_by_iscycle 值为1这行数据导致了错误发生。所以解决问题的办法就是把Kind 的manager_id 设置为空。同样在RSF中也能实现相同的功能。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl
    from employees emp
   where emp.employee_id ='100'
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
--set is_or_cycle 表示别名 to 只能设置但字符值。
      cycle  employee_id set is_or_cycle to '1' default '0'  
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, employee_id,
manager_id,is_or_cycle
  from emp_recurse er
  order by  last_name_order;

5、connect_by_isleaf

connect_by_isleaf 可以很方便的在层级函数中识别叶子节点。

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_isleaf
  from employees emp
  connect by    prior emp.employee_id=emp.manager_id
  start with emp.manager_id  is null
  order    siblings by emp.last_name;

结果中叶子节点被标注了出来，即为1的数据行。

RSF想要复制该功能会有一点挑战性。用lead 函数实现

with emp_recurse (last_name,
first_name,
employee_id,
manager_id,
as_level) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as as_level
    from employees emp
   where emp.manager_id is null  
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.as_level + 1 as as_level
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', as_level * 2 - 1, ' ') || er.last_name last_name, as_level,
lead(as_level)over(order by last_name_order ) next_as_level,
case when as_level-lead(as_level)over(order by last_name_order )<0
  then 0
    else 1 end is_leaf
  from emp_recurse er
  order by  last_name_order;

但是需要主要 的是lead函数是非常依赖排序的
所以当把depth改为breadth时

with emp_recurse (last_name,
first_name,
employee_id,
manager_id,
as_level) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as as_level
    from employees emp
   where emp.manager_id is null  
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.as_level + 1 as as_level
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search breadth first by last_name set   last_name_order
select lpad(' ', as_level * 2 - 1, ' ') || er.last_name last_name, as_level,
lead(as_level)over(order by last_name_order ) next_as_level,
case when as_level-lead(as_level)over(order by last_name_order )<0
  then 0
    else 1 end is_leaf
  from emp_recurse er
  order by  last_name_order;

很明显得到的结果时错误的。