oracle subquery是什么,Oracle11gR2RSF(RecuriveSubqueryfactoring)-Oracle

Oracle11gR2RSF(RecuriveSubqueryfactoring)，在Oracle 11gR2之前，一般用start with和connect by来递归的显示树形结构，请看如下脚本

select lpad(‘ ‘,2*level-1,’ ‘) || ename ename,empno,mgr

from emp

where empno is not null

connect by prior empno = mgr

start with mgr is null

ENAME EMPNO MGR

——————– ———- ———-

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

从11gR2开始，可以用RSF来实现

with sun(lvl,ename,empno,mgr) as

(select 1,lpad(‘ ‘,2*1-1,’ ‘) || ename ename,empno,mgr from emp where mgr is null

union all

select sun.lvl+1,lpad(‘ ‘,2*lvl-1,’ ‘) || d.ename ename,d.empno,d.mgr

from sun, emp d

where sun.empno = d.mgr)

select lvl,ename,empno,mgr from sun

LVL ENAME EMPNO MGR

———- ——————– ———- ———-

1 KING 7839

2 JONES 7566 7839

2 BLAKE 7698 7839

2 CLARK 7782 7839

3 ALLEN 7499 7698

3 WARD 7521 7698

3 MARTIN 7654 7698

3 SCOTT 7788 7566

3 TURNER 7844 7698

3 JAMES 7900 7698

3 FORD 7902 7566

3 MILLER 7934 7782

4 SMITH 7369 7902

4 ADAMS 7876 7788

观察一下上述两份输出，用传统的start with和connect by，结果顺序类似于对emp表的前序遍历(preorder traversal)；而使用RSF，则是对emp表的层次遍历(level traversal)