記事投稿日：2021年6月14日
本記事はiWorldコラム「シリーズ：押さえておきたい！RPGの新機能の【第25回】RPGをストアドプロシージャとして再利用する」の続編です。今回は既存RPGプログラムを改造して複数のレコード（結果セット）を返すストアドプロシージャを作る方法を解説しています。いささか古い記事ではありますが、今回の解説にあるような複雑な処理を行った複数の結果を一度に返すストアドプロシージャは、より幅広いアプリケーションへの応用が期待できます。（編集部）
SQLカーソルを使って結果セットを返す。RPGに対するV7.1サポートの詳細
04/18/2012
ジョン・パリス＆スーザン・ガントナー
前回の記事（【第25回】RPGをストアドプロシージャとして再利用する）で、私達はRPGプログラムから簡単なストアドプロシージャを作りました。そのケースでは「通常の」パラメータを使って結果を返しました。ストアドプロシージャの優れた機能の1つは、結果セット（つまり、一種の「サブファイル的」項目リスト）も呼び出し元に返すことができるということです。この記事では、その選択肢を詳しく調べると共に、RPGがストアドプロシージャから結果セットを受け取って処理できるようにするV7.1 の新機能についても述べるつもりです。
前回の記事の例では、ストアドプロシージャは顧客番号を入力パラメータとして受け取り、その顧客に関する幾つかの詳細事項を提供することで、特定の顧客に関する情報を返しました。しかし、もし１つの州内の全顧客リストを情報として提供する必要があるとしたらどうでしょう？その場合、ストアドプロシージャはSQL文で呼び出されるので、この情報を提供する最善の方法は、あたかもデータベースが直接その結果セットを返したかのように呼び出し元プログラムが処理できるSQLの結果セットを返すことでしょう。
RPGでストアドプロシージャを書いた場合、結果セットを作成して返す方法は2通りあります。SQLカーソルを作成して直接それを返すか、RPGのデータ構造（DS配列つまり多重オカレンスDS）内に結果セットを構築してそれを返すことができます。私達は両方の選択肢を調べるつもりです。
結果セットを構築するのにどちらの方法を選択するにせよ、RPGでSQLを使う必要があるでしょう。なぜなら、RPGプログラムから結果セットを返す唯一の方法は、“SET RESULT SETS …”というSQL文を使うことだからです。ですから、ソースメンバータイプをSQLRPGLE（または、残念ながら何らかの理由で旧式のRPG様式を使わなければならない場合にはSQLRPG）にし、ストアドプロシージャにしようとしているプログラムをコンパイルするのにCRTSQLRPGI（または、CRTSQLRPG）コンパイルコマンドを使いたいと思うでしょう。
SQLカーソルを使って結果セットを返す
SQLカーソルをストアドプロシージャの結果セットとして返すことにした場合、SQLカーソルの宣言方法と使い方を知っていれば、やることはとても簡単です。もし知らなければ、このテーマについての私達の以前の記事（”Bringing the Power of SQL to Your RPG Program”）（訳注）を見てください。
訳注：残念ながらこの記事は既にWebから削除されており、参照できません。
このストアドプロシージャと、SQLカーソルを使ってデータ処理をする通常のRPGプログラムとの間には２つの大きな違いがあります。最も顕著に違うのは、ストアドプロシージャの方はカーソルを使ったデータ処理をまったく行わないことです。単にカーソルを宣言してオープンしただけで、呼び出し元にオープンしたカーソルを返します。言い換えると、いかなるカーソルのフェッチやクローズもストアドプロシージャの中で行うべきではありません。なぜなら、呼び出し元にカーソルを返したときに、呼び出し元のプログラムがフェッチとクローズを行うからです。
もう1つの要求は、先に述べたSET RESULT SETS文です。これは、この後に出てくる例題の<A>で示されています。RESULT SETSの後に「Cursor」(カーソル)を指定しているという事実は、SQLカーソルを返す予定であることを意味しています。
ですから、入力パラメータとして州の値を受け取り、その州内の全顧客リストを内に含むSQLカーソル結果セットを返す必要のあるストアドプロシージャとして動作する非常に単純なRPGプログラムは、次の様なものになるでしょう。
D CustRsCSR PI D State 2A /FREE EXEC SQL Declare CustCsr cursor for SELECT CustNo, Name, City, State FROM Customers WHERE State = :State ORDER BY Name; EXEC SQL Open CustCsr; <A> EXEC SQL <A> SET RESULT SETS Cursor CustCsr; *InLR = *On; Return; |
明らかにSQL文の他にいくらかの追加ロジックが必要かもしれませんが、見てのとおり、SQL結果セットを返すのはとても簡単です。プロシージャインターフェース（または、*ENTRY PLIST）は、返される結果セットに関して何も宣言せず、単にもっと「普通の」タイプのパラメータを宣言していることに注意してください。
配列を使って結果セットを返す
今度はRPG内の結果セットに対するもう一方の選択肢を見てみましょう。返されるデータを更に揉んだり、編集したりするためにSQLカーソルの外部にいくつかの追加ロジックを使う必要があるような状況では、これはより良い代替策かもしれません。あるいは、現在既に「ネイティブな」I/Oを使ってサブファイルを満たす幾つかのRPGプログラムがあり、サブファイルの代りにDS配列をその情報で満たすようにそのプログラムに比較的小さな変更を加えて、このプログラムを再利用したいかもしれません。
SQLはこれを配列結果セットと呼びますが、もちろんそれは実質的に多重オカレンスDSつまり（圧倒的に私達の好きなやり方である）DS配列というデータ構造です。DSを満たすデータを抽出する方法がどのようなものであるかは問いません。DSまたはその一部を満たすためにSQL、多分SQLカーソルからの複数行フェッチ、を使うことすら選択するかもしれません。
F仕様書で宣言したテーブルからデータを読み込み、結果セットを返すためのDS配列にそれを入れる非常に簡単なプログラムは、下記のようなものになるでしょう。このプログラムを観察しながら、幾つかの詳細事項に話題を集中させます。
FCustomers2IF E K DISK
D CustRSCSR PR ExtPgm('CUSTRSARR')
D StateIn 2A
D CustRSCSR PI
D StateIn 2A
D Counter S 5P 0
<C>D CustomersDS DS LikeRec(Custrec)
<D>D ReturnDta DS Dim(99) Qualified
D CustNo Like(CustNo)
D Name Like(Name)
D City Like(City)
D State Like(State)
/FREE
Counter = 0;
DoU ( Counter = 99 ) or %Eof(Customers);
<E> Read Customers2 CustomersDS;
If %Eof(Customers2);
Leave;
EndIf;
<F> If CustomersDS.State = StateIn;
Counter += 1;
<G> Eval-Corr ReturnDta(Counter) = CustomersDS;
EndIf;
EndDo;
<H> Exec SQL
<H> SET RESULT SETS Array :ReturnDta FOR :Counter ROWS;
*InLR = *On;
Return;
|
RPGの幾つかの新しいデータ構造定義とI/O機能の経験があまりない場合に備え、それらの幾つかの詳細について取り上げようと思います。<C>でLIKERECキーワードを使ってデータ構造を作成しました。このようにして作成されたデータ構造は、常に暗黙的に修飾されていることを覚えておくことが重要です。これが<F>でCustomersDS.Stateを参照している理由です。<E>の読み込み操作で使用するための適当なDSを用意するためにそのDSを作成しました。
読み込み操作でDS名を使うということは、行（別名、レコード）からのデータがプログラムの典型的なI仕様書のフィールドではなく、直接DSに入れられることを意味します。DSへデータを読み込ませたかった理由は、１つのDSから他のDSへデータを取得するために、<G>で新しいEval-Corr（Eval Corresponding）操作を使ってRPGプログラムを簡素化できるようにするためです。この命令は、同じ名前を付けられたサブフィールドの内容を1つのDSから他のDSに移動します。結果セットとして返されるデータ構造は、<D>でQualifiedキーワードを使用する必要のあるデータ構造配列として定義されています。
最後に、<H>で前の例と同じSET RESULT SETS文を使用しています。ただし、DS配列を使っていることを明記するために「Cursor」（カーソル）の代りに「Array」（配列）を使っている点が異なります。もし前の例でカーソルの代わりに多重オカレンスDSを使ったなら、同じ文を使っていたでしょう。
ストアドプロシージャを作る
呼び出し元に結果セットを満たして返すためにどちらの方法を使おうが、ちょうど前回簡単な例で行った様に、RPGプログラムが書かれた後にデータベースに対してストアドプロシージャを定義する必要があります。このとき、2つの重要な違いについて注意しなければなりません。
第1に、ストアドプロシージャが結果セットを返すことを指定する必要があります。第2に、それがSQLを使用するということを指定する必要があります。ストアドプロシージャ用のプログラムがSQLを使用する場合は、プロシージャを作る際に常にそのことを宣言しなければなりません。そして、SQLが使用される範囲を宣言しなければなりません。選択肢は以下の通りです。
- No SQL – これは前回、省略時の値として使用したものです
- Contains SQL – これは非常に限られたSQL文だけが使えます
- Reads SQL data – SELECTが使えますが、INSERT、UPDATE、DELETEは使えません
- Modifies SQL data – SQL文の任意の組み合わせが使えます
前回の記事ではこの選択肢について述べませんでしたが、ストアドプロシージャとして使用されているRPGプログラムがSQLを含んでいるなら、仮に結果セットを返さないとしてもこれらの選択肢の1つを指定する必要があります。しかし、結果セットを返すには常にSQLを使用しますから、単にSET RESULT SETS文を使うだけだとしても、これらの選択肢の１つが指定されなければなりません。
ここに示されたプログラムのいずれかからストアドプロシージャを作るために、SQL内で次のような文を使用することができます。
CREATE PROCEDURE CustomersByState
( IN State CHAR(2) )
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
READS SQL DATA
EXTERNAL NAME MYLIB/CUSTRSARR
PARAMETER STYLE GENERAL ;
|
これらの例では、入力パラメータがただ1つで出力パラメータが無いケースを扱っていますが、（前回の記事で示したように）簡単なパラメータ渡しと返される結果セットを容易に混ぜ合わせることができます。また、”SET RESULT SETS Cursor InvCsr, Cusor InvDetailCsr;”のように複数の結果セットを返すこともできます。
ストアドプロシージャをテストする
前回の記事で、ナビゲーターのSQLスクリプト実行ウインドウから、ストアドプロシージャを呼び出すことでそのテストができると述べました。これは、少なくともストアドプロシージャを最初にテストする方法としては依然として素晴らしいやり方です。また、ストアドプロシージャを呼び出す一種のテスト装置としてRPGプログラムを書くことができるとも述べました。しかし、ストアドプロシージャが結果セットを返すとき、V7.1以降を使うまでは（あるいは、使わない限り）RPGプログラムからそのテストを行えません。なぜなら、V7.1以前では結果セットをRPGプログラムに返せないからです。そのやり方でストアドプロシージャのテストをしようと決めた場合に備え、V7.1での新しい機能がどのようなものかを説明しようと思います。
RPGプログラム中でストアドプロシージャから結果セットを受け取って処理する機能は、幾つかの新しいRPGおよびSQL構文を使用する必要があります。まず、下記の<I>で示されるRESULT_SET_LOCATORというD仕様書項目を宣言する必要があります。次に、（<J>で示される）ストアドプロシージャ呼び出しの後、そのロケータを<K>で示すようにストアドプロシージャと関連付ける必要があります。それから、<L>に示すように、プログラム中のSQLカーソルをそのロケータに割り振ります。
これらすべてのステップを実施した後、SQLカーソルを使った通常の処理と同様にカーソルのフェッチ、クローズを行います。これら<I>から<L>までのステップは、カーソルの宣言およびオープンの代りをします。プログラムの残りの部分は、SQLカーソルを通常通り処理します。RPGプログラムの中で結果セットを処理するために、SQLを使わなければならないことに注意してください。このことは多分驚くべきことではないでしょう。なぜなら、ストアドプロシージャを呼び出すためにもSQLを使う必要があるからです。
<I>D CustResultSet S SQLType(RESULT_SET_LOCATOR)
D CustData DS
D CustNo 5A
D Name 30A
D City 24A
D State 2A
D InputState s 2a
/free
dsply 'Enter State:' ' ' InputState;
<J> Exec SQL
<J> Call CustomersByState( :InputState );
<K> Exec SQL
<K> Associate Result Set Locator (:CustResultSet)
<K> with Procedure CustomersByState;
<L> Exec SQL
<L> Allocate C1 Cursor for Result Set :CustResultSet;
Dow SQLCode <> 100;
Exec SQL
fetch next from C1 into :CustData;
If SQLCode = 0;
Counter += 1;
Dsply ('Customer ' + %Char(Counter));
Dsply (CustNo + ' ' + %Subst(Name:1:20) + City + State) ;
EndIf;
EndDo;
Dsply ('Total for ' + InputState + ' ' + %Char(Counter));
Exec SQL
Close C1;
*inLR = *on;
Return;
|
ここまで、ナビゲーターおよびRPGプログラム（ただし、V7.1以降を使用していることを想定）からどのようにストアドプロシージャをテストすることができるかを述べてきました。多くのお客様が、IBM iまたは別のプラットフォーム上で実行されるJavaアプリケーションから呼び出されるストアドプロシージャを書いています。筆者自身はJavaの専門家ではありませんが、前回の記事を書いた後にJava専門家である友人のグレッグ・ヘルトンから連絡をもらいました。親切にも彼は、テスト用にIBM iのストアドプロシージャを呼び出すためのJavaを生成する幾つかのプログラムを提示している彼のブログ記事へのリンクを申し出てくれました。筆者はまだそれを試す機会を得ていませんが、Javaからストアドプロシージャが起動されるのを見てみたければ遠慮なく試してください。
これからが本番
当記事では、RPGプログラムを使ってストアドプロシージャを作る入門編をお届けしました。もちろん、これら前回と今回の2つの記事で時間を割いたよりも詳細な話があります。後日の記事で、異なるタイプのパラメータスタイルや、ここで使用した一般的なスタイルとは違うものを使いたいと思うかもしれませんが、その方法と理由などこれらの詳細の幾つかについて取り上げる予定です。それまでの間、ご自身のストアドプロシージャ作成に幸あらんことを！
