-またの名を再帰クエリ (recursive query)と言う。
-再帰的な問い合わせを行う SELECT ステートメントである。階層構造を持つデータなどに使う。
って言ってもよく分かんないんで、実例で見てみよう。
-scottユーザーのempテーブルを見ると、empnoとmgrコラムがあり、
mgrコラムのデータは各社員の管理者empnoを意味する。
SQL> SELECT empno, ename, sal, mgr FROM emp;
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7499 ALLEN 1600 7698
....
13行が選択されました。
--社員SMITHの管理者の社員番号は7902
--社員ALLENの管理者の社員番号は7698
この上位階層と下位階層の関係をORACLEではSTART WITHとCONNECT BYを使って
簡単に検索できる。
■階層構造クエリSynctax
・START WITH
-階層問合せ文のルートで使用される行を指定する。
-サブクエリを使用できる。
・CONNECT BY
-階層クエリで上位階層と下位階層の関係を定義できる。
-PRIOR演算子と一緒に使用して階層構造で表現できる。
-CONNECT BY PRIOR 下位コラム=上位コラム：上位から下位へのツリー構造(Top Down)
-CONNECT BY 下位コラム=PRIOR 上位コラム：下位から上位へのツリー構造（Bottom Up）
-CONNECT BY NOCYCLE PRIOR：NOCYCLEパラメータを利用してループ防止
-サブクエリを使えない
・LEVEL Pseudocoolumn
-LEVELは階層構造クエリ結果のDepthを表現するコラム
・ORDER SIBLINGS BY
-ORDER SIBLINGS BYを使用すると階層構造クエリで簡単に整列できる。
・CONNECT BY実行順番は以下になる。
-１．START WITH
-２．CONNECT BY
-３．WHERE
■階層構造クエリ例
-基本例：職業PRESIDENTを基準に階層構造で検索する例。
--LEVELコラムでdepthが見れる
--JONESの管理者はKINGでSCOTTの管理者はJONES
--上下の階層構造を検索できる
SQL> SELECT LEVEL, empno, ename, mgr
2 FROM emp
3 START WITH job = 'PRESIDENT'
4 CONNECT BY PRIOR empno = mgr;
LEVEL 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
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
2 7782 CLARK 7839
3 7934 MILLER 7782
13行が選択されました。
■LEVELの活用
-LEVEL Pseudocolumnを利用すると階層構造クエリを様々に活用できる。
-下記例は、LEVELに空白を追加し、階層構造を一目で見れるようにした例
--SQL*PLUSできれいに見るための設定
SQL> COL ename FORMAT A20;
--左にLEVEL程の空白を追加して階層構造を検索
SQL> SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ename ename, empno, mgr, job
2 FROM emp
3 START WITH job = 'PRESIDENT'
4 CONNECT BY PRIOR empno = mgr;
LEVEL ENAME EMPNO MGR JOB
---------- -------------------- ---------- ---------- ---------
1 KING 7839 PRESIDENT
2 JONES 7566 7839 MANAGER
3 SCOTT 7788 7566 ANALYST
4 ADAMS 7876 7788 CLERK
3 FORD 7902 7566 ANALYST
4 SMITH 7369 7902 CLERK
2 BLAKE 7698 7839 MANAGER
3 ALLEN 7499 7698 SALESMAN
3 MARTIN 7654 7698 SALESMAN
3 TURNER 7844 7698 SALESMAN
3 JAMES 7900 7698 CLERK
2 CLARK 7782 7839 MANAGER
3 MILLER 7934 7782 CLERK
13行が選択されました。
■以下はLEVEL別で給与合計と社員数を検索する例
SQL> SELECT LEVEL, SUM(sal) total, COUNT(empno) cnt
2 FROM emp
3 START WITH job = 'PRESIDENT'
4 CONNECT BY PRIOR empno = mgr
5 GROUP BY LEVEL
6 ORDER BY LEVEL;
LEVEL TOTAL CNT
---------- ---------- ----------
1 5000 1
2 8275 3
3 12600 7
4 1900 2
■PRIORの活用
・PRIOR演算子をSELECTで使用してみよう。
・下記は社員の管理者をPRIOR演算子を利用して検索する例
--SQL*PLUSできれいに見るための設定
SQL> COL mgrname FORMAT A10;
--SELECTで"PRIOR ename mgrname"を確認してみる
SQL> SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename,
2 PRIOR ename mgrname,
3 empno, mgr, job
4 FROM emp
5 START WITH job = 'PRESIDENT'
6 CONNECT BY PRIOR empno = mgr;
LEVEL ENAME MGRNAME EMPNO MGR JOB
---------- -------------------- ---------- ---------- ---------- ---------
1 KING 7839 PRESIDENT
2 JONES KING 7566 7839 MANAGER
3 SCOTT JONES 7788 7566 ANALYST
4 ADAMS SCOTT 7876 7788 CLERK
3 FORD JONES 7902 7566 ANALYST
4 SMITH FORD 7369 7902 CLERK
2 BLAKE KING 7698 7839 MANAGER
3 ALLEN BLAKE 7499 7698 SALESMAN
3 MARTIN BLAKE 7654 7698 SALESMAN
3 TURNER BLAKE 7844 7698 SALESMAN
3 JAMES BLAKE 7900 7698 CLERK
2 CLARK KING 7782 7839 MANAGER
3 MILLER CLARK 7934 7782 CLERK
13行が選択されました。
■Bottom Up検索例
・上の例を逆順（下位から上位へのツリー構造、Bottom Up）で検索する。
--SQL*PLUSできれいに見るための設定
SQL> COL ename FORMAT A20;
--enameを基準にBottom Upで検索する例
SQL> SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
2 FROM emp
3 START WITH ename = 'SMITH' --最下位ノード値を記載
4 CONNECT BY empno = PRIOR mgr;
LEVEL ENAME EMPNO MGR JOB
---------- -------------------- ---------- ---------- ---------
1 SMITH 7369 7902 CLERK
2 FORD 7902 7566 ANALYST
3 JONES 7566 7839 MANAGER
4 KING 7839 PRESIDENT
0 件のコメント:
コメントを投稿