在学习RSF之前，需要先理解如下内容：
- Subquery Factoring的基本使用方法
- 层次查询的语法及相关功能的实现
Oracle中称为Recursive Subquery Factoring : RSF, ANSI标准中的名称是递归公共表表达式（Recursive Common Table Expression）
RSF是Oracle 11g R2版本中的新特性之一，它按照ANSI标准进行了设计，因此，在其他的数据库当中也都能够以同样的语法使用。
与层次查询（Hierarcical Query）相比，它有更好的兼容性。虽然在实现某些层次结构查询的需求时，层次查询的语法相对简洁，而且在性能上也可能会优于RSF，但是，正是由于它的兼容性，以及能够更加灵活地完善层次查询功能的特点，使它在某些场景当中相当有用。
学习RSF的一个比较好的方法也是将其与层次查询进行比较，在使用RSF实现层次查询中各种功能的同时，能够更加深刻地理解RSF的特性，同时也会巩固对层次查询的理解。
RSF的限制条件
-
DISTINCT关键字或GROUP BY子句 -
MODEL子句 - 聚合函数。但是，在
SELECT列表中可以使用分析函数 - 引用
query_name的子查询 - 引用
query_name作为右表的外联接
首先看一个例子：
实现对SCOTT Schema的EMP表中的上下级关系查询的需求，得到如下结果：
LV EMPNO ENAME MGR -------------------------
1 7839 KING -- 定位点成员部分输出的列 --
2 7566 JONES 7839 -------------------------
3 7788 SCOTT 7566 -- --
4 7876 ADAMS 7788 -- --
3 7902 FORD 7566 -- 递归成员部分定义后，一 --
4 7369 SMITH 7902 -- 行一行地反复递归查询并 --
2 7698 BLAKE 7839 -- 与前一次执行后的结果合 --
3 7499 ALLEN 7698 -- 并（UNION ALL） --
3 7521 WARD 7698 -- --
3 7654 MARTIN 7698 -- --
3 7844 TURNER 7698 -- --
3 7900 JAMES 7698 -- --
2 7782 CLARK 7839 -- --
3 7934 MILLER 7782 -------------------------
使用层次查询实现该功能：
SELECT LEVEL LV
, EMPNO
, LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
, MGR
FROM EMP_RSF
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;
使用RSF实现该功能：
WITH E( LV, EMPNO, ENAME, MGR ) AS( --RSF需要对数据集合中的列进行定义之后才能使用。
SELECT 1 LV --<定义层次查询LEVEL的初始值> -----------------------
, EMPNO -- 定位 --
, ENAME -- 成员 --
, MGR -- 部分 --
FROM EMP_RSF -- --
WHERE MGR IS NULL --<START WITH> -----------------------
UNION ALL --使用UNION ALL连接定位点成员部分和递归部分
SELECT Y.LV+1 --<定义LEVEL值不断递增> -----------------------
, X.EMPNO -- 递归 --
, X.ENAME -- 成员 --
, X.MGR -- 部分 --
FROM EMP_RSF X, E Y -- --
WHERE Y.EMPNO = X.MGR) --<CONNECT BY> -----------------------
SEARCH DEPTH FIRST BY EMPNO SET ORDER1 --<ORDER SIBLING BY> 使用SEARCH子句控制递归的方向
SELECT LV
, EMPNO
, LPAD(' ', LV*2-1,' ')||ENAME ENAME
, MGR
FROM E;
通过这个例子可以看出，RSF能够实现层次查询中的功能，虽然从语法上来看，比层次查询要复杂一些。
从RSF实现的代码中可以看出：
- RSF实现时需要对数据集合中的列进行定义
- RSF分为定位点成员(锚点成员：Anchor Member)和递归成员(Recursive Member)两部分，且两部分之间需要使用UNION ALL进行连接
- 定位点成员部分中的WHERE子句用来定位开始的点，对应层次查询中的START WITH子句
- 在递归成员部分当中定义递归的规律，这部分相当于层次查询中的CONNECT BY子句，而且还可以在递归成员的SELECT语句后对列进行操作
- 可以使用SEARCH子句控制递归的方向，其中分为深度优先和广度优先。默认选项是广度优先，但是大部分层次结构是深度优先。在SEARCH子句可以定义一个顺序的伪列，该伪列对应于层次查询中的ORDER SIBLINGS BY中定义的列
以上两个图只是说明概念，与实际输出结果无直接关系。
- 其实内部实际执行的过程还是按照广度优先的方式进行，只是在最终输出结果时，会根据SEARCH子句中指定的方式输出。
层次查询其他功能实现的比较
使用RSF实现层次查询的SYS_CONNECT_BY_PATH：
--Hierarcical Query
SELECT LEVEL LV
, EMPNO
, LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
, MGR
, SYS_CONNECT_BY_PATH(ENAME,':')
FROM EMP_RSF
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;
--RSF
WITH E( LV, EMPNO, ENAME, MGR, PATH) AS(
SELECT 1 LV, EMPNO, ENAME, MGR
, ':'||ENAME PATH
FROM EMP_RSF
WHERE MGR IS NULL
UNION ALL
SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR
, Y.PATH||':'||X.ENAME
FROM EMP_RSF X, E Y
WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
SELECT LV
, EMPNO
, LPAD(' ', LV*2-1,' ')||ENAME ENAME
, MGR
, PATH
FROM E;
LV EMPNO ENAME MGR PATH
1 7839 KING :KING
2 7566 JONES 7839 :KING:JONES
3 7788 SCOTT 7566 :KING:JONES:SCOTT
4 7876 ADAMS 7788 :KING:JONES:SCOTT:ADAMS
3 7902 FORD 7566 :KING:JONES:FORD
4 7369 SMITH 7902 :KING:JONES:FORD:SMITH
2 7698 BLAKE 7839 :KING:BLAKE
3 7499 ALLEN 7698 :KING:BLAKE:ALLEN
3 7521 WARD 7698 :KING:BLAKE:WARD
3 7654 MARTIN 7698 :KING:BLAKE:MARTIN
3 7844 TURNER 7698 :KING:BLAKE:TURNER
3 7900 JAMES 7698 :KING:BLAKE:JAMES
2 7782 CLARK 7839 :KING:CLARK
3 7934 MILLER 7782 :KING:CLARK:MILLER
比较一下使用两种方法实现SYS_CONNECT_BY_PATH的这两段代码。首先不讨论是否简洁，起码从功能上都是一样的。
但是，有一个小差别是，如果在层次查询当中，想取消PATH中第一个：号的话，需要在SYS_CONNECT_BY_PATH前使用LTRIM函数去掉引号，但是使用RSF的话，只需要在定义起点的时候，当第一个引号字符串去掉即可。
使用RSF实现层次查询的CONNECT_BY_ROOT：
--Hierarcical Query
SELECT LEVEL LV
, EMPNO
, LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
, MGR
, CONNECT_BY_ROOT ENAME ROOT
, SYS_CONNECT_BY_PATH(ENAME,':')
FROM EMP_RSF
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;
--RSF
WITH E( LV, EMPNO, ENAME, MGR, PATH) AS(
SELECT 1 LV, EMPNO, ENAME, MGR, ':'||ENAME PATH
FROM EMP_RSF
WHERE MGR IS NULL
UNION ALL
SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR, Y.PATH||':'||X.ENAME
FROM EMP_RSF X, E Y
WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
SELECT LV
, EMPNO
, LPAD(' ', LV*2-1,' ')||ENAME ENAME
, MGR
, NVL(SUBSTR(PATH, 2, INSTR(PATH, ':', 2)-2),ENAME) ROOT
, PATH
FROM E;
LV EMPNO ENAME MGR ROOT PATH
1 7839 KING KING :KING
2 7566 JONES 7839 KING :KING:JONES
3 7788 SCOTT 7566 KING :KING:JONES:SCOTT
4 7876 ADAMS 7788 KING :KING:JONES:SCOTT:ADAMS
3 7902 FORD 7566 KING :KING:JONES:FORD
4 7369 SMITH 7902 KING :KING:JONES:FORD:SMITH
2 7698 BLAKE 7839 KING :KING:BLAKE
3 7499 ALLEN 7698 KING :KING:BLAKE:ALLEN
3 7521 WARD 7698 KING :KING:BLAKE:WARD
3 7654 MARTIN 7698 KING :KING:BLAKE:MARTIN
3 7844 TURNER 7698 KING :KING:BLAKE:TURNER
3 7900 JAMES 7698 KING :KING:BLAKE:JAMES
2 7782 CLARK 7839 KING :KING:CLARK
3 7934 MILLER 7782 KING :KING:CLARK:MILLER
使用RSF实现层次查询的CONNECT_BY_ISCYCLE和NOCYCLE：
在层次查询中，CONNECT_BY_ISCYCLE主要用于检验是否存在循环，所谓的CYCLE，是只如下图：
CYCLE图片
此时，在查询的过程当中就会出现死循环，因此，就会弹出错误。
因此，在层次查询当中使用NOCYCLE来控制不产生多余的死循环，而在RSF中则使用CYCLE子句来对其进行控制
根据上面图中所示，对表中数据进行修改，使SMITH称为KING的父节点。
--Update
UPDATE EMP_RSF SET MGR = 7369 WHERE MGR IS NULL;
--Hierarcical Query
SELECT LEVEL LV
, EMPNO
, LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
, MGR
, CONNECT_BY_ISCYCLE ISCYCLE
FROM EMP_RSF
START WITH EMPNO = 7839
CONNECT BY NOCYCLE PRIOR EMPNO = MGR;
LV EMPNO ENAME MGR ISCYCLE
1 7839 KING 7369 0
2 7566 JONES 7839 0
3 7788 SCOTT 7566 0
4 7876 ADAMS 7788 0
3 7902 FORD 7566 0
4 7369 SMITH 7902 1
2 7698 BLAKE 7839 0
3 7499 ALLEN 7698 0
3 7521 WARD 7698 0
3 7654 MARTIN 7698 0
3 7844 TURNER 7698 0
3 7900 JAMES 7698 0
2 7782 CLARK 7839 0
3 7934 MILLER 7782 0
--RSF
WITH E( LV, EMPNO, ENAME, MGR) AS(
SELECT 1 LV, EMPNO, ENAME, MGR
FROM EMP_RSF
WHERE EMPNO = 7839
UNION ALL
SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR
FROM EMP_RSF X, E Y
WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
CYCLE EMPNO SET ISCYCLE TO '1' DEFAULT '0'
SELECT LV
, EMPNO
, LPAD(' ', LV*2-1,' ')||ENAME ENAME
, MGR
, ISCYCLE
FROM E;
LV EMPNO ENAME MGR ISCYCLE
1 7839 KING 7369 0
2 7566 JONES 7839 0
3 7788 SCOTT 7566 0
4 7876 ADAMS 7788 0
3 7902 FORD 7566 0
4 7369 SMITH 7902 0
5 7839 KING 7369 1
2 7698 BLAKE 7839 0
3 7499 ALLEN 7698 0
3 7521 WARD 7698 0
3 7654 MARTIN 7698 0
3 7844 TURNER 7698 0
3 7900 JAMES 7698 0
2 7782 CLARK 7839 0
3 7934 MILLER 7782 0
对两种方式实现的结果进行比较，可以发现RSF实现的结果当中会多出一行，并标记为1来表示发生的循环。而使用层次查询时，如果想使用CONNECT_BY_ISCYCLE的话，必须同时使用NOCYCLE，并且是在最下一层节点上标记是否发生了循环。
使用RSF实现层次查询的CONNECT_BY_ISLEAF：
--Hierarcical Query
SELECT LEVEL LV
, EMPNO
, LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
, MGR
, CONNECT_BY_ISLEAF ISLEAF
FROM EMP_RSF
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
--RSF(Out Join)
WITH LEAVES AS(
SELECT EMPNO
FROM EMP
WHERE EMPNO NOT IN (SELECT DISTINCT MGR
FROM EMP_RSF
WHERE MGR IS NOT NULL)),
E(LV, EMPNO, ENAME, MGR) AS(
SELECT 1 LV, EMPNO, ENAME, MGR
FROM EMP_RSF
WHERE MGR IS NULL
UNION ALL
SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR
FROM EMP_RSF X, E Y
WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
SELECT E.LV, E.EMPNO
, LPAD(' ', LV*2-1,' ')||ENAME ENAME, MGR
, DECODE(LEAVES.EMPNO, NULL, 0, 1) ISLEAF
FROM E LEFT JOIN LEAVES
ON (E.EMPNO = LEAVES.EMPNO);
--RSF(Analysis Function)
WITH E(LV, EMPNO, ENAME, MGR) AS(
SELECT 1 LV, EMPNO, ENAME, MGR
FROM EMP_RSF
WHERE MGR IS NULL
UNION ALL
SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR
FROM EMP_RSF X, E Y
WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
SELECT E.LV, E.EMPNO, LPAD(' ', LV*2-1,' ')||ENAME ENAME, MGR
, CASE WHEN LV - LEAD(LV) OVER(ORDER BY ORDER1) < 0 THEN 0 ELSE 1 END ISLEAF
FROM E;
LV EMPNO ENAME MGR ISLEAF
1 7839 KING 0
2 7566 JONES 7839 0
3 7788 SCOTT 7566 0
4 7876 ADAMS 7788 1
3 7902 FORD 7566 0
4 7369 SMITH 7902 1
2 7698 BLAKE 7839 0
3 7499 ALLEN 7698 1
3 7521 WARD 7698 1
3 7654 MARTIN 7698 1
3 7844 TURNER 7698 1
3 7900 JAMES 7698 1
2 7782 CLARK 7839 0
3 7934 MILLER 7782 1
网友评论