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

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

第131回「実用WEBアプリ 文書管理システム(12)検索機能(その3 生成したSELECT文の実行)」

2015.06.04

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

検索機能の第3回目です。今回で検索機能は完成ですね。
前回は入力した検索条件に応じたSELECT文を生成するところまでを実装しました。今回は生成したSELECT文を実行し、その結果を画面に表示します。

では、そのソースコードを解説しますが、その前にいつものように、文書やディレクトリの情報を格納するDOCS表の定義を掲載します。この表を問い合わすSELECT文を実行するわけです。表の定義も参照しながらプログラムロジックを確認してください。

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             --更新日

では早速、前回のプロシージャに今回の機能を追加したソースコードを以下に掲載します。

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

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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
CREATE OR REPLACE PROCEDURE DOC_FIND
( P_OYA_DIR   IN  VARCHAR2 DEFAULT NULL ,   -- 親ディレクトリ
   P_KEYWORD1  IN  VARCHAR2 DEFAULT NULL ,   -- キーワード1
   P_KEYWORD2  IN  VARCHAR2 DEFAULT NULL ,   -- キーワード2
   P_AND_OR    IN  VARCHAR2 DEFAULT 'AND' -- 「AND」または「OR」
   P_COL       IN  VARCHAR2 DEFAULT 'BOTH' , -- 「タイトル」または「タイトルと本文」
   P_BUTTON    IN  VARCHAR2 DEFAULT  NULL -- 送信ボタンをクリックしたかどうかの判定
IS
   V_OYA_DIR   NUMBER;
   V_ERRM      VARCHAR2(1000);
   V_KBN       DOCS.KBN%TYPE;
   V_SELECT_KBN_LIST  VARCHAR2(300);
   V_AND_OR_LIST      VARCHAR2(300);
   USERERROR   EXCEPTION;
   V_SELECT_STR       VARCHAR2(32767);
   V_KEYWORD1          VARCHAR2(32767);
   V_KEYWORD2          VARCHAR2(32767);
   V_WHERE_COL         VARCHAR2(100);
   V_WHERE_SUBDIR      VARCHAR2(32767);
   CNT                 NUMBER := 0;  -- ★追加(No.131)
   TYPE CUR_DOC_TYPE IS REF CURSOR ;    -- ★追加(No.131)
   CUR_DOC CUR_DOC_TYPE;               -- ★追加(No.131)  カーソル変数の宣言
   TYPE CUR_REC_TYPE  IS RECORD ( ID   DOCS.ID%TYPE,   -- ★追加(No.131) レコード型の宣言
                            TITLE  DOCS.TITLE%TYPE,
                            KBN    DOCS.KBN%TYPE,
                            INSERT_DATE  DOCS.INSERT_DATE%TYPE,
                            UPDATE_DATE  DOCS.UPDATE_DATE%TYPE);
   REC  CUR_REC_TYPE;   -- ★追加(No.131)   そのレコード型の変数宣言
BEGIN
/**********************************/
/*  検索条件の入力フォームを生成  */
/**********************************/
   -- 検索対象区分のリストを生成
   V_SELECT_KBN_LIST := '<SELECT NAME="P_COL"><OPTION VALUE="TITLE">タイトルのみ</OPTION>' ||
                        '<OPTION VALUE="BOTH">タイトルと本文</OPTION></SELECT>' ;
   V_SELECT_KBN_LIST := REPLACE (V_SELECT_KBN_LIST, '"' || P_COL || '"' , '"' ||P_COL|| '" SELECTED' );
   -- AND_OR のリストを作成
   V_AND_OR_LIST := '<SELECT NAME="P_AND_OR"><OPTION>AND</OPTION><OPTION>OR</OPTION></SELECT>' ;
   V_AND_OR_LIST := REPLACE (V_AND_OR_LIST, '<OPTION>' ||P_AND_OR, '<OPTION SELECTED>' ||P_AND_OR);
   -- 検索条件の入力フォームを作成
   HTP.P( '<HTML>' );
   HTP.P( '<HEAD><TITLE>ディレクトリ・文書検索</TITLE></HEAD>' );
   HTP.P( '<BODY>' );
   HTP.P( '<H2>ディレクトリ・文書検索</H2>' );
   HTP.P( '<FORM  ACTION="doc_find" METHOD="POST">' );
   HTP.P( '<TABLE BORDER>' );
   HTP.P( '<TR><TD>親ディレクトリ(省略時:全ディレクトリ対象)</TD>' ||
             '<TD><INPUT TYPE="TEXT" NAME="P_OYA_DIR" VALUE="' ||P_OYA_DIR|| '"></TR>' );
   HTP.P( '<TR><TD>区分</TD><TD>' || V_SELECT_KBN_LIST || '</TD></TR>' );
   HTP.P( '<TR><TD>キーワード1</TD>' ||
         '<TD><INPUT TYPE="TEXT" NAME="P_KEYWORD1" VALUE="' || P_KEYWORD1 || '"></TD></TR>' );
   HTP.P( '<TR><TD>キーワード2</TD>' ||
         '<TD><INPUT TYPE="TEXT" NAME="P_KEYWORD2" VALUE="' || P_KEYWORD2 || '"></TD></TR>' );
   HTP.P( '<TR><TD>条件</TD><TD>' ||V_AND_OR_LIST || '</TD></TR>' );
   HTP.P( '</TABLE>' );
   HTP.P( '<INPUT TYPE="SUBMIT" NAME="P_BUTTON" VALUE="検索実行">' );
   HTP.P( '</FORM>' );
   IF P_BUTTON = '検索実行' THEN
    /******************************************/
    /*  チェックする                          */
    /******************************************/
     -- 区分
     IF P_COL NOT IN  ( 'BOTH' , 'TITLE' ) THEN
        V_ERRM := '・区分リストの値が不正です' ;
     END IF;
     IF P_AND_OR NOT IN ( 'AND' , 'OR' ) THEN
        V_ERRM := V_ERRM || '<BR>・条件リストの値が不正です' ;
     END IF;
     -- 親ディレクトリのチェック
     IF P_OYA_DIR IS NOT NULL  THEN
        BEGIN
        V_OYA_DIR := TO_NUMBER(P_OYA_DIR);
        SELECT KBN INTO V_KBN FROM DOCS WHERE ID = V_OYA_DIR;
        IF  V_KBN <> 2  THEN   --  ディレクトリでない
            V_ERRM := V_ERRM || '<BR>・指定された番号はディレクトリではありません' ;
            --RAISE  USERERROR;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
               V_ERRM := V_ERRM || '<BR>' || SQLERRM;
        END -- ネストブロック終わり
     END IF;
     -- 検索キーワードが二つともNULLでないことをチェックしつつ、キーワードの変数セット
     IF P_KEYWORD1 IS NULL AND P_KEYWORD2 IS NULL THEN
        V_ERRM := V_ERRM || '<BR>' || '・最低一つ検索キーワードを指定してください' ;
     ELSIF P_KEYWORD1 IS NOT NULL AND P_KEYWORD2 IS NULL THEN
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD1) || '%' ;
     ELSIF P_KEYWORD1 IS NULL AND P_KEYWORD2 IS NOT NULL THEN
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD2) || '%' ;
     ELSE
        V_KEYWORD1 := '%' || UPPER (P_KEYWORD1) || '%' ;
        V_KEYWORD2 := '%' || UPPER (P_KEYWORD2) || '%' ;
     END IF;
     -- 上記のチェックの結果、チェックに反することがあれば、エラーとする
     IF V_ERRM IS NOT NULL  THEN
        RAISE  USERERROR;
     END IF;
     /*************************************************/
     /*  SELECT 文を生成する                           */
     /*************************************************/
     V_SELECT_STR := 'SELECT ID, TITLE, KBN, INSERT_DATE, UPDATE_DATE  FROM DOCS WHERE ' ;
     -- 検索条件の列(TILTEなのか、TITTLEとHONBUNを連結したものか)
     IF P_COL = 'TITLE' THEN
        V_WHERE_COL := 'UPPER(TITLE)' ;
     ELSE
        V_WHERE_COL := 'UPPER(TITLE || HONBUN)' ;
     END IF;
    --キーワード1 を指定する
    V_SELECT_STR := V_SELECT_STR || '(' || V_WHERE_COL || ' LIKE :V_KEYWORD1 ' ;
    IF V_KEYWORD2 IS NOT NULL THEN
       V_SELECT_STR := V_SELECT_STR || ' ' || P_AND_OR || ' ' || V_WHERE_COL || ' LIKE :V_KEYWORD2 ' || ')' ;
    ELSE
       V_SELECT_STR := V_SELECT_STR || ')' ;
    END IF;
    -- 親ディレクトリの指定がある場合
    IF V_OYA_DIR IS NOT NULL THEN
        -- 「ID IN (1,5,6,・・・)」といった親ディレクトリ以下のIDを指定する条件を生成
        V_WHERE_SUBDIR := ' ID IN (' ;
          FOR REC IN ( SELECT ID FROM DOCS
                 START WITH ID = V_OYA_DIR
                 CONNECT BY PRIOR ID = OYA_DIR)  LOOP
                 V_WHERE_SUBDIR := V_WHERE_SUBDIR || TO_CHAR(REC.ID) || ',' ;
          END LOOP;
          -- 最後の余計の',' をとる
          V_WHERE_SUBDIR := SUBSTR(V_WHERE_SUBDIR,1,LENGTH(V_WHERE_SUBDIR)-1);
          -- 最後に')'を付ける
          V_WHERE_SUBDIR := V_WHERE_SUBDIR || ')' ;
          -- その条件を 生成するSELECT文の最後に付ける
          V_SELECT_STR := V_SELECT_STR || ' AND ' || V_WHERE_SUBDIR;
    END IF;
    -- SELECT文が完成
    V_SELECT_STR := V_SELECT_STR || ' ORDER BY UPDATE_DATE DESC' ;
   /************************************************************/
   /*  その SELECT 文を実行する   ★★今回( No .131) 追加する処理  */
   /************************************************************/
   IF V_SELECT_STR LIKE '%:V_KEYWORD2%'  THEN
      OPEN CUR_DOC FOR V_SELECT_STR USING V_KEYWORD1, V_KEYWORD2;
   ELSE
      OPEN CUR_DOC FOR V_SELECT_STR USING V_KEYWORD1;
   END IF;
   HTP.P( '<TABLE BORDER>' );
   HTP.P( '<TR BGCOLOR="SILVER"><TD>ID</TD><TD>区分</TD><TD>タイトル</TD><TD>登録日時</TD><TD>更新日時(↑)</TD></TR>' );
   LOOP
   FETCH CUR_DOC INTO REC;
   EXIT WHEN CUR_DOC%NOTFOUND;
      HTP.P( '<TR><TD>' || TO_CHAR(REC.ID) || '</TD>' ||
   --   '<TD>' ||TO_CHAR(REC.KBN) ||'</TD>' ||
      '<TD><CENTER>' || CASE REC.KBN  WHEN 1 THEN '-' WHEN 2 THEN '▼' ELSE NULL  END || '</CENTER></TD>' ||
      '<TD><A HREF="doc_show?p_id=' || TO_CHAR(REC.ID) || '">' || REC.TITLE || '</A></TD>' ||
      '<TD>' || TO_CHAR(REC.INSERT_DATE, 'YY/MM/DD HH24:MI:SS' )|| '</TD>' ||
      '<TD>' || TO_CHAR(REC.UPDATE_DATE, 'YY/MM/DD HH24:MI:SS' ) || '</TD></TR>' );
      CNT := CNT + 1;
   END LOOP;
   CLOSE CUR_DOC;
   HTP.P( '</TABLE>' );
   HTP.P( '該当 ' || TO_CHAR(CNT)|| '件' );
   /*******************************************************************/
   /*▲▲▲▲  ★★追加した処理( SELECT 文の実行)の終わり ▲▲▲▲ ***/
   /*******************************************************************/
   ELSE
       HTP.P( '検索画面へようこそ。条件を指定して検索実行ボタンをクリックしてください' );
   END IF;
   -- HTMLページの最後
   HTP.P( '</BODY>' );
   HTP.P( '</HTML>' );
