e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第79回 「再帰コールで階層問い合わせをしてみる」

2013.12.02

こんにちは。インストラクターの蓑島です。

ついに師走になりました。仕事も大詰め、かつクリスマスや忘年会などの恒例行事でもいそがしくなる時期ですね。
体調とスケジュールに気をつけて、ストレスがかかりすぎないようにしてください。

さて前回は、簡単な例で再帰コールを解説しました。
再帰コールとは自分自身をコールするプログラミングですが、自分自身というよりも「自分のコピー」をコールするといったほうがわかりやすいかもしれませんね。 すなわち、次々に自分のコピーを産み出して、そのコピーに仕事をさせる、ということを繰り返す。最後のコピーが仕事を終えると、今度は逆にその結果を前のほうに戻していきます。
すなわち結果が前のコピーに戻り、その結果がさらに前のコピーにもどり・・ということを繰り返し、最初にコールしたおおもとの自分に結果が帰る、といったイメージがわかり易いと思います。

それでは前回は、データベースの表を使わず、単に計算結果を返すファンクションの例でしたが、今回はデータベースの表を使った、プロシージャの例で再帰コールをしてみたいと思います。

今回のタイトルにもありますが、階層問い合わせと同じ内容の処理を再帰コールでやってみたいと思います。

階層問い合わせとは、表の中に階層的な関係が含まれているときに、階層の形のまま問い合わす方法です。
例えば、社員表(EMP表)には、上司、部下の関係が含まれています。具体的には上司の番号列(MGR列)があり、この列がその社員の上司の社員番号(EMPNO列)を表します。
つまり、1人の上司に対して、複数人の部下がいるわけです。

そんなとき、以下のような問い合わせが階層問い合わせです。上司とその部下、さらにその部下というように階層的に問い合わせています。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT LEVEL , EMPNO, ENAME, MGR
FROM   EMP
START WITH EMPNO = 7839
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       7521 WARD             7698
          3       7654 MARTIN           7698
          3       7844 TURNER           7698
          3       7900 JAMES            7698
          2       7782 CLARK            7839
          3       7934 MILLER           7782
 
14行が選択されました。

階層問い合わせの詳しい説明はここではしませんが、「START WITH」が問い合わせをする階層の起点を指定するもので、「CONNECT BY PRIOR」が階層の関連を表します。
LEVELは擬似列であり、階層の深さを表します。
上の例は社員番号7839番を起点とした階層の問い合わせです。
以下のように立体的に表示すればよりわかりやすくなります。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT LPAD( ' ' ,5*( LEVEL -1), ' ' ) || LEVEL || ' ' ||
                           EMPNO || ' ' || ENAME || ' ' || MGR
FROM   EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME
/
 
LPAD( '' ,5*( LEVEL -1), '' )|| LEVEL || '' ||EMPNO|| '' ||ENAME|| '' ||MGR
------------------------------------------------------------------------
1 7839 KING
      2 7698 BLAKE 7839
           3 7499 ALLEN 7698
           3 7900 JAMES 7698
           3 7654 MARTIN 7698
           3 7844 TURNER 7698
           3 7521 WARD 7698
      2 7782 CLARK 7839
           3 7934 MILLER 7782
      2 7566 JONES 7839
           3 7902 FORD 7566
                4 7369 SMITH 7902
           3 7788 SCOTT 7566
                4 7876 ADAMS 7788
 
14行が選択されました。

このように立体的にすれば、上司部下の関係が視覚的にわかりやすくなりますね。
なお、問い合わせの最後に「ORDER SIBLINGS BY ENAME」を追加しましたが、これはソートの指定です。しかし全体をソートするのではなく、同じ階層の兄弟(SIBLINGS)の間でソートするというものです。同じレベルの階層では、社員名(ENAME)の順番となっているわけです。SIBLINGSキーワードをつけないと全体がソートされて階層表示の順番がくずれてしまいます。

では、この階層問い合わせと同じことを再帰コールを使うプロシージャでやってみます。そのプロシージャは「PROC79」という名前で先に作成しました。
ソースコードの説明は後にしますので、以下の実行結果をご覧ください。

