【FFRPG】第五回データベース – SQL（更新・削除・追加プログラム）

前回の復習

「第四回データベース – SQL（照会プログラム）」では、FFRPG のデータベース・プログラミングの手法として、データベース操作の共通言語 SQL を FFRPG に組み込んで実行する方法を解説しました。多くのサンプル・プログラムを通して、基本的な照会から、フィールドの多いテーブルに対するデータ構造を使用した効果的なアクセスまで、プログラムの作成を通して理解していただけたと思います。データベース処理は照会の他に、更新、削除および追加も必要になります。今回は、これらの処理を SQL で行う方法を前回同様、実際のプログラムを作りながら理解していきます。この記事で FFRPG と SQL でのデータベースの基本操作は一通り解説する予定です。最初に、SELECT で複数レコードが対象になる場合の処理について解説し、その後レコードの変更および削除および追加を行う方法を解説したいと思います。その前に、前回の演習問題の解説からはじめましょう！

演習問題解説（icafe008）

前回の演習問題はいかがでしたか？解説の前に少し思い出しておきましょう。演習問題は、サブ・プロシージャー getCustomerName を作成し、main プロシージャーからそれを呼び出すという問題でしたね。演習問題の main プロシージャーを見るとわかる通り、getCustomerName は引数として得意先コードを受け取り、得意先名を戻り値として返す必要があります。引数と戻り値の定義はサブ・プロシージャー内の dcl-pi ステートメントを使います。

では getCusotmerName サブ・プロシージャーの解答例を見てみましょう。

getCustomerName サブ・プロシージャーは、受け取った得意先コードに対応する得意先名を戻さなければいけません。引数として受け取る得意先コードは、dcl-pi ステートメント内で定義した customerCode 変数にセットされるので、これを select ステートメントで使用するために、where 内でホスト変数として指定しています。ホスト変数を使うことで getCustomerName サブ・プロシージャーは様々な得意先コードに対応した得意先名を戻すことができるようになります。

では演習問題全体の解答例を見てみましょう。皆さんが作成したコードと比べていかがですか？演習問題が終わっていない方は、ぜひこのコードを icafe008.sqlrpgle という名前で登録して、コンパイルおよび実行してみましょう。

icafe009 プログラム（複数レコードの処理）

前回解説した SQL を組み込んだ FFRPG プログラムはすべて、SELECT 文の WHERE で対象レコードが 1 件になるような条件指定がされていました。では、条件設定で対象となるレコードが複数になった場合の処理はどうすれば良いのでしょうか？この方法を icafe009 プログラムを通して学習していきましょう。複数レコードの処理を組込 SQL で行うには、いくつかの手順が必要になります。

まず、最初に行うのがカーソルの定義です。カーソルの定義は DECLARE CURSOR を使用します。カーソル内に含める複数レコードを選択する SELECT ステートメントをカーソル定義内に記述します。

この例における tecsmp_csr はカーソル名で、ユーザーが自由に記述します。このカーソル名を使用してこれ移行の操作が行われます。次に定義したカーソルをオープンします。

オープンしたカーソルから 1 レコードずつ読み取るのが FETCH です。

FETCH は最初にレコードを読み取るカーソル名を指定し、読み取った値を INTO の後のホスト変数にセットします。tecsmp_csr は TECSMP テーブルの全フィールドを含んでいる（SELECT *）ので、ホスト変数も全てのフィールドを含むデータ構造を指定しています。複数のレコードを含むカーソルから FETCH する場合は、FETCH の直後でレコードを取得できたかどうかを判断しなければなりません。SQL でこの判断を行うためには SQLSTATE フィールドを使用します。SQLSTATE には、SQL を実行するたびに正常、警告およびエラー状態を表す 5 桁の文字が自動的に保存されます。SQLSTATE フィールドは、プリコンパイラーが自動的に挿入する SQLCA データ構造内に定義済で、FFRPG プログラム内ではそのままの名称で参照することができます。SQLCA データ構造の詳細については以下を参照してください。

https://www.ibm.com/support/knowledgecenter/ja/ssw_ibm_i_73/rzajp/rzajpsqlcairpg.htm

