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

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

第125回「実用WEBアプリ 文書管理システム(6)ディレクトリ表示~最上位の階層表示は構文が違うので動的SQLを使う~」

2015.04.16

こんにちは。インストラクターの蓑島です。
今年の春はなかなか暖かくならないですね。早く春らしくなってもらいたいものです。

前回の内容は、親ディレクトリの番号を指定して、そのディレクトリ以下の文書や、サブディレクトリを一覧表示するものでした。つまり親ディレクトリがあり、その下の子供たちを表示するわけです。

では、番号を省略したとき、親ディレクトリのない最上位のディレクトリの一覧を表示するためには、どのようにプログラムを修正すればよいのか?

それが今回のテーマですね。
そのためには、最上位のディレクトリ一覧を取得する場合だけ、異なる構文のSELECT文を使う必要がある。そのためには、動的SQLを使う方法が素直な方法である。ということを、前回の最後に少しだけ予告しました。

つまり、場合により以下のふたつのSELECT文のどちらかの構文を使う必要がある。そのためには、構文毎にカーソルを宣言するのではなく、動的SQL文を使えば簡単に取り扱えるということでしたね。

●親ディレクトリ以下の一覧を取得する場合
SELECT * FROM DOCS WHERE OYA_DIR = REC2.ID ORDER BY SEQ (WHERE句の条件が OYA_DIR = 番号)

●最上位ディレクトリの一覧を表示する場合
SELECT * FROM DOCS WHERE OYA_DIR IS NULL ORDER BY SEQ (WHERE句の条件が OAY_DIR IS NULL)

ここで少し動的SQLについて復習します。動的SQLとは、文字列のSQL文のことです(バックナンバー第43回 「動的SQLの概要」参照)。例えばシングルコーテーションで囲ったSQL文('SELECT・・・') や、あるいは、VARCHAR2型などの文字型変数を連結した組み立てたSQL文のことです。逆に動的でないSQL文、つまりシングルコーテーションで囲まずに、直接記述されるSQL文のことを埋め込みSQLといいます。埋め込みSQLは構文が確定していますが、動的SQLはプロシージャ作成時に構文が決定されている必要はなく、プロシージャの実行時にSQLの構文を確定させることができます。

PL/SQL言語では埋め込みSQLの方が一般的で、動的SQLはSQLの構文が実行時まで確定しないような特殊な状況でのみ使われます。しかし、PL/SQL以外の言語では、動的SQLの方が一般的です。なぜなら、PL/SQL以外の言語で、埋め込みSQLを使おうとすれば、そのSQL文をその言語用のコンパイラでコンパイル可能な形にいったん変換(プリコンパイル)してからコンパイルする必要があるからです。
そのようなプリコンパイルが言語や環境によっては用意されていないことが多いので、PL/SQL以外の言語では、動的SQL文(シングルコーテーションや、ダブルコーテーションで囲まれたSQL文、つまり文字列のSQL文)を使うことが多いのです。PL/SQL言語は、オラクルが開発した言語であり、PL/SQLブロックも、その中のSQL文もオラクルデータベース上で実行されます。ですから埋め込みSQLのままでそのままコンパイルできますので、埋め込みSQLが一般的です。

では、動的SQLの概要復習はここまでにして、前回のプロシージャを動的SQL文を使った形に修正して最上位ディレクトリの対応をします。

いつものように、ソースコードの解説の前に、文書やディレクトリを格納する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             --更新日

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

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
CREATE OR REPLACE PROCEDURE DOC_SHOW
(P_ID  IN VARCHAR2 DEFAULT NULL )
IS
/*****************************************/
/*  文書・ディレクトリ  表示処理         */
/*****************************************/
   V_ID NUMBER;  -- 文書(またはディレクトリ)の番号
   V_ERRMSG   VARCHAR2(1000);   -- エラーメッセージ
   REC         DOCS%ROWTYPE;    -- DOCS表の1行を格納する変数
   USER_ERROR     EXCEPTION;    -- ユーザ定義例外
