More than 1 year has passed since last update.

020-032 帳票系プログラムその３（SQLを用いた応用

Last updated at 2023-09-27Posted at 2023-09-27

本記事の内容とサンプルコード

本記事では、集計構造を持つ「帳票系プログラム」の解説を行う。
IBM i 上に QEOLFF 環境が存在する場合は/QEOLFF/QRPGLESRC/BPL030@YU2.RPGLE ,BPL030@YU3～.SQLRPGLE を、環境がない場合でもサンプルコードは次のURLより取得、閲覧可能である。

■例で使用するサンプルコード

■印刷イメージ

前提条件

以下のリンクより、本コンテンツの FF-RPG サンプルコードを閲覧、実行する環境を整備する

参考リンク

以下、リンクは OS バージョンが V7R5 のリンク先となっているが、サイト左の 「バージョンの変更」 より、自身の環境に合ったバージョンへ変更可能。

RPGLE版とSQLRPGLE版の違い

RPGLE 版の集計一覧 BPL030@YU2.RPGLE と SQLRPGLE 版の集計一覧 BPL030@YU3,4,5.SQLRPGLE の一番の違いは、データベースのアクセスに SQL を用いることである。
そのため ファイル・ステートメント（旧F仕様書） の部分にデータベースの記述が必要なくなる。BPL030@YU2.RPGLE で必要だった次の部分が不要となる。

BPL030@YU2.RPGLE

DCL-F    JUMIDL02      KEYED    USAGE(*INPUT                )                ;
DCL-F    TOKMSP        KEYED    USAGE(*INPUT                )                ;

これにより、SQLによる柔軟なアクセスが可能となり、例えば動的にファイル名を変更する様なプログラムも作成可能である。しかし、柔軟になる一方 ファイル・ステートメント（旧F仕様書） と比較して、ファイル参照に対するブラックボックス化が、起きやすい事もまた事実なため設計には十分留意する必要がある。

このサンプルプログラムは以下の仕様となっている

受注見出ファイルを 「地区コード、得意先番号」 順に出力する
帳票の明細行は、「得意先番号」 で集計された 「レコード数、受注金額、受注平均金額」 を出力する
「地区コード」 の変更では、 「地区計」 出力し、改ページする

そして、3つの SQLRPGLE 版の集計一覧は以下の点で少しずつ実装が異なっている

BPL030@YU3.SQLRPGLE ：ファイル参照をSQLで行い、「地区コード、得意先番号」 の集計処理はプログラムが行っている
BPL030@YU4.SQLRPGLE ：ファイル参照をSQLで行い、「得意先番号」 の集計処理は SQL で行い、「地区コード」 集計処理はプログラムが行っている
BPL030@YU5.SQLRPGLE ：ファイル参照をSQLで行い、「地区コード、得意先番号」 の集計処理は SQL が行っている

ファイル参照をするためのカーソル

サンプルプログラムでは、ファイル・ステートメント（旧F仕様書） の代わりに カーソル を使用してデータベースを参照しながら帳票を発行している。
次の例では、カーソル定義／オープンに関する箇所を抜粋している。

■カーソル定義／オープン／クローズ

BPL030@YU3.SQLRPGLE


～～～「1.」中略～～～
IF @OPEN_CURSOR() ;

～～～「2.」中略～～～
//   P*-***************************************************************
//   P*-* < @OPEN_CURSOR  > :カーソルオープン処理                  **
//   P*-*------------------------------------------------------------**
//   P*-*          RETURN  : *ON=正常オープン,*OFF=エラー        **
//   P*-***************************************************************
DCL-PROC @OPEN_CURSOR                                                          ;
  DCL-PI *N                              IND                                   ;
  END-PI                                                                       ;

  DCL-S    W#RTN_VAR     IND                                                   ; //結果フラグ

     EXEC SQL
       DECLARE C1 CURSOR FOR
         SELECT JHTIKU
               ,JHTOKB
               ,JHKING
               ,TKNAKJ
           FROM JUMIDP
           LEFT OUTER JOIN
                TOKMSP
                ON TKBANG = JHTOKB
         ORDER BY JHTIKU
                 ,JHTOKB
                 ,JHCHUB
         FOR READ ONLY
     ;

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

     IF SQLSTT = '00000' ;   // 00000:操作正常終了
          W#RTN_VAR  = *ON ;
     ELSE  ;
          W#RTN_VAR  = *OFF;
     ENDIF ;

     RETURN W#RTN_VAR  ;