SQLSTATE に設定される値で代表的なものは以下の通りです。

SQLSTATE に設定されるコードの詳細については以下を参照してください。

https://www.ibm.com/support/knowledgecenter/ja/ssw_ibm_i_73/rzala/rzalaccl.htm

FETCH を実行した結果、次のレコードが取得できなかった場合は SQLSTATE に ‘02000’ がセットされます。プログラムでこれを判断するには以下のようにコーディングします。

「if SQLSTATE <> ‘02000’;」は、「SQLSTATE 変数の値が ‘02000’ でなければ」という条件判断です。「<>」は左辺と右辺が「等しくない」ことを表します。等しい場合の条件を指定するときは「=」を使用します。今回のサンプルでは「レコードがない」状態を示す ‘02000’ を直接プログラム内に記述しましたが、 5 桁のコードをすべて覚えてプログラミングをするわけではないので、読みやすさの観点からあまりお薦めできません。その代りに、以下のようによく使用するコードに名前を付けてそれをプログラム内で使うと良いでしょう。

この例では、’02000′ という値に対して、dcl-c ステートメントで END_OF_FILE という名前を付けています。こうすることで、プログラム内で ‘02000’ と記述する場所では、END_OF_FILE と指定することができるようになります。 FETCH はカーソル内の「次のレコードを取得」します。処理は一度に 1 レコードのみなので、全てのレコードを処理するためには繰り返し処理が必要になります。繰り返し処理は dou、dow および for ステートメントがありますが、ここでは dou を使用して、「レコードが読めなくなるまで」繰り返すようにします。

すべてのレコードの処理が終わったら、カーソルはクローズします。それではプログラム全体を見てみましょう。

上記コードを icafe009.sqlrpgle というファイルで登録し、コンパイルおよび実行してみてください。今までのプログラム同様、dsply で得意先名を出力していますが、今回は TECSMP テーブルの全レコードが処理されるので、ジョブ・ログには 44 レコードの得意先名が表示されているはずです。

icafe010 プログラム（フィールド値の変更）

それでは次に、テーブルのフィールドの値を SQL で変更する方法を見ていきましょう。得意先コード ‘01010’ の得意先名「荒川薬局」を皆さんの名前に変えるプログラムを作ります。テーブルのレコードの値を更新するには UPDATE を使用します。

この SQL により、以下の更新が実行されます。

更新対象テーブルは TECSMP（UPDATE 直後に記述）
検索条件は、CSCSCD（得意先コード）が ‘01010’ のもの（WHERE）
更新するフィールドは CSCSKJ
更新する値はCSCSKJ（ホスト変数）にセットされている値

もし、検索条件に該当するレコードが存在しない場合は、SQLSTATE に ‘02000’ がセットされてレコードの更新は行われません。では、以下の SQL はどのレコードが更新されるでしょうか？

検索条件が指定されていないため、該当のレコードがないという判断になりそうですが、このケースは全てのレコードが対象となります。今回のテーブル TECSMP の 44 レコードすべてが、customer.CSCSKJ にセットされている値で一括更新されるので注意しましょう。 icafe010 のソース・コードは以下の通りです。

では上記コードを icafe010.sqlrpgle というファイルで登録し、コンパイルします。今回のプログラムはデータの更新なので、実行前後のデータの状態を確認してみましょう。

メニューのオプション 8「SQL プログラムの作成」を実行
メニューのオプション 9「データの照会」を実行し、得意先コード ‘01010’ の得意先名を確認
icafe010 をメニューのオプション２で実行
メニューのオプション 9「データの照会」を実行し、得意先コード ‘01010’ の得意先名が変わったか確認
変わっていない場合はジョブ・ログで SQLSTATE を確認

メニューのオプション 9 を実行すると以下の画面が表示されます。

実行されるコマンドは RUNQRY です。このコマンドのファイルに参照したいテーブル名を指定します。今回は TECSMP と指定してください。Enter キーでテーブルのレコード一覧が表示されます（以下は一部）。

