■CONNECT_BY_ROOT
・階層構造クエリでLEVELが0の最上位ROWの情報を取得できる。
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
2 CONNECT_BY_ROOT empno "Root empno", level
3 FROM emp
4 START WITH job = 'PRESIDENT'
5 CONNECT BY PRIOR empno = mgr;
ENAME EMPNO Root empno LEVEL
-------------------- ---------- ---------- ----------
KING 7839 7839 1
JONES 7566 7839 2
SCOTT 7788 7839 3
ADAMS 7876 7839 4
FORD 7902 7839 3
SMITH 7369 7839 4
BLAKE 7698 7839 2
ALLEN 7499 7839 3
MARTIN 7654 7839 3
TURNER 7844 7839 3
JAMES 7900 7839 3
CLARK 7782 7839 2
MILLER 7934 7839 3
13行が選択されました。
■CONNECT_BY_ISLEAF
・階層構造クエリでROW最下位レベルかどうかを返す。最下位なら1、じゃなければ0
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
2 CONNECT_BY_ISLEAF "leaf", level
3 from emp
4 START WITH job = 'PRESIDENT'
5 CONNECT BY NOCYCLE PRIOR empno = mgr;
ENAME EMPNO leaf LEVEL
-------------------- ---------- ---------- ----------
KING 7839 0 1
JONES 7566 0 2
SCOTT 7788 0 3
ADAMS 7876 1 4
FORD 7902 0 3
SMITH 7369 1 4
BLAKE 7698 0 2
ALLEN 7499 1 3
MARTIN 7654 1 3
TURNER 7844 1 3
JAMES 7900 1 3
CLARK 7782 0 2
MILLER 7934 1 3
13行が選択されました。
■SYS_CONNECT_BY_PATH
・階層構造クエリで現在のROWのPATH情報を簡単に検索できる。
--SQL*Plusできれいに見るために
SQL> COL PATH FORMAT A40
--SYS_CONNECT_BY_PATH例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
2 SYS_CONNECT_BY_PATH(ename, '/') "PATH"
3 FROM emp
4 START WITH job='PRESIDENT'
5 CONNECT BY PRIOR empno=mgr;
ENAME EMPNO PATH
-------------------- ---------- ----------------------------------------
KING 7839 /KING
JONES 7566 /KING/JONES
SCOTT 7788 /KING/JONES/SCOTT
ADAMS 7876 /KING/JONES/SCOTT/ADAMS
FORD 7902 /KING/JONES/FORD
SMITH 7369 /KING/JONES/FORD/SMITH
BLAKE 7698 /KING/BLAKE
ALLEN 7499 /KING/BLAKE/ALLEN
MARTIN 7654 /KING/BLAKE/MARTIN
TURNER 7844 /KING/BLAKE/TURNER
JAMES 7900 /KING/BLAKE/JAMES
CLARK 7782 /KING/CLARK
MILLER 7934 /KING/CLARK/MILLER
13行が選択されました。
・下記のようにLeaf Nodeだけ、全体PATH情報が出るように作成できる。
SQL> SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path
2 FROM emp
3 WHERE CONNECT_BY_ISLEAF = 1
4 START WITH mgr IS NULL
5 CONNECT BY PRIOR empno = mgr;
LEVEL PATH
---------- ----------------------------------------
4 KING,JONES,SCOTT,ADAMS
4 KING,JONES,FORD,SMITH
3 KING,BLAKE,ALLEN
3 KING,BLAKE,MARTIN
3 KING,BLAKE,TURNER
3 KING,BLAKE,JAMES
3 KING,CLARK,MILLER
7行が選択されました。
■ORDER SIBLINGS BY ・階層構造クエリで相関関係を維持しながら整列させる
SQL> COL ename FORMAT A25
SQL> COL ename2 FORMAT A10
--ORDER SIBLINGS BY例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
2 ename ename2, empno, level
3 FROM emp
4 START WITH job = 'PRESIDENT'
5 CONNECT BY NOCYCLE PRIOR empno = mgr
6 ORDER SIBLINGS BY ename2;
ENAME ENAME2 EMPNO LEVEL
------------------------- ---------- ---------- ----------
KING KING 7839 1
BLAKE BLAKE 7698 2
ALLEN ALLEN 7499 3
JAMES JAMES 7900 3
MARTIN MARTIN 7654 3
TURNER TURNER 7844 3
CLARK CLARK 7782 2
MILLER MILLER 7934 3
JONES JONES 7566 2
FORD FORD 7902 3
SMITH SMITH 7369 4
SCOTT SCOTT 7788 3
ADAMS ADAMS 7876 4
13行が選択されました。
--きれいに整列された。
--ORDER BY例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
2 ename ename2, empno, level
3 FROM emp
4 START WITH job = 'PRESIDENT'
5 CONNECT BY NOCYCLE PRIOR empno = mgr
6 ORDER BY ename2;
ENAME ENAME2 EMPNO LEVEL
------------------------- ---------- ---------- ----------
ADAMS ADAMS 7876 4
ALLEN ALLEN 7499 3
BLAKE BLAKE 7698 2
CLARK CLARK 7782 2
FORD FORD 7902 3
JAMES JAMES 7900 3
JONES JONES 7566 2
KING KING 7839 1
MARTIN MARTIN 7654 3
MILLER MILLER 7934 3
SCOTT SCOTT 7788 3
SMITH SMITH 7369 4
TURNER TURNER 7844 3
13行が選択されました。
--変な整列になっている。
0 件のコメント:
コメントを投稿