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

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

第133回「実用WEBアプリ 文書管理システム(14) 階層ツリー表示(階層問い合わせ、NVL2関数の利用) 」

2015.07.09

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

このシリーズの文書管理システムもほぼ基本機能(登録・更新・削除・検索)が揃ったところですね。これからは使い勝手をよくするための、+α的な機能を実装していきましょう。
そこで今回は表題にあるように、階層ツリー表示を取り上げたいと思います。ディレクトリや文書など階層ツリー構造で表示できれば、文書を分類するときなど便利ですね。

では早速、そのためのプロシージャを作成しましたので、コードを掲載します。その前にいつものように、文書やディレクトリの情報を格納する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           --親ディレクトリ(ID列を参照する外部キー)
  SEQ                                                NUMBER           --ディレクトリ内の順序
  INSERT_DATE                               NOT NULL DATE             --登録日
  UPDATE_DATE                               NOT NULL DATE             --更新日

それでは以下が階層ツリーを表示するプロシージャです。名前をDOC_TREEとしました。なお、このプロシージャには、コード中に「&」の記述があります(73行目)ので、開発ツールがSQL*Plusや、SQL*Developerの場合は、文字列置換機能(置換変数)が働かないように、コンパイルする前に「SET DEFINE OFF」の指定をしてください。

(注意)以下のソースコードはブラウザ表示のために、山括弧(「<」と「>」)を全角にしています。 コピーして実行する方は、必ず、すべての山括弧(「<」と「>」)を半角に変換してください。

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

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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
CREATE OR REPLACE
/******************************************************************/
--  階層ツリーを表示するファンクション
/******************************************************************/
PROCEDURE DOC_TREE
(P_ID IN VARCHAR2 DEFAULT NULL ,   --指定された文書またはディレクトリ
  P_ORG_ID IN VARCHAR2 DEFAULT NULL ) -- 当初の開始点
IS
V_ERRMSG  VARCHAR2(1000);
V_KBN     DOCS.KBN%TYPE;
V_ID     DOCS.ID%TYPE;     --起点の番号
V_OYA_DIR DOCS.OYA_DIR%TYPE;  --親ディレクトリ番号
V_UP      DOCS.ID%TYPE;       --上のディレクトリの番号
/*****************************************************************/
--レベルの深さに応じてインデントした行を生成するファンクション
/******************************************************************/
FUNCTION  FUNC_DISP(P_LEVEL IN NUMBER,
                     P_ID IN NUMBER,
                     P_KBN IN NUMBER,
                     P_TITLE IN VARCHAR2,
                     P_ORG_ID IN VARCHAR2 ) RETURN VARCHAR2
IS
   V_STR  VARCHAR2(32767);
   V_HIGHLIGHT_START  VARCHAR2(100);
   V_HIGHLIGHT_END    VARCHAR2(100);
BEGIN
    --当初の開始点であれば黄色にハイライトする
      IF P_ID = TO_NUMBER(P_ORG_ID) THEN
         V_HIGHLIGHT_START  := '<font style=" background-color: yellow">' ;
         V_HIGHLIGHT_END    := '</font>' ;
      ELSE
         V_HIGHLIGHT_START  := NULL ;
         V_HIGHLIGHT_END    := NULL ;
      END IF;
      -- レベルの深さ(P_LEVEL)に応じて左側に全角の空白を埋める(LPAD)
      V_STR := LPAD( ' ' ,6*(P_LEVEL-1), ' ' )   --←「' '」は全角の空白です。
      || case P_KBN when 1 then '-' when 2 then '▼' end
      || '(' ||TO_CHAR(P_ID) || ')' || '<a href="doc_show?P_ID='
               ||to_char(P_ID) || '">' ||
               V_HIGHLIGHT_START ||P_TITLE ||  V_HIGHLIGHT_END || '</a><BR>' ;
      RETURN V_STR;
