はじめに
COBOLでSQLを用いて様々なプログラムを作ってきましたが、
READを複数回行うのに端的に言うとファイルをCLOSEしてから、
再OPENする手法を取らないといけない事に気づいたので、今回紹介しようと思います。
今回は入力ファイルである試験結果ファイルからフェッチ処理でSELECT文を実行して、
一定条件を満たした受験者IDを合格者として抽出して、
合格者の情報を出力ファイルである合格者結果ファイルに書き込むというものです。
今回使用するテーブルは以下の試験結果テーブルになります。
> select * from shikenkekka;
jukenbi | jukenshaid | gozen | gogo1 | gogo2 | ronjutu | heikinten
------------+------------+-------+-------+-------+---------+-----------
それぞれ列は、受験日、受験者ID、午前、午後1、午後2、論述、平均点を意味しています。
なぜ、一旦CLOSEして、再オープンするのか？
一回目のREAD文で「AT END」になったら「OS」で｢EOF(=END OF FILE)｣を検知しますから、そのままで「READ」命令を実行しようとしたらエラーとなります。
そこで以下の手順を取ります。
１.「AT END」になったら、そのファイルを「CLOSE」処理する。
２.１の次に、そのファイルを「OPEN」処理する。
３.２の後に、そのファイルを「READ」し「AT END」になるまで処理をする。
この流れで、同一ファイルを再「READ」出来ます。
実際にプログラムを見ていきましょう。
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT 試験結果ファイル ASSIGN TO
"/home/XXXXX/DATA/SHIKENKEKKA"
FILE STATUS IS 結果状態.
SELECT 合格者結果ファイル ASSIGN TO
"/home/XXXXX/DATA/SHIKENKEKKAFINAL"
FILE STATUS IS 合格状態.
DATA DIVISION.
FILE SECTION.
FD 試験結果ファイル.
01 試験結果レコード.
05 受験日 PIC 9(8).
05 試験結果基本.
10 受験者ID PIC X(6).
10 午前 PIC 9(4).
10 午後1 PIC 9(4).
10 午後2 PIC 9(4).
10 論述 PIC 9(4).
10 平均点 PIC 9(4).
FD 合格者結果ファイル.
01 合格者結果レコード.
05 受験日 PIC 9(8).
05 試験結果基本.
10 受験者ID PIC X(6).
10 午前 PIC 9(4).
10 午後1 PIC 9(4).
10 午後2 PIC 9(4).
10 論述 PIC 9(4).
10 平均点 PIC 9(4).
WORKING-STORAGE SECTION.
01 状態.
03 結果状態 PIC X(2).
03 合格状態 PIC X(2).
01 件数.
03 ロールバック件数 PIC 9(2) VALUE ZERO.
03 コミット件数 PIC 9(2) VALUE ZERO.
03 合格者件数 PIC 9(2) VALUE ZERO.
** 1.ホスト変数の定義
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DBNAME PIC X(10) VALUE "XXXX".
01 USERNAME PIC X(10) VALUE "xxxxx".
01 PASSWORD PIC X(10) VALUE "xxxxx".
01 SW-AREA.
03 SW-NOTFOUND PIC X(1) VALUE SPACE.
01 CST-AREA.
03 CST-1X PIC X(1) VALUE "1".
03 CST-SQL-NF PIC S9(5) VALUE +100.
01 WK-SHIKENKEKKA.
03 JUKENBI PIC X(10).
03 SHIKENKEKKA1.
05 JUKENSHAID PIC X(6).
05 GOZEN PIC 9(4).
05 GOGO1 PIC 9(4).
05 GOGO2 PIC 9(4).
05 RONJUTU PIC 9(4).
05 HEIKINTEN PIC 9(4).
EXEC SQL END DECLARE SECTION END-EXEC.
** 2.共通領域の定義
EXEC SQL INCLUDE SQLCA END-EXEC.
*
PROCEDURE DIVISION.
KAISHI SECTION.
MAIN.
*
DISPLAY "*DBPRO02 START*".
** 3.ファイルOPEN
OPEN INPUT 試験結果ファイル.
OPEN OUTPUT 合格者結果ファイル.
IF "00" = 結果状態 OR 合格状態
THEN
CONTINUE
ELSE
CLOSE 試験結果ファイル
CLOSE 合格者結果ファイル
DISPLAY "オープンエラー。"
PERFORM PROEND
END-IF.
*
** 4.データベース接続
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY
:PASSWORD USING :DBNAME
END-EXEC.
** 5.データベースアクセス
** 追加
PERFORM UNTIL 結果状態 NOT = "00"
READ 試験結果ファイル
AT END
DISPLAY "READ END"
NOT AT END
MOVE 受験日
OF 試験結果レコード(1:4)
TO JUKENBI(1:4)
MOVE "-" TO JUKENBI(5:1)
MOVE 受験日
OF 試験結果レコード(5:2)
TO JUKENBI(6:2)
MOVE "-" TO JUKENBI(8:1)
MOVE 受験日
OF 試験結果レコード(7:2)
TO JUKENBI(9:2)
MOVE 試験結果基本
OF 試験結果レコード
TO SHIKENKEKKA1
EXEC SQL
INSERT INTO shikenkekka
VALUES (:JUKENBI,:JUKENSHAID,:GOZEN,
:GOGO1,:GOGO2,:RONJUTU,
:HEIKINTEN)
END-EXEC
DISPLAY "SQLコード" SQLCODE
*
IF JUKENSHAID = "SW1900"
THEN
EXEC SQL
ROLLBACK
END-EXEC
DISPLAY "受験者ID='SW1900'は"
"ロールバックされました。"
ADD 1 TO ロールバック件数
ELSE
EXEC SQL
COMMIT
END-EXEC
ADD 1 TO コミット件数
END-IF
END-READ
END-PERFORM.
*
* 試験結果ファイルを一旦CLOSEさせる。
CLOSE 試験結果ファイル.
*
DISPLAY "ロールバック件数"
ロールバック件数.
DISPLAY "コミット件数"
コミット件数.
*
DISPLAY "DB INSERT END".
*
入力ファイルの結果状態が00以外(READ文でAT ENDになるまで)になるまで
READ文を読み込む処理です。
NOT AT ENDになった時に験結果ファイルの受験日が、
YYYYMMDD形式をSQLでDATA型でINSERTするため、
YYYYｰMMｰDDの形式に転記した上で試験結果ファイルの内容をWK-SHIKENKEKKAの
SQLで使用するエリアに格納して、埋込みSQLでINSERTしています。
それを試験結果ファイルのレコード件数分、AT ENDになるまでループ処理を繰り返し行きます。
こうすることで試験結果ファイルの件数分のデータをINSERTすることが出来ます。
イメージ的には入力ファイルを件数分、
READで読み込んで出力ファイルにWRITEして出力していく感じに似ているでしょうか。
これで試験結果ファイルが1回目のAT ENDまで読み込んだので、試験ファイルを一旦CLOSEさせます。
カーソルオープンについて
** 6.カーソルオープン処理
PERFORM OPEN-RTN.
・
・
・
OPEN-RTN SECTION.
DISPLAY "カーソルオープン処理".
EXEC SQL
DECLARE CRS01 CURSOR FOR
SELECT *
FROM shikenkekka
WHERE gozen >= 60
AND gogo1 + gogo2 >= 120
AND 0.3 * (gozen + gogo1 + gogo2 +
ronjutu) <= ronjutu
ORDER BY jukenbi,jukenshaid
END-EXEC.
*
EXEC SQL
OPEN CRS01
END-EXEC.
*
OPEN-RTN-EX.
カーソルオープンについて軽く説明すると、カーソル名CRS01に対してSELECT文で全ての列に対して
午前が60が以上、午後1＋午後2が120以上、論述が午前、午後1、午後2、
論述の合計得点の3割以上の条件で絞り込んで受験日と受験者IDで昇順で並び替えるSQLで、
合格者を抽出します。
埋込みSQLで全ての列を抽出したい場合、「＊」が使えます
それでカーソル名CRS01をオープンするとカーソルオープンについては完了です。
フェッチ処理から2回目以降のREAD文を見ていく
** 7.FETCH処理
PERFORM FETCH-RTN
UNTIL SW-NOTFOUND = CST-1X.
FETCH-RTNセクションをSW-NOTFOUND = CST-1Xつまりフェッチ文で、
検索完了(NOT FOUND)になるまでループ処理させます。
FETCH-RTNセクションを見ていましょう。
FETCH-RTN SECTION.
*
DISPLAY "FETCH処理".
*
* 試験結果ファイル再OPEN
OPEN INPUT 試験結果ファイル.
IF "00" = 結果状態
THEN
CONTINUE
ELSE
CLOSE 試験結果ファイル
CLOSE 合格者結果ファイル
DISPLAY "オープンエラー。"
PERFORM PROEND
END-IF.
*
EXEC SQL
FETCH CRS01
INTO :WK-SHIKENKEKKA
END-EXEC.
*
*
試験結果ファイルを再オープンさせます。
結果状態でエラーハンドリングの処理を加えて、フェッチ文を実行させます。
この時INTOを改行させないと、opensourceCOBOLではコンパイルは通りますが、
フェッチ処理で無限ループにハマったりする事があるので注意しましょう。
INTOの指定についてSQLで使用するエリアを:をつけて書き連ねる事もできますが、
レベル番号01の項目で書いて簡略化することが出来ます。
それでは本題の2回目以降のREAD文を見ていきましょう。
IF SQLCODE = CST-SQL-NF
THEN
MOVE CST-1X TO SW-NOTFOUND
DISPLAY SQLCODE
ELSE
DISPLAY "受験日:" JUKENBI
"合格者ID:" JUKENSHAID
PERFORM UNTIL 結果状態 NOT = "00"
READ 試験結果ファイル
AT END
CONTINUE
NOT AT END
IF 受験者ID
OF 試験結果レコード =
JUKENSHAID
THEN
MOVE 受験日
OF 試験結果レコード
TO 受験日
OF 合格者結果レコード
ELSE
CONTINUE
END-IF
END-READ
END-PERFORM
MOVE SHIKENKEKKA1
TO 試験結果基本
OF 合格者結果レコード
WRITE 合格者結果レコード
DISPLAY "合格者結果ファイル出力"
ADD 1 TO 合格者件数
CLOSE 試験結果ファイル
END-IF.
DISPLAY "合格者件数" 合格者件数.
*
FETCH-RTN-EX.
EXIT.
*
SQLCODE = CST-SQL-NF(+100)になった時に検索が完了させてフェッチ処理を終了させます。
ELSEの処理では2回目以降の試験結果ファイルを結果状態が00でなくなるまで(AT END)になるまで、
読み込みます。
その時試験結果レコードの受験者IDがSQLで使用するエリアのJUKENSHAIDと条件が合致した時に
試験結果レコードの受験日を合格者結果レコードの受験日に転記します。
なぜ、試験結果レコードの受験日を合格者結果レコードの受験日を単純に転記しないかというと
試験結果レコードの受験日の最終レコードで設定された値を取ってきてします為です。
ここで先ほど実際にプログラムを見ていきましょうのおさらいです。
試験結果ファイルの受験日がYYYYMMDD形式をSQLでDATA型でINSERTするため、
YYYYｰMMｰDDの形式にする。
SQLで使用するエリアのJUKENBIはSQLでDATA型で取り込む為、
YYYY-MM-DDの形式に対して、合格者結果レコードの受験日はYYYYMMDDの形式なので、
単純移送が出来ないのです。
その為、同じ入力ファイルを2回目以降のREAD文を読み込んで、
受験者IDを合致した場合のみ、
試験結果レコードの受験日を合格者結果レコードの受験日を転記しています。
後の項目はSQLで使用するエリアからを合格者結果レコードに転記します。
それで合格者結果レコードに書き込みます。
最後忘れてはいけないのは、試験結果ファイルを一旦クローズすることです。
それは、
FETCH-RTNセクションをSW-NOTFOUND = CST-1Xつまりフェッチ文で、
検索完了(NOT FOUND)になるまでループ処理させます。
後、READ文をAT ENDまで読み終えているためです。
それでフェッチ処理を検索完了まで実行させることが出来ます。
カーソルクローズとファイルクローズを行う。
CLOSE-RTN SECTION.
*
DISPLAY "カーソルクローズ・"
"ファイルクローズ処理".
* カーソルクローズ
EXEC SQL
CLOSE CRS01
END-EXEC.
* ファイルクローズ
CLOSE 試験結果ファイル.
CLOSE 合格者結果ファイル.
*
CLOSE-RTN-EX.
EXIT.
最後にカーソルクローズとファイルクローズを行います。
番外編 合格者ファイルを作成するシェルを実行させる。
#!/bin/bash
#カレントディレクトリを設定する。
cd /home/XXXXX && pwd
# pro2006を実行。(入力ファイルを作成するドライバ)
./pro2006
# dbpro02を実行。
./dbpro02
# 試験結果ファイル
echo '*試験結果ファイル*'
cat /home/XXXXX/DATA/SHIKENKEKKA
# 合格者結果ファイル
echo '*合格者結果ファイル*'
cat /home/XXXXX/DATA/SHIKENKEKKAFINAL
ここで合格者ファイルを作成するシェルを考えます。
pro2006は入力ファイルを作成するドライバ的プログラムです。
ここでは説明を割愛します。
./dbpro02を実行して、試験結果ファイルと合格者結果ファイルをcatコマンドで表示させます。
実行結果を見る。
$ ./pro2006.sh
/home/XXXXX
*PRO2006 START*
出力処理
出力処理
出力処理
出力処理
出力処理
出力処理
終了処理
*PRO2006 END*
*DBPRO02 START*
SQLコード+000000000
DB ALL DELETE END
SQLコード+000000000
SQLコード+000000000
SQLコード+000000000
SQLコード+000000000
SQLコード+000000000
SQLコード+000000000
受験者ID='SW1900'はロールバックされました。
READ END
ロールバック件数01
コミット件数05
DB INSERT END
SQLコード+000000000
SQLコード+000000000
SQLコード+000000000
DB UPDATE END
カーソルオープン処理
FETCH処理
受験日:2023-11-16合格者ID:SW1350
合格者結果ファイル出力
合格者件数01
FETCH処理
受験日:2023-11-19合格者ID:SW1880
合格者結果ファイル出力
合格者件数02
FETCH処理
+000000100
合格者件数02
カーソルクローズ・ファイルクローズ処理
*DBPRO02 END*
*試験結果ファイル*
20231115SW104600860000006800910080
20231116SW135000650053007000000068
20231117SW187700000059005600360056
20231118SW187800700070007000700070
20231119SW188000700070007000900075
20231119SW190000700070007000900075
*合格者結果ファイル*
20231116SW135000650053007000840068
20231119SW188000700070007000900075
FETCH処理のDISPLAYの表示結果が合格者結果ファイルにも反映されているのが分かると思います。
PostgreSQL側も見てみましょう。
> select * from shikenkekka
> where gozen >= 60
> and gogo1 + gogo2 >= 120
> and 0.3 * (gozen + gogo1 + gogo2 + ronjutu) <= ronjutu
> order by jukenbi,jukenshaid;
jukenbi | jukenshaid | gozen | gogo1 | gogo2 | ronjutu | heikinten
------------+------------+-------+-------+-------+---------+-----------
2023-11-16 | SW1350 | 65 | 53 | 70 | 84 | 68
2023-11-19 | SW1880 | 70 | 70 | 70 | 90 | 75
(2 行)
カーソルで指定した時のSELECT文が合格者結果ファイルと一致している事が
お分かり頂けたと思います。
最後に
入力ファイルのREAD文を複数回AT ENDまで読む方法が理解して頂けたと思います。
特に今回説明を割愛しましたが、入力ファイルを作成して、
SQLにINSERTで書き込んで、フェッチ処理で今回合格者を抽出する形でしたが条件を件数分抽出して、
それを基に出力ファイルに書き込むところが出来ます。
最後までご覧いただきありがとうございました。