/************************************************/
/*  文書を表示するローカルプロシージャ          */
/************************************************/
   PROCEDURE  DOC_DISPLAY(REC1 IN DOCS%ROWTYPE)
   IS
   BEGIN
      HTP.P( '<HTML>' );
      -- タイトルをセット
      HTP.P( '<HEAD><TITLE>' || REC1.TITLE || '</TITLE></HEAD>' );
      HTP.P( '<BODY>' );
      HTP.P( '<FONT COLOR="BLUE"><I>' ||REC1.TITLE || '</I></FONT>'
      || '<FONT SIZE="-1">['   --タイトルの横に更新日時をセット
      ||TO_CHAR(REC1.UPDATE_DATE, 'YYYY/MM/DD HH24:MI' ) || ']</FONT>' );
      -- 本文は改行を含むので、PREタグで囲み、そのままを表示する
      HTP.P( '<PRE>' || REC1.HONBUN || '</PRE>' );
      HTP.P( '</BODY>' );
      HTP.P( '</HTML>' );
   END DOC_DISPLAY;
/************************************:***********/
/*  ディレクトリを表示するローカルプロシージャ  */
/************************************************/
   PROCEDURE  DIRECTORY_DISPLAY(REC2 IN DOCS%ROWTYPE)
   IS
      TYPE CUR_DOCS_TYPE IS REF CURSOR ;   -- 型の宣言
      CUR_DOCS   CUR_DOCS_TYPE;           -- その型を使って、カーソル変数を宣言
      REC_DOCS DOCS%ROWTYPE;
      V_TITLE  DOCS.TITLE%TYPE;
   BEGIN
   /************************************************/
   /*   動的SQLを使った、明示カーソルのオープン    */
   /************************************************/
      IF REC2.ID IS NULL THEN  -- パラメータが指定されていないとき、(OYA_DIR IS NULL)
         OPEN CUR_DOCS FOR 'SELECT * FROM DOCS WHERE OYA_DIR IS NULL ORDER BY SEQ' ;
         V_TITLE := '最上位ディレクトリの一覧' ;
      ELSE  -- パラメータが指定されているとき (OYA_DIR = :1)
         OPEN CUR_DOCS FOR 'SELECT * FROM DOCS WHERE OYA_DIR = :1 ORDER BY SEQ' USING REC2.ID;
         V_TITLE := REC2.TITLE;
      END IF;
      HTP.P( '<HTML>' );
      HTP.P( '<HEAD><TITLE>' || V_TITLE|| '</TITLE></HEAD>' );
      HTP.P( '<BODY>' );
      -- ディレクトリのタイトルを表示
      HTP.P( '<FONT COLOR="BLUE"><I>' || V_TITLE || '</I></FONT>' );
      -- このディレクトリ以下の文書とサブディレクトリを表示
      HTP.P( '<TABLE BORDER>' );
      HTP.P( '<TR BGCOLOR="LIGHTYELLOW"><TD>ID</TD><TD>#</TD><TD>タイトル</TD><TD>更新日</TD></TR>' );
   /************************************************/
   /*   カーソル変数を使った、行の取り出し( FETCH )  */
   /************************************************/
      LOOP
         FETCH CUR_DOCS INTO REC_DOCS;
         EXIT WHEN CUR_DOCS%NOTFOUND;
         HTP.P( '<TR>' ||
               '<TD>' || TO_CHAR(REC_DOCS.ID) || '</TD>' ||
               '<TD>' || CASE REC_DOCS.KBN  WHEN 1 THEN '文' ELSE  '▼' END || '</TD>' ||
               '<TD><A href="doc_show?p_id=' || TO_CHAR(REC_DOCS.ID) || '">' || REC_DOCS.TITLE || '</A></TD>' ||
               '<TD>' || TO_CHAR(REC_DOCS.UPDATE_DATE, 'YYYY/MM/DD HH24:MI' ) || '</TD>' ||
               '</TR>' );
      END LOOP;
      CLOSE CUR_DOCS;
      HTP.P( '</TABLE>' );
   END DIRECTORY_DISPLAY;
