タイガー!タイガー!じれったいぞー!(SE編)

AS400,WAS,GlassFish,Java,JavaEE,JSF等の開発における日々の気づきをまとめたブログ(備忘録)。

【AS400】SQLRPGLE-カーソルを使った集計処理

SQLカーソル処理にて、集計処理させるプログラムについてです。

はっきり言って、SQLは超・便利です。
CRUD処理はもちろん、複雑な照会であっても、副問い合わせなどで簡単にデータの取り出しが可能です。
ただ、複雑なSQL文は、解読するのに一苦労します(私のレベルの問題もありますが・・・)。

そこで、簡単な集計+データ抽出は、SQLに任せて、具体的な書き込みや更新処理等はRPGに任せるなど、分担させることで、幾分処理がわかりやすく書くこともできるかと思います。ケース・バイ・ケースかとは思いますが・・・。
SQLは、カーソル処理でさばく形になります。

カーソルとは?

  • カーソルとは、クエリーの結果セットを一時的に蓄えておくための仮想的な作業領域のことです。
  • SQLは、そのままではデータを1行ずつ処理していくことができないため、対象となるすべてのデータをまとめて表から取り出し、その結果セットをカーソルというオブジェクトへいったん蓄えておいたうえで、ループ処理を実行しながら、その中のデータを1行ずつ取り出し、処理していきます。
ポインタ
  • カーソルには、カーソルの中での現在位置を示すポインタが容易されています。
  • ポインタは、カーソルに蓄えられている行の集合の中から、取り出す対象となる行を指します。ループ処理が実行されるたびに、ポインタは行の集合の中を1行ずつ進んでいき、次に取り出す対象となる行を指し示します。

簡単な集計処理のサンプルを書いてみました。

ターゲットとなるデータはこちら。

売上データ・サンプル

     A                                      UNIQUE                
     A          R RECURI                    TEXT(' 売上データ ')  
     A*                                                           
     A            DENNO         10A         COLHDG(' 伝票№ ')    
     A            URIDATE        8S 0       COLHDG(' 売上日 ')    
     A                                      EDTWRD('    /  /  ')  
     A                                      COMP(GE 0)            
     A            URISEC         2A         COLHDG(' 所属部署 CD')
     A            URITANTO       4A         COLHDG(' 担当者 CD')  
     A            SYOHIN        30O         COLHDG(' 商品 ')      
     A            SURYO          7S 0       COLHDG(' 数量 ')      
     A            TANKA         11P 2       COLHDG(' 単価 ')      
     A*                                                           
     A          K DENNO                                           

集計処理はこちら。
SQLで集計処理済みの表を作成し、RPGのループ処理で、集計結果を1行ずつ表示させるものになっています。
サンプルでは、指定した日付について部門ごとに売上金額合計を画面に出力させます。

SQLRPGLEサンプル

     D MAIN            PR                  EXTPGM('SAMPLE29')
     D                                8P 0 
     D MAIN            PI                     
     D  P@YYMM                        8P 0    
     D*                                      
     D W@LOOP          S              1A   INZ(*OFF) 
     D P@SUMM          S             11P 0    
     D*                                      
     D CURSORDS        DS                    
     D C@URISEC                       2A     
     D C@URIAGE                       9P 0    
     D*                                                        
     D******************************************************* 
     D*  MAIN             
     D******************************************************* 
      /FREE   
           EXEC SQL  
             SET OPTION COMMIT = *NONE;                        
                       
           EXEC SQL     
             DECLARE CSR CURSOR FOR   
                  SELECT URISEC, SUM(SURYO * TANKA)  
                    FROM URIAGE  
                   WHERE URIDATE = :P@YYMM 
                   GROUP BY URISEC
                   ORDER BY URISEC; 
           EXEC SQL
             OPEN CSR; 
                       
           IF SQLCODE = *ZERO;  // カーソルOPEN成功
               DOU W@LOOP = *ON; 
                   EXEC SQL   
                     FETCH CSR INTO :CURSORDS; 
                       
                   SELECT;    
                       WHEN SQLCODE = 100;  // NO-DATA
                           W@LOOP = *ON;
                       WHEN SQLCODE = *ZERO;  
                           DSPLY C@URISEC; 
                           DSPLY C@URIAGE; 
                       OTHER;
                           DSPLY 'SQL ERROR !';
                           DSPLY SQLCODE; 
                           W@LOOP = *ON; 
                   ENDSL; 
               ENDDO;
                    
               EXEC SQL  
                 CLOSE CSR;  
                
           ELSE; 
               DSPLY 'SQL ERROR !'; 
               DSPLY SQLCODE;  
           ENDIF;
                      
           *INLR = *ON;     
           RETURN;   
      /END-FREE        
  • 「FETCH CSR INTO :CURSORDS」のところで、1行読み取った値を定義済みデータ・ストラクチャーへ格納しています。

  • SQLCODE=100は「FETCH ステートメントで参照されたカーソルの位置が、結果表の最終行の後になった」という意味で、つまりは、データが無くなったわけです(あるいは対象のデータが元々無し)ので、ループアウトさせます。

    実行結果

  • 売上日 2011/01/11の集計結果

> CALL PGM(TIGEROBJ/SAMPLE29) PARM(X'020110111F') 
  DSPLY  01                                       
  DSPLY    1010490                                
  DSPLY  02                                       
  DSPLY     202400                                
  DSPLY  20                                       
  DSPLY       3900