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

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

第104回「WEBアプリ作成(2)(キーを指定して1レコードを表示)」

2014.09.11

こんにちは。インストラクターの蓑島です。
お盆休みや、業務多忙、体調不良などが重なり、ここ数週間ごぶさたしておりました。

前回は、OracleデータベースとPL/SQL言語を使って、WEBアプリケーションが作成できることを解説しました。
今回からは具体的な肉付けをしていきます。

今回のテーマは、「キーを指定して1レコードを表示する」です。

主キーで1レコード(行)を取得し、それをブラウザに表示するWEBアプリケーションを作ってみましょう。
大変基本的な機能ですね。

はじめに、WEBアプリケーションの3つの基本用語について解説します。
「リクエスト」と「レスポンス」、そして、「リクエストパラメータ」です。これらの用語はHTTPプロトコルの用語です。
それは・・・
HTTPプロトコルは、もともと、WEBアプリのための専用のプロトコルではなく、単純にインターネット上のHTMLドキュメント(静的なWEBページ)を、クライアントとWEBサーバーの間でやり取りする通信手順(プロトコル)です。その際、クライアントのWEBブラウザからWEBサーバーに対して、指定したWEBページを要求するメッセージを送信するわけですが、それがすなわち、リクエストです。
そして、そのリクエストの結果、クライアントへ戻るメッセージ全体(WEBページを含む)をレスポンスといいます。

WEBアプリケーションの場合は、要求されているものが静的なWEBページではなく、プログラム的に生成される動的なWEBページです。
そのプログラムがWEBアプリケーションそのものです。
そしてそれはプログラムですから、呼び出し時にパラメーターを指定できます。それらのパラメータをリクエストパラメータといい、WEBアプリに対するリクエストの中に含まれます。

PL/SQL Web Toolkitを使ったWEBアプリ開発においては、WEBアプリは、PL/SQLプロシージャ(普通はパッケージ化する)であり、リクエストパラメータは、そのプロシージャの仮パラメータです。
※仮パラメータという用語については、バックナンバー第17回 「ストアド・プロシージャ作成構文」を参照してください。

ではそういった用語の意味を押さえたうえで今回の具体例です。

今回は、SCOTTスキーマの社員表(EMP表)に対して、主キー(社員番号 EMPNO列)を指定して、1レコード(1人の社員情報)を取得しそれをWEBブラウザに表示するWEBアプリケーションのプロシージャを作成します。
プロシージャの名前を EMP_SHOW、主キーを指定するパラメータの名前をP_EMPNOとしました。
以下をご覧ください。
なお、SCOTTスキーマが存在していない方は、 ORACLE_HOME/rdbms/admin ディレクトリに、utlsampl.sql というスクリプトがあります。
これを実行すればSCOTTスキーマを作成できます。

では以下は、SCOTTユーザーによるプロシージャ作成の様子です。

SQL> SHOW USER
ユーザーは"SCOTT"です。
SQL> L
  1  create or replace
  2  PROCEDURE EMP_SHOW ( P_EMPNO IN VARCHAR2)
  3  IS
  4     V_EMPNO  NUMBER(4);
  5     REC   EMP%ROWTYPE;
  6     V_ERRM VARCHAR2(500);
  7  BEGIN
  8     -- パラメータの社員番号(VARCHR2)をNUMBER型に変換する
  9     V_EMPNO := TO_NUMBER(P_EMPNO);
10     SELECT * INTO REC FROM EMP WHERE EMPNO = V_EMPNO;
11     -- 以下は1レコード取得後の処理
12     HTP.P('<HTML>');
13     HTP.P('<HEAD><TITLE>社員レコード表示</TITLE></HEAD>');
14     HTP.P('<BODY>');
15     HTP.P('<H1>社員明細 </H1>');
16     HTP.P('<TABLE BORDER="1">');
17     HTP.P('<TR><TD>社員番号</TD><TD>' || TO_CHAR(REC.EMPNO) ||'</TD></TR>');
18     HTP.P('<TR><TD>社員名</TD><TD>' || REC.ENAME ||'</TD></TR>');
19     HTP.P('<TR><TD>職種</TD><TD>' || REC.JOB ||'</TD></TR>');
20     HTP.P('</TABLE>');
21     HTP.P('</BODY>');
22     HTP.P('</HTML>');
23  EXCEPTION
24     WHEN  NO_DATA_FOUND THEN
25           HTP.P('エラー: 社員番号 ' || P_EMPNO ||' は存在しません');
26     WHEN  OTHERS  THEN
27           V_ERRM  := SQLERRM;
28           HTP.P(V_ERRM);
29* END;
SQL> /

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