EXCEPTION
   WHEN USERERROR  THEN
       HTP.P(V_ERRM);
   WHEN OTHERS  THEN
       V_ERRM    := SQLERRM;
       HTP.P(V_ERRM);
END DOC_FIND;
/
 
プロシージャが作成されました。

重要な部分を解説します。

前回の実装で、生成した文字列のSELECT文は、132行目で、V_SELECT_STRという変数に格納されています。よってその下の133行目から、今回実装する処理(SELECT文の実行と結果の表示)が始まります。

V_SELECT_STR変数には文字列のSQL、つまり動的SQLが格納されています。動的SQLで複数行を返すSELECT文を実行するためには、カーソル変数が必要です。カーソル変数は、21~22行目で宣言しています。まず、REF CURSOR(参照カーソル)の型を宣言する(21行目)。そして、その型を使って、カーソル変数を宣言する(22行目)。ここでは、CUR_DOCという名前のカーソル変数を宣言しているわけです。
なお、カーソル変数についてはバックナンバー第46回 「動的SQL(複数行返す問い合わせの場合)」で詳しく解説しています。

カーソル変数に対して、動的SQLのSELECT文を指定してOPENすれば、SELECT文が実行されて結果セット(行の集合)が生成されます。
ただし、バインド変数が含まれていれば、その数だけUSING句でバインド変数の値を指定する必要があります。バインド変数の値は検索条件項目の「キーワード1」と「キーワード2」の値なのでバインド変数の数は1つの場合もあれば、2つの場合もあります。
ですから、バインド変数が1つなのか、2つなのかを判定して、それにあったUSING句で動的SQLをOPENする必要があります。その記述が136~140行目のIF文です。ご覧のように、SELECT文に、「:V_KEYWORD2」という文字が含まれていれば、バインド変数が2つあると判断し、「USING V_KEYWORD1, V_KEYWORD2」というUSING句でそれぞれのバインド変数の値を指定してOPENします。
「:V_KEYWORD2」という文字が含まれていなければ、バインド変数の数は1つと判断し、「USING V_KEYWORD1」というUSING句でOPENします。今回、バインド変数が0個ということはあり得ません。なぜなら84~93行目の検索条件のチェック処理で、「キーワード1」と「キーワード2」がともにNULLの場合はエラーにするからです。
そして、「キーワード2」のみ値をセットしてあったとしても、セットするバインド変数は「V_KEYWORD1」です(88~89行目)。したがって、SELECT文に「:V_KEYWORD2」という文字が含まれないときは必ず、「V_KEYWORD1」というバインド変数のみが存在することになります。
なお、バインド変数についての詳細はバックナンバー第47回 「動的SQLでのバインド変数の使用」をご覧ください。

