DFU, Query, SQL

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