END-PROC              

～～～「3.」中略～～～                                                         ;
  @CLOSE_CURSOR() ;

～～～中略～～～
//   P*-***************************************************************
//   P*-* < @CLOSE_CURSOR > :カーソルクローズ処理                  **
//   P*-*------------------------------------------------------------**
//   P*-*          RETURN  : *ON=正常クローズ.*OFF=エラー        **
//   P*-***************************************************************
DCL-PROC @CLOSE_CURSOR                                                         ;
  DCL-PI *N                              IND                                   ;
  END-PI                                                                       ;

  DCL-S    W#RTN_VAR     IND                                                   ; //結果フラグ

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

     IF SQLSTT = '00000' ;   // 00000:操作正常終了
          W#RTN_VAR  = *ON ;
     ELSE  ;
          W#RTN_VAR  = *OFF;
     ENDIF ;

     RETURN W#RTN_VAR  ;

END-PROC                                                                       ;

上記ではコード例では次の事を行っている

IF @OPEN_CURSOR() 「カーソルを正常にオープン」 出来たかを判定
DCL-PROC @OPEN_CURSOR ～ END-PROC プロシージャ・ステートメントで引数なし、戻り値が真偽型の 「カーソル・オープン」 用ルーチンを定義
EXEC SQL DECLARE C1 CURSOR 指定されたSQL でカーソル 「C1」 を定義
EXEC SQL OPEN C1 カーソル 「C1」 をオープン
SQLSTT の内容でカーソルのオープン状況を判定
EXEC SQL CLOSE C1 カーソル 「C1」 をクローズ

本サンプルでは、カーソル・オープンの状況を関数化するために、内部プロシージャで作成されている。その為先頭の制御ステートメントに DFTACTGRP(*NO) ACTGRP(*NEW)が追記されている。
カーソル・オープンは関数化されているため IF @OPEN_CURSOR() でカーソルのオープン状況を直接判定している。
更に 「C1」 というカーソルを定義する際に、任意の SQL を指定する事が可能となっている。
サンプルでは、受注見出ファイル（JUMIDP）を元に、得意先マスタ（TOKMSP）の結合及び、並び順（地区、得意先番号、受注番号）が指定されている。
ノーマルのRPG開発では省略で 暗黙のオープン をするため、ファイルオープンの意識が薄れがちだが、カーソルのオープンをする事でファイルの操作が可能になる。
SQLSTT 等の変数は 「SQL 連絡域」 と呼ばれ、OSが行ったSQLの操作状況をプログラム側が知るための変数群である。これらの変数の値を参照し、プログラムのハンドリングを行う必要があり、自身で定義する必要はなく、コンパイル時に組み込まれる。

カーソル・フェッチ処理

次の例では、READ系命令の代わりとなる、オープン済みカーソル 「C1」 でフェッチ処理を行い、レコードを参照している。

■カーソル定義／オープン

BPL030@YU3.SQLRPGLE


～～～「1.」中略～～～
//   D*-********************************************************************
//   D*-* データ構造（ＤＳ）                                            **
//   D*-********************************************************************
DCL-DS JUMIDR                                    //レコード構造
                     EXTNAME('JUMIDP'  )
END-DS                                        ;
DCL-DS TOKMSR                                    //レコード構造
                     EXTNAME('TOKMSP'  )
END-DS                                        ;
DCL-DS T0            QUALIFIED                ;  //レコード構造
        JHTIKU       LIKE(JHTIKU      )       ;
        JHTOKB       LIKE(JHTOKB      )       ;
        JHKING       LIKE(JHKING      )       ;
        TKNAKJ       LIKE(TKNAKJ      )       ;
END-DS                                        ;
DCL-DS I0            QUALIFIED                ;  //ＮＵＬＬ標識
        JHTIKU       LIKE(NULL_VAL    )       ;
        JHTOKB       LIKE(NULL_VAL    )       ;
        JHKING       LIKE(NULL_VAL    )       ;
        TKNAKJ       LIKE(NULL_VAL    )       ;
        NULL_INDS    LIKE(NULL_VAL ) DIM(04 ) POS(001) ;
END-DS                                        ;