/*****************************************************************/
/*   実行部                                                      */
/*****************************************************************/
BEGIN
   /********************/
   /* チェック         */
   /********************/
   IF P_ID IS NULL THEN    --番号が指定されていなければ
      DIRECTORY_DISPLAY( NULL );     --最上位のディレクトリ一覧を表示する
   ELSE                    --番号が指定されていれば
       V_ID := TO_NUMBER(P_ID);     --その番号を数字に変換
                           -- その番号でDOCS表の1レコード(行)を取得
       SELECT * INTO REC  FROM DOCS WHERE ID = V_ID;
       /**************************************************************/
       /* その1レコードの区分(KBN)に応じて文書またはディレクトリを表示 */
       /**************************************************************/
       IF REC.KBN = 1 THEN     -- その行が文書(KBN=1)なら
          DOC_DISPLAY(REC);    -- 文書表示のローカルプロシージャをコール
       ELSIF  REC.KBN = 2 THEN -- その行がディレクトリ(KBN=2)なら
          DIRECTORY_DISPLAY(REC);  -- ディレクト表示のローカルプロシージャを
       ELSE
          V_ERRMSG := 'KBN列の値が1,2以外です。ありえません。' ;
          RAISE USER_ERROR;
       END IF;
   END IF;
EXCEPTION
   WHEN VALUE_ERROR THEN
        HTP.P( '文書番号が数値ではありません' );
   WHEN NO_DATA_FOUND THEN
        HTP.P( '指定された番号が文書表(DOCS表)に存在しません' );
   WHEN USER_ERROR THEN
        HTP.P(V_ERRMSG);
   WHEN OTHERS THEN
        V_ERRMSG := SQLERRM;
        HTP.P(V_ERRMSG);
END ;
/
 
プロシージャが作成されました。

では重要な部分について解説します。

最初に、2行目ですが、P_IDパラメターに「DEFAULT NULL」を指定しました。

2行目 (P_ID IN VARCHAR2 DEFAULT NULL)

この意図は、P_IDパラメータの指定を省略可能にするためです。省略されれば、NULL値となり、最上位ディレクトリの一覧をリクエストすることになります。

次に実行部(76~97行目)をご覧ください。実行部の先頭にIF文(80行目)が追加されています。このIF文で、P_IDパラメータがNULLのときは、最上位のディレクトリの一覧を表示するために、DIRECTORY_DISPLAYプロシージャをNULLのパラメータで呼び出しています。ELSE以下の処理(83~96行目)は前回の実行部全体の処理とまったく同じです。つまり前回と比べるとP_IDパラメータがNULLの場合の処理を追加しているわけです。

その追加された処理とは、DIRECTORY_DISPLAYプロシージャをNULLのパラメータで呼び出す処理です。つまり、このプロシージャはパラメータがNULLのときは、最上位のディレクトリ一覧を表示するように修正してあります。

そのDIRECTORY_DISPLAYプロシージャの記述は、32~72行目です。

その記述で、まず、34~35行目ですが、カーソル変数(CUR_DOCS)の宣言を追加しました。動的SQLのSELECT文を明示カーソル処理するためには、カーソル変数が必要です。(バックナンバー 第46回 「動的SQL(複数行返す問い合わせの場合)」参照)

カーソル変数を使って、以下の構文で動的SQLのSELECT文の結果セットの参照を取得できます。

OPNE カーソル変数名 FOR '動的SQLのSELECT文' [USING バインド変数の値];

その部分は、42~48行目のIF文をご覧ください。

ここのIF文で、パラメータがNULLのときと、そうではないときで以下の文をそれぞれ実行します。

パラメータがNULLのとき、
(43行目) OPEN CUR_DOCS FOR 'SELECT * FROM DOCS WHERE OYA_DIR IS NULL ORDER BY SEQ';

パラメータがNULLでないとき
(46行目) OPEN CUR_DOCS FOR 'SELECT * FROM DOCS WHERE OYA_DIR = :1 ORDER BY SEQ' USING REC2.ID;

