AS400/IBMi SQLRPGの書き方 フリーフォームRPG

IT関連
2021.06.04

書き方といって偉そうに投稿できるものではありませんが、文献が少ないと思うのでご参考になれば幸いです。

尚、これは自分の備忘録なので、間違いがあればご容赦を。

SQLRPGをなぜ使ったのか

例えば在庫照会のプログラムを作るとします。

商品情報が入った「商品マスタ」があり、在庫数が格納された「在庫ファイル」があるとします。

在庫照会なのでもちろんＭ「在庫ファイル」をメインでREADします。

その中で「商品マスタ」をCHAINもしくはJOINしレコードを完成させます。

なぜJOINするのか？商品名やサイズは「商品マスタ」にしか無いからです。

正規化するのが基本なので当たり前ですよね。

ところが今回、「商品マスタ」にある某カラム順に並べて表示してほしいという要望がありました。

それが事の発端です。

ちょっと何言っているかわからないかもしれませんが、あるんですそういう事例が。

（先人が作ったファイルレイアウトの致命的なミスかもしれません）

しかしこれ、CHAINで解決できますか？論理ファイルで解決できますか？OPNQRYFで解決できますか。

できないのです（たぶん）。論理ファイルでもJOINを使うことができますが、

論理ファイルのキーにはプライマリーファイルのフィールドしか指定できない。

という決まりがあるからです。IBMiのサイトに書かれてありました。

しかし、SQLを使えばいたって簡単。このように書くだけ。SQLを知っている方はなんとなく理解できると思います。

SELECT HOGE...(略) FROM ZAIKO A 
LEFT JOIN SYOHIN B 
ON A.HOGE1 = B.HOGE1 ..(略) 
ORDER BY A.HOGE1,A,HOGE2,B.HOGE1,A.HOGE3;

ポイントはORDERBYの中にJOINされる側のカラムが入っていることです。

論理ファイルでもJOINでもOPNQRYFでもそうですが、AS400の場合は並び順を変えようとしたら共にキーも変わってしまうのです。

これが大変です。

ゴチャゴチャ書きましたが、簡単に言うとサクっと並び順を変えて読みたいということです。

それをOPNQRYFでやろうとしたのがこちらの記事です。結局ダメでしたが参考までに。

AS400/IBMi OPNQRYFでJOINを使ってみる
前置き 私が思い出すための備忘録なので間違いがあるかもです。 逆にこういう使い方があるよというのがあれば教えてほしいです。 やりたいこと SYOHINという商品マスタがあり、IDとしてDAI/CYU/RENという３つのフィールドをキーとして...
qryheavy.com
2019.04.22

サンプルコード

見た方が早いのでいきなりサンプルコードです。

処理内容は在庫ファイルを商品マスタと結合して読んで、サブファイルに書き込んで表示します。

解説は後述します。

ファイル定義部分です。２つのファイルと画面ファイルの定義をしています。

**FREE CTL-OPT DATEDIT(*YMD); //いわゆるYの定義
DCL-F ZAIKO DISK KEYED USAGE(*INPUT); //在庫ファイル
DCL-F SYOHIN DISK KEYED USAGE(*INPUT); //商品マスタ
DCL-F DSPZAI WORKSTN USAGE(*INPUT : *OUTPUT) SFILE(SFLREC : SFLRRN);

定義部分です。END_OF_FILEという定数を定義します。後述します。

// 02000はSQLのEOFを表わします
DCL-C END_OF_FILE CONST('02000');

同じく定義です。SQLのSELECTで定義したものを入れる変数のようなものを作ります。これを定義しないと落ちます。

//メインREAD用SQLワーク
DCL-DS ZWORK;
  MISE CHAR(5);  //店コード
  CODEA CHAR(3);  //品種A
  CODEB CHAR(3);  //品種B
  CODEC CHAR(3);  //品種C
  NAME CHAR(100); //商品名
  SURYO PACKED(6 : 0); //在庫数
  SIIRE PACKED(6 : 0); //仕入数
END-DS;

メインロジックです。

//----------------------------- 
//サブファイルクリア
//-----------------------------
*IN51 = *ON; //SFLCLR
WRITE SFLCTL;
*IN51 = *OFF; //SFLCLR

SFLRRN = 1; //SFLNO

//----------------------------- 
//ＳＱＬ宣言
//----------------------------- 

//カーソル定義とSQL文
EXEC SQL DECLARE ZAIKO_CSR CURSOR FOR
SELECT MISE,CODEA,CODEB,CODEC,NAME,SURYO,SIIRE
    FROM ZAIKO A
      LEFT JOIN SYOHIN B
        ON A.CODEA = B.CODEA AND A.CODEB = B.CODEB AND A.CODEC = B.CODEC
           ORDER BY A.MISE,A.CODEA,A.CODEB,B.HOGE,A.CODEC;

//----------------------------- 
//ＳＱＬによるREAD
//----------------------------- 

//カーソルオープン
EXEC SQL OPEN ZAIKO_CSR;

  // SQLのDO_UNTIL
  DOU SQLSTATE = END_OF_FILE; //EOL_OF_FILEは02000が入っている

    // SQLによるREAD
    EXEC SQL FETCH ZAIKO_CSR INTO :ZWORK;

    // SQLのEOFをチェック
    IF SQLSTATE <> END_OF_FILE;

       //サブファイルに書き込む
       DSP1 = MISE;  //店コード
       DSP2 = CODEA; //品種A 
       DSP3 = CODEB; //品種B 
       DSP4 = CODEC; //品種C 
       DSP5 = NAME;  //商品名 
       DSP6 = SURYO; //在庫数 
       DSP7 = SIIRE; //仕入数
       
       WRITE SFLREC; //書き込み
       SFLRRN += 1;

    ENDIF;

  ENDDO;

