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

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

第128回「実用WEBアプリ 文書管理システム(9)再帰的外部キーは階層構造を保証するものではない」

2015.05.13

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

前回はディレクトリの循環を防ぐために、チェックを行うファンクションを作成しました。今回はそのファンクションを更新処理プログラムに埋め込むのですが、その前に、ここで、今までのポイントを一般化しておきましょう。

ディレクトリが循環する状況というのは、同じ一つの表の各行の間に親子関係が存在する場合に起きえます。ですから、ディレクトリとサブディレクトリという状況以外でも起こり得ることです。
よく引き合いに出される例としては、社員表の行の間の上司、部下という階層関係があります。社員表(EMP)に上司番号列(MGR)があり、その列はその社員の上司の社員番号列(EMPNO)を参照するといったケースです(再帰的な外部キー)。一般的に再帰的な外部キーは階層構造を管理すると理解されることが多いと思いますが、正確には階層構造を保証するものではありません。たとえ、再帰的な外部キーを制約として定義しても、階層構造は保証できないのです。つまり、部下の部下のさらに部下が自分の上司であるというような、おかしな上司番号列の更新が可能となってしまいます。確かに制約として定義すれば、存在しない社員番号を上司番号にセットできないことは保証できます。しかし、階層構造を乱す(循環する)更新を防止することはできないわけです。

一般化すれば

階層構造を管理するために、再帰的な外部キー制約を定義しても、それだけでは階層構造を保証するのに不十分である。階層構造を保証するためにはプログラム的なチェックを追加する必要がある。

ということですね。

そのためのプログラム的チェックを、前回、ファンクションという形で作ったわけです。このファンクションは文書表(DOCS表)の文書(KBN=1)またはディレクトリ(KBN=2)の親ディレクトリ(OYA_DIR列)が更新されるときにコールされるべきファンクションとして作ってあり、以下の仕様でした。ソースコードそのものは前回(No.127回)を参照してください。

<<ファンクションの仕様>>
FUNCTION DOC_FUNC_OYA_CHECK
( P_ID IN DOCS.ID%TYPE, -- 指定された番号
P_OYA IN DOCS.OYA_DIR%TYPE) -- 親の番号
RETURN BOOLEAN -- チェックOKの時は TRUEを返す。NGの時はエラーを発生させる

このファンクションはパラメータ P_ID(更新対象の文書またはディレクトリと思われる番号)と、P_OYA(親ディレクトリと思われる番号)を受け取り、チェックします。チェックがNGである場合は以下のエラーを返します。

  • P_IDが文書の番号であり、P_OYAがNULLのとき
    ORA-20000 親ディレクトリのない文書は許可できません
  • P_OYAがディレクトリの番号ではないとき
    ORA-20001 P_OYA=nnn がディレクトリでありません
  • P_IDがディレクトリの番号であり、P_OYAが P_ID以下のサブディレクトリ(階層の深さに制限なし)のいずれかの番号と等しいとき
    ORA-20002 ディレクトリが循環します P_ID=nnn P_OYA=mmm

では実際にこのファンクション(DOC_FUNC_OYA_CHECK)を文書やディレクトリを更新するプロシージャ(DOC_UPDATE_EXE)でコールするように修正します。

その前にいつものように、文書やディレクトリの情報を格納するDOCS表の定義を掲載します。この定義を参照しながらプログラムのロジックを確認してください。

SQL> SHOW USER
ユーザーは"SCOTT"です。

1
2
3
4
5
6
7
8
9
10
11
SQL> DESC DOCS
  名前                                      NULL ?    型
  ----------------------------------------- -------- ----------------------------
  ID                                        NOT NULL NUMBER           --番号(主キー)
  TITLE                                     NOT NULL VARCHAR2(300)    --タイトル
  KBN                                       NOT NULL NUMBER           --区分1:文書 2:ディレクトリ
  HONBUN                                             CLOB             --本文(文書のときのみセット)
  OYA_DIR                                            NUMBER           --親ディレクトリ
  SEQ                                                NUMBER           --ディレクトリ内の順序
  INSERT_DATE                               NOT NULL DATE             --登録日
  UPDATE_DATE                               NOT NULL DATE             --更新日

では、以下が修正したDOC_UPDATE_EXEプロシージャです。

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE OR REPLACE PROCEDURE DOC_UPDATE_EXE
( P_ID  IN  VARCHAR2,
   P_OYA_ID  IN VARCHAR2,
   P_SEQ     IN VARCHAR2,
   P_TITLE   IN VARCHAR2,
   P_HONBUN  IN VARCHAR2  DEFAULT NULL )
IS
   V_ERRMSG  VARCHAR2(1000);
   V_OLD_OYA_ID NUMBER;    -- ★追加(No.128)
