オラクルＦＡＱ検索システムの構築

PCDN OracleWG Toshikazu Fukuoka　

オラクルＦＡＱ検索システムは、pcdn.oracleで討論された内容からFAQとして採用できるものを抜き出して登録したものです。このシステムを作るには、

  1. システムデザイン
  2. DB構造の論理設計
  3. DB構造の物理設計
  4. PL/SQLパッケージの設計
のような設計工程が必要です。

システムデザイン
１つのQuestionに対して、複数の回答を設定できるようにします。例えば、新製品が発売され状況が変化したときにも、更新履歴と共に複数の回答が表示します。
また、質問や回答に含まれる技術的なバックグラウンドが検索に影響するようにカテゴリを質問に関連づけておき、カテゴリ検索が可能なようにします。

DB構造の論理設計
１つの質問に、複数のカテゴリと複数の回答を関連付けるので、正規化後の構造は、以下のようになります。
ER Diagram

DB構造の物理設計
今回は、論理設計の構造をそのまま物理構造として採用しています。
しかし、場合によっては検索速度などの観点からRDBMSにあった構造に変更する必要があります。

PL/SQLパッケージの設計
WASやOWSを使ってRDBMSと接続してHTMLを生成する方法は何種類かありますが、今回はPL/SQLを使う方法を採用しています。
通常、Webアプリケーションのプログラムは、サーバ上に格納されますが、PL/SQLはRDBMSの中にデータベースオブジェクトとして格納されます。そのため、普通の手段では、そのプログラムを実行することはできません。そこで、PL/SQLカートリッジを使って、urlとPL/SQLパッケージを関係つけます。つまり、ある特定のurlが特定のストアードパッケージに対応するようになります。今回は、
CREATE OR REPLACE PACKAGE www_user.PCDN_FAQ
IS
/*
||パッケージ名:PCDN_FAQ
||概要　　　　:Oracle TIPS PL/SQL
||オブジェクト:Search()　　:検索フォーム表示
||　　　　　　:List()　　　:対象一覧表示
||　　　　　　:Result()　　:検索結果表示
*/
TYPE achkKey IS TABLE OF VARCHAR2(8) INDEX BY BINARY_INTEGER;
PROCEDURE Search;
PROCEDURE List(rastrKey IN achkKey,rstrKeyword IN VARCHAR2);
PROCEDURE Result(rstrFAQ IN CHAR,rstrQ IN VARCHAR2);
END PCDN_FAQ;
というストアドパッケージを作成しましたので、その入り口は、

  • /was/plsql/pcdn_faq.search
  • /was/plsql/pcdn_faq.list
  • /was/plsql/pcdn_faq.result
    • というurlになります（wasは、DAD名です）。
    （※現在は上記とurlが異なっていますのでご注意ください）

    Marksearch
    search部の重要な部分を抜粋してみました。
        CURSOR curKey IS
       SELECT * 
         FROM KeyMaster
        ORDER BY KeyNo;
             :
           （中略）
             :
   HTP.FormOpen('PCDN_FAQ.List');
   HTP.FormHidden('rastrKey','dummy');
   FOR recKey IN curKey LOOP
      IF intCount=0 THEN
         HTP.P('<TR>');
      END IF;
      HTP.P('<TD>');
      HTP.FormCheckBox('rastrKey',recKey.KeyNo);
      HTP.P(recKey.Key);
      HTP.P('</TD>');
      IF intCount=2 THEN
         HTP.P('</TR>');
         intCount := 0;
      ELSE
         intCount := intCount + 1;
      END IF;
   END LOOP;
   IF intCount>0 THEN
      HTP.P('</TR>');
   END IF;
   HTP.P('</TABLE>');
   HTP.P('<P>');
   HTP.P('キーワード');
   HTP.FormText('rstrKeyword');
   HTP.P('<P>');
   HTP.FormSubmit(null,'検索');
   HTP.FormClose;
    HTPパッケージは、同一の名前の変数をPL/SQL表として扱います。「rastrKey」が今回はPL/SQL表として、フォームがSubmitされたときに「PCDN_FAQ.List」を呼び出す引数となります。
    ただし、引数として利用しているPL/SQL表に１件もデータが設定されていないとエラーが発生してしまいますので、

       HTP.FormHidden('rastrKey','dummy');
    として、ダミーの値を必ず設定するようにします。

    Marklist
    list部の重要な部分を抜粋してみました。
    PROCEDURE List(rastrKey IN achkKey,rstrKeyword IN VARCHAR2 DEFAULT '')
