本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら！オラ！ Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。（編集局）
これまではSQL文の視認性を向上させるテクニックをご紹介してきましたが、今回は抽出結果の視認性を向上させる階層問い合わせをご紹介します。
階層問い合わせの基本
階層問い合わせの一番よい例は実行計画だと思いますが、その前にまず階層問い合わせの基本からおさらいしましょう。
SQL> select empno,ename,job,mgr,sal,comm from emp;
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
12行が選択されました。
見慣れたEMP表のSELECT結果ですが……、誰が上司で、誰が部下？！は一目では分かりづらいと思います。そんなときに使えるのが、階層問い合わせです。例えば、上記の見慣れたSQL文と見慣れた結果を階層問い合わせにすると……。
SQL> select
2 empno,
3 lpad(' ',2*(level)) || ename ename,
4 job,
5 hiredate,
6 sal,
7 comm
8 from
9 emp
10 start with ename = 'KING'
11 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
12行が選択されました。
誰が上司なのかはすぐ分かりますよね。KING社長をルートとして、JONES、BLAKE、CLARKが各部門のManagerに派生し、以下それぞれの部署の人員がすぐ分かります。
では、このSQL文をじっくりと見ていきましょう。まず3行目のlpad関数は、整形に使える関数です。分かりやすい例を出しましょう。
SQL> select lpad('+',5,'-') || 'abcde' from dual;
LPAD('-',5
----------
----+abcde
この例では、第1引数に指定した文字が5文字目にあり、それまでは第3引数に指定した文字で埋められます。lpad関数の第3引数を指定しないとデフォルトで空白が入ります。
リスト2に戻って、EMP表の階層構造で考えます。今回の場合、
lpad(' ',2*(level))
としています。第3引数はデフォルトで空白なので
lpad(' ',2*(level),' ')
と同じ結果になります。第2引数は「2*(level)」となっています。
このlevelというのは階層レベルです。「階層レベル×2」を指定することで、階層が1つ下がると2つずつ空白が増えていくことになります。
次に10行目のstart with句に'KING'を指定してENAMEが'KING'のものを起点（ルート）に、階層化しています。でも、KINGがルートかどうかなんて、分からない可能性があります。
ならば、ルートの条件をSQL文で抽出してしまえばよいのです。
SQL> select
2 empno,
3 lpad(' ',level*2) || ename ename,
4 mgr
5 from
6 emp
7 start with ename = ( select ename from emp where mgr is null )
8 connect by prior empno = mgr
9 /
EMPNO ENAME MGR
---------- -------------------- ----------
7839 KING
7566 JONES 7839
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
12行が選択されました。
リスト4の8行目、条件式「connect by prior empno = mgr」で、親子関係を指定します。この例では、EMPNOとMGRを関連付けることにより、上司・部下の関係性を指定しています。（次ページへ続く）
Copyright © ITmedia, Inc. All Rights Reserved.