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

迷南。 2022-05-10 10:46 389阅读 0赞

###  ###

*  一、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;

结果:  
![在这里插入图片描述][70]

同样也能得到相同的结果，只是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;
          结果并不是我们要的：

![在这里插入图片描述][70 1]  
search 子句中指定了按照last\_name 进行搜索，也可以指定first\_name ,manager\_id进行搜索，以控制每个层级中各行 的顺序。

# 四、复制connect by功能 #

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

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

## 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;

![在这里插入图片描述][70 2]

## 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;

![在这里插入图片描述][70 3]

可以看到 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;

![在这里插入图片描述][70 4]

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

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

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;

![在这里插入图片描述][70 5]

但是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;

![在这里插入图片描述][70 6]

## 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可以获取到节点的根节点  
![在这里插入图片描述][70 7]

同样也可以在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';

![在这里插入图片描述][70 8]

显而易见，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;

![在这里插入图片描述][70 9]

而对于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错误  
![在这里插入图片描述][70 10]

接下来就是  
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;

![在这里插入图片描述][70 11]

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;

![在这里插入图片描述][70 12]

## 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;

![在这里插入图片描述][70 13]

结果中叶子节点被标注了出来，即为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;

![在这里插入图片描述][70 14]

但是需要主要 的是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;

很明显得到的结果时错误的。  
![在这里插入图片描述][70 15]

[70]: /images/20220510/afaaa8f1b59d480095040ee48fb6cf3a.png
[70 1]: /images/20220510/bdac9b5922474b7880c69564b601c47a.png
[70 2]: /images/20220510/934f1c137ec641a1a4c8696a8f7f78ce.png
[70 3]: /images/20220510/afa67cea75fa4ae9814c415654e5a594.png
[70 4]: /images/20220510/b487ea39031d46e8840fd5319d71df6c.png
[70 5]: /images/20220510/9398885d2c5147fd9068d7b8c2334fad.png
[70 6]: /images/20220510/e99c85ecf21d4d7eb9df590c2fbb16cc.png
[70 7]: /images/20220510/6a384777ba574305bfbfc00220c588fa.png
[70 8]: /images/20220510/f5229e1c316e4e63bf7334aad80eebc0.png
[70 9]: /images/20220510/fe968a398e104e56b1019b8f4cf0b005.png
[70 10]: /images/20220510/515e33d294124b0199c9e9502e1f1f71.png
[70 11]: /images/20220510/6746e786a9ac4a9b816713ebd9b46c31.png
[70 12]: /images/20220510/802b95a756c34a1faad404f48c232412.png
[70 13]: /images/20220510/c0190c1badd542a4be0ded9980c61620.png
[70 14]: /images/20220510/6dca6c904e5041f2a03926f62ac25e30.png
[70 15]: /images/20220510/7bbbd8515d1d494e8ac94853c8cf998b.png