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

前回は、データ登録のための、入力フォーム画面を作成したところまででしたね。今回は、フォームの送信先のデータ登録プロシージャを完成させていきましょう。
これでデータ登録の機能が完成することになります。

では、今回作成するプロシージャの名前とパラメータは前回の内容から以下の通りです。前回の入力フォームのソースコードを見ると確認できます。
あるいは、前回のプロシージャのFORMタグのACTION属性をGETにして（もしくはACTION属性を省略して)送信ボタンをクリックすると、URLにパラメータが付加されますので、それでパラメータ名を確認する方法も確実で良い方法だと思います。

今回作成するプロシージャの仕様は次のとおりです。（下記のパラメータをもとに、EMP2表に1行INSERTする)

一方、INSERTする表はEMP2表です。前回作成した通り、以下のような定義の表です。

SQL> conn scott/パスワード
接続されました。

では早速、リクエストパラメータをもとに、EMP2表に1行INSERTするプロシージャEMP2_INSERT_EXEを作成します。
以下のようにコーディングしてみました。

まず、リクエストパラメータは、プロシージャのパラメータに対応していて、常に文字型つまりVARCHAR2型で定義します。(ソースコード 2～5行目)
※リクエストパラメータがVARCHAR2以外の例外的なケースとしては、一つのリクエストパラメータ名で複数の値のとき、配列を使うケースがありますが、別の機会で説明します。

リクエストパラメータは常に文字型なので、場合によっては、データ型を正しく変換してからINSERTする必要があります。
例えば入社日がそうです。EMP2表の入社日列(HIREDATE)はDATE型ですから、DATE型に変換した値が必要です。
そこで、宣言部で、V_HIREDATEというDATE型の変数を宣言して(8行目)、そして実行部でTO_DATE関数を使ってDATE型に変換した値を変数に取得しているわけです。(15行目)　
このとき、ソースコードにあるように、その日付は、'YYMMDD' という書式である要がありますが、もし書式に合わずデータ型変換が失敗する場合は、例外処理部のOTHERSハンドラ(33～35行目)で、エラーメッセージを表示してくれます。

例えば、入力した入社日が、141032 なら、 (2014年10月32日　32日はありえない)以下のエラーメッセージが表示されます。

ORA-01847: day of month must be between 1 and last day of month

本来であれば、しかるべき例外ごとの例外ハンドラを設けて、それぞれでわかりやすいエラーメッセージを決めて表示をしたほうが良いです。
しかしそれが難しければ、最低でも OTHERSハンドラで、単純にエラーメッセージそのものの表示処理はしましょう。そうでないと、エラーのときに何も表示されず、状況が判断できないからです。

部門番号についても、表の列のデータ型はNUMBER型ですから、型変換が必要です。そこで、宣言部でNUMBER型の変数 V_DEPTNOを用意して(9行目)、その変数にパラメータ P_DEPTNOをTO_NUMBER関数でNUMBER型に変換して代入します。
ただし、P_DEPTNOパラメータの値は、前回、入力フォームで選択リストから選択した値です。そのリストの先頭の表示はハイフン「-」であり、その値は文字列で、'NULL'でしたね。そこで、選択リストからNULLが選択された場合の値は、文字列'NULL'ですが、TO_NUMBERで数字には変換できないので、例外的にその場合は、V_DEPTNO変数に直接NULL値を代入しています。(17-18行目)。それ以外は、部門番号の文字列をNUMBER型に変換して代入しているわけです(19-20行目)。

では次に話題を変えて、社員番号に注目してみましょう。
社員番号は主キーですから、入力フォーム画面から手入力することは通常ありません。前回、入力フォームでは社員番号の部分は「自動生成」ということで、入力は不可でした。したがってリクエストパラメータに社員番号はありません。