バインド変数が1つでも2つでも、OPENして実行し、そこで得られる結果セット(行の集合)の参照をカーソル変数に取得した後は、普通の明示カーソル処理と同じ流れです。

最初に、カーソル変数から1行フェッチします(144行目)。代入先のREC変数は23~28行目で宣言しています。REC変数はレコード型の変数です。レコード型の変数は2段階の宣言になります。最初にカーソルの行と同じ列をもつレコード型を宣言し(23~27行目)、次にその型でレコード型変数を宣言しています(28行目)。

1行フェッチした後はその行(REC変数)を使って、HTML表の1行を生成します(146~151行目)。FETCHして、1行を生成する処理を繰り返すので、143~153行の範囲がループ処理(LOOP~ END LOOP;)です。ループの終了判定が145行目の EXIT WHEN CUR_DOC%NOTFOUND; の記述です。つまりFETCHして取り出し行がなかったときに、%NOTFOUNDがTRUEなので、その条件でループをEXITします。そして、ループを終えた後、最後にCLOSE処理(154行目)して結果セットを開放します。まったく普通の明示カーソル処理と同じ流れですね。

実行結果の列はご覧のように、「ID」列、「区分」列、「タイトル」列、「登録日時列」列、「更新日時」列がありますが、「区分」列については、コメントアウトしている147行目「'' ||TO_CHAR(REC.KBN) ||''」のように生成すると、「1」または「2」という値なのでわかりにくいですね。したがって、148行目のように、CASE式で、「1」の場合は「-」、「2」の場合は「▼」を表示するようにしました。「-」は文書、「▼」はディレクトリを表しているつもりです。

