組み込みSQLを使用した行と列データの取り出し｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2009.09.07

組み込みSQLを使用した行と列データの取り出し

ジェフ・サザーランド著

SQLによるRPGアプリケーションの簡素化

RPGアプリケーションでSQLを使用するとプログラムをモダン化でき、また多くの場合コーディングも簡素化できます。本稿では組み込みSQLを使用して行(レコード)と列(フィールド)からデータを取り出す方法、およびどのような場合にどのSQL文を使用するのかについて説明します。本稿をお読みいただくと、すべての場合とはいきませんがほとんどの場合F仕様を使用しなくてもSQLでテーブル(ファイル)に簡単にアクセスできることがおわかりいただけるはずです。

SQLの用語では「ファイル」のことを「テーブル」、「レコード」のことを「行」、「フィールド」のことを「列」と使いますが、本稿では以後IBMのi/RPGの用語であるファイル、レコード、フィールドを使用します。ただしコード中のコメントではSQLの用語を使用しています。これはSQLの用語にもっと慣れていただくためです。本稿のトピックにフォーカスしたコードにするため、各文の後に入れるべき堅牢なエラー・チェック用の処理は記述していません。ただし、SQL文の実行後にチェックすべき基本的な処理は記述してあります。

本稿で使用するすべてのSQL文を組み込んだRPGプログラムを図-1に示します。このプログラムが使用するプライマリ・ファイルはQCUSTCDTライブラリにあるQIWSファイルです。このファイルはiシステム上にあり、本稿で紹介するサンプル・プログラムで容易にテストおよび試用が可能です。QIWSファイルとその構造を図-2に示します。

一時ファイルを作成してレコードを読み込む

図-1の呼び出しDからFまでは一般的なIBM iアプリケーションの実行方法を示しています。すなわち、一時ファイルを作成し、ファイルからのレコードを一時ファイルに読み込むというものです。IBM iアプリケーションのプログラマはCLプログラムの中でCPYF関数を使用して一時ファイルを読み込むことが多いようです。または一時ファイルをCLプログラム中で作成し、RPGプログラムを使用して一時ファイルにレコードを読み込むという方法をとっているようです。しかしこのタスクはRPGアプリケーション内でSQLを使用すればできてしまいます。

一時ファイルCUSTCDTtmpはまず呼び出しDのSQL DROP TABLE文で「ドロップ」または削除されます。次の文(呼び出しE)でファイルが再度作成されます。CREATE TABLE文でLIKEオプションを使用するとファイルCUSTCDTtmpは既存のファイルの構造に基づいて作成されます。一時テーブルが常に同じ場所に存在していることが確実ならば、CREATE TABLEの代わりに、既存のレコードを削除するのに使用できる別オプションは次のコードです。

Exec DELETE FROM CUSTCDTtmp;

呼び出しFの文はQCUSTCDTファイルからレコードの一部を一時ファイルCUSTCDTtmp中に挿入します。INSERT文のWHERE節中の置換変数が、利用可能なすべてのレコードから一部を選択したものを取得している点に注目してください。SQL INSERT文が実行されると、RPG変数として500に設定されているCredit_Limitの値を使用して、QCUSTCDT中のCdtLmtフィールドの値が500以上の場合に限ってレコードを挿入します。SQL文中でRPG変数を使用するときはその前にコロンをつけ、コンパイラがSQL文中でRPG変数と予約語あるいはフィールド名を区別できるようにします。INSERT文の実行が終わるとその結果一時ファイルにレコードが読み込まれ、以後処理に使用できるようになります。CUSTCTtmpに挿入されたレコードが図-3で反転表示された部分のエントリに示されています。(後述の「組み込みSQL INESRT文およびCREATE TABLE文を使用する際のヒント」ではこれらの文を使用する際のその他の識見が述べられています。)

INSERT文の実行後はSQLcodが検査され、INSERT文が成功裏に実行されたかが確認されます。SQLerrd配列の3番目の要素にはINSERT文からのエントリの数が格納されており、その値はNbr_of_custに保存されています。

1つのレコードを取り出す

