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 .. によって結合されていることが、おわかりになると思う。