SQL> SET SERVEROUTPUT ON -- 画面表示(DBMS_OUTPUTパッケージ)を有効にして

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC PROC79(7839)         -- 起点となる社員番号を 7839でコール
1 7839 KING
.....2 7698 BLAKE 7839
..........3 7499 ALLEN 7698
..........3 7900 JAMES 7698
..........3 7654 MARTIN 7698
..........3 7844 TURNER 7698
..........3 7521 WARD 7698
.....2 7782 CLARK 7839
..........3 7934 MILLER 7782
.....2 7566 JONES 7839
..........3 7902 FORD 7566
...............4 7369 SMITH 7902
..........3 7788 SCOTT 7566
...............4 7876 ADAMS 7788
 
PL/SQLプロシージャが正常に完了しました。

DBMS_OUTPUTパッケージの画面表示機能の都合で、先頭に空白があるとその空白が表示できないのでやむなく先頭をピリオド(...)で埋めましたが、内容的に階層問い合わせとまったく同じですね。

ではこのPROC79プロシージャのソースコードは以下のようになっています。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE
PROCEDURE PROC79( P_EMPNO IN EMP.EMPNO%TYPE, P_LEVEL IN NUMBER DEFAULT 1)
/************************************************************************/
-- 再帰コールで階層問い合わせを行うプロシージャ
/************************************************************************/
IS
     REC  EMP%ROWTYPE;
BEGIN
/**************************************************************/
--  指定された社員の行を取得する
/**************************************************************/
     SELECT * INTO REC FROM EMP WHERE EMPNO = P_EMPNO;
/**************************************************************/
--  その社員の情報を階層のレベルのインデントをつけて表示する
/**************************************************************/
     DBMS_OUTPUT.PUT_LINE(LPAD( '.' ,5*(P_LEVEL-1), '.' ) || P_LEVEL || ' ' ||
                           REC.EMPNO || ' ' || REC.ENAME || ' ' || REC.MGR);
/***********************************************************************/
--  その社員の部下それぞれに対して、階層を一つ深くして同じ処理を行う
/**********************************************************************/
     FOR REC IN ( SELECT * FROM EMP WHERE MGR = P_EMPNO ORDER BY ENAME)  LOOP
         PROC79(REC.EMPNO, P_LEVEL+1);     -- 自分のコピー(PROC79)をコール(再帰コール)
     END LOOP;
END PROC79;
/
 
プロシージャが作成されました。

まずこのプロシージャのパラメータでP_LEVEL(2行目)がありますが、これは階層の深さを表すもので、階層問い合わせのLEVEL擬似列の役割をします。デフォルト値(=1)がありますので、コール時に省略すれば1から始まります。

注釈を入れているのでわかり易いとは思いますが、パラメータで指定された社員番号(P_EMPNO)で社員の行を取得し、階層の深さ(P_LEVEL)を考慮してインデントをつけて表示します。(9~17行目)

次にその社員の部下をカーソルFORループ文で社員名の順番に取得し(ORDER BY ENAME)、それぞれの部下に対して同じ処理をする、つまり再帰コールしているわけです。そのときに、階層を一つ深くしたパラメータを設定します(P_LEVEL+1)。これにより各部下はその階層の深さに応じたインデントをつけて自分を表示し、さらにその部下に同じことを繰り返す。(18行目~23行目) 
いずれ部下のいない社員まで降りてくれば、そこで再帰コールは終わり、上の階層にもどり次の部下の処理を行うといった流れです。

いかがですか? わかりやすいでしょう?
しかし、階層問い合わせで同じことができるなら、あまり意味がないのではと思われるかもしれません。確かに問い合わせであれば、そうかもしれません。しかしこれが問い合わせでなく、更新だったとしたらどうでしょう。
ただの更新ではなく、上の階層から下の階層に向かって結果を引き継ぎながら一定のルールにしたがって次々と更新をしなければいけないとき、さらにそのような階層の深さが固定ではなく、不定だったとしたら?
そのような状況は、再帰コールを使えばとてもシンプルに記述できます。

では次回はそのような再帰コールを使ったデータ更新をケースに解説します。
ご期待ください。

先頭へ戻る