//----------------------------- 
//ＳＱＬ終了
//----------------------------- 

//カーソルクローズ
EXEC SQL CLOSE ZAIKO_CSR;


//-----------------------------
//画面表示
//-----------------------------
*IN50 = *ON; //SFLDSP & SFLDSPCTL
EXFMT SFLCTL;　//画面表示
*IN50 = *OFF; //SFLDSP & SFLDSPCTL

SELECT;

  //-----------------------------
  // F1で終了
  //-----------------------------
  WHEN *IN01;

      *INLR = *ON;
      RETURN;

  //-----------------------------
  //実行キー
  //-----------------------------
  OTHER;

  //適当な処理をどうぞ・・

ENDSL;

解説

ざっとこんな感じです。簡単に解説します。

１．EOL判定のための定数

DCL-C END_OF_FILE CONST('02000');

EOLの判定についてですが、SQLで読む場合はREADの結果標識がありません。

IBMiのDB2に限らずOracleやMySQLでも同じですが、DBにSQLで命令したら、結果の状態を表わすリターンコードというのが返ってくるのでそれを使います。

正常終了は「00000」ですが、レコードが無い状態は「02000」というコードが返ってきます。

このリターンコードが02000かどうかを、IF文で確認することでEOFの判定を行います。

リターンコードはSQLSTATEという名前の変数で、これはユーザーが定義する必要はありませんが、それに対応する「02000」を定数で定義しておきます。

今回はEND_OF_FILEという定数名にしていますが、何でも大丈夫です。

２．ワークの指定

DCL-DS [ワーク名];
  [変数A][型];
  [変数B][型];
  ....(略)
END-DS;

指定されたSQL文を元にREADみたいなことしますが、そのときにレコードのデータが保管されるワークです。

改善の余地があるかもしれませんが、SELECT文で指定したカラムとまったく同じ構成にしています。

３．カーソル宣言とSQLの指定

今回はWRITEやUPDATEやDELETEではなくファイルを読む系なので、カーソルというものがいるそうです。

あまりよく理解していませんが、カーソルとはファイルのどのレコードを指しているかを示すものでしょう。

DECLAREは日本語で宣言という意味です。私の場合はカーソル名をZAIKO_CSRにしましたが、好きな名前を付けてください。SQL文は自分で作ってください。

EXEC SQL DECLARE [カーソル名] CURSOR FOR
SELECT [変数A],[変数B]....(略)
FROM ファイル名
(以下略)

SQLはめちゃくちゃ簡単なので自分で勉強してください。

掘り下げればアホみたいにややこしい文も作れますが、SQLRPGではあまり凝ったものにしないほうがよさそうです。

SQLはこの辺を勉強すればよろしいかと思います。

４．カーソルのオープン

次にカーソルを開きます。先ほど定義したカーソル名を指定します。

宣言したものを使いますという指定でしょう。

EXEC SQL OPEN [カーソル名];

５．読む

後は読んで必要な処理をするだけです。もちろんループで繰り返し読みます。

FETCHとは1件読む、いわゆるREADということです。

読んだ値を、INTO :[ワーク名]を使ってワークに代入します。

// SQLのDO_UNTIL
DOU SQLSTATE = END_OF_FILE;

  // SQLによるREAD
  EXEC SQL FETCH [カーソル名] INTO :[ワーク名];

  // SQLのEOFをチェック
  IF SQLSTATE <> END_OF_FILE;

    //適当な処理をどうぞ..

  ENDIF;

ENDDO;

前述のとおりEND_OF_FILEには’02000’が入っています。

SQLがグルグル回るときにレコードが無ければSQLSTATEが’02000’になるということですね。

６．カーソルのクローズ

最後はカーソルをクローズして一連のSQLを終了します。

あまりよく分かっていませんが、メモリ空間からSQL定義などなどをクリアしているのかもです。

EXEC SQL CLOSE [カーソル名];

その他

ざっくりこんな感じです。作る上で気になった点があるので明記しておきます。

他にあれば追記します。

同じカーソル定義はプログラム内に重複できないみたい

同じカーソル名のSQLを作るとコンパイル自体が通らないみたい。

開いても閉じればいいと思ったがダメみたい。

CSR1とかCSR2にすれば大丈夫なので、SQLやカーソルはプログラム内に複数定義できるみたい。

SETLLができない

今回の場合に限って言えば、キーを替えたので従来使っていたSETLLができなくなりました。

ゆえに上記プログラムは実際にはもっと肥大化しています。

SQLでSETLLの代わりになるものといえばOFFSETがあり、これでレコードの何番目から読むというのを指定できますがやや大変です。

いかにSETLLとREADEが神なのかが良く分かりました。

以上です。FFRPGの書き方も備忘録があります。よろしければご覧ください。

AS400/IBMi フリーフォームRPGの書き方備忘録
私の備忘録です。順次追加していきます。 何も知らない初心者なので間違いがあればご指摘いただければ幸いです。 尚、RPGIVとRPGLEは厳密には違うようですが、ここではRPGLEに統一しておきます。 登場人物はRPG(III)とRPGLEと...
qryheavy.com
2020.03.26
スポンサーリンク

コメント

タイトルとURLをコピーしました