Oracle SQL Tricks - 分析函數 - 階層結構抽出

用這技術可實作不限階層 bom 表, 搭配先前 With clause 的技術,
彈性就更大. 實作出的 bom 表遠比前人用一堆子查詢作出的漂亮的許多.
以下我們以人事的例子來看...

/*
EMPNO ENAME      JOB              MGR        SAL       COMM
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600        300
      7521 WARD       SALESMAN        7698       1250        500
      7566 JONES      MANAGER         7839       2975
      7654 MARTIN     SALESMAN        7698       1250       1400
      7698 BLAKE      MANAGER         7839       2850
      7782 CLARK      MANAGER         7839       2450
      7839 KING       PRESIDENT                  5000
      7844 TURNER     SALESMAN        7698       1500          0
      7900 JAMES      CLERK           7698        950
      7902 FORD       ANALYST         7566       3000
      7934 MILLER     CLERK           7782       1300
*/
select
  empno,
  lpad(' ',2*(level))  ename ename,
  job, hiredate, sal, comm
from emp
start with ename = ( select ename from emp where mgr is null )
connect by prior empno = mgr;
/*
     EMPNO ENAME                JOB       HIREDATE        SAL     COMM
---------- -------------------- --------- -------- ---------- --------
      7839   KING               PRESIDENT 81-11-17       5000
      7566     JONES            MANAGER   81-04-02       2975
      7902       FORD           ANALYST   81-12-03       3000
      7369         SMITH        CLERK     80-12-17        800
      7698     BLAKE            MANAGER   81-05-01       2850
      7499       ALLEN          SALESMAN  81-02-20       1600      300
      7521       WARD           SALESMAN  81-02-22       1250      500
      7654       MARTIN         SALESMAN  81-09-28       1250     1400
      7844       TURNER         SALESMAN  81-09-08       1500        0
      7900       JAMES          CLERK     81-12-03        950
      7782     CLARK            MANAGER   81-06-09       2450
      7934       MILLER         CLERK     82-01-23       1300
*/