SQL>

解説します。

まず、主キーを指定してその1レコードを表示しますので、主キーをWEBアプリに対して指定しなければなりません。
それはつまり、主キーの値をWEBブラウザから、WEBアプリに送信するわけです(これを「リクエスト」というわけですね)。
リクエストするときに指定する情報を「リクエストパラメータ」といいます。

上記のソースコードでは、プロシージャ名(= WEBアプリ)が、EMP_SHOW であり、仮パラメータ名が P_EMPNOです。(2行目) 
一般にリクエストパラメータは、常に文字型なので、PL/SQLプロシージャの仮パラメータのデータ型も常に VARCHAR2 です。また、仮パラメータはサイズ指定ができないので、「VARCHAR2(サイズ)」ではなく、常に「VARCHAR2」で宣言されることにも留意してください。
(バックナンバー第17回 「ストアド・プロシージャ作成構文」参照)

この仮パラメータ P_EMPNOは、社員番号の意味で用いますが、実際にはEMP表の社員番号列(EMPNO列)は数値型(NUMBER(4))です。
よってここでは、数値型に変換した社員番号を格納する数値型の変数を別に宣言しました。すなわち、4行目の、V_EMPNO変数です。このV_EMPNO変数に対して実行部の最初のステップで、仮パラメータP_EMONOをNUMBER型に変換した値を代入します。(8-9行目)

そして、その数値型の社員番号( V_EMPNO変数 )を条件にして、社員表の1レコードを、SELECT INTO文で取得します。(10行目)
取得された1レコードは、RECという変数に格納されます。REC変数は5行目で宣言されています。
その宣言である5行目のEMP%ROWTYPEという記述は、EMP表の1レコードと同じ構造のレコード型変数の型を表しています。%ROWTYPEで宣言されたレコード変数は、SELECT * INTO ~の代入先として最適です。
(バックナンバー第12回 「レコード変数について」参照)

いずれにしろ、これでRECレコード変数に該当するEMP表の1行が格納されています。あとは、これを使ってブラウザ画面への表示を生成します。
つまりレスポンスを生成するわけです。

PL/SQLプロシージャから、レスポンスを生成するもっとも簡単な方法は、「HTP.P(~);」を用いることです。HTP.P(~)で指定された内容が、そのままレスポンスの出力となります。(12~22行目)

また、PL/SQLプロシージャからレスポンスを出力するときは、レスポンス全体をプロシージャから生成する必要があります。
レスポンスの内容は、HTMLドキュメントなので、HTMLドキュメント全体をすべてのHTMLタグを含めたそのままの形でPL/SQLプロシージャから生成する必要があります。よって、12行目のHTMLの開始タグから、22行目のHTMLの終了タグに至るまですべてHTMLドキュメント全体を生成しています。

ここでは、RECレコード変数に含まれるフィールドのうち、社員番号(EMPNO)と社員名(ENAME)と職種(JOB)について、HTMLのTABLEの行(<TR>~</TR>)として、それぞれ生成しています。
その際、REC.EMPNO、REC.ENAME、REC.JOB を文字列連結で、その行の中のセル(<TD>~</TD>)として埋め込んで生成しています。
(バックナンバー第93回 「文字列連結プログラミングのコツ」を参照)

また、一般にアプリではユーザーの想定される誤った処理に基づくエラーについては、ユーザーにエラーの原因を通知するようなレスポンスを生成します。
上記のコードの場合、パラメータに指定する社員番号がEMP表に存在しない場合は、10行目のSELECT INTO 文が0件となるので、エラーとなります。
SELECT INTO 文が0件となるときのエラーに対しては、「NO_DATA_FOUND」という例外名がついていますので、例外処理部の24-25行目のNO_DATA_FOUND例外ハンドラで、「エラー: 社員番号 ~ は存在しません」というエラー表示のレスポンスを生成しているわけです。これにより、ユーザーはエラーの原因を判断できます。