END FUNC_DISP;
/*************************************/
--  実行部
/*************************************/
BEGIN
   IF P_ID IS NOT NULL THEN
     SELECT KBN, OYA_DIR INTO V_KBN, V_OYA_DIR
     FROM DOCS
     WHERE ID = TO_NUMBER(P_ID);
   -- 指定された P_IDが文書(KBN=1)であればその親ディレクトリをV_IDとする
     IF V_KBN = 1 THEN
        V_ID := V_OYA_DIR;
   --ディレクトリあればその番号をV_IDとする
     ELSE
        V_ID := TO_NUMBER(P_ID);
     END IF;
     -- 上の階層に移動するための親ディレクトリのIDを取得する(V_UP)
     SELECT OYA_DIR INTO V_UP FROM DOCS WHERE ID = V_ID;
   ELSE
      V_ID := NULL --P_IDがNULLなら起点(V_ID)はNULL(最上位)
   END IF;
/************************************/
-- レスポンス(画面)開始
/************************************/
  HTP.P( '<HTML><TITLE>階層ツリー</TITLE><BODY>' );
  IF V_ID IS NULL THEN
  --上の階層がなければグレイアウト(silver)
     HTP.P( '<font color="silver">▲上の階層へ<BR></font>' );
  ELSE
--上の階層があればそこへのリンクを張る
     HTP.P( '▲<a href="doc_tree?p_id='
          || to_char(V_UP) || '&p_org_id=' ||NVL(P_ORG_ID,P_ID)
          || '">上の階層へ</a><BR>' );
END IF;
HTP.P( '括弧()の中の数字はID番号です。▼はディレクトリです。<BR>' );
/***********************************/
-- V_IDを起点として階層問い合わせ
/***********************************/
FOR REC IN ( SELECT LEVEL , ID, KBN, TITLE
             FROM   DOCS
             START WITH NVL(NVL2(V_ID,ID,OYA_DIR),0) = NVL(V_ID,0)
             CONNECT BY PRIOR ID = OYA_DIR
             ORDER SIBLINGS BY SEQ, UPDATE_DATE DESC ) LOOP  -- ループ
   /***************************************************************/
   -- ここで得られた行をレベルの深さに応じてインデントを付けて表示
   /***************************************************************/
   HTP.P(FUNC_DISP(REC. LEVEL , REC.ID, REC.KBN, REC.TITLE,NVL(P_ORG_ID,P_ID)));
END LOOP;  -- ループの終わり
HTP.P( '</BODY></HTML>' );  --レスポンスの終わり
/*********************************/
-- 例外処理部
/*********************************/
EXCEPTION
   WHEN OTHERS THEN
     V_ERRMSG := SQLERRM;
     HTP.P(V_ERRMSG);
END DOC_TREE;
/
 
プロシージャが作成されました。

それでは解説の前に作成したプロシージャを実際に実行して、動作を確認してからソースコードの解説をします。

このプロシージャは起点となる文書またはディレクトリの番号(P_ID)を指定して実行します。指定された番号が文書の場合はその親ディレクトリ以下を、指定された番号がディレクトリの場合は、そのままそのディレクトリ以下を階層ツリーで表示します。

では最初に文書番号を指定してプロシージャをコールした場合を確認してみましょう。ここでは16番の文書番号でコールします。バックナンバー 第103回「WEBアプリ作成(1) (Oracle DBとPL/SQLだけで、即、WEBアプリ)」と同等の設定ができていれば、以下のURLです。ログインを求められたらスキーマユーザ(私の場合はSCOTT)でログインします。

http://localhost:8080/dad/doc_tree?p_id=16

16番の文書の親ディレクトリ(番号:17)をプログラム内で特定して、そのディレクトリーからすべてのデータをツリー構造で表示していますね。(このデモのために多くのデータを入れ替えたので、以前のデモで表示したデータ内容と違っています)画像にはありませんが、それぞれの行にはその文書・ディレクトリを表示するリンクが張ってありますので、タイトルをクリックすればその文書内容を表示可能です。なお、16番の文書は黄色にハイライトされて、指定された行であることを表しています。それぞれの左端の(n)の数値nは文書番号またはディレクトリの番号を表します。ここで、「▲上の階層へ」をクリックしてみましょう。

