Oracle学习笔记|PLSQL - 递归子查询RSF打破CONNECT BY LOOP限制 plsql|sql|数据库|oracle

【Oracle学习笔记|PLSQL - 递归子查询RSF打破CONNECT BY LOOP限制】例如有表EMP_ROLES存有数据如下图

文章图片

RNAME表示员工充当的角色名称，当一人身兼多职时，便会以+号串联各个角色
这是一种很聪明的存储方法，但有时我们却又可能需要拆出每个人的所有角色以便匹配，也就是说要以员工+单角色名称的维度来组织数据，如下图

文章图片

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

SQL> select regexp_substr('Oracle_Dev_Share', '[^\_]+', 1, level) r 2from dual 3connect 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 2from emp_roles 3start with rid = 1 4connect by level <= regexp_count(rname, '\+') + 1; RID ENAMERNAMELEVEL ---- ------------ ------ ----- 1 YUSUFDAD1 3 SCOTTGCM2 5 TURNERGCM3 4 MARTINMPC2 5 TURNERGCM3 5 TURNERMPC2 5 TURNERGCM37 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, 2ename, 3regexp_substr(rname, '[^\+]+', 1, level) rname 4from emp_roles 5connect by prior rid = rid 6and 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, '\+') + 1ORA-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 3from emp_roles er 4union all 5select er.rid, 6er.ename, 7regexp_substr(er.rname, '[^\+]+', 1, rq.lv + 1) rname, 8rq.lv + 1 lv 9from rsf_q rq, emp_roles er 10where rq.rid = er.rid 11and rq.lv <= regexp_count(er.rname, '\+')) 12search depth first by rid set rorder 13select rid, ename, rname from rsf_q; RID ENAMERNAME ---- ------------ ------ 1 YUSUFDAD 2 QINJINGBAOSON 3 SCOTTMPC 3 SCOTTGCM 4 MARTINGCM 4 MARTINMPC 5 TURNERMM 5 TURNERMPC 5 TURNERGCM9 rows selected

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

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