また、これ以外に想定できるエラーとしては、ユーザーが指定する社員場号に数字ではない文字がセットされるケースもあると思います。
その場合、9行目のTO_NUMBERファンクションがエラーになります。その場合のエラーには、VALUE_ERRORという事前定義例外名がついていますので、VALUE_ERROR例外ハンドラを設けることで対応できます。
上記のソースコードには、VALUE_ERROR例外ハンドラは設けていませんが、興味のある方は試してみてください。

また上記のソースコードでは、それ以外のエラーが発生した場合、26-27行目のOTHERSハンドラで、エラーメッセージ(SQLERRMファンクション)の値を、いったん文字型の変数(V_ERRM 6行目で宣言)を経由し、それをそのままレスポンスに出力する処理で対応しています。
これにより、たとえば、上述したVALUE_ERROR例外ハンドラが設けられていなくても、9行目のTO_NUMBERファンクションのコールが失敗すれば、OTHERSハンドラにより、「ORA-06502: PL/SQL: numeric or value error: character to number conversion error」とレスポンスが表示されます。
一般ユーザーに対するレスポンスとしては適切なメッセージではないと思いますが、皆さんが個人的に使うようなシステムであれば、問題解決に役立つ情報と思います。

いずれにしろ、なんらかのエラーが発生した場合、そのエラーに関するレスポンスを生成しない限りWEBブラウザにエラーに関する情報は表示されないので注意してください。

ではこれで、WEBアプリのプロシージャが完成しました。

後は、前回(第103回)解説した環境が整っていれば、すぐにでも実行できます。
その設定は、「Oracel XML DB HTTP リスナー」と、「DAD(Data Access Descriptor)」ですね。どちらも簡単にすぐに設定できます。設定されていない方は前回の内容をご覧ください。

前回の設定と同じだとすると、以下のようなURLをブラウザから、リクエストします。
これは、社員番号7934のレコードを表示するものです。

http://localhost:8080/dad/emp_show?p_empno=7934

ここで一つ補足すると、ブラウザのURLなどから、リクエストパラメータを指定する場合、以下の構文になります。

WEBアプリ名?パラメータ名=値

もしも、リクエストパラメータが複数の場合は、以下のように「&」で連結してください。
WEBアプリ名?パラメータ1=値&パラメータ2=値&パラメータ3=値
このとき、パラメータの指定順番は任意です。

上記のURLをリクエストすると、前回と同じように、認証が求められるので、データベースユーザー名 soctt と、パスワードを入力してください。
パスワードは大文字・小文字を区別しますので気を付けてください。
utlsampl.sqlでSCOTTスキーマを作成した場合、パスワードはデフォルトで大文字のTIGER になっていると思います。

認証もOKであれば、以下のレスポンスが表示されます。

それぞれ、レスポンスのどの部分がプロシージャの何行目のソースコードで生成されたものかをコメントしておきましたので全体の把握ができると思います。

参考までにこのレスポンスのソースそのものは以下の通りです。(ブラウザ上から右ボタンクリックで表示できます)

<HTML>
<HEAD><TITLE>社員レコード表示</TITLE></HEAD>
<BODY>
<H1>社員明細 </H1>
<TABLE BORDER="1">
<TR><TD>社員番号</TD><TD>7934</TD></TR>
<TR><TD>社員名</TD><TD>MILLER</TD></TR>
<TR><TD>職種</TD><TD>CLERK</TD></TR>
</TABLE>
</BODY>
</HTML>

上記のソースはすべて、PL/SQLプロシージャから生成されたものであることがわかります。

いかがでしたか、簡単なWEBアプリを作成する一連の手順を解説しました。

では、今回はここまでにしておきます。

今回は1レコードを表示するパターンでしたが、次回は条件などで該当する複数レコードを表示するパターンを行ってみたいと思います。
その後はレコード登録や、更新などテーマを広げて解説していきたいと思います。

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

先頭へ戻る