25. SQLパッケージ入門 = RPG でSQLを使用するには？
RPGやC/400 の内部にSQLを組み込んだものを「SQLパッケージ」と呼ぶ。
正確には CRTSQLPKGコマンド によって作成させるオブジェクトがSQL パッケージで
あるが、単にRPG や COBOL、C/400 などの高級言語にSQL文を埋め込んだものをここでは
SQLパッケージと呼ぶ。
従来のRPG開発に比べて、SQLパッケージの開発において異なるのは
- SEUの編集タイプは RPG や RPGLE ではなく、SQLRPGまたはSQLRPGLEを指定する。
- コンパイルは CRTRPGPGM や CRTBNDRG ではなく、CRTSQLRPGまたは
CRTSQLRPGI を使用する。
単なるRPGではなく、SQLパッケージによって開発する利点は概ね次のとおりである。
- RPGによるDB2/400の構造に不慣れな開発者でもSQLの知識があれば容易に
データ・ベースの結合や更新処理を記述することができる。 - 1つのRPGの中でバッチ更新や累計処理などバッチ処理を行ってからレポートを
印刷するなどの柔軟な処理を記述できる。 - 論理ファイルなどの処理データベース空間が存在していない場合でも動的に
RPG内部で処理したいデータベース空間を生成することができる。 - 大量のデータ・ベースのバッチ処理のパフォーマンスを挙げることができる。
一方、短所も十分、理解しておく必要がある。
- 更新処理においては COMMIT-ROLLBACK などによる更新処理のキャンセルには
十分な配慮が必要である。 - RPG の CHAIN とは異なって単なる
SELECT文では
レコードの欠落が発生する可能性があることを十分、考慮しておく必要がある。 - 大量のバッチ処理では他のAppのパフォーマンスを低下させてしまう可能性があることに
配慮すべきである。
それにしても、SQLパッケージは柔軟で動的な処理を開発できるという点においては魅力的で
ありRPGだけではかなり困難であった開発も SQLであれば容易に実現可能である場合もある。
開発の可能性をかなり拡大してくれるという観点から SQLの利用は十分、検討に値する
ものである。
さて前置きの知識はこれくらいにして、SQLによる開発の具体例を紹介しよう。
SQLの使用をより理解して頂くために、最初は伝統的なRPGサイクルを使った印刷の例として
「RPG001」というプログラムを紹介する。
RPG001 は商品マスター(QTRFIL/SHOHIN) の品種コード順の論理ファイル
(QTRFIL/LSHOHNS) を読んで、品種コード別に改ページして印刷する単純な例である。
【 RPG001: 品種別商品マスター一覧表 】
| 使用データ・ベース | SHOHIN.MBR | LSHOHINS.MBR | HINSHU.MBR |
0001.00 H 1 Y/ 0002.00 F********** 品種別商品一覧表 ************************* 0003.00 FLSHOHNS IP E K DISK 0004.00 FHINSHU IF E K DISK 0005.00 FQPRINT O F 132 OF LPRINTER 0006.00 F***************************************************************** 0007.00 E HDR 1 1 32 見出し 0008.00 E DAR 2 10 0009.00 LQPRINT 66FL 62OL 0010.00 I@SHOHIN 01 0011.00 I SHSCODL1 0012.00 I*( データ・ベース外部データ構造 ) 0013.00 IDSF001 EIDSHINSHU 0014.00 I DS 0015.00 I 1 5 TESCOD 0016.00 C****************************************************** 0017.00 C *INL1 IFEQ '1' *INL1 0018.00 C****************************************************** 0019.00 C* SHSCOD : 品種 ﾃｰnﾞ の切れ目 * 0020.00 C SETOF 40 改頁 0021.00 C END *INL1 0022.00 C****************************************************** 0023.00 C* 明 細 演 算 0024.00 C****************************************************** 0025.00 C*----------------------------------------------------+ 0026.00 C KEY001 KLIST | 0027.00 C KFLD SHSCOD | 0028.00 C*----------------------------------------------------+ 0029.00 C CLEARDSF001 CHAIN 0030.00 C SETOF 99 CHAIN 0031.00 C KEY001 CHAINHINSHU 99 CHAIN 0032.00 C* 0033.00 C*( 明細印刷 ) 0034.00 C*----------------------------------------------------+ 0035.00 C SETON 42 | 0036.00 C EXSR OUTPUT | 明細印刷 0037.00 C*----------------------------------------------------+ 0038.00 C ADD 1 KENSU 70 件数 0039.00 C*( 小計 ) 0040.00 C* 0041.00 C END TAG 0042.00 C*( T-LR 最終合計 ) 0043.00 C*----------------------------------------------------+ 0044.00 CLR SETON 49 | 0045.00 CLR EXSR OUTPUT | 件数印刷 0046.00 C*----------------------------------------------------+ 0047.00 C****************************************************** 0048.00 C *INZSR BEGSR 0049.00 C****************************************************** 0050.00 C* 初期 ﾅbhﾓ のみの実行 0051.00 CSR MOVE *BLANKS DMYCOD 15 0052.00 CSR INZEND ENDSR 0053.00 C****************************************************** 0054.00 C OUTPUT BEGSR 0055.00 C****************************************************** 0056.00 CSRN40 SETON 4041 0057.00 CSR EXCPT 0058.00 CSR OF SETOF 40OF 0059.00 CSR SETOF 414243 0060.00 CSR SETOF 444546 0061.00 CSR SETOF 474849 0062.00 CSR ENDSR 0063.00 OQPRINT E 206 41 0064.00 O UDATE Y 8 0065.00 O 14 ' 作成 ' 0066.00 O HDR,1 82 0067.00 O 128 'PAGE.' 0068.00 O PAGE Z 131 0069.00 O E 1 41 0070.00 O 12 ' 品種コード ' 0071.00 O SHSCOD 16 0072.00 O 24 ' 品種名 ' 0073.00 O HNSNAM 38 0074.00 O E 1 41 0075.00 O 12 ' 商品コード ' 0076.00 O 34 ' 商品名 ' 0077.00 O 68 ' 単価 ' 0078.00 O E 2 42 0079.00 O SHCODE 11 0080.00 O SHNAME 46 0081.00 O SHTANKJ 70 0082.00 O E 1 49 0083.00 O 40 ' 処理件数 ' 0084.00 O 57 '. . . . . . . . .' 0085.00 O KENSU 2 65 0086.00 ** HDR 0087.00 品種別商品一覧表
【 PGM001 の実行の結果 】
この PGM001 を今すぐ、この場で実行して、その結果を HTML としてご覧にいれる。
次のURLをクリックして頂きたい。これはSpoolライターVer 3.0 によって印刷JOBの
結果としてのスプールをその場でHTML化して表示する機能を利用している。
http://218.44.135.18/cgi-bin/RPG001CL
印刷スプールをHTML化する解説は、
「25.印刷スプールをHTMLで戻すCLP-CGI」を参照して頂きたい。
今回使用したCLP のソースは
http://218.44.135.18/QSYS.LIB/PGMRLIB.LIB/QCLLESRC.FILE/RPG001CL.MBRである。
次にいよいよ上記のRPGプログラムを SQLパッケージに置き換えたサンプルを紹介する。
SQLRPG の中には
SELECT T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD,
T2.HNSNAM FROM QTRFIL/SHOHIN T1, QTRFIL/HINSHU T2
WHERE T1.SHSCOD = T2.HNSCOD
ORDER BY T1.SHSCOD, T1.SHCODE
という SQL: SELECT文を組み込む。これは2つのデータ・ベース:
商品マスター(QTRFIL/SHOHIN) と品種マスター(QTRFIL/HINSHU) を
品種コードによって結合 (WHERE T1.SHSCOD = T2.HNSCOD) して品種コード順、
商品コード順に分類 (ORDER BY T1.SHSCOD, T1.SHCODE) する
SELECT文である。
RPG の中に挿入するSQL命令は
C/EXEC SQL C+ ........... ( SQL命令) ........ C+ ........... ( SQL命令) ........ C/END-EXEC
のように /EXEC SQL から /END-EXEC で囲む形式によって記述する。
【 SQL001: SQLサンプル:品種別商品マスター一覧表 】
0001.00 H 1 Y/ 0002.00 F********** SQL 印刷サンプル：品種別商品一覧表 ****************** 0003.00 FQPRINT O F 132 OF LPRINTER 0004.00 F***************************************************************** 0005.00 E HDR 1 1 32 見出し 0006.00 E DAR 2 10 0007.00 LQPRINT 66FL 62OL 0008.00 I*( データ・ベース外部データ構造 ) 0009.00 IFMT001 E DSSHOHIN 0010.00 IFMT002 E DSHINSHU 0011.00 C****************************************************** 0012.00 C* SQL 文のカーソルの前準備 0013.00 C****************************************************** 0014.00 C* SELECT 文によってカーソル C1 を用意 0015.00 C/EXEC SQL DECLARE C1 CURSOR FOR 0016.00 C+ SELECT T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD, 0017.00 C+ T2.HNSNAM FROM QTRFIL/SHOHIN T1, QTRFIL/HINSHU T2 0018.00 C+ WHERE T1.SHSCOD = T2.HNSCOD 0019.00 C+ ORDER BY T1.SHSCOD, T1.SHCODE 0020.00 C/END-EXEC 0021.00 C* カーソルをオープン 0022.00 C/EXEC SQL 0023.00 C+ OPEN C1 0024.00 C/END-EXEC 0025.00 C****************************************************** 0026.00 C* 明 細 演 算 0027.00 C****************************************************** 0028.00 C *LIKE DEFN SHSCOD SHSCOB 0029.00 C MOVE *LOVAL SHSCOB 0030.00 C/EXEC SQL WHENEVER NOT FOUND GOTO EOF 0031.00 C/END-EXEC 0032.00 C DO *HIVAL 0033.00 C/EXEC SQL 0034.00 C+ FETCH C1 INTO :SHCODE, :SHNAME, :SHTANK, :SHSCOD, 0035.00 C+ :HNSNAM 0036.00 C/END-EXEC 0037.00 C*( 明細印刷 ) 0038.00 C SHSCOB COMP SHSCOD 40 0039.00 C*----------------------------------------------------+ 0040.00 C SETON 42 | 0041.00 C EXSR OUTPUT | 明細印刷 0042.00 C*----------------------------------------------------+ 0043.00 C MOVE SHSCOD SHSCOB 0044.00 C ADD 1 KENSU 70 件数 0045.00 C END 0046.00 C EOF TAG 0047.00 C* カーソルをクローズ 0048.00 C/EXEC SQL 0049.00 C+ CLOSE C1 0050.00 C/END-EXEC 0051.00 C END TAG 0052.00 C*( T-LR 最終合計 ) 0053.00 C*----------------------------------------------------+ 0054.00 C SETON 49 | 0055.00 C EXSR OUTPUT | 件数印刷 0056.00 C*----------------------------------------------------+ 0057.00 C SETON LR 0058.00 C****************************************************** 0059.00 C *INZSR BEGSR 0060.00 C****************************************************** 0061.00 C* 初期のみの実行 0062.00 CSR CLEARFMT001 0063.00 CSR CLEARFMT002 0064.00 CSR INZEND ENDSR 0065.00 C****************************************************** 0066.00 C OUTPUT BEGSR 0067.00 C****************************************************** 0068.00 CSRN40 SETON 4041 0069.00 CSR EXCPT 0070.00 CSR OF SETOF 40OF 0071.00 CSR SETOF 414243 0072.00 CSR SETOF 444546 0073.00 CSR SETOF 474849 0074.00 CSR ENDSR 0075.00 OQPRINT E 206 41 0076.00 O UDATE Y 8 0077.00 O 14 ' 作成 ' 0078.00 O HDR,1 82 0079.00 O 128 'PAGE.' 0080.00 O PAGE Z 131 0081.00 O E 1 41 0082.00 O 12 ' 品種コード ' 0083.00 O SHSCOD 16 0084.00 O 24 ' 品種名 ' 0085.00 O HNSNAM 38 0086.00 O E 1 41 0087.00 O 12 ' 商品コード ' 0088.00 O 34 ' 商品名 ' 0089.00 O 68 ' 単価 ' 0090.00 O E 2 42 0091.00 O SHCODE 11 0092.00 O SHNAME 46 0093.00 O SHTANKJ 70 0094.00 O E 1 49 0095.00 O 40 ' 処理件数 ' 0096.00 O 57 '. . . . . . . . .' 0097.00 O KENSU 2 65 0098.00 ** HDR 0099.00 品種別商品一覧表
【 解説 】
コンパイルは
CRTSQLRPG PGM(MYLIB/SQL001) SRCFILE(MYSRCLIB/QRPGSRC) COMMIT(*NONE) RDB(*NONE)
のように指定する。 更新処理は行わないのでコミットメントの指定は行わない。
さてこの SQLRPGソースであるが、SELECT文によってデータ・ベースを取り出すので
ファイル仕様書にはデータ・ベースの記述は無い。
SELECT文によってアクセス・パスが一時的に作成されてFETCH 文によって1レコードずつ
T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD, T2.HNSNAM
の各フィールド値が取り出されて
フィールド; :SHCODE, :SHNAME, :SHTANK, :SHSCOD, :HNSNAM
に埋め込まれる。
SQL文の中では RPG で使用するフィールド名は MYFLD というフィールドは :MYFLD と
記述する。
FETCH による埋め込みは選択したフィールド順にフィールドのタイプは正確に一致して
いなければならない。
例えば文字フィールドを選択しているのに対応するのに対応するのが数字フィールドであれば
フィールド・タイプの不一致として SQL0303 のエラーが戻される。
初めてSQLパッケージを使用するときは、初心者は必ずといってよいほどこのエラーに
悩まされる。(失礼)
このようなエラーを防止するために、ここではSELECT文で使用するデータ・ベースの記述を
0008.00 I*( データ・ベース外部データ構造 ) 0009.00 IFMT001 E DSSHOHIN 0010.00 IFMT002 E DSHINSHU
によって外部記述としてインクルードしていることに注意されたい。
2つのデータ・ベースが
ライブラリー・リストに存在していればコンパイルによってこの記述が DSの作業フィールドと
して取り込まれることになる。
よって FETCH で使用する SHCODE , .... は正確に元のデータ・ベースと同じタイプと
して包含されるので FETCH によるタイプ不一致のエラーは絶対に起こらない。
0032.00 C DO *HIVAL
:
:
0045.00 C END
によって FETCH を繰り返してレコード毎に印刷を行うのであるが
0030.00 C/EXEC SQL WHENEVER NOT FOUND GOTO EOF 0031.00 C/END-EXEC
によって FETCH によってレコードが見つからなくなればタグ : EOF へ処理は飛ばされて
処理は終了する。
【 SQL001 の実行の結果 】
それでは早速、この実行結果を見てみよう。
http://218.44.135.18/cgi-bin/SQL001CL
今度は最終の品種コード = 0009 のレコードが印刷されていないはずだ。
実は品種コード=0009 は品種マスターには存在しないエラー・レコードである。
これは単純なSELECT文による結合なので結合できなかったレコードは欠落してしまう。
このようにSQL文を使用する場合にはレコードの欠落にも注意が必要である。
SQL SELECT文によってレコードを欠落させたくない場合は
SELECT T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD,
T2.HNSNAM FROM QTRFIL/SHOHIN T1 LEFT OUTER JOIN
QTRFIL/HINSHU T2 ON T1.SHSCOD = T2.HNSCOD
ORDER BY T1.SHCODE ASC
のように LEFT OUTER JOIN ... ON .... によって記述することが必要である。
WHERE によるSELECT文は一般的に普及しているので WHERE を使用しがちであるが
注意が必要である。
Chicago Ver 5.0 のユーザーであれば Excelサーバーの
[表示]-[Excelサーバー定義ビュー]によって生成されたSQL文を参照することができる。
Chiacgo Ver 5.0 のSQL文は、レコードの脱落が無いように
LEFT OUTER JOIN ... ON .. によって結合されていることが、おわかりになると思う。