また、タイトル列に注目すると、リンクが貼ってあることがわかります。(149行目 <A HREF="doc_show?p_id=' || TO_CHAR(REC.ID) ||'">')このリンク先のdoc_showは、第123回第124回で作成した文書やディレクトリの表示プロシージャです。
ですから、検索結果のタイトルをクリックすると、その文書の内容やディレクトリ以下の一覧が表示できます。

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

http://localhost:8080/dad/doc_find

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

ディレクトリ・文書検索画面が開きました。まだ条件は指定していません。

「タイトル」に「太郎」を含むディレクトリや文書を検索します。

正しく検索できました。3件の該当があります。

次に、本文に「鬼退治」を含むものに絞り込みます。

「桃太郎」に絞りこまれました。タイトルに表示プロシージャへのリンクが貼ってありますので、クリックします。

「桃太郎」の文書が表示されました。確かに本文に「鬼退治」というキーワードが含まれています。

いかがですか? この検索機能があればたくさんの文書やディレクトリがあっても、自分の必要な情報がすぐに見つかりますね。ぜひ使ってみてください。

それでは今回はここまでにします。

今後、この文書管理システムに実装すべき機能として以下のようなことを考えていいます。

  • 階層的にディレクトリや文書を表示する。
  • 複数文書の所属先ディレクトリや表示順番を一括で変更する。
  • 画面上部にメニューバーを設け、レコード移動や階層移動を容易にしたり、登録、更新、検索機能を集約する。
  • JavaScript+再帰コールで階層の深さに制限のない開閉可能フォルダ機能を実装する。
  • etc

それでは今後もご期待ください。

先頭へ戻る