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

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

第80回 「再帰コールを使った階層的なデータ更新」

2013.12.09

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

前回は、再帰コールを使って階層問い合わせと同じ処理を行いましたが、今回は再帰コールを使ってデータ更新をしてみます。

前回と同じく社員表(EMP表)の例で解説したいと思います。
例えばこんなストーリーです。ある社員(Aさんとします)に、ある金額のお金を渡します。
Aさんは、渡されたお金の半分を自分の取り分として、自分の給与(SAL列)に加算し、残りのお金は、自分の部下たちに均等に分けて渡します。それぞれの部下はその渡されたお金で同じことを行います。
つまり渡されたお金の半分を自分の取り分として、自分の給与に加算し、残りの金額を均等に自分の部下たちに渡して行く。
このような更新をどこまでも続けていく。
しかしいずれ部下のいない社員まで降りてくれば、その部下はもらったお金の全額を自分の取り分として自分の給与列(SAL列)に加し、それより下に部下がいないので階層を伝播する更新はそこで止まる。

以下はそんな処理のプロシージャの例です。 パラメータのP_EMPNOが、起点となる社員の番号を表し、P_KINGAKUがその社員に渡すお金の額を表します。(1行目) 以下、ソースコードのコメントで「自分」とは、パラメータP_EMPNOの社員の立場です。

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
28
29
30
31
32
33
34
CREATE OR REPLACE PROCEDURE PROC80 ( P_EMPNO IN NUMBER , P_KINGAKU IN NUMBER)
IS
   BUKA_NUMBER   NUMBER;   -- 部下の数
   BUKA_KINGAKU  NUMBER;   -- 部下一人当たりの分配金額
   MY_KINGAKU    NUMBER;   -- 自分に加算する金額
BEGIN
/*************************************************************************/
   -- 自分の部下の数を調べる
/*************************************************************************/
   SELECT COUNT (*) INTO BUKA_NUMBER FROM EMP WHERE MGR = P_EMPNO;
/*************************************************************************/
   -- 部下がいなければ、もらった金額全額(P_KINGAKU)を自分の給与(SAL)に加算する
/*************************************************************************/
   IF  BUKA_NUMBER = 0  THEN
       UPDATE EMP SET SAL = SAL + P_KINGAKU WHERE EMPNO = P_EMPNO;
/*************************************************************************/
   -- 部下がいれば、自分の取り分は半分として残りは部下に均等に分配する
/*************************************************************************/
   ELSE
    -- P_KINGAKUの半分は自分の取り分
       MY_KINGAKU := P_KINGAKU / 2;
    -- その金額を自分の給与(SAL列)に加算する
       UPDATE EMP SET SAL = SAL + MY_KINGAKU WHERE EMPNO = P_EMPNO;
    -- 残りの金額を部下の数で割って、部下一人当たりの分配額を計算
       BUKA_KINGAKU  :=  ( P_KINGAKU - MY_KINGAKU ) / BUKA_NUMBER;
       FOR REC IN ( SELECT * FROM EMP WHERE MGR = P_EMPNO) LOOP -- カーソルFORループ
        -- それぞれの部下に、分配額を与えその部下の立場で同じことが行われる
           PROC80(REC.EMPNO, BUKA_KINGAKU);  -- 再帰コール
       END LOOP;
   END IF;
END PROC80;
/
 
プロシージャが作成されました。

簡単に解説します。

まず、自分の部下の数を取得します。(10行目)
もしも部下がいなければ、パラメータで指定された金額(P_KINGAKU)をすべて自分の給与(SAL列)に加算します。(14~15行目)そして、注目していただきたいのは、部下がいなければ、再帰コールはしていないので、再帰コールの連鎖がとまることです。

前々回でも説明したように、再帰コールは連鎖していくので、何かのタイミングで連鎖が止まる必要があります。このケースにおいては、部下のいない社員番号でコールされた場合は、再帰コールの連鎖が止まるわけです。

逆に部下がいる場合は、再帰コールが連鎖します。
まず、P_KINGAUの半分を自分の取り分にして、給与(SAL列)に加算します。(21~23行目) そして部下一人当たりの分配額を計算し(25行目)、自分の部下一人一人にその金額を与え、その部下の立場で同じことをする、つまり再帰コール(PROC80プロシージャがPROC80プロシージャをコール)します。(28行目)
このようにして部下のいない階層まで更新が伝播していきます。

それでは、さっそく検証します。 まず、このプロシージャによる更新後の値を更新前の値と比較したいので、事前に現在の社員表(EMP表)をEMP2という表名でコピーしておきます。

1
2
3
CREATE TABLE  EMP2 AS SELECT * FROM EMP;     -- EMP表をEMP2という名前でコピー
 
表が作成されました。

そして、社員番号 7839の社員(社員名 KING)を起点にして、30000の金額で、上記の階層伝播する更新処理プロシージPROC80をコールします。
これにより、7839の社員およびその配下の部下のSAL列が階層的に更新されます。

1
2
3
EXEC PROC80(7839,30000)
 
PL/SQLプロシージャが正常に完了しました。

これにより、EMP表の該当社員の給与列が更新されました。

では、EMP2表(更新前の表)とEMP表(更新後の表)を比較して、金額の変動を階層問い合わせで確認してみましょう。前回の階層問い合わせを元に少し修正した問い合わせです。
この問い合わせにより、階層的に社員を問い合わせ、給与の増加額を表示できます。

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
SELECT LPAD( ' ' ,5*( LEVEL -1), ' ' ) || LEVEL || ' ' ||  RPAD( AFTER .ENAME,10, ' ' ) || ' '
        ||    TO_CHAR( AFTER .SAL - BEFORE.SAL)  AS "SAL列の増加額"
FROM   EMP2 BEFORE, EMP  AFTER
WHERE  BEFORE.EMPNO = AFTER .EMPNO
START WITH AFTER .EMPNO = 7839
CONNECT BY PRIOR AFTER .EMPNO = AFTER .MGR
ORDER SIBLINGS BY AFTER .ENAME
/
 
SAL列の増加額
--------------------------------------------------------------------------------
1 KING       15000
      2 BLAKE      2500
           3 ALLEN      500
           3 JAMES      500
           3 MARTIN     500
           3 TURNER     500
           3 WARD       500
      2 CLARK      2500
           3 MILLER     2500
      2 JONES      2500
           3 FORD       625
                4 SMITH      625
           3 SCOTT      625
                4 ADAMS      625
 
14行が選択されました。

上記の結果をみると、まず、KINGを起点として30000の金額でコールしたのですが、KINGの給与の増額はその半分の15000ですね。さらにKINGの直属の部下は、BLAKE、CLARK、JONESの3人ですが、この3人に残りの15000を均等に配分したわけです。
ですから一人当たり5000の配分額なのですが、その半分を自分のSAL列に加算し残りを部下に配分するので、BLAKE, CLARK, JONESの3人の給与の増額はそれぞれ、5000の半分である2500となっていますね。
このように正しく更新されています。
なおこの問い合わせの各社員の給与増加額を加算すると、30000になります。
(15000+2500+500+500+500+500+500+2500+2500+2500+625+625+625+625=30000)
全体でも正しい結果ですね。

再帰コールはあまり一般的なものではありませんが、このように階層的な関連が連鎖するような場合は、再帰コールを使うことでシンプルに記述できます。

それでは今回はここまでにします。また次回ご期待ください。

先頭へ戻る