得先コード ‘01010’ の得意先名をメモしてください。その後 Enter キーでメニューに戻ります。続いてメニューのオプション 2 で icafe010 を実行します。実行後、もう一度メニューのオプション 9 でデータの内容を確認してください。icafe010.sqlrpgle で指定した自分の名前でデータが更新されていますか？

もし更新されていなければジョブ・ログに出力された SQL エラーを確認してください。その後、コードを修正して再作成および実行してみてください。SQL ステートメントが正常に実行されると SQLSTATE = 00000 がジョブ・ログに表示されます。

icafe011 プログラム（レコードの削除）

今度はレコードを削除してみましょう。レコードを削除するには DELETE を使用します。

この SQL により、得意先コード ‘01010’ のレコードが削除されます。削除は永久的なもので、削除したレコードを復活させることはできないので注意しましょう。また、UPDATE と同様、where による条件が指定されていなければすべてのレコードが削除対象となるので注意してください。万が一全レコード削除してしまった場合は、メニューのオプション 10「TECSMPデータの初期化」を実行してください（コマンドのプロンプトは表示されますが、すべての項目は指定済なので、そのまま実行してください）。 icafe011 のソース・コードは以下の通りです。

icafe010 同様にプログラムをコンパイルし、メニューのオプション 9 を使用して得意先コード ‘01010’ のレコードが削除されたことを確認してください。

削除されなかった場合は、ジョブ・ログの SQLSTATE を確認してみましょう。

icafe012 プログラム（レコードの追加）

icafe010 でデータの変更、icafe011 でデータの削除を行いました。残るはデータの追加です。icafe012 プログラムを通して、先程削除したデータを再度追加してみましょう。レコードの追加には INSERT を使用します。

この SQL の指定内容は以下の通りです。

レコードを追加するテーブルは TECSMP（INTO）
レコードの全てのフィールドにセットする値をカンマで区切ったホスト変数で指定（VALUES）

TECSMP は 4 フィールドだけなので上記のように全てのフィールドを記述しても良いのですが、フィールドの数が多いとコーディングが大変になります。その場合はデータ構造名を指定することで記述を簡素化することも可能です。

icafe012 のソース・コードは以下の通りです。

では icafe012.sqlrpgle を Orion で登録し、コンパイルおよび実行してください。実行後、メニューのオプション 9 で ‘01010’ のレコードが追加されていることを確認してください。今回のプログラムでは、実行時エラーの可能性が２つあります。

すでに存在する得意先コードのレコードを追加する
得意先カナに 2 バイト文字を指定する

TECSMP テーブルは得意先コードがプライマリー・キーのため、同じ得意先コードが重複できないのは前回解説した通りです。このルールはどのようなインターフェースからの操作であっても必ずチェックされますので、今回の INSERT も同様にチェックされます。このエラーは、icafe012 を 2 回連続して実行した場合に発生します。重複エラーの SQLSTATE は ‘23505’（固有索引または固有制約で定められている制約に対する違反）です。あえてこのエラーを発生させてジョブ・ログで確認してみてください。もうひとつのエラーですが、TECSMP テーブルの得意先カナには半角文字しか入力できません。そのため、以下のようにダブルバイトの文字を入力しようとすると SQLSTATE が ‘22021’（文字がコード化文字セットの中にないか、または変換がサポートされない）のエラーで INSERT が失敗します。

時間があればこのエラーも意図的に発生させて、ジョブ・ログで確認してみましょう。

終わりに

いかがでしたか？最初にも書きましたが、前回と今回で FFRPG に組み込んだ SQL でデータベースを処理する基本的な方法はほぼ網羅できたと思います。もちろん、実際のプログラムではエラー処理を組み込んだり、SQL 固有の処理を組み込んだりとまだまだ多くの知識が必要になります。今回解説したカーソルを使用したプログラミングも色々なパターンが必要になってきますので、前回と今回解説したことを土台にして、より実践的なプログラミングにもチャレンジしていってください。次回もデータベース・プログラミングですが、今度は SQL から離れて、IBM i のネイティブ・アクセスの方法を解説していきます。組込 SQL 同様、次回のネイティブ・アクセスも RPG プログラミングに欠かせない知識となりますので楽しみに待っていてください。