起点となるディレクトリが1つ上の階層に移動しましたのでより広い範囲が表示されます。そして当初の16番の文書は依然としてハイライトされたままです。このように、上のディレクトリに移動しても、当初の位置が一目でわかるように工夫してあります。そのような工夫により、文書を分類しやすくしています。

次に指定された番号がディレクトリだった場合も同様に行ってみましょう。ディレクトリ番号17を指定します。

http://localhost:8080/dad/doc_tree?p_id=17

17番のディレクトリ以下の階層がツリーで表示されていますね。そして、そのディレクトリそのものが黄色にハイライトされて、当初の指定位置であることを示しています。画像にはありませんが、上の階層に移動しても、ハイライトされた位置は変わりません。

このように、指定された番号が文書であれば、その親ディレクトリから、指定された番号がディレクトリであれば、そのままそのディレクトリから階層表示します。そして上の階層に移動しても、ハイライトされた行は変わらないので、文書を適切なディレクトリに分類したい場合など便利です。

上記の動きをソースコードで確認しましょう。まずこのプロシージャは起動時に番号(P_ID)を指定しますが、そのパラメータの定義が、ソースコード6行目です。そしてそのP_IDパラメータの番号をもとにそれが文書であるか、ディレクトリであるかを判定し、それぞれに合った起点となるディレクトリ番号を特定する記述が47~62行目のIF文です。このIF文により、起点となるディレクトリ番号が、V_ID変数に格納されます。なお、引数P_IDがNULLの場合は、V_IDもNULLであり、その場合は後述するロジックにより、最上位のすべてのディレクトリを表示するようにしてあります。また上の階層に移動するための親ディレクトリ番号(V_UP)もこのIF文で取得します。

実際に、P_IDを指定しない場合のケースもご覧ください。URLにP_IDパラメータを指定せずにプロシージャをコールします。
http://localhost:8080/dad/doc_tree

ご覧のように、P_IDを指定しなければ、すべての最上位ディレクトリ以下を階層表示します。

では再びソースコードの解説に戻ります。指定された行を黄色くハイライトする記述は、28~34行目のコードが関係します。この場所は指定されたレコードをインデント(左に空白を埋める)を付けた形に整形するファンクション内ですが、ここで、そのレコードが、当初の開始位置を表すP_ORG_IDパラメータと一致していれば、黄色にハイライトするための開始タグと終了タグをそれぞれ、V_HIGHLIGHT_START変数と、V_HIGHLIGHT_END変数に格納します。この2つの変数は、該当する場合以外ではNULL値です。35行目以下で常にそれらの変数を使って表示する行を生成しますので、該当する場合だけ黄色にハイライトされる訳です。

なお、「▲上の階層へ」には、1つ上の階層のディレクトリの番号(V_UP)をP_IDに指定してこのプロシージャへのリンクを貼ってあります(72~74行目)。同時に当初の開始位置をしめす、P_ORG_IDパラメータの値もセットします。なお、初回時は、P_ORG_IDパラメータはURLにセットしていないので、7行目のパラメータ定義によりNULL値になります。その場合は、NVL関数により、P_IDパラメータの値が使われます( 73行目 NVL(P_ORG_ID,P_ID)の記述)。つまり、初回時は指定した番号の位置がハイライトされますが、その後、上の階層に移動しても、その位置は「▲上の階層へ」のリンク内に保持されるので、階層が移動しても、黄色くハイライトされる行は変わらない訳です。

次に該当するレコードの取得方法に焦点をあてて解説します。

このプロシージャは起点となるディレクトリ以下を階層的に表示しますので、階層問い合わせを使ったカーソルFORループでレコードを1行ずつ取得します。階層の深さに制限のない状況を扱うためには、再帰コールを使う手法もありますが、今回は再帰コールではなく、階層問い合わせを使いました。(80~84行目の括弧内のSELECT文)