そこで社員番号は、プロシージャ内で生成しています。
あらかじめ申し上げておくと、今回、コーディングした方法は簡単ですが、あまり推奨できない方法です。詳細は下で触れています。まず、ソースコード13行目をご覧ください。すなわち、EMP2表の一番大きな社員番号に1を足したものを次の社員番号にしているわけです。つまり1ずつ、社員番号は増えていきます。わかりやすいですね。
NVL関数はEMP2表が空のときの対応です。
すなわちNULLを0に置き換えて1を足すことで、最初の社員番号は 1となります。もしNULLを0に置き換えないと、NULLに1を足しても結果はNULLとなってしまします。

既述のとおり、このような番号の生成については問題点があります。
それは、もし同時に二人の人がこのWEBアプリを実行したとすると、タイミングによっては同じ社員番号が生成されてしまいます。それを防ぐためには、12行目のSELECT文を排他的に実行します。そのためにはEMP2表に対して表ロック(LOCK TABLE)をかけてから処理する必要があります。
表全体をロックしますので、トランザクションが終了するまではINSERTだけでなく、UPDATE処理もロックされます。非常に頻繁に同時にINSERT、UPDATEが発生するデータに対してはお勧めできません。

ロックが不要で、同時登録・更新が損なわれない主キーの生成方法としては、順序オブジェクトを使用する方法が推奨です。
順序オブジェクトであれば、ロックによるパフォーマンス悪化がなく、コーディング的にも「順序名.NEXTVAL」が値そのものなので記述も簡単です。
問題点があるとすれば、順序の番号は連続番号を保障できない点です。つまり値が欠落することが常にあり得ます。
例えば、発番予定の順序の値は先にメモリにまとめてキャッシュされるので、LRUアルゴリズムでメモリアウトされる可能性があります。あるいは、トランザクションのロールバックにより表のデータは元の状態に戻っても、順序は元に戻らないといった状況です。
こういった「欠番」が生じることが許容できれば、順序オブジェクトによる主キーの生成はベストな方法です。

どうしても連続番号を保障したい場合の方法としては、発番のための表（発番表）を用意し、その表を使ってプログラム的に番号を生成する方法がよく使われます。
この方法であれば、データ登録の対象となる表全体に対するロックは不要で、発番表の該当する発番単位の行ロックだけですみます。連続番号を保障しつつ、同時実行性もある程度は保障できますね。（例えば INSERT と UPDATEは同時に可能です)　
詳しくはバックナンバー第19～20回で、連続性を保障した発番処理について解説していますのでご覧ください。

では、次に、「備考」に注目してみます。備考は、EMP2表のNOTE列であり、CLOB型の列ですね。
CLOB型の列は、カテゴリーとしてはLOB型(Large OBject型)と総称される列であり、表の1セルあたりに4GBの大量データを格納できます。
しかし、表セグメントにはそのデータを格納せず、表とは別の場所にデータを格納します。表に格納されているデータは「ロケータ」という小さな情報だけです。ロケータは、LOBデータの場所を示す情報であり、プログラム用語でいうと、ポインタに相当します。
このように、LOB列の実体はポインタなので、本来であれば、ポインタを取得してのプログラムアクセスということになります。
しかし、PL/SQLでは、CLOB列の実体がポインタであることを意識せずに、普通にSQL文によって、INSERT、UPDATE、DELETE、SELECTの対象とすることができます。ただしその場合は、LOB型の列値すべてを対象に変数に格納することになるので、PL/SQLの変数制限により、32767バイト(約32KB)に制限されてしまうわけです。

もしも、LOBロケータを取得してのプログラム的なアクセスですと、LOB値全体を変数に読み込む必要はなく、一部分だけの読み込みや一部分だけの更新、追加などができます。
PL/SQLでは、そのためのパッケージ(DBMS_LOBパッケージ)が用意されています。ただ、このパッケージは、アプリ側からの使用例を前提に解説しないと実感が湧かないと思います。いずれ機会をみて本メルマガでも解説したいと思います。

ということで、「備考」については、CLOB型ではありますが、ロケータを使わない普通のINSERT文によるデータ登録という簡単な形となっています。
以上で、INSERT文に必要なすべての列の値が揃い、最終的にINSERTを実行しています。(23～24行目)