～～～「2.」中略～～～
DOW @FETCH_CURSOR()

～～～「3.」中略～～～
//   P*-***************************************************************
//   P*-* < @FETCH_CURSOR > :カーソル読取処理                      **
//   P*-*------------------------------------------------------------**
//   P*-*          RETURN  : *ON=正常読取,*OFF=ＥＯＦ又はエラー  **
//   P*-***************************************************************
DCL-PROC @FETCH_CURSOR                                                         ;
  DCL-PI *N                              IND                                   ;
  END-PI                                                                       ;

  DCL-S    W#RTN_VAR     IND                                                   ; //結果フラグ

     //カーソル読み込み
  // EXEC SQL
  //   FETCH NEXT FROM C1 INTO  :T0  :NULL_INDS
  // ;
     EXEC SQL
       FETCH NEXT FROM C1 INTO  :T0  :I0.NULL_INDS
     ;

     SELECT ;
     WHEN SQLSTT = '00000' ;   // 00000:操作正常終了
         W#RTN_VAR  = *ON  ;
     WHEN SQLSTT = '02000' ;   // 02000:ＥＯＦ
         W#RTN_VAR  = *OFF ;
     OTHER ;                           //
         W#RTN_VAR  = *OFF ;
     ENDSL ;


     RETURN W#RTN_VAR  ;

END-PROC                                                                       ;

上記ではコード例では次の事を行っている

DCL-DS T0 フェッチしたレコードの値を格納するレコード構造を定義
DCL-DS I0 フェッチしたレコードの値がNULL値か判定する整数型構造を定義
DOW @FETCH_CURSOR() 「カーソルを正常にフェッチ」 出来たかを判定
DCL-PROC @OPEN_CURSOR ～ END-PROC プロシージャ・ステートメントで引数なし、戻り値が真偽型の 「カーソル・フェッチ」 用ルーチンを定義
FETCH NEXT FROM C1 指定されたカーソル 「C1」 で次のレコードを取得（フェッチ）し、INTO :T0 :I0.NULL_INDS レコード構造、NULL判定構造、に値を格納
SQLSTT の内容でカーソルのフェッチ状況を判定

フェッチした内容を格納するためのデータ構造 T0（レコード構造） と I0（NULL判定構造） を定義ステートメントで定義している。
カーソル・フェッチは、関数化されているため DOW @FETCH_CURSOR() でフェッチ可能な限り処理をループしている。
フェッチが正常に行われた場合、T0（レコード構造） と I0（NULL判定構造） には値が格納され、通常のプログラミング変数と同様に扱うことが出来る。

SQLの柔軟性

BPL030@YU4.SQLRPGLE と BPL030@YU5.SQLRPGLE の2つのサンプルでは異なった方法で集計処理を行っている。これらサンプルは SQL で既に備わっている関数を利用する事で、集計処理などをプログラミング実装せずに帳票出力を実現させている。

BPL030@YU4.SQLRPGLE は GROUP BY JHTIKU,JHTOKB で得意先番号別にグループ化し SUM関数 と COUNT関数 を用いてレコード件数と受注金額集計を行っている。

■ 例） 「得意先番号別集計」

BPL030@YU4.SQLRPGLE

     EXEC SQL
       DECLARE C1 CURSOR FOR
         SELECT  RECCNT
                ,JHTIKU
                ,JHTOKB
                ,JHKING
                ,TKNAKJ
           FROM (
            (
             SELECT COUNT(*) AS RECCNT
                   ,JHTIKU
                   ,JHTOKB
                   ,SUM(JHKING) AS JHKING
               FROM JUMIDP
             GROUP BY JHTIKU
                     ,JHTOKB
             ) LEFT OUTER JOIN
                  TOKMSP
                  ON TKBANG = JHTOKB
           )
           ORDER BY JHTIKU
                   ,JHTOKB
         FOR READ ONLY
     ;

更に BPL030@YU5.SQLRPGLE は、GROUP BY ROLLUP( JHTIKU,JHTOKB ) で地区別合計行も取得結果に加えている。

■ 例） 「得意先番号別集計＋地区別集計行の追加」

