在學(xué)習(xí)RSF之前证膨，需要先理解如下內(nèi)容：
- Subquery Factoring的基本使用方法
- 層次查詢(xún)的語(yǔ)法及相關(guān)功能的實(shí)現(xiàn)
Oracle中稱(chēng)為Recursive Subquery Factoring : RSF, ANSI標(biāo)準(zhǔn)中的名稱(chēng)是遞歸公共表表達(dá)式（Recursive Common Table Expression）
RSF是Oracle 11g R2版本中的新特性之一仅叫，它按照ANSI標(biāo)準(zhǔn)進(jìn)行了設(shè)計(jì)，因此情屹，在其他的數(shù)據(jù)庫(kù)當(dāng)中也都能夠以同樣的語(yǔ)法使用军掂。
與層次查詢(xún)（Hierarcical Query）相比嫡意，它有更好的兼容性秆撮。雖然在實(shí)現(xiàn)某些層次結(jié)構(gòu)查詢(xún)的需求時(shí)，層次查詢(xún)的語(yǔ)法相對(duì)簡(jiǎn)潔船侧，而且在性能上也可能會(huì)優(yōu)于RSF欠气，但是，正是由于它的兼容性镜撩，以及能夠更加靈活地完善層次查詢(xún)功能的特點(diǎn)预柒，使它在某些場(chǎng)景當(dāng)中相當(dāng)有用。
學(xué)習(xí)RSF的一個(gè)比較好的方法也是將其與層次查詢(xún)進(jìn)行比較，在使用RSF實(shí)現(xiàn)層次查詢(xún)中各種功能的同時(shí)宜鸯，能夠更加深刻地理解RSF的特性憔古，同時(shí)也會(huì)鞏固對(duì)層次查詢(xún)的理解。
RSF的限制條件
-
DISTINCT關(guān)鍵字或GROUP BY子句 -
MODEL子句 - 聚合函數(shù)淋袖。但是鸿市，在
SELECT列表中可以使用分析函數(shù) - 引用
query_name的子查詢(xún) - 引用
query_name作為右表的外聯(lián)接
首先看一個(gè)例子：
實(shí)現(xiàn)對(duì)SCOTT Schema的EMP表中的上下級(jí)關(guān)系查詢(xún)的需求，得到如下結(jié)果：
LV EMPNO ENAME MGR -------------------------
1 7839 KING -- 定位點(diǎn)成員部分輸出的列 --
2 7566 JONES 7839 -------------------------
3 7788 SCOTT 7566 -- --
4 7876 ADAMS 7788 -- --
3 7902 FORD 7566 -- 遞歸成員部分定義后适贸，一 --
4 7369 SMITH 7902 -- 行一行地反復(fù)遞歸查詢(xún)并 --
2 7698 BLAKE 7839 -- 與前一次執(zhí)行后的結(jié)果合 --
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 -------------------------
使用層次查詢(xún)實(shí)現(xiàn)該功能：
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實(shí)現(xiàn)該功能：
WITH E( LV, EMPNO, ENAME, MGR ) AS( --RSF需要對(duì)數(shù)據(jù)集合中的列進(jìn)行定義之后才能使用灸芳。
SELECT 1 LV --<定義層次查詢(xún)LEVEL的初始值> -----------------------
, EMPNO -- 定位 --
, ENAME -- 成員 --
, MGR -- 部分 --
FROM EMP_RSF -- --
WHERE MGR IS NULL --<START WITH> -----------------------
UNION ALL --使用UNION ALL連接定位點(diǎn)成員部分和遞歸部分
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;
通過(guò)這個(gè)例子可以看出，RSF能夠?qū)崿F(xiàn)層次查詢(xún)中的功能拜姿，雖然從語(yǔ)法上來(lái)看，比層次查詢(xún)要復(fù)雜一些冯遂。
從RSF實(shí)現(xiàn)的代碼中可以看出：
- RSF實(shí)現(xiàn)時(shí)需要對(duì)數(shù)據(jù)集合中的列進(jìn)行定義
- RSF分為定位點(diǎn)成員(錨點(diǎn)成員：Anchor Member)和遞歸成員(Recursive Member)兩部分蕊肥，且兩部分之間需要使用UNION ALL進(jìn)行連接
- 定位點(diǎn)成員部分中的WHERE子句用來(lái)定位開(kāi)始的點(diǎn)，對(duì)應(yīng)層次查詢(xún)中的START WITH子句
- 在遞歸成員部分當(dāng)中定義遞歸的規(guī)律蛤肌，這部分相當(dāng)于層次查詢(xún)中的CONNECT BY子句壁却，而且還可以在遞歸成員的SELECT語(yǔ)句后對(duì)列進(jìn)行操作
- 可以使用SEARCH子句控制遞歸的方向，其中分為深度優(yōu)先和廣度優(yōu)先裸准。默認(rèn)選項(xiàng)是廣度優(yōu)先展东，但是大部分層次結(jié)構(gòu)是深度優(yōu)先。在SEARCH子句可以定義一個(gè)順序的偽列炒俱，該偽列對(duì)應(yīng)于層次查詢(xún)中的ORDER SIBLINGS BY中定義的列
以上兩個(gè)圖只是說(shuō)明概念盐肃，與實(shí)際輸出結(jié)果無(wú)直接關(guān)系。
- 其實(shí)內(nèi)部實(shí)際執(zhí)行的過(guò)程還是按照廣度優(yōu)先的方式進(jìn)行权悟，只是在最終輸出結(jié)果時(shí)砸王，會(huì)根據(jù)SEARCH子句中指定的方式輸出。
層次查詢(xún)其他功能實(shí)現(xiàn)的比較
使用RSF實(shí)現(xiàn)層次查詢(xún)的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
比較一下使用兩種方法實(shí)現(xiàn)SYS_CONNECT_BY_PATH的這兩段代碼峦阁。首先不討論是否簡(jiǎn)潔谦铃，起碼從功能上都是一樣的。
但是榔昔，有一個(gè)小差別是驹闰，如果在層次查詢(xún)當(dāng)中，想取消PATH中第一個(gè)：號(hào)的話(huà)撒会，需要在SYS_CONNECT_BY_PATH前使用LTRIM函數(shù)去掉引號(hào)嘹朗，但是使用RSF的話(huà)，只需要在定義起點(diǎn)的時(shí)候茧彤，當(dāng)?shù)谝粋€(gè)引號(hào)字符串去掉即可骡显。
使用RSF實(shí)現(xiàn)層次查詢(xún)的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實(shí)現(xiàn)層次查詢(xún)的CONNECT_BY_ISCYCLE和NOCYCLE：
在層次查詢(xún)中，CONNECT_BY_ISCYCLE主要用于檢驗(yàn)是否存在循環(huán)，所謂的CYCLE惫谤，是只如下圖：
CYCLE圖片
此時(shí)壁顶，在查詢(xún)的過(guò)程當(dāng)中就會(huì)出現(xiàn)死循環(huán)，因此溜歪，就會(huì)彈出錯(cuò)誤若专。
因此，在層次查詢(xún)當(dāng)中使用NOCYCLE來(lái)控制不產(chǎn)生多余的死循環(huán)蝴猪，而在RSF中則使用CYCLE子句來(lái)對(duì)其進(jìn)行控制
根據(jù)上面圖中所示调衰，對(duì)表中數(shù)據(jù)進(jìn)行修改，使SMITH稱(chēng)為KING的父節(jié)點(diǎn)自阱。
--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
對(duì)兩種方式實(shí)現(xiàn)的結(jié)果進(jìn)行比較嚎莉，可以發(fā)現(xiàn)RSF實(shí)現(xiàn)的結(jié)果當(dāng)中會(huì)多出一行，并標(biāo)記為1來(lái)表示發(fā)生的循環(huán)沛豌。而使用層次查詢(xún)時(shí)趋箩，如果想使用CONNECT_BY_ISCYCLE的話(huà)，必須同時(shí)使用NOCYCLE加派，并且是在最下一層節(jié)點(diǎn)上標(biāo)記是否發(fā)生了循環(huán)叫确。
使用RSF實(shí)現(xiàn)層次查詢(xún)的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