INSERT文の次のIF文(25～31行目)は、SQL%ROWCOUNT(直近のDML文の処理行数)を使って、確実に1行登録されたかどうかの確認を行っています。1行の処理行数でない場合は、トランザクションをロールバックして異常である旨のメッセージを表示しています。(29～30行目)

このような処理行数の確認は、INSERT文よりも、特にUPDATEやDELETE文の場合に大事だと言えます。
UPDATEやDELETE文では通常、WHERE句の条件があります。例えば、主キーで行を選択した場合、処理される行数は常に1行のはずです。しかし存在しない主キーを指定した場合などは、処理行数は0行ですが、UPDATEやDELETEはエラーになりません。
このように、DML文(INSERT、UPDATE、DELETE)は処理行数に関係なく、結果が正常ステータスなので、誤った結果であるかどうかを判断するために場合により、プログラム的に処理行数を確認する必要があるわけです。

ちなみに、SELECT ～ INTO文に対して、このようなSQL%ROWCOUNTを使った処理行数の事後確認は意味がありません。なぜかというと、SELECT　～ INTO文は、1行以外の結果の場合は、そもそもエラー(0行　NO_DATA_FOUND例外, 複数行 TOO_MANY_ROWS例外)だからです。
エラーなく次のステップ(IF文)を実行できること自体が結果が1行である証です。よって処理行数の事後確認をするまでもありません。
（詳しくはバックナンバー第6回「SELECT INTO文の例外処理」をご覧ください）

それでは早速、先週の入力フォームで入力項目をセットして、送信ボタンをクリックし、今回のデータ登録プロシージャを実行し、社員のデータを登録してみましょう。前回の例では社員名を「鈴木一郎」にしましたが、EMP2表のENAME列のサイズが小さかったようなので、データ型をVARCHR2(10)からVARCHAR2(20)に拡大しておきます。

SQL> ALTER TABLE EMP2 MODIFY ENAME VARCHAR2(20);

表が変更されました。

データ型のサイズを大きくすることは常に可能ですが、小さくする場合はデータに影響があるとエラーになります。注意しましょう。

それでは、前回の入力フォームでデータ登録します。

バックナンバーの設定ができれていれば、ブラウザに以下のURLを入力します。

http://localhost:8080/dad/emp2_insert_form

ユーザ名とパスワードを求められるので、SCOTTユーザとパスワード(大文字・小文字区別）を入力します。
前回作成した、入力フォームが表示されます。

入力して送信ボタンを押すと・・・・

今回作成した、EMP2_INSERT_EXEプロシージャが実行され、きちんと登録できました。

本来であれば、登録完了メッセージをクリックすれば、そのレコードを表示するリンクを張りたいところです。
そのためには、EMP2表の行を表示するプロシージャを作成する必要があります。リンク機能はHTMLのたいへん便利な基本機能なので、また後日、説明します。

ここでは直接EMP2表をSELECTして、データが登録されているかどうか確認します。

上記の例では、今回登録された社員は社員番号 7938なので、そのデータを問い合わせます。

SQL> SET LONG 2000 -- CLOB型やLONG型の表示幅がデフォルト80なので、広げている(NOTE列のため)
SQL> SELECT * FROM EMP2 WHERE EMPNO = 7938;

いかがですか？確かに、鈴木一郎さんが登録されていますね。
NOTE列も、若干見にくいものの、画面に入力した通りの複数行のデータであることがわかります。

それでは、今回はここまでにします。
前回と今回はデータ登録(INSERT)をテーマにしました。登録されたデータは、WEBアプリで表示可能でなければなりませんね。データの表示についてはすでにバックナンバー第104回～106回にかけて説明済みです。がしかし、今回のように、CLOB列が含まれているときは少し注意点があります。
次回はそのことを説明します。そしてさらに次の回では、データ更新(UPDATE)について解説していこうと思っております。

では、引き続きご期待ください。