BEGIN
/********************************************************/
/*   更新・削除処理                                     */
/********************************************************/
   IF P_TITLE = '削除' THEN
      DELETE FROM DOCS WHERE ID = TO_NUMBER(P_ID);
      IF SQL%ROWCOUNT = 1 THEN
         COMMIT ;
         HTP.P( 'ID=' ||P_ID || 'が削除されました' );
      ELSE
         ROLLBACK ;
         HTP.P( 'ID=' || P_ID || 'の削除が異常です。処理を取り消しました' );
      END IF;
   ELSE    -- 更新処理の場合
      /***************************************************/
      /*  ★追加した処理( No .128)   ▼▼ここから▼▼      */
      /***************************************************/
      SELECT OYA_DIR INTO V_OLD_OYA_ID FROM DOCS WHERE ID = TO_NUMBER(P_ID);
      IF V_OLD_OYA_ID = TO_NUMBER(P_OYA_ID) THEN  -- 親ディレクトリ変更なし       
         NULL ;
      ELSE                              -- 親ディレクトリが変更される場合
                                                  -- ファンクションコール
         IF DOC_FUNC_OYA_CHECK(TO_NUMBER(P_ID), TO_NUMBER(P_OYA_ID)) THEN
            NULL ;
         END IF;
      END IF;
      /***************************************************/
      /*                          ▲▲ここまで▲▲      */
      /***************************************************/
      UPDATE DOCS
         SET  TITLE = P_TITLE,
              HONBUN = P_HONBUN,
              OYA_DIR = TO_NUMBER(P_OYA_ID),
              SEQ     = TO_NUMBER(P_SEQ),
             UPDATE_DATE = SYSDATE
      WHERE ID = TO_NUMBER(P_ID);
      IF SQL%ROWCOUNT = 1 THEN
         COMMIT ;
         HTP.P( 'ID=' ||P_ID || 'が正常に更新されました
' );
         HTP.P( '<a href="doc_show?p_id=' || P_ID || '">表示する</a>
' ||
               '<a href="doc_update?p_id=' ||P_ID || '">更新画面へもどる</a>' );
      ELSE
         ROLLBACK ;
         HTP.P( 'ID=' || P_ID || 'の更新が異常です。処理を取り消しました' );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS  THEN
        V_ERRMSG := SQLERRM;
        HTP.P(V_ERRMSG);
END DOC_UPDATE_EXE;
/
 
プロシージャが作成されました。

では大事な点を解説します。

まず、このプロシージャ(DOC_UPDATE_EXE)は、フォーム画面(DOC_UPDATEプロシージャ バンクナンバーNo.126参照)から送信されるリクエストパラメータ(2~6行目)をもとに、文書やディレクトリの実際の更新(UPDATE 39行目)や削除処理(DELETE 15行目)を行うものです。
今回修正(追加)した箇所は、27~35行目です。コメント「★追加した処理(No.128) ▼▼ここから▼▼」があるからわかりますね。
ここで、見ていただきたい点は、チェックのファンクションは毎回コールする必要はなく、親ディレクトリが変化する場合だけ、コールしてチェックすればよいという点です。そのためには、更新予定の行の現在の親ディレクトリを調べ、それが更新予定の親ディレクトの値(リクエストパラメータの値)と同じであるかどうか比較する必要があります。そのために、27行目のSELET INTO文で、現在の(更新前の)親ディレクトリの番号を変数(V_OLD_OYA_ID)に格納し、その値とリクエストパラメータP_OYA_IDと比較します(28行目のIF文)。
その結果、値が違う、つまり親ディレクトリの番号が変化すると判定された場合のみ、チェックファンクションをコールします(32行目のIF文)。
その結果、チェックがOKで、ファンクションがTRUEを返した場合は、29行目のNULL;(なにもしないという実行文)を実行し、以下のステップに続き、39行目のUPDATE文を実行します。
もし、チェックがNGであれば、上述したように、システムエラー(ORA-20000, ORA-20001, ORA-20002)を返しますので、なにも更新せずに、まっすぐ例外処理部の57行目に飛び、エラーメッセージを画面表示して処理を終わります。

ディレクトリが循環するかどうかのチェックはサブディレクトリの階層の深さに制限なくチェックしなければいけないので、チェックファンクション内の処理で階層問い合わせを利用しています。この処理は一般に負荷が大きいので、なるべく回数を少なくしたい。そのために、実際に親ディレクトリが変化する場合だけ、コールするように工夫しました。

では検証してみましょう。

前回(第127回)の内容で、私の環境の場合、4番のディレクトリの親ディレクトリの値を11番にすると、ディレクトリが循環する状況でした。ですから、4番のディレクトリの更新フォーム画面を開いて、親ディレクトリの値を11番に更新すると、エラーになる様子を検証しましょう。

バックナンバー 第103回「WEBアプリ作成(1) (Oracle DBとPL/SQLだけで、即、WEBアプリ)」と同等の設定ができていれば、以下のURLです。

http://localhost:8080/dad/doc_update?p_id=4

ログインを求められたらスキーマユーザ(私の場合 SCOTT)でログインします。

4番のディレクトリがフォーム画面に読み込まれています。

親ディレクトリの値を「11」に更新して、送信ボタンをクリックします。
これにより画面上の項目がDOC_UPDATE_EXEプロシージャに送信されます。

ご覧のように、DOC_UPDATE_EXEプロシージャ内でコールされたDOC_FUNC_OYA_CHECKファンクションがチェックNGと判断しユーザ定義のシステムエラーを発生させました。これにより、ディレクトリが循環するようなデータ更新を防止できたわけです。

これで、ディレクトリが循環するような、不正なディレクトリ番号で更新する状況を防止することができるようになりました。これで文書やディレクトリの更新機能については完成ですね。

では今回はここまでにいたします。

次回は検索機能を実装しようと考えています。ご期待ください。

先頭へ戻る