BPL030@YU5.SQLRPGLE

     EXEC SQL
       DECLARE C1 CURSOR FOR
         SELECT  RECCNT
                ,JHTIKU
                ,JHTOKB
                ,JHKING
                ,TKNAKJ
           FROM (
            (
             SELECT COUNT(*) AS RECCNT
                   ,JHTIKU
                   ,JHTOKB
                   ,SUM(JHKING) AS JHKING
               FROM JUMIDP
             GROUP BY ROLLUP(
                      JHTIKU
                     ,JHTOKB
                    )
             ) LEFT OUTER JOIN
                  TOKMSP
                  ON TKBANG = JHTOKB
           )
           ORDER BY JHTIKU
                   ,JHTOKB
         FOR READ ONLY
     ;

本サンプルの参考リンク

ここでは本サンプルで使用された技術の参考リンクを掲載する。

■参考リンク

終わりに

これまで３回に渡り、帳票系プログラム・サンプルに関して紹介してきた。この中で出てきた RPGLE(READ/WRITE) と SQLRPGLE(SQL) どちらが良いか？私はこの２つの選択に正解や答えがある訳ではないと考えている。またそれぞれの特徴とメリット／デメリットが存在する。環境、文化、慣れ、学習コスト様々なものがあり、ここでは敢えて「どちらがオススメなのか？」などにも触れないでおく。しかし前述の例の様に、SQL には、ロジックや実装を置き換える事が可能で便利な 組み込み関数 等が備わっていることも事実で、これらをマスターできれば、プログラム開発も効率化が上がり、プログラミングの幅が広がることは間違いないだろう。

編集履歴

作成日：IBMCJ-2023-C06 (CSC)Y.USHIDA 2023.08.28 18:00 [新規投稿]
更新日：IBMCJ-2023-C06 ().____ 20__.. : []

資料に関する注意書き

本資料の著作権は、日本アイ・ビー・エム株式会社（IBM Corporationを含み、以下、IBMといいます。）に帰属します。
ワークショップ、セッション、および資料は、IBMまたはセッション発表者によって準備され、それぞれ独自の見解を反映したものです。それらは情報提供の目的のみで提供されており、いかなる参加者に対しても法律的またはその他の指導や助言を意図したものではなく、またそのような結果を生むものでもありません。本資料に含まれている情報については、完全性と正確性を期するよう努力しましたが、「現状のまま」提供され、明示または暗示にかかわらずいかなる保証も伴わないものとします。本資料またはその他の資料の使用によって、あるいはその他の関連によって、いかなる損害が生じた場合も、IBMまたはセッション発表者は責任を負わないものとします。本資料に含まれている内容は、IBMまたはそのサプライヤーやライセンス交付者からいかなる保証または表明を引きだすことを意図したものでも、IBMソフトウェアの使用を規定する適用ライセンス契約の条項を変更することを意図したものでもなく、またそのような結果を生むものでもありません。

本資料でIBM製品、プログラム、またはサービスに言及していても、IBMが営業活動を行っているすべての国でそれらが使用可能であることを暗示するものではありません。本資料で言及している製品リリース日付や製品機能は、市場機会またはその他の要因に基づいてIBM独自の決定権をもっていつでも変更できるものとし、いかなる方法においても将来の製品または機能が使用可能になると確約することを意図したものではありません。本資料に含まれている内容は、参加者が開始する活動によって特定の販売、売上高の向上、またはその他の結果が生じると述べる、または暗示することを意図したものでも、またそのような結果を生むものでもありません。パフォーマンスは、管理された環境において標準的なIBMベンチマークを使用した測定と予測に基づいています。ユーザーが経験する実際のスループットやパフォーマンスは、ユーザーのジョブ・ストリームにおけるマルチプログラミングの量、入出力構成、ストレージ構成、および処理されるワークロードなどの考慮事項を含む、数多くの要因に応じて変化します。したがって、個々のユーザーがここで述べられているものと同様の結果を得られると確約するものではありません。

記述されているすべてのお客様事例は、それらのお客様がどのようにIBM製品を使用したか、またそれらのお客様が達成した結果の実例として示されたものです。実際の環境コストおよびパフォーマンス特性は、お客様ごとに異なる場合があります。

IBM、IBM ロゴは、米国やその他の国におけるInternational Business Machines Corporationの商標または登録商標です。他の製品名およびサービス名等は、それぞれIBMまたは各社の商標である場合があります。現時点での IBM の商標リストについては、ibm.com/trademarkをご覧ください。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up