RPGではCHAIN命令コードを使用してファイルから1つのレコードを取り出します。SQLでは図-1の呼び出しGのようにSELECT文で1つのレコードを取り出します。この文のinto :CUSTds部分に注目してください。内部データ構造にCHAINなどといった命令コードが格納されているRPGのネイティブI/Oとは違って、SQLの命令ではデータ構造が定義されていなければなりません。CUSTdsはD仕様(呼び出しB)で定義されているデータ構造で、SELECT文の実行結果が格納されています。このデータ構造には、使用するファイルと同じフィールド構造がなければなりません。CUSTdsのD仕様の定義でEXTNAMEオプションを使用すると既存のファイルに基づいてデータ構造を作成するのが容易になります。私の場合はデータ構造に対してQUALIFIEDキーワードを使用します。こうするとフィールド名がコード中で読みやすくなるからです。

呼び出しGのSELECT文では、文が成功裏に実行されるには1つのレコードだけを返すことしかできません。ここではユニークな顧客番号397267を使用してファイルから1つのレコードを取り出しています。この例では顧客番号をハードコードしましたが、以下のようにWHERE節中でRPGの変数を使えば良かったかもしれません。

Exec SQL SELECT * INTO :CUSTds
　 FROM QGPL/CUSTCDTtmp
　 WHERE Cusnum = :customer;

SELECT文の実行が完了すると、ファイルから1つのレコードがCUSTds構造に読み込まれます。このCUSTds構造はQUALIFIEDデータ構造ですから、このデータ構造の値は"structure.field name"というフォーマット・データ (例えばCUSTds.lstnam) で参照します。

1つのレコードからフィールドを取り出す

呼び出しHでは、SELECT文を使用してレコード構造全体ではなくいくつかのフィールドだけを取り出しています。このテクニックを使用することでデータ構造とファイルをマッチングさせる必要をなくしています。ファイルから読み込んだフィールドはSELECTの後に個々に並べられ、次にINTOに続いてRPGのフィールド名が並んでいます。SELECT文の場合と同様に、WHERE節を使用してレコードを1つだけ選択しなければなりません。customer、name、amt_dueはRPGのフィールドなので、呼び出しAのように宣言しておかなければなりません。

定義された数だけレコードを取り出す

取り出す必要のあるレコード数を正確に知っている場合もあります。たとえば、「トップ10」のリストを取り出すときは10個のレコードを取り出さなければならないとわかっています。定められた数のレコードを取り出すにはSQL FETCH文にROWSオプションをつけて使用します。

呼び出しIでは、SQL文がTopFiveCsrというカーソルを宣言しています。取り出されたレコードはBalDueフィールドに降順に並べられますので、一番残高の高いものが1番最初に来ます。SQLカーソルを使用するときはカーソルをオープンし(呼び出しJ)、この定義したカーソルTopFiveCsrを付けてFETCH文を使用します(呼び出しK)。FOR 5 ROWS節は最初の5個のレコードだけがCUSTarrデータ構造配列に取り出されるように指定しています。

呼び出しCではCUSTarrデータ構造を配列として定義していますが、これはFETCH FOR 5 ROWSを正しく動作させるための鍵となっています。EXTNAMEキーワードはQCUSTCDTを参照してCUSTarrデータ構造がFROM節にあるファイルと同じ構造をしていることを確認します。DIM(5)はCUSTarrをデータ構造配列として宣言します。ここでも私はQUALIFIEDキーワードを使用してデータ構造の個々のサブフィールドがコード中でどこにあるのかをわかりやすくしています。

FETCH文の動作が完了すると図-4に示した通り、CUSTarrデータ構造には5つのレコードが取り出され、残高(BalDue)の最も高い顧客がCustarrデータ構造の最初の配列要素となります。そしてカーソルをオープンしていたのであればそれを図-1の呼び出しLで示したようにクローズしなければなりません。

FETCHでレコードの集合を読み込む

RPGアプリケーションで一般的なもう一つの命令は、SETLLを使用してファイル・ポインターを正しい位置にセットした後、ループ構造内でレコード集合を読み込むというものです。各レコードを読み込むにはREAD命令コードまたはREADE命令コードを使用します。SQLを使用してレコードを取り込む処理を図-1の呼び出しMからPに示します。

呼び出しMでカーソルが宣言され、呼び出しNでオープンされています。そしてDoWループ内で呼び出しOのFETCH文が次のレコードをCUSTdsデータ構造に取り出します。毎回のFETCH文の後でSQLcodを使用してFetch Return Code (呼び出しPのFetchRC)の値を更新し、次にFetchRCを使用してレコードの終わりに到達したか、またはエラーコードが返ってきていないかを判断します。

