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

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

第106回「WEBアプリ作成(4)(検索条件を指定して複数レコードを表示 2/2)」

2014.10.09

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

前回は、検索条件の入力フォームー画面を作成しました。
その画面では、検索キーワードを入力し、送信ボタンを押すとプロシージャに送信します。しかし、前回はそのプロシージャをまだ作成していなかったのでエラーとなったわけですね。

その検索条件のキーワードは「P_KEYWORD1」というリクエストパラメータ名で送信され、送信先のプロシージャは「emp_search_exe」という名前でした。

そこで、今回はその続きですが、emp_search_exe というプロシージャを実際に作成し、検索処理を実際に行ってみましょう。
それでは早速、そのようなEMP_SEARCH_EXEというプロシージャの例を作成しましたのでご覧下さい。

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

  1  create or replace
  2  PROCEDURE EMP_SEARCH_EXE ( P_KEYWORD1 IN VARCHAR2)
  3  IS
  4  /**************/
  5  /** 宣言部   **/
  6  /**************/
  7    V_SQLERRM   VARCHAR2(500);
  8    /*** 部門番号から部門名を返すファンクション  ***/
  9    FUNCTION  FNC_DEPT_NAME ( P_DEPTNO IN DEPT.DEPTNO%TYPE)
 10    RETURN  VARCHAR2
 11    IS
 12        V_DNAME  DEPT.DNAME%TYPE;
 13    BEGIN
 14        SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO = P_DEPTNO;
 15        RETURN  V_DNAME;
 16    EXCEPTION
 17        WHEN NO_DATA_FOUND THEN
 18             RETURN NULL;
 19    END  FNC_DEPT_NAME;
 20  BEGIN
 21  /**************/
 22  /** 実行部   **/
 23  /**************/
 24    HTP.P('<HTML>');
 25    HTP.P('<HEAD><TITLE>社員検索結果</TITLE></HEAD>');
 26    HTP.P('<BODY>');
 27    HTP.P('<H1>社員検索結果</H1>');
 28    HTP.P('<TABLE BORDER>');
 29    HTP.P('<TR BGCOLOR="SILVER"><TD>社員番号</TD><TD>社員名</TD><TD>入社日</TD><TD>部門名</TD></TR>');
 30    FOR REC IN (SELECT * FROM EMP
 31               WHERE TO_CHAR(EMPNO) || ENAME || TO_CHAR(HIREDATE,'YY/MM/DD')
 32                      LIKE '%' ||P_KEYWORD1 || '%'
 33               ORDER BY EMPNO)
 34    LOOP
 35           HTP.P( '<TR><TD>' || TO_CHAR(REC.EMPNO)                -- 社員番号(数値)
 36             ||  '</TD><TD>' || REC.ENAME                         -- 社員名(文字)
 37             ||  '</TD><TD>' || TO_CHAR(REC.HIREDATE,'YY/MM/DD')  -- 入社日(日付)
 38             ||  '</TD><TD>' || FNC_DEPT_NAME(REC.DEPTNO)     -- 部門名(外部キー⇒ファンクション)
 39             ||  '</TD></TR>');
 40    END LOOP;
 41    HTP.P('</TABLE>');
 42    HTP.P('<HR>');
 43    HTP.P('</BODY>');
 44    HTP.P('</HTML>');
 45  EXCEPTION
 46  /*****************/
 47  /** 例外処理部  **/
 48  /*****************/
 49    WHEN OTHERS THEN
 50       V_SQLERRM := SQLERRM;  -- エラーメッセージ取得
 51       HTP.P(V_SQLERRM);      -- それを表示
 52* END  EMP_SEARCH_EXE;
SQL> /

プロシージャが作成されました。

簡単に解説します。

EMP_SEARCH_EXEプロシージャは、P_KEYWORD1というリクエストパラメータで検索条件のキーワードを受け取ります。ですから、P_KEYWORD1という名前のパラメータが必要です。そしてリクエストパラメータのデータ型は常にVARCHAR2です。(2行目)

では、準備ということで宣言部に注目してください。
ここに部門番号から部門名を返すファンクションを定義しています。というのは、社員表(EMP表)を問い合わせた結果として、部門名を表示するつもりでいるのですが、部門名はEMP表には存在しません。
EMP表に存在するのは、部門番号(DEPTNO列)であり、部門名はありません。そこで部門名を取得する機能を得るために、部門名を返すファンクションを定義したわけです。(9~19行目)
このFNC_DEPT_NAMEファンクションはパラメータとして部門番号をもち、そのパラメータは部門表(DEPT表)の部門番号(DEPTNO列)と同じデータ型です。(9行目 P_DEPTNO IN DEPT.DEPTNO%TYPE) 
またこのファンクションは部門名を返すので、RETURNする値はVARCHAR2型です。(10行目) 
そして、パラメータの部門番号(P_DEPTNO)をもとに、DEPT表をSELECT~INTO文で問い合わせて、部門名(DNAME列)を変数(V_DNAME)に取得し、それをRETURNします。(14~15行目)
もし該当する部門が存在しなければ、SELECT~INTO文が0件ということで、NO_DATA_FOUND例外となるので、NULLを返します。
これによりたとえば、部門に所属しない社員があったときに部門名はNULLとなります。

