PLSQL - 递归子查询RSF打破CONNECT BY LOOP限制

例如有表EMP_ROLES存有数据如下图

RNAME表示员工充当的角色名称,当一人身兼多职时,便会以+号串联各个角色

这是一种很聪明的存储方法,但有时我们却又可能需要拆出每个人的所有角色以便匹配,也就是说要以员工+单角色名称的维度来组织数据,如下图

棘手的地方在于RNAME的内容不是固定的:角色的组合有长有短,组合起来的顺序也可能不同,这使得SQL需要做到动态拆分,这让人容易想到使用正则表达式搭配层次查询拆分,比如:

SQL> select regexp_substr('Oracle_Dev_Share', '[^\_]+', 1, level) r
  2    from dual
  3  connect by level <= regexp_count('Oracle_Dev_Share', '\_') + 1;

R
-------
Oracle
Dev
Share

但如果在这张表上直接这样使用,结果将是一个灾难

PS:结果行数太多,所以这里加一个START WITH rid = 1以便看出问题原因

SQL> select rid, ename, regexp_substr(rname, '[^\+]+', 1, level) rname, level
  2    from emp_roles
  3   start with rid = 1
  4  connect by level <= regexp_count(rname, '\+') + 1;

 RID ENAME        RNAME  LEVEL
---- ------------ ------ -----
   1 YUSUF        DAD        1
   3 SCOTT        GCM        2
   5 TURNER       GCM        3
   4 MARTIN       MPC        2
   5 TURNER       GCM        3
   5 TURNER       MPC        2
   5 TURNER       GCM        3

7 rows selected

简单来说,这是一个未指示父子关系的放飞自我的递归,它只对LEVEL进行了一下筛选:首先YUSUF是第一层,然后全表作为他的下一层(LEVEL = 2),进行到这里要求RNAME的+号数量 ≥ 1,从而QINJINGBAO(角色是SON)被淘汰;下面第二层的各行分别来到第三层,依然是全表作为遴选对象,这里要求RNAME的+号数量 ≥ 2,唯有TURNER可以入选;第四层要求RNAME的+号数量 ≥ 3,不存在这样的行,递归结束

可想而知去掉START WITH rid = 1将得到什么

既然自由的递归不行,指示SQL在同一行上递归可以吗?这样也不行,因为这形成了递归循环(CONNECT BY loop)

SQL> select rid,
  2         ename,
  3         regexp_substr(rname, '[^\+]+', 1, level) rname
  4    from emp_roles
  5  connect by prior rid = rid
  6         and level <= regexp_count(rname, '\+') + 1;
select rid,
       ename,
       regexp_substr(rname, '[^\+]+', 1, level) rname
  from emp_roles
connect by prior rid = rid
       and level <= regexp_count(rname, '\+') + 1

ORA-01436: 用户数据中的 CONNECT BY 循环

递归子查询RSF是Oracle 11.2引入的新特性,它没有递归循环的限制,用RSF可以较为轻松地解决这个问题

SQL> with rsf_q(rid, ename, rname, lv) as
  2   (select er.rid, er.ename, regexp_substr(er.rname, '[^\+]+') rname, 1 lv
  3      from emp_roles er
  4    union all
  5    select er.rid,
  6           er.ename,
  7           regexp_substr(er.rname, '[^\+]+', 1, rq.lv + 1) rname,
  8           rq.lv + 1 lv
  9      from rsf_q rq, emp_roles er
 10     where rq.rid = er.rid
 11       and rq.lv <= regexp_count(er.rname, '\+'))
 12  search depth first by rid set rorder
 13  select rid, ename, rname from rsf_q;

 RID ENAME        RNAME
---- ------------ ------
   1 YUSUF        DAD
   2 QINJINGBAO   SON
   3 SCOTT        MPC
   3 SCOTT        GCM
   4 MARTIN       GCM
   4 MARTIN       MPC
   5 TURNER       MM
   5 TURNER       MPC
   5 TURNER       GCM

9 rows selected

在11.2以前,可以考虑构造虚拟行集来关联实现裂行

SQL> with e_roles as
  2   (select er.rid, er.ename, er.rname, regexp_count(er.rname, '\+') + 1 rcount
  3      from emp_roles er),
  4  pseudo_rows as
  5   (select level row_num
  6      from dual
  7    connect by level <= (select max(rcount) from e_roles))
  8  select er.rid,
  9         er.ename,
 10         regexp_substr(er.rname, '[^\+]+', 1, pr.row_num) rname
 11    from e_roles er, pseudo_rows pr
 12   where er.rcount >= pr.row_num
 13   order by er.rid, er.ename, pr.row_num;

 RID ENAME        RNAME
---- ------------ ------
   1 YUSUF        DAD
   2 QINJINGBAO   SON
   3 SCOTT        MPC
   3 SCOTT        GCM
   4 MARTIN       GCM
   4 MARTIN       MPC
   5 TURNER       MM
   5 TURNER       MPC
   5 TURNER       GCM

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值