ここで、46行目の「:1」の記述ですが、この記述をバインド変数といいます。その値は、USING句で指定する、REC2.IDの値です。

これにより、パラメータが指定されていないときは、「OYA_DIR IS NULL」のSELECT文が実行され、パラメータが指定されているときは 「OYA_DIR = 値」のSELECT文が実行されます。いずれにしろ、そのSELECT文の結果セット(行の集合)への参照がCUR_DOCSというカーソル変数にセットされます。

それ以降は、そのカーソル変数名で、あたかも普通のカーソル名と同じように操作ができます(FETCH , %NOTFOUND, CLOSE)

ということで、そのカーソル変数を使って、60~69行目のLOOP処理で、1行ずつ結果セットから行を取得して最上位ディレクトリの明細を形成しているわけです。前回まではこの部分の処理は、明示カーソル処理の簡便な書き方であるカーソルFORループ文の構文で書いていましたが、今回は、カーソル変数を使っていますので、カーソルFORループ文ではなく、OPEN、FETCH、 終了判定(%NOTFOUND)、CLOSEをそれぞれ記述しています。

では解説はここまでにして、早速実行して試してみましょう。今回、私の環境では最上位ディレクトリが2つあります。

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

http://localhost:8080/dad/doc_show

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

確かに、P_IDを省略すると、最上位ディレクトリの一覧が表示され、2つの最上位ディレクトリがあることがわかります。正しい結果です。

これで、今回のテーマは実装できました。

最後に、別の方法として、P_IDパラメータがNULLの場合の特別扱いを、動的SQLで対応するのではなく、以下のように、NVL関数で対応すれば単純な一つのSELECT文で対応できるのではないか?と思われる方もいると思います。

SELECT * FROM DOCS WHERE NVL(OYA_DIR,0) = NVL(REC2.ID,0) ORDER BY SEQ

つまり、OYA_DIR列と、REC2.IDパラメータそれぞれをNULLの時に、0に置き換えれば、NULLの場合とNULL以外の場合と統一的に同じSELECT文で扱えるのではないだろうか?という考え方です。

これは確かにその通りです。このような考え方でも今回のケースでは対応可能です。しかし、構文が大きく変わる、例えば、WHERE句の条件式の数が変わる、表名や列名が変わるといった場合は、動的SQLを使わなければ無理です。ですから、動的SQLでケースに応じたSQL構文でカーソル処理ができるようにマスターしておけば、複雑な状況でも柔軟に対応できます。

プログラミング以外で一点、補足ですが、もし上記のNVL関数を使ったSELECT文を使う時、パフォーマンスを向上させるために、DOCS表のOYA_DIR列に索引を作成するのであれば、普通の索引では効果がありません。以下のように、ファンクション索引を作成してください。

●普通の索引 (DOCS表のOYA_DIR列に対する索引)
CREATE INDEX 索引名 ON DOCS(OYA_DIR);
-- 上記のSELECT文はこの索引を使用しない。(パフォーマンスは改善されない)

●ファンクション索引(DOCS表のOYA_DIRを使ったNVL(OYA_DIR,0)ファンクションに対する索引)
CREATE INDEX 索引名 ON DOCS(NVL(OYA_DIR,0));
-- 上記のSELECT文はこの索引を使用する。(索引が使用されるので、パフォーマンスが改善される)

WHERE句の条件が単純に「列名 = 値」であれば、その列に普通の索引を作成して効果はありますが、WHERE句の条件が「関数(列名) = 値」である場合は、「列」ではなく、「関数(列名)」に対して索引を作成すると効果的です。これをファンクション索引といいます。

※索引使用についてはオプティマイザが総合的に判断しますので、索引があるからといって常に索引が使用されるとはかぎりません。

それでは今回はここまでにいたします。次回以降は、文書やディレクトリの更新、検索機能、ディレクトリ以下の複数選択処理、開閉可能フォルダ機能など、順次実装していきたいと考えています。

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

先頭へ戻る