部門名を取得するために、わざわざファンクションを用意するの?と思われた方がいると思います。ファンクションではなく、単に社員表(EMP)と部門表(DEPT)を結合した問い合わせを行うだけでいいのではないか?と。
もちろんその方法でも可能です。
しかし私は、こういった場合はファンクションでデータを取得する方法を用いることが多いです。
というのは、外部キーが多く、結合する表が多い場合、実際にたくさんの表を結合するとSELECT文が複雑になります。また、普通の結合だと、結合する相手の行が存在しなければ問い合わせされないので、それが不都合である場合は外部結合を用いなければなりません。
例えば、部門に所属しない社員(DEPTNO列がNULLの社員)も対象にしたければ外部結合が必要です。
またこの目的のファンクションは参照される表からみれば主キーをもとに1行を返すファンクションです。したがって、主キーにもとづく索引の存在により最速なので、パフォーマンス上も問題はありません。
このような工夫によって問い合わせは単純なSELECT文の形に保つことができます。
(参照 バックナンバー 第82回 コラム「複雑で長すぎるSELECT文」)

宣言部の説明がすこし長くなりましたが、次に実行部(20~43行目)をご覧ください。

実行部では検索結果をテーブル形式に表示するため、28行目でHTMLのTABLEタグを出力し、29行目でテーブルの見出し行の出力を行っています。

ポイントは、その後の30~40行目でカーソルFORループ文により、EMP表から1行づつ取り出し、それををHTMLテーブルの各行(TRタグ)の各セル(TDタグ)に文字列連結して1行('~') を生成し、出力(HTMP.P(~) )していることです。
TABLEタグや、TRタグ、TDタグなど若干のHTMLドキュメントの知識が必要ですが、難しいものではないので挑戦してみましょう。

EMP表にはたくさんの列がありますが、パターンを網羅できるので、今回は社員番号と、社員名と、入社日と、部門名のみを表示することにしました。

まず社員番号ですが、カーソルFORループ文のレコード変数名が「REC」(30行目)なので、ループの中では、「REC.EMPNO」で参照することができます。
REC.EMPNO列はNUMBER型の列です。しかし文字列連結に用いるにはこれを文字型に変換しなければいけません。
よって、TO_CHAR(REC.EMPNO) により文字型に変換して文字列連結(||)しているわけです。(35行目)

同様に、入社日はREC.HIREDATEですが、日付型(DATE型)なので、これも文字型に変換する必要があります。よって、TO_CHAR(REC.HIREDATE,'YY/MM/DD') というように文字型に変換して文字列連結しています。

社員名(REC.ENAME)についてはもともと文字型なので、そのまま文字列連結しています。(37行目)

また部門名については部門番号(REC.DEPTNO)をもとに、宣言部で宣言しているファンクションで取得します。ファンクションなのでその記述自体が値を意味します。その値は文字型です(10行目)。
ということで「FNC_DEPT_NAME(REC.DEPTNO)」をそのまま文字列連結してるのです。

そしてループなので1行1行同じように処理します。わかりやすですね。

なお、参考までに文字列連結は、左から右へ順番に文字列を連結するようなコーディングをしていると、複雑なパターンになると必ず間違えます。
おすすめなのは、完成版の文字列を用意して、その文字列の中で動的な部分を周りの文字列から切り離し、その部分をそのまま変数に置き換える方法です。詳しくはバックナンバー第93回「文字列連結プログラミングのコツ」を参照してください。
ただ今回は該当しませんが、動的SQLなど、SQL文そのものを文字列連結で生成すると、セキュリティ上の問題点もあります。
その場合はバックナンバー第94回「文字列連結の注意点(SQL文において バインド変数の使用)」も参照してください。

さて肝心の検索条件の使い方の部分ですが、それはカーソルFORループ文のSELECT文のWHERE句です。(31~32行目)

見ていただければわかると思いますが、社員番号(EMPNO)、社員名(ENMAE)、入社日(HIREDATE)を文字列連結した 「TO_CHAR(EMPNO) || ENAME || TO_CHAR(HIREDATE,'YY/MM/DD')」(31行目)が、リクエストパラメータ(P_KEYWORD1)の値を含むという条件つまり、「 LIKE '%' ||P_KEYWORD1 || '%'」という条件(32行目)であることです。
「LIKE '%キーワード%'」がキーワードを含むという比較条件であることをご存じの方は多いと思いますが、まさにそのキーワード部分をパラメータにした条件です。
これで、指定した検索キーワードが、社員番号や、社員名、入社日に含まれれば検索されます。

なお最後に例外処理部(45~51行目)ですが、ご覧のようなOTHERSハンドラ(49~51行目)は必ず記述しておいてください。
というのは、このような記述を書いておかないと、オラクルのシステムエラーが起きてもレスポンスのページの中にオラクルのエラーメッセージは表示されません。 その結果エラーの分析ができなくなるため記述をおすすめします。

では早速、検索してみましょう。

前回の入力フォームを呼び出します。

http://localhost:8080/dad/emp_search_form
そして検索条件に、「78」と入力して送信ボタンをクリックします。

そうすると、以下のような結果となりました。


そうすると、ご覧のように、社員番号、社員名、入社日のどれかに「78」という文字が含まれる結果が表示されましたね。
このパターンを応用して、より複雑なコーディングにトライしてみてください。

それでは今回はここまでにします。次回はデータ登録のパターンをご紹介したいと思います。

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

先頭へ戻る