IS
    CURSOR curKey(strKey IN VARCHAR2) IS
       SELECT * 
         FROM KeyMaster
        WHERE KeyNo=strKey;
             :
           （中略）
             :
   /* 検索キー表示 */
   FOR intCount IN 2..rastrKey.count LOOP
      OPEN curKey(rastrKey(intCount));
      FETCH curKey INTO recKey;
      IF curKey%FOUND THEN
         HTP.P(recKey.Key || ' ');
      ELSE
         HTP.P('''' || rastrKey(intCount) || ''' ');
      END IF;
      CLOSE curKey;
      strWhere := strWhere || ' OR KeyNo = ''' || rastrKey(intCount) || '''';
   END LOOP;
             :
           （中略）
             :
   /* SELECT文生成 */
   IF rastrKey.count >= 2 THEN
      strSQL := '   SELECT DISTINCT FAQ.FAQID,FAQ.Title' ||
                '     FROM FAQ,Key' ||
                ' WHERE FAQ.FAQID = Key.FAQID';
      strSQL := strSQL || ' AND (' || SUBSTR(strWhere,5) || ')';
      IF rstrKeyword IS NOT NULL THEN
         strSQL := strSQL || ' AND FAQ.Question Like ''' || rstrKeyword || '%''';
      END IF;
   ELSE
      strSQL := '   SELECT DISTINCT FAQ.FAQID,FAQ.Title' ||
                '     FROM FAQ ';
      IF rstrKeyword IS NOT NULL THEN
         strSQL := strSQL || ' WHERE FAQ.Question Like ''' || rstrKeyword || '%''';
      END IF;
   END IF;
   strSQL := strSQL || ' ORDER BY FAQID DESC';

   /* 問い合わせ用のカーソルをオープンする */
   curSearch := DBMS_SQL.OPEN_CURSOR;

   /* SELECT SQLを解析 */
    DBMS_SQL.PARSE(curSearch,strSQL,DBMS_SQL.V7);

   /* 出力変数を定義 */
   DBMS_SQL.DEFINE_COLUMN(curSearch,1,recFAQID,8);
   DBMS_SQL.DEFINE_COLUMN(curSearch,2,recTitle,100);
 
   /* SELECT SQL実行 */
   intCount := DBMS_SQL.EXECUTE(curSearch);

   LOOP
      IF DBMS_SQL.FETCH_ROWS(curSearch) = 0 THEN
         EXIT;
      END IF;
      /* 値を取り出す */
      DBMS_SQL.COLUMN_VALUE(curSearch,1,recFAQID);
      DBMS_SQL.COLUMN_VALUE(curSearch,2,recTitle);
      /* HTML生成 */
      HTP.P(recFAQID);
      HTP.P('<A HREF="/was/plsql/pcdn_faq.Result?rstrFAQ=' || recFAQID || '">' || recTitle || '</A><BR>');
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR(curSearch);
    Listプロシージャでのポイントは、動的SQL文をサポートしている点です。

    Searchプロシージャで指定されたカテゴリーとキーワードを引数として受け取り、その内容を基にして、「strSQL」にSQL文を組み立てていきます。そして、そのSQL文をDBMS_SQLパッケージにより、カーソルに割当ててFETCH_ROWSしていきます。
    動的SQLによる、SELECT SQL文の実行は、
    OPEN_CURSOR
    PARSE
    DEFINE_COLUMN
    EXECUTE
    FETCH_ROWS
    CLOSE_CURSOR
    の手順を取ります。詳しくはリスト中のコメントを参照してください。

    Markresult
    Result部の重要な部分を抜粋してみました。
    PROCEDURE Result(rstrFAQ IN CHAR)
IS
             :
           （中略）
             :
    CURSOR curAns IS
       SELECT *
         FROM AnswerMaster
        WHERE ANO IN (SELECT ANO FROM Answer WHERE FAQID=rstrFAQ)
        ORDER BY AnswerDate DESC;
             :
           （中略）
             :
   HTP.P('<dl><B>Answer</B>');
   IF recAnsCount.AnsCount>0 THEN
      FOR recAns IN curAns LOOP
         HTP.P('<dt>' || TO_CHAR(recAns.AnswerDate,'YYYY/MM/DD'));
         HTP.P('<dd>');
         HTP.P(recAns.Answer || '<BR>');
         IF recAnsCount.AnsCount > 1 THEN
             HTP.P('<p>');
             HTP.P('<hr width=60%>');
         ELSE
            recAnsCount.AnsCount := recAnsCount.AnsCount - 1;
         END IF;
      END LOOP;
      HTP.P('</dl>');
   ELSE
      HTP.P('<H2>現在、回答作成中です。もう暫くお待ち下さい。</H2><BR>');
   END IF;

    MarkPL/SQLパッケージ
    PL/SQLパッケージヘッダ PL/SQLパッケージ本体

    Oracle WorkGroup
    int21 ホームページ | PCDN ホームページ


    Copyright (c) 1998 Toshikazu Fukuoka & PCDN All Rights Reserved.
    For questions or comments, please send mail to: pcdn@int21.co.jp