階層問い合わせでは起点を指定するための、START WITH句(82行目)が必要ですが、この記述でNVL関数の引数がNVL2関数となっており、少し解りにくいですね。今回の階層ツリー表示は、P_IDの指定の有無によって、特定のディレクトリを起点としたり、すべての最上位ディレクトリを起点とするなど、START WITH句の条件がそれぞれ違うわけです。その違いを吸収する工夫をした結果、START WITH句が少し複雑になりました。以下でもう少し詳しく解説します。

82行目 START WITH NVL(NVL2(V_ID,ID,OYA_DIR),0) = NVL(V_ID,0)

まず、NVL関数については一般的によく使われている関数であり、第1パラメータがNULLでないときは第1パラメータをそのまま返し、第1パラメータがNULLの時は第2パラメータを返す関数ですね。NVL2関数は、あまり一般的ではない関数ですが、第1パラメータがNULLでないときは、第2パラメータを返し、第1パラメータがNULLの時は、第3パラメータを返します。

そうすると、NVL2関数の仕様を当てはめると、V_ID変数が指定されている(NULLでない)のなら、82行目は以下と同じですね。

START WITH NVL(ID,0) = V_ID

さらに、ID列には主キー制約が設定してあるので、ID列にNULLはありえません。したがって、NVL(ID,0)はIDと同じです。結局、82行目は以下の記述と同じ意味になります。

START WITH ID = V_ID

したがってV_ID変数で指定されたディレクトを起点(START WITH)として、階層的に問い合わせることになります。

では、次にV_ID変数が指定されていない場合(NULL)はどうなるでしょうか? その場合はNVL2関数の仕様により、82行目は以下と同じ意味になります。

START WITH NVL(OYA_DIR,0) = 0

ここで条件「NVL(OYA_DIR,0) = 0」が成り立つのは、OYA_DIR列がNULLか、OYA_DIR列が0の時だけですね。しかし本システムではOYA_DIR列が0になることはありえません。なぜならOYA_DIR列には自分の表のID列を参照する外部キー制約が設定してあります。そして本システムではID列は登録時のプログラムロジックにより、1番から始まります。したがってそれを参照するOYA_DIR列が0になることはありえません。そのため実質的には「NVL(OYA_DIR,0) = 0」という条件は「OYA_DIR IS NULL」という条件と同じです。よって先のSTART WITH句は以下と同じ意味となります。

START WITH OYA_DIR IS NULL

OYA_DIR列がNULLのレコードは最上位ディレクトリですから、すべての最上位ディレクトリを起点として階層的に問い合わせることになります。

このように、82行目の記述は短いかわりに、その内容は複雑なのですが、P_IDパラメータを指定した場合と指定しない場合で、START WITH句の実質的な条件列と条件式をまったく違うものにすることができるわけです。バックナンバー第125回ではこれとよく似た状況を動的SQLで対応しましたが、今回はNVL関数とNVL2関数を組みわせる方法で対応したわけです。動的SQLの方法では多少コードが長くなるかわりに、意味的にわかりやすく書くことができます。どちらの方法も一長一短です。もしかすると、もっと簡単な方法が他にもあるのかもしれません。

最後に補足ですが、インデント処理で、LPAD関数を使っているコード(36行目)をご覧ください。LPAD関数で全角の空白文字を、6*(P_LEVEL-1)の長さ分だけ、左側に埋め込むことで、階層の深さ(P_LEVEL)に応じたインデント(横にずらす)処理をしています。ブラウザではPREタグなどを使わなければ、連続する半角の空白文字は1文字分しか表示されないので、全角の空白で対応したものです。この方法なら特別なタグなど使わずに簡単にきれいにインデントできます。

それでは今回はここまでといたします。

今後、近いうちに実装しようと思っている機能はメニューバーです。それを使って、登録、更新、検索など、個別に開発してきた基本機能をそのメニューバーに集約し、次レコードや前レコードへの移動、階層移動など、クリック1つで簡単にできるようにしたいと考えています。適切にリンクしたメニューバーを付ければ、羽が生えたように便利でサクサクと使いやすくなること請け合いです。

それでは次回ご期待ください。

先頭へ戻る