呼び出しQではSQLを使用して現在のレコードを更新することもできることを示すために、UPDATE文を使用してBalDueフィールドを0に設定して、カーソルReadCsrによって定義されている現在のレコードを更新します。FetchRCを使用してFETCH文の実行結果を分析するのと同様に、UpdateRCを使用してUPDATE文の実行結果を分析しています。SQLcod自身の代わりに別の変数を使用している理由については、後述の「SQLcodの潜在的な『落とし穴』」を参照してください。

最後に、呼び出しRではカーソルがFetchループの外側でクローズされています。

柔軟性と機能性－手に入れてしまえばあなたのもの

ここで紹介した短いアプリケーションは、フィールドとレコードの基本的な取り出し操作をRPGアプリケーション中で使用するいろいろな方法を紹介しています。一時ファイルを使用しているのでレコード選択を修正することもできますし、更新ルーチンも修正でき、プログラム中のSQL文をくりかえし試しに実行させることができます。SQL文の使い方に慣れるにしたがって組み込みSQLをRPGアプリケーション中でもっと使うようになり、従来のRPGのI/O命令コードを置き換えていくようになるでしょう。

組み込みSQL INSERT文およびCREATE TABLE文を使用する際のヒント

一時ファイルCUSTCDTtmpの構造がプライマリ・ファイルQCUSTCDTの構造と正確に一致していない場合でも、INSERT文を使用して一時ファイルにレコードを追加することができます。たとえばCUSTCDTtmpにCustomer、State、CreditLimitの3つのフィールドしかない場合、INSERT文は次のようになります。

Exec SQL INSERT INTO CUSTCDTtmp
　　(Customer, State, CreditLimit)
　　SELECT Cusnum, State, Cdtlmt
　　FROM QCUSTCDT;

INSERTを使用する際のもう一つの有用なヒントはINSERT文中で定数が使えるということです。CUSTCDTtmpにStatusという文字フィールドがあり、これはFROMファイルにはないのですが、各挿入レコードの値をStatusフィールドのTEMPという値にしたい場合は次のようなINSERT文を使用します。

Exec SQL INSERT INTO CUSTCDTtmp
　　(Customer, State, CreditLimit, Status)
　　SELECT Cusnum, State, Cdtlmt, 'TEMP'
　　FROM QCUSTCDT;

CREATE TABLE文を使用してテーブルを作成し、そのテーブルにデータを読み込む方法があります。ただし、CREATE TABLE文はホスト変数を受け付けませんので、この方法はテーブルを作成して元のファイルのすべてのレコードを読み込む場合か、もしくは次の例のようにWHERE節中に値をハードコードさせる場合にしかうまく動作しません。

Exec SQL CREATE TABLE QGPL/CUSTCDTtmp AS
　　(SELECT * FROM QIWS/QCUSTCDT
　　WHERE CdtLmt > 500 )
　　WITH DATA;

このWHERE節のWITH DATA節とCdtLmt > 500の部分に注目してください。WITH DATA節を使用すると新しいテーブルを作成して元のテーブルからデータを読み込むことができます。CREATE TABLE文ではホスト変数を使用することはできませんので、500がハードコードされているCdtLmt > 500の部分により、実現したいことに対して制約が生じるかもしれません。しかし、ホスト変数を必要としないのであれば上記のCREATE TABLE . . . WITH DATAオプションによりSQL文を2つ書かずに1つで済みます。

SQLcodの潜在的な『落とし穴』

各組み込みSQL文は、同じSQLcaデータ構造を最後のSQL文が処理される際のフィードバックに利用しています。SQLcodはファイルの終端に到達したかどうか(SQLcod = 100)や、SQL文の実行時に何か問題が発生したか(SQLcod <> 0)を判断するのに頻繁に使用される変数です。したがって、DOWループのような制御ループにSQLcod を使って条件をつけます。たとえば次のようになります。

DoW SQLcod <> 0;
　　.. processes
Enddo;

ただし、次の例のように同じ制御ループの中に他のSQL文がある場合は、制御ループの変数であるSQLcodが、DoWのテスト箇所に戻ってくるまでに0以外に書き換えられている可能性があるので問題となることがあります。

DoW SQLcod <> 0;
　　Exec SQL UPDATE . . .
Enddo;

このような状況を解決する方法はいくつかありますが、私の場合は別の変数を用意してSQL文の結果を保存しておき、実行結果をそれぞれ別々に検査できるようにしています。この方法は各SQL文の後でSQLcodの値を見るだけで済むという方法に比べると不便ですが、デバッグやロギングする際には役立ちます。