第9章データベース(SQL)操作

9.1 概要

データベース(SQL)操作機能は、SIT COBOLが規定する埋込みSQL文を使用して、各種データベースにアクセスし、データ操作をすることができる。(*1)

(*1) SIT COBOLは、SQLITE3のデータ操作のみを行うことができる。

データベース

データベースは、二次元の表の集合であり、表は、行および列の集合で構成される。データベース中のデータは、利用者が示す値によって動的に関係付けることができる。利用者は、データの物理的な配置や順序を意識せずに表中のデータを呼び出すことができる。処理はすべて、論理的な利用者インタフェースである表への操作で行う。

表(テーブル)

実表とビュー表を総称して表と呼ぶ。データは実表に格納される。ビュー表は、データ操作を行う際に使用する仮想的な表で、データの実体は存在しない。

埋込みSQL

埋込みSQLは、応用プログラムからデータベースを操作するための記述である。埋込みSQLは、以下の2つから構成される。

埋込みSQL宣言節
埋込みSQL宣言節は、埋込みSQL開始宣言と、埋込みSQL終了宣言によって区切られる節である。埋込みSQL宣言節では、ホスト変数を定義する。
埋込みSQL文
埋込みSQL文は、データベースを操作したり、例外処理を記述するための文である。本章で規定する埋込みSQL文は、SIT COBOLの規定する埋込みSQL文である。埋込みSQL文の動作および記述の詳細については、各データベースの仕様に従う。(*1)

(*1) SIT COBOLは、SQLITE3の仕様に従っている。

ホスト変数

ホスト変数は、データベースと応用プログラムの間で、データを受け渡すために使用するデータ項目である。応用プログラムからデータをデータベースに格納したり、データベースのデータを読み込むために使用する。

ホスト変数の定義には、データベースの1つの列を、基本項目として定義する方法(単一列指定ホスト変数)と、集団項目として定義する方法(複数列指定ホスト変数、複数行指定ホスト変数、表指定ホスト変数)がある(*1)。

(*1) SIT COBOLは、集団項目として定義する方法は未サポートである。(サポート予定あり)

9.2 SQLの使い方例

この節では、埋込SQLがどのようなものかを理解するために、簡単なSQLプログラムを実際に見ていく。

9.2.1 例１：データベースと表の作成

以下のプログラムは、’TESTDB.sqlite3’というデータベースと、住所録という表(テーブル)を作成する例である。

(サンプルプログラム名：埋込みSQL-表の作成.cob)

000000 IDENTIFICATION DIVISION.
000010 PROGRAM-ID. CREATE001.
000020 DATA DIVISION.
000030 WORKING-STORAGE SECTION.
000040 PROCEDURE DIVISION.
000050 MAIN00.
000060* データベース'TESTDB.sqlite'への接続。もし './TESTDB.sqlite3'が
000070* 存在しない場合は、作成される。
000080     EXEC SQL 
000090        CONNECT TO './TESTDB.sqlite3'
000100     END-EXEC.
000110* CONNECTの結果を確認。
000120     IF SQLCODE NOT = 0
000130        DISPLAY "CONNECT:    NG SQLCODE=" SQLCODE
000140        DISPLAY "               SQLMSG= " SQLMSG
000150        STOP RUN
000160     END-IF.
000170* テーブル(表)の作成 
000180     EXEC SQL
000190        CREATE TABLE address_book(
000200            name       char(20),
000210            address    varchar(255))
000220     END-EXEC.
000230* CREATEの結果を確認。
000240     IF SQLCODE NOT = 0
000250        DISPLAY "CREATE:     NG SQLCODE=" SQLCODE
000260        DISPLAY "               SQLMSG= " SQLMSG
000270        STOP RUN
000280     END-IF.
000290* データベースへの接続解除
000300     EXEC SQL
000310        DISCONNECT
000320     END-EXEC.
000330* DISCONNECTの結果を確認
000340     IF SQLCODE NOT = 0
000350        DISPLAY "DISCONNECT: NG SQLCODE=" SQLCODE
000360        DISPLAY "               SQLMSG= " SQLMSG
000370        STOP RUN
000380     END-IF.
000390     STOP RUN.

(1) データベースの接続

データベースへの接続は、CONNECT文によって行う。
下記の000090行目のCONNECT文にてデータベース’./TESTDB.sqlite3’へ接続している。

000060* データベース'TESTDB.sqlite'への接続。もし './TESTDB.sqlite'が
000070* 存在しない場合は、作成される。
000080     EXEC SQL 
000090        CONNECT TO './TESTDB.sqlite3'
000100     END-EXEC.

ここでは、カレントディレクトリ配(*1)下にTESTDB.sqlite3というデータベースがある想定で’./TESTDB.sqlite3’を指定しているが、コメントにもあるように、もし、カレントディレクトリ配下に’TESTDB.sqlite3’というデータベースが存在しない場合には、新たに’TESTDB.sqlite3’というデータベースが作られる。

(*1) カレントディレクトリの既定値は、実行するプログラムの保存されているディレクトリである。

なお、データベースの拡張子を、sqlite3としているのは、データベースの実体がSQLITE3であることを示しているだけであり、特に拡張子には何を指定してもよい。

CONNECT文の実行結果は、SQLCODEという特殊レジスタに設定される。成功した場合には0が、そうでない場合には0以外の値が設定される。
000110行目からのIF文では、SQLCODEの値が0かどうか確認し、0でない場合はDISPLAYでSQLCODEとSQLMSGを出力し、最後にSTOP文によりプログラムを終了している。

000110* CONNECTの結果を確認。
000120     IF SQLCODE NOT = 0
000130        DISPLAY "CONNECT:    NG SQLCODE=" SQLCODE
000140        DISPLAY "               SQLMSG= " SQLMSG
000150        STOP RUN
000160     END-IF.

ここで、SQLMSGは発生したエラーの詳細メッセージが格納される特殊レジスタであり、CONNECT文が失敗した場合にはその理由が表示される。

(2) 表(テーブル)の作成

表の作成は、CREATE文にて行う。
この例では、000190～000210行のCREATE文にて、address_bookという表を作成している。

000170* テーブル(表)の作成 
000180     EXEC SQL
000190        CREATE TABLE address_book(
000200            name       char(20),
000210            address    varchar(255))
000220     END-EXEC.

難しく感じるかもしれないが、ここでサンプルとして作ったのは、名前(name)と住所(adress)を持つ住所録(address_book)である。
name、addressは、それぞれ列名と呼ばれ、それぞれの列の属性は、nameが、20文字のテキスト、addressが、最大255文字の可変長テキストである。

さて、CREATE文が成功したかどうかは、下記部分で確認している。
IF文、DISPLAY文の処理内容はCONNECT文と同じなので説明は省く。(以降も省く)

000230* CREATEの結果を確認。
000240     IF SQLCODE NOT = 0
000250        DISPLAY "CREATE:     NG SQLCODE=" SQLCODE
000260        DISPLAY "               SQLMSG= " SQLMSG
000270        STOP RUN
000280     END-IF.

(3) データベースとの接続解除

データベースとの接続解除は、DISCONNECT文によって行う。
下記では000310行でDISCONNECTを実行している。

000290* データベースへの接続解除
000300     EXEC SQL
000310        DISCONNECT
000320     END-EXEC.

DISCONNECTの結果確認の説明については省く。

(4) 実行結果

このプログラムを実行したあと、sqlite3.exeでTESTDB.sqlite3を参照すると、次のように、address_bookという表が生成されているのがわかる。

>sqlite3.exe TESTDB.sqlite3
sqlite> .tables
address_book
sqlite>

また、このプログラムを2回実行すると、次のようなメッセージが表示される。

CREATE:     NG SQLCODE=-1
               SQLMSG= table address_book already exists

このメッセージを出力したのは下記箇所であり、CREATE文の実行でSQLCODEが0でなかったために出力されている。
SQLMSGの内容から原因は「すでにaddress_bookは作成されている」ためであることがわかる。

000230* CREATEの結果を確認。
000240     IF SQLCODE NOT = 0
000250        DISPLAY "CREATE:     NG SQLCODE=" SQLCODE
000260        DISPLAY "               SQLMSG= " SQLMSG
000270        STOP RUN
000280     END-IF.

なお、SIT COBOLは、データベースが接続された状態でSTOP文が実行された場合、STOP文の中で自動的にDISCONNECT文を実行する。

9.2.2 例２：表へのデータ挿入

次に、例１にて作成した表address_table(住所録)へ、いくつかデータを挿入する例を見ていく。

(サンプルプログラム名：埋込みSQL-データの挿入.cob)

000000 IDENTIFICATION DIVISION.
000010 PROGRAM-ID. INSERT001.
000020 DATA DIVISION.
000030 WORKING-STORAGE SECTION.
000040 01 DATA01.
000050    02  PIC X(20)  VALUE "青山 睦子".
000060    02  PIC X(100) VALUE "大阪府 牧方市".
000070    02  PIC X(20)  VALUE "岩間 年男".
000080    02  PIC X(100) VALUE "東京都 港区".
000090    02  PIC X(20)  VALUE "西山 峰雄".
000100    02  PIC X(100) VALUE "千葉県 長生村".
000110    02  PIC X(20)  VALUE "飯島 裕一".
000120    02  PIC X(100) VALUE "東京都 国分寺市".
000130 01 DATA02 REDEFINES DATA01.
000140    02  OCCURS 4.
000150        03 D-NAME PIC X(20).
000160        03 D-ADDR PIC X(100).
000170 01 I COMP-1.
000180 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000190 01 NAME PIC X(20).
000200 01 ADDR PIC X(100).
000210 EXEC SQL END DECLARE SECTION END-EXEC.
000220 PROCEDURE DIVISION.
000230 MAIN00.
000240* 'TESTDB.sqlite3'への接続
000250     EXEC SQL 
000260        CONNECT TO 'TESTDB.sqlite3'
000270     END-EXEC.
000280* SQLCODEチェック
000290     IF SQLCODE NOT = 0
000300        DISPLAY "CONNECT:    NG SQLCODE=" SQLCODE
000310        DISPLAY "               SQLMSG=" SQLMSG
000320        STOP RUN
000330     END-IF.
000340* 4件のデータを格納
000350     PERFORM VARYING I FROM 1 BY 1 UNTIL I > 4
000360        MOVE D-NAME(I) TO NAME
000370        MOVE D-ADDR(I) TO ADDR   
000380        EXEC SQL
000390          INSERT INTO address_book(name, address)
000400            VALUES (:NAME, :ADDR)
000410        END-EXEC
000420* SQLCODEチェック
000430        IF SQLCODE NOT = 0
000440           DISPLAY "INSERT:     NG SQLCODE=" SQLCODE
000450           DISPLAY "               SQLMSG= " SQLMSG
000460           DISPLAY "               SQLIMAGE=" SQLIMAGE
000470           STOP RUN
000480        END-IF
000490     END-PERFORM.
000500* COMMIT処理
000510     EXEC SQL
000520        COMMIT
000530     END-EXEC.
000540* SQLCODEチェック
000550     IF SQLCODE NOT = 0
000560        DISPLAY "COMMIT:     NG SQLCODE=" SQLCODE
000570        DISPLAY "               SQLMSG= " SQLMSG
000580        STOP RUN
000590     END-IF.
000600* データベース接続解除
000610     EXEC SQL
000620        DISCONNECT
000630     END-EXEC.
000640* SQLCODEチェック
000650     IF SQLCODE NOT = 0
000660        DISPLAY "DISCONNECT: NG SQLCODE=" SQLCODE
000670        DISPLAY "               SQLMSG= " SQLMSG
000680        STOP RUN
000690     END-IF.
000700     STOP RUN.

(1) データベースへの接続

まずは、データベース’TESTDB.sqlite3’へ接続をする。処理およびSQLCODEチェックなどは例１と同じなので省略する。

000240* 'TESTDB.sqlite3'への接続
000250     EXEC SQL 
000260        CONNECT TO 'TESTDB.sqlite3'
000270     END-EXEC.

(2) データの挿入

以下の処理では、4件のデータをaddress_bookに挿入している。

000340* 4件のデータを格納
000350     PERFORM VARYING I FROM 1 BY 1 UNTIL I > 4
000360        MOVE D-NAME(I) TO NAME
000370        MOVE D-ADDR(I) TO ADDR   
000380        EXEC SQL
000390          INSERT INTO address_book(name, address)
000400            VALUES (:NAME, :ADDR)
000410        END-EXEC
000420* SQLCODEチェック
000430        IF SQLCODE NOT = 0
000440           DISPLAY "INSERT:     NG SQLCODE=" SQLCODE
000450           DISPLAY "               SQLMSG= " SQLMSG
000460           DISPLAY "               SQLIMAGE=" SQLIMAGE
000470           STOP RUN
000480        END-IF
000490     END-PERFORM.

まず、000350行のPERFORM文で、Iを1から4まで1ずつ変化させてループ構造を作っている。

000360～000370行の MOVE文では、D-NAME(I)、D-ADDR(I)は、DATA01に記述されている、“青山睦子”、“大阪府牧方市”などの値を参照しており、それらの値が、それぞれ、NAME、ADDRに転記されている。

000390～000400行のINSERT文において、:NAMEや :ADDR という書き方があるが、これらは、それぞれ、データ名NAME、ADDRの値を示している。すなわち、例えば１回目のループではSQL文は次のようになる。

INSERT INTO address_book(name, address) VALUES('青山 睦子', '大阪府 牧方市')

このINSERT文が実行されると、name列が’青山睦子’、address列が’大阪府牧方市’であるデータが１つ address_bookに挿入される。

ここで、NAMEやADDRは、データ部で以下のように定義されている。

000180 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000190 01 NAME PIC X(20).
000200 01 ADDR PIC X(100).
000210 EXEC SQL END DECLARE SECTION END-EXEC.

’BEGIN DECLARE’から’END DECLARE’で囲まれた範囲に定義されているデータを「ホスト変数」と呼ぶ。ホスト変数は、COBOLプログラムでも使用でき、またSQL文の中でも参照できるデータである。

なお、SIT COBOLでは、特に’BEGIN DECLARE’、’END DECLARE’の範囲で定義されていないデータ名もすべてホスト変数として扱うことができる。

さて、000420～000480行は、SQLCODEのチェックであるので詳細説明は省略するが、000460行にでてきているSQLIMAGE特殊レジスタは、最後に実行したSQL文イメージが格納されている。従って、このINSERT文の例のように、ホスト変数の値が展開されたあとのSQL文イメージがどのようになっているかを確認するには、このSQLIMAGEの値を確認すると便利である。

(3) コミット処理

INSERT文でデータを挿入したが、それだけでは、データベースに反映されず、実更新するためにはCOMMIT文の実行が不可欠である。

000500* COMMIT処理
000510     EXEC SQL
000520        COMMIT
000530     END-EXEC.

このプログラムでは、4件挿入したあとにコミットをしたが、もちろん、1件1件挿入するごとにコミットしてもよい。

(4) データベースの接続解除

最後にデータベースの接続を解除する。

000600* データベース接続解除
000610     EXEC SQL
000620        DISCONNECT
000630     END-EXEC.

(5) プログラムの実行結果

このプログラムを実行後、address_bookのデータを参照すると次のようになっており、4件のデータが格納されているのがわかる。

>sqlite3.exe TESTDB.sqlite3
sqlite> select * from address_book;
青山 睦子|大阪府 牧方市
岩間 年男|東京都 港区
西山 峰雄|千葉県 長生村
飯島 裕一|東京都 国分寺市
sqlite>

さて、ここで、このプログラムを再実行するとどうなるであろうか？
重複エラーが出そうな気がするが、実は同じデータが追加で書き込まれる。

>sqlite3.exe TESTDB.sqlite3
sqlite> select * from address_book;
青山 睦子|大阪府 牧方市
岩間 年男|東京都 港区
西山 峰雄|千葉県 長生村
飯島 裕一|東京都 国分寺市
青山 睦子|大阪府 牧方市
岩間 年男|東京都 港区
西山 峰雄|千葉県 長生村
飯島 裕一|東京都 国分寺市
sqlite>

これは、name列, address列とも、制約を加えていないためであり、もしname列の値の重複を許さないようにしたかったら、CREATE文において、name列の定義に、UNIQUE制約を指定すればよい。

EXEC SQL
     CREATE TABLE address_book(
        name       char(20) UNIQUE,
        address    varchar(255))
END-EXEC.

UNIQUE制約を加えると、name列に同じ値のデータを挿入するようなSQL文は失敗する。

9.2.3 例３：埋込み例外宣言

さて、例１、例２では、SQL文を実行するたびに、SQLCODEの値を参照してエラーとなっているかどうか確認してきたが、実はもっと効率的に確認する方法がある。それは「埋込み例外宣言」を使う方法である。

早速、プログラムを見ていく。
下記のプログラムは例２のプログラムを「埋込み例外宣言」を使って書き直したものである。

(サンプルプログラム名：埋込みSQL-例外宣言.cob)

000000 IDENTIFICATION DIVISION.
000010 PROGRAM-ID. INSERT001.
000020 DATA DIVISION.
000030 WORKING-STORAGE SECTION.
000040 01 DATA01.
000050    02  PIC X(20)  VALUE "青山 睦子".
000060    02  PIC X(100) VALUE "大阪府 牧方市".
000070    02  PIC X(20)  VALUE "岩間 年男".
000080    02  PIC X(100) VALUE "東京都 港区".
000090    02  PIC X(20)  VALUE "西山 峰雄".
000100    02  PIC X(100) VALUE "千葉県 長生村".
000110    02  PIC X(20)  VALUE "飯島 裕一".
000120    02  PIC X(100) VALUE "東京都 国分寺市".
000130 01 DATA02 REDEFINES DATA01.
000140    02  OCCURS 4.
000150        03 D-NAME PIC X(20).
000160        03 D-ADDR PIC X(100).
000170 01 I COMP-1.
000180 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000190 01 NAME PIC X(20).
000200 01 ADDR PIC X(100).
000210 EXEC SQL END DECLARE SECTION END-EXEC.
000220 PROCEDURE DIVISION.
000230 MAIN00.
000240     EXEC SQL
000250        WHENEVER SQLERROR GOTO :SQL-ERROR
000260     END-EXEC.
000270* 'TESTDB.sqlite3'への接続
000280     EXEC SQL 
000290        CONNECT TO 'TESTDB.sqlite3'
000300     END-EXEC.
000310* 4件のデータを格納
000320     PERFORM VARYING I FROM 1 BY 1 UNTIL I > 4
000330        MOVE D-NAME(I) TO NAME
000340        MOVE D-ADDR(I) TO ADDR   
000350        EXEC SQL
000360          INSERT INTO address_book(name, address)
000370            VALUES (:NAME, :ADDR)
000380        END-EXEC
000390     END-PERFORM.
000400* COMMIT処理
000410     EXEC SQL
000420        COMMIT
000430     END-EXEC.
000440* データベース接続解除
000450     EXEC SQL
000460        DISCONNECT
000470     END-EXEC.
000480     STOP RUN.
000490 SQL-ERROR.
000500     DISPLAY "SQLCODE = " SQLCODE.
000510     DISPLAY "SQLMSG=   " SQLMSG.
000520     DISPLAY "SQLIMAGE= " SQLIMAGE.
000530     STOP RUN.

(1) 埋込み例外宣言の追加

このプログラムを例２のプログラムと比較すると、各SQL文の実行直後にSQLCODEが0でないかを確認するIF文がなくなっていると同時に、以下の埋込みSQL文が追加されているのがわかる。

000240     EXEC SQL
000250        WHENEVER SQLERROR GOTO :SQL-ERROR
000260     END-EXEC.

このWHENEVER文が「埋込み例外宣言」そのものであり、各SQL文でSQLCODEが0以外となったときの処理を宣言している。
ここでは、’GOTO :SQL-ERROR’と書かれており、SQLCODEが0以外となったときは、SQL-ERROR手続きに制御を移すことを宣言している。

SQL-ERROR手続きは下記のとおりであり、DISPLAY文でSQLCODE、SQLMSG、SQLIMAGEを出力してSTOP文で終了するだけの段落である。

000490 SQL-ERROR.
000500     DISPLAY "SQLCODE = " SQLCODE.
000510     DISPLAY "SQLMSG=   " SQLMSG.
000520     DISPLAY "SQLIMAGE= " SQLIMAGE.
000530     STOP RUN.

なお、埋込み例外宣言は、必ず手続き部に記述する必要がある。
また、その宣言が有効なのは、次の”WHENEVER SQLERROR”の例外宣言が現れるまでである(現れなければ、プログラムの最後の文までである)。

’WHENEVER SQLERROR’以外に、’WHENEVER NOT FOUND’という指定もある。これは、SQL文で検索結果が見つからなかった場合の処理を宣言するものである。詳しくは”埋込み例外宣言”を参照されたい。

(2) プログラムの実行結果

このプログラムをそのまま実行すると、4件のデータが追加されるだけである。
埋込み例外宣言の機能を確認したいので、カレントディレクトリにある”TESTDB.sqlite3”を、別名(例えば、“_TESTDB.sqlite3”)にしてから、このプログラムを実行してみる。

すると、最初のCONNECT文は成功する(“TESTDB.sqlite3”が存在しないので新たに”TESTDB.sqlite3”が作成される)が、その後、CREATE文でaddress_bookを作成せずに、INSERT文を実行するので、INSERT文は失敗し、制御がSQL-ERROR段落に移る。
SQL-ERROR段落では、DISPLAY文により、次のメッセージが表示される。

SQLCODE = -1
SQLMSG=   no such table: address_book         
SQLIMAGE= INSERT INTO address_book ( name , address ) VALUES ( '青山 睦子' , '大阪府 牧方市' )

SQLMSGのメッセージより、address_bookという表が存在しないということがわかる。
また、SQLIMAGEより、例外が発生したのは、最初のデータ(‘青山睦子’)を書き出したINSERT文であることがわかる。

9.2.4 例４：カーソルの利用

次に、単純に、address_bookの内容を全件表示させるだけのプログラムを見ていく。
WHENEVER文、CONNECT文、DISCONNECT文の説明は省く。

(サンプルプログラム名：埋込みSQL-カーソル宣言.cob)

000000 IDENTIFICATION DIVISION.
000010 PROGRAM-ID. CURSOR001.
000020 DATA DIVISION.
000030 WORKING-STORAGE SECTION.
000040 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000050 01 NAME PIC X(20).
000060 01 ADDR PIC X(100).
000070 EXEC SQL END DECLARE SECTION END-EXEC.
000080 PROCEDURE DIVISION.
000090 MAIN00.
000100     EXEC SQL
000110        WHENEVER SQLERROR GOTO :SQL-ERROR
000120     END-EXEC.
000130* 'TESTDB.sqlite3'への接続
000140     EXEC SQL 
000150        CONNECT TO 'TESTDB.sqlite3'
000160     END-EXEC.
000170* カーソルの宣言
000180     EXEC SQL
000190        DECLARE C1 CURSOR FOR
000200        SELECT name, address from address_book
000210     END-EXEC.
000220* カーソルのオープン
000230     EXEC SQL
000240        OPEN C1
000250     END-EXEC.
000260* データの読み出し
000270     PERFORM FOREVER
000280        EXEC SQL
000290          FETCH C1 INTO :NAME, :ADDR     
000300        END-EXEC   
000310        IF SQLCODE = 100  *> AT END
000320           EXIT PERFORM
000330        END-IF
000340        DISPLAY FUNCTION TRIM(NAME) ":" FUNCTION TRIM(ADDR)
000350     END-PERFORM.
000360* カーソルのクローズ
000370     EXEC SQL
000380        CLOSE C1
000390     END-EXEC.
000400* データベース接続解除
000410     EXEC SQL
000420        DISCONNECT
000430     END-EXEC.
000440     STOP RUN.
000450 SQL-ERROR.
000460     DISPLAY "SQLCODE = " SQLCODE.
000470     DISPLAY "SQLMSG=   " SQLMSG.
000480     DISPLAY "SQLIMAGE= " SQLIMAGE.
000490     STOP RUN.

(1) カーソルの宣言

データベースへの接続であるCONNECT文の次に現れるのが次のSQL文である。

000180     EXEC SQL
000190        DECLARE C1 CURSOR FOR
000200        SELECT name, address from address_book
000210     END-EXEC.

これは、名前がC1というカーソルの宣言であり、その宣言内容は、address_bookから、全データを抽出し、name列とaddress列をもつ表を作成し、その表の行位置を示すカーソルC1を作成せよということである。
特に抽出条件等の指定がないので、address_bookをコピーした表が作られることになる。

実は、このカーソル宣言は、宣言するだけであり、どこに書いてもよい。SIT COBOLの場合は、プログラムのIDENTIFICATION DIVISIONの前でもよいし、カーソル宣言より後の、プログラムの一番最後でもよい。

実際に、カーソル宣言で宣言されたカーソルを有効化するのは、OPEN文である。

(2) カーソルのオープン

以下のOPEN文によりカーソルC1が開かれる。

000220* カーソルのオープン
000230     EXEC SQL
000240        OPEN C1
000250     END-EXEC.

カーソルが開かれるというのは、カーソル宣言で宣言された’SELECT name, address from address_book’が実際に実行され、name列とaddress列を持つ表が作られ、カーソルC1の位置がその表の先頭行の直前に位置づいた状態になったと考えてよい。

(3) カーソル位置のデータの読み込み

カーソル位置のデータを読み込むには、FETCH文を使用する。

000260* データの読み出し
000270     PERFORM FOREVER
000280        EXEC SQL
000290          FETCH C1 INTO :NAME, :ADDR     
000300        END-EXEC   
000310        IF SQLCODE = 100  *> AT END
000320           EXIT PERFORM
000330        END-IF
000340        DISPLAY FUNCTION TRIM(NAME) ":" FUNCTION TRIM(ADDR)
000350     END-PERFORM.

上記の例では、まず、000270行のFOREVER指定のPERFORM文で無限ループ構造を作っている。そして、000290行の ’FETCH C1 INTO :NAME, :ADDR’によって、カーソルC1が指す行位置にあるデータを読み込み、その第1列目のデータをNAMEに、第2列列目のデータをADDRに設定している。

FETCH文の結果が成功したかどうかは、000310のIF文で確認している。ここで、SQLCODEが100の場合はもはやFETCHするデータが存在しないことを示しており、その場合は、PERFORM文から抜けるために、000320行で EXIT PERFORMが実行される。
データの読み込みが成功した場合には、000340行目のDISPLAYが実行され、NAMEとADDRが表示される。
ここで、FUNCTION TRIM()は、引数の前後の空白を削除した文字列を返す組込み関数である。

(4) プログラムの実行結果

例２、例３のプログラム実行直後に、このプログラムを実行すると、下記のような内容が表示される。

青山 睦子:大阪府 牧方市
岩間 年男:東京都 港区
西山 峰雄:千葉県 長生村
飯島 裕一:東京都 国分寺市

これは、例２、例３で挿入されたデータそのものであり、address_bookの内容がすべて読み込めたことがわかる。

9.2.5 例５：データの検索

例６では、address_tableのすべてのデータを取得したが、今度はある特定の条件を持つデータのみを取得する例を見てみる。

下記は、住所(address)の中に’東京’という文字を含むもののみを取得する例である。

(サンプルプログラム名：埋込SQL-データの検索.cob)

000000 IDENTIFICATION DIVISION.
000010 PROGRAM-ID. CURSOR002.
000020 DATA DIVISION.
000030 WORKING-STORAGE SECTION.
000040 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000050 01 NAME PIC X(20).
000060 01 ADDR PIC X(100).
000070 EXEC SQL END DECLARE SECTION END-EXEC.
000080 PROCEDURE DIVISION.
000090 MAIN00.
000100     EXEC SQL
000110        WHENEVER SQLERROR GOTO :SQL-ERROR
000120     END-EXEC.
000130* 'TESTDB.sqlite3'への接続
000140     EXEC SQL 
000150        CONNECT TO 'TESTDB.sqlite3'
000160     END-EXEC.
000170* カーソルの宣言
000180     EXEC SQL
000190        DECLARE C1 CURSOR FOR
000200        SELECT name, address from address_book
000210               WHERE address LIKE '%東京%'
000220     END-EXEC.
000230* カーソルのオープン
000240     EXEC SQL
000250        OPEN C1
000260     END-EXEC.
000270* データの読み出し
000280     PERFORM FOREVER
000290        EXEC SQL
000300          FETCH C1 INTO :NAME, :ADDR     
000310        END-EXEC   
000320        IF SQLCODE = 100  *> AT END
000330           EXIT PERFORM
000340        END-IF
000350        DISPLAY FUNCTION TRIM(NAME) ":" FUNCTION TRIM(ADDR)
000360     END-PERFORM.
000370* カーソルのクローズ
000380     EXEC SQL
000390        CLOSE C1
000400     END-EXEC.
000410* データベース接続解除
000420     EXEC SQL
000430        DISCONNECT
000440     END-EXEC.
000450     STOP RUN.
000460 SQL-ERROR.
000470     DISPLAY "SQLCODE = " SQLCODE.
000480     DISPLAY "SQLMSG=   " SQLMSG.
000490     DISPLAY "SQLIMAGE= " SQLIMAGE.
000500     STOP RUN.

(1) カーソル宣言

例４のプログラムと異なるのはカーソル宣言のみである。

000170* カーソルの宣言
000180     EXEC SQL
000190        DECLARE C1 CURSOR FOR
000200        SELECT name, address from address_book
000210               WHERE address LIKE '%東京%'
000220     END-EXEC.

上記の「WHERE address LIKE ‘%東京%’」の書き方は、LIKE述語といい、addressの中に右辺のパターンと一致するものがあるという条件を指定している。

‘%’は0個以上の文字列を意味し、例えば、’ABC%’とすると、ABCという文字から始まる任意の文字列、’%XYZ’とすると、最後がXYZで終わっている任意の文字列、’%HIJ%’は、途中にHIJという文字がある任意の文字列を現す。

ここでは、’%東京%’なので、addressの中に東京という文字列を含むデータのみが取得される。

なお、この例では、LIKE述語によりデータの選択条件を指定したが、選択条件を指定する方法は、たくさんある。詳しくは、“行の選択(SELECT文)” を参照されたい。

(2) プログラムの実行結果

このプログラムを実行すると、住所に’東京’という文字列があるもののみが出力される。

岩間 年男:東京都 港区
飯島 裕一:東京都 国分寺市

9.2.6 データの更新

データの更新は、UPDATE文で行う。
例えば、’西山峰雄’の住所を’北海道札幌市’にするのであれば、次のようにする。

000590     EXEC SQL
000600        UPDATE address_book SET address = '北海道 札幌市'
000610           WHERE name = '西山 峰雄'
000620     END-EXEC.

ホスト変数を使うのであれば次のようになる。

000000     MOVE "北海道 札幌市" TO ADDR.
000000     MOVE "西山 峰雄" TO NAME.
000590     EXEC SQL
000600        UPDATE address_book SET address = :ADDR
000610           WHERE name = :NAME
000620     END-EXEC.

また、FETCHしたデータを更新する場合は、次のようにする。(カーソル名はC1とする)

000000     MOVE "北海道 札幌市" TO ADDR.
000590     EXEC SQL
000600        UPDATE address_book SET address = :ADDR
000610           WHERE CURRENT OF C1
000620     END-EXEC.

9.2.7 データの削除

データの削除は、DELETE文で行う。
例えば、’西山峰雄’のデータを削除するのであれば、次のようにする。

000590     EXEC SQL
000600        DELETE FROM address_book WHERE name = '西山 峰雄'
000610     END-EXEC.

ホスト変数を使うのであれば次のようになる。

000000     MOVE "西山 峰雄" TO NAME.
000590     EXEC SQL
000600        DELETE FROM address_book WHERE name = :NAME
000620     END-EXEC.

また、FETCHしたデータを削除する場合は、次のようにする。(カーソル名はC1とする)

000590     EXEC SQL
000600        DELETE FROM address_book WHERE CURRENT OF C1
000620     END-EXEC.

WHEREを指定しなければ、すべてのデータが削除される。

000590     EXEC SQL
000600        DELETE FROM address_book
000620     END-EXEC.

9.2.8 表の削除

表を削除するには、DROP文を使う。

000000     EXEC SQL
000000        DROP TABLE address_book
000000     END-EXEC.

9.3 埋込みSQLの正書法

ここでは、埋込みSQLの正書法について説明する。

9.3.1 全般規定

埋込みSQL開始宣言、埋込みSQL終了宣言および埋込みSQL文は、SQL先頭子(“EXEC SQL”)とSQL終了子(“END-EXEC”)で囲んで記述しなければならない。
埋込みSQL開始宣言、埋込みSQL終了宣言および埋込みSQL文は、すべてB領域に記述しなければならない。(*1)

(*1) SIT COBOLは、A領域から記述することもできる。

9.3.2 行のつなぎ

埋込みSQL開始宣言、埋込みSQL終了宣言および埋込みSQL文の継続(行のつなぎ)の規則は、COBOLの正書法の規則に準じる。

9.3.3 COBOLの注記行および行内注記

COBOLの注記行、デバッグ行(*1)、行内注記、および空白行は、埋込みSQLの記述中に書くことができる。

(*1) SIT COBOLは、デバッグ行は未サポートである。

次のような行内注記があった場合、すべて注記と解釈される。

000000     *> EXEC SQL
000000     *>     DELETE FROM address_book
000000     *> END-EDEC.

9.4 データ部

9.4.1 埋込みSQL宣言

書き方

EXEC SQL

BEGIN DECLARE SECTION END-EXEC.

[ {ホスト変数定義} … ]

EXEC SQL

END DECLARE SECTION END-EXEC.

一般規則

埋込みSQL宣言節は、データ部の作業場所節または連絡節に記述することができる。
埋込みSQL開始宣言(EXEC SQL BEGIN DECLARE SECTION END-EXEC)と埋込みSQL終了宣言(EXEC SQL END DECLARE SECTION END-EXEC)は省略できない。
埋込みSQL開始宣言と埋込みSQL終了宣言は、対で指定しなければならない。また、入れ子であってはならない。
埋込みSQL開始宣言または埋込みSQL終了宣言をはさんで，データを再定義することはできない。

(※) SIT COBOLは、埋込みSQL開始宣言、埋込みSQL終了宣言をメモとみなす。かつ、データ部の作業場所節、連絡節、ファイル節のいずれのデータもホスト変数として定義されているとみなす。
また、ホスト変数定義の中にCOPY文を記述してもよい。

9.4.2 ホスト変数定義

ホスト変数定義は、ホスト変数の性質を指定する。

書き方

レベル番号ホスト変数名

[ IS EXTERNAL ]

[ IS GLOBAL ]

[ { PICTURE PIC } IS 文字列 ]

[ [ USAGE IS ] { BINARY COMPUTATIONAL COMP COMPUTATIONAL-5 |

COMP-5 COMP-1 COMP-2 DISPLAY PACKED-DECIMAL }]

[ [ SIGN IS ] { LEADING SEPARATE CHARACTER TRAILING } ]

[ OCCURS 整数-1 [ TIMES ]]

各句の詳細については、「データ部」を参照のこと。

9.4.3 ホスト変数の参照

ホスト変数は、埋込みSQLの中でも、一般のCOBOL文の中でも、参照することができる。埋込みSQL文中でホスト変数を参照する場合、コロン(:)をホスト変数の直前に付加しなければならない。たとえば、“A”という名前のホスト変数があれば、埋込みSQL文中では“:A”と記述する。また、A OF Bのように一意参照付けをする場合には、親(B)と子(A)をピリオドで区切って”:B.A”のように記述する。

ホスト変数を一般のCOBOL文の中で参照する場合は、コロンを付加してはならない。

9.4.4 SQLCODE

SQL文の実行によって例外事象が発生した場合、例外事象の内容を示すコードが応用プログラムに通知される。SQLCODEは、このコードを格納するための領域である。利用者は、SQLCODEを参照することにより、例外事象に応じた処理を行うことができる。
SQLCODEは、特殊レジスタとして用意されており、利用者が定義する必要はない。また、その属性は倍精度2進数(COMP-2)である。

9.4.5 SQLMSG

SQL文の実行によって例外事象が発生した場合、例外事象の内容を示すメッセージが応用プログラムに通知される。SQLMSGは、このメッセージを格納するための領域である。利用者は、出力文を利用して、SQLMSGの内容を印字または表示することができる。
SQLMSGは、特殊ジレスタとして用意されており、利用者が定義する必要はない。また、その属性は、PIC X(256) である。

9.4.6 SQLIMAGE

SQL文が実行された場合、実際の埋込みSQLのイメージが、SQLIMAGEに格納される。利用者は、出力文を利用して、SQLIMAGEの内容を印字または表示することができる。
SQLIMAGEは、特殊レジスタとして用意されており、利用者が定義する必要はない。また、その属性は、PIC X(256) である。

9.5 手続き部

9.5.1 埋込みSQLの名前

埋込みSQLの名前には、以下の3種類がある。

表名
カーソル名
列名

表名、カーソル名、列名は、EXEC SQLからEND-EXECの中に出現する。
これらの名前は、ハイフンが含まれないCOBOLの語、あるいは日本語でなければならない。
また、英小文字と英大文字は区別され、等価とみなされない。すなわち、Abc と aBc という表があったとき、これらは別の表として区別される。

表名

表名は、実表の名前である。表名は、データ操作の対象とする表を指定する場合に使用する。表名は、修飾することもできる。表名を修飾する場合は、修飾される表名と修飾する名前を“.”で区切って表現する。

カーソル名

カーソル名は、カーソルに付ける名前である。カーソルは、表の中の1行を特定する行指示子であり、カーソル宣言で定義する。

列名

列名は、表の各列の名前である。列名は、表名で修飾することができる。列名を修飾する場合は、修飾される列名と修飾する名前を“.”で区切って表現する。

9.5.2 カーソル宣言

カーソルを宣言する。

書き方

EXEC SQL

DECLARE カーソル名-1 CURSOR FOR SELECT文

END-EXEC.

構文規則

カーソル宣言は、プログラムの中のデータ部、手続き部を問わず、どこにでも書くことができる。
SELECT文の書き方については、「データの選択(SELECT文)」を参照のこと。

一般規則

カーソル宣言は、カーソルの定義だけが行われる。手続き中に記述されていても、その時点で実行されるわけではない。

書き方例

住所録(address_book)から、住所(address)中に「東京」を含むデータを選択するカーソルcur1を宣言する。

000000     EXEC SQL
000000         DECLARE cur1 CURSOR FOR 
000000           SELECT name, address FROM address_book WHERE address LIKE '%東京%'
000000     END-EXEC.

9.5.3 埋込み例外宣言

埋込み例外宣言は、SQL文に例外事象が発生したときにとる動作を指定する。

書き方

EXEC SQL

WHENEVER { SQLERROR | NOT FOUND } { GOTO : 手続き名 | PERFORM :手続き名 | CONTINUE }

END-EXEC.

一般規則

SQLERRORを指定したとき、埋込み例外宣言は、SQLCODEの値が正常終了でないとき（SQLCODE<0）に実行する文を指定する。
NOT FOUNDを指定したとき、埋込み例外宣言は、SQLCODEの値がデータなしを示すとき(SQLOCDE=100)に実行する文を指定する。
GOTOを指定した場合は、例外が発生したSQL文から「GO TO手続き名」が実行されたように手続き名に制御が移る。
PERFORMを指定した場合は、例外が発生したSQL文から「PERFORM 手続き名」が実行されたように手続き名に制御が移る。
CONTINUEを指定した場合は、例外が発生したSQL文の次の文に制御が移る。
埋込み例外宣言は、原始プログラム上の次の文以降、同一の条件を指定した埋込み例外宣言が現れるか、またはプログラムの終わりに達するまで、その間にあるすべてのSQL文に対して有効である。
埋込み例外宣言の有効範囲は、プログラム単位である。

9.5.4 データベースへの接続(CONNECT文)

データベースに接続をする。

書き方

EXEC SQL

CONNECT TO データベース名-1

END-EXEC.

構文規則

データベース名は、文字定数またはホスト変数で指定する。
データベース名の中は、パスを含んでいてもよい。(例： “tmp.sqlite”)

一般規則

CONNECT文は、データベース名-1 で指定されたデータベース(*1)に接続する。
データベース名-1が存在しない場合には、新たに作成する。ただし、データベース名-1がフォルダ名など、新たに作成できない場合はCONNECT文は失敗する。
すでに接続済のデータベースを指定してはならない。
CONNECT文が成功した場合は、SQLCCODEに0が、失敗した場合には、-1が設定される。

(*1) 実体は、SQLITEデータベースであり、Windows上の１つのファイルに見える。従って、他のファイルと識別するために”testDB.sqlite3”のように拡張子をつけたほうがよい。

書き方例1

カレントフォルダ配下にあるTESTDB.sqliteに接続する。

000000     EXEC SQL 
000000         CONNECT TO 'TESTDB.sqlite'
000000     END-EXEC.

書き方例2

ホスト変数(DB-NAME)を使用して、カレントフォルダ配下にあるTESTDB.sqliteに接続する。

000000     MOVE "TESTDB.sqlite" TO DB-NAME.
000000     EXEC SQL 
000000         CONNECT TO :DB-NAME
000000     END-EXEC.

9.5.5 データベースへの接続解除(DISCONNECT文)

データベースへの接続を解除する。

書き方

EXEC SQL

DISCONNECT TO データベース名-1

END-EXEC.

構文規則

データベース名は、文字定数またはホスト変数で指定する。
データベース名の中は、パスを含んでいてもよい。(例： “tmp.sqlite”)

一般規則

DISCONNECT文は、データベース名-1 で指定されたデータベースの接続を解除する。
データベース名-1は、CONNECT文で接続済みのデータベースでなければならない。
DISCONNECT文が成功した場合は、SQLCCODEに0が、失敗した場合には、-1が設定される。

書き方例1

カレントフォルダ配下にあるTESTDB.sqliteへの接続を解除する。

000000     EXEC SQL 
000000         DISCONNECT TO 'TESTDB.sqlite'
000000     END-EXEC.

書き方例2

ホスト変数(DB-NAME)を使用して、カレントフォルダ配下にあるTESTDB.sqliteへの接続を解除する。

000000     MOVE "TESTDB.sqlite" TO DB-NAME.
000000     EXEC SQL 
000000         DISCONNECT TO :DB-NAME
000000     END-EXEC.

9.5.6 表の作成(CREATE)

データベース上に、表（テーブル）の作成を行う。

書き方

EXEC SQL

CREATE TABLE 表名-1 (

列名-1 [ 属性名-1 ] [ 制約-1 ] …

[ , 列名-2 [ 属性-2 ] [ 制約-2 ]… ]

)

END-EXEC.

構文規則

属性-1, 属性-2 には、次の属性を指定できる。

TEXT（テキスト）：CHAR(n) , VARCHAR(n)も指定可。
NUMERIC（数値全般）： DECIMAL(n, m)も指定可。
INTEGER（整数）：INTも指定可
REAL（小数を含む数値データ）：DOUBLE、FLOATも指定可。
NONE（制約なし、どんなデータも扱える）
日付/時間：DATE, DATETIME, TIMESTAMP

制約-1, 制約-2には、次の制約を指定できる。

UNIQUE: 同値を持てない列であることを指定
NOT NULL：NULL値を持てない列であることを指定
PRIMARY KEY：主キーであることを指定
AUTOINCREMENT：データ挿入時、その列名が持つ最大値に１加えられた値が格納されることを指定
DEFAULT 値-1：データ挿入時、その列名の値が指定がないときの既定値として”値-1”の値を格納することを指定

一般規則

CREATE文は、列名-1, 列名-2 を列として持つ、表名-1という名前の表を作成する。
属性-1、属性-2が省略されていたときの既定値の属性は、NONEである。
CREATE文を実行する前に、CONNECT文が実行されていて成功していなければならない。
表名-1の表は、最後に実行されたCONNECT文に指定されているデータベース上に作成される。
表名、列名、属性は、ホスト変数で与えることもできる。
CREATE文が成功した場合は、SQLCCODEに0が、失敗した場合には、-1が設定される。

書き方例1

住所録(address_book)という表を作成する。列名は、名前(name)と住所(address)で、属性は、それぞれ、char(20), vchar(255)とする。

000000     EXEC SQL
000000         CREATE TABLE address_book(
000000           name       char(20),
000000           address    varchar(255))
000000     END-EXEC.

書き方例2

商品管理という日本語名の表を作成する。列名も日本語で定義する。

000000     EXEC SQL
000000       CREATE TABLE 商品管理 (
000000           商品番号          INTEGER PRIMARY KEY AUTOINCREMENT,
000000           更新日            TIMESTAMP,
000000           商品名            VARCHAR(50),
000000           カテゴリ          VARCHAR(50),
000000           在庫量            INTEGER,
000000           価格（単位千円）  DECIMAL(12, 3)
000000       )
000000     END-EXEC.

9.5.7 表の削除(DROP)

データベースから表を削除する

書き方

EXEC SQL

DROP 表名-1

END-EXEC.

一般規則

DROP文は、表名-1という名前の表を削除する。
DROP文を実行する前に、CONNECT文が実行されていて成功していなければならない。
表名-1の表は、最後に実行されたCONNECT文に指定されているデータベース上から削除される。
表名-1は、ホスト変数で与えることもできる。
DROP文が成功した場合は、SQLCCODEに0が、失敗した場合には、-1が設定される。

書き方例1

address_bookという表を削除する。

000000     EXEC SQL 
000000         DROP address_book
000000     END-EXEC.

書き方例2

ホスト変数(TABLE-NAME)を使用して、address_bookという表を削除する。

000000     MOVE "address_book" TO TABLE-NAME.
000000     EXEC SQL 
000000         DROP :TABLE-NAME
000000     END-EXEC.

9.5.8 データの追加(INSERT)

表にデータを追加する。

書き方

EXEC SQL

INSERT INTO 表名-1 ( 列名-1 [, 列名-2] … )

VALUES ( 値-1 [, 値-2] … )

END-EXEC.

構文規則

列名-nの個数と値-nの個数は同じでなければならない。

一般規則

INSERT文は、表名-1の表に、列名-1, 列名-2, … が、それぞれ、値-1, 値-2, …の値をもつデータを追加する。
INSERT文を実行する前に、CONNECT文が実行されていて成功していなければならない。
表名-1の表は、最後に実行されたCONNECT文に指定されているデータベース上にあるものである。
値-1, 値-2は、文字列、数字のほか、式や、SQLiteの組込み関数、日時関数を指定することもできる。

組み込み関数
MAX(), MIN(), ABS(), LENGTH(), …
日時関数
DATETIME(‘now’ [, ‘localtime’]), DATE(‘20204-01-01’, ‘+7 days’), …

INSERT文が成功した場合は、SQLCCODEに0が、失敗した場合には、-1が設定される。

書き方例

000000     MOVE "鈴木" TO 氏名.
000000     MOVE "東京都ｘｘ区" TO 住所.
000000     EXEC SQL 
000000         INSERT address_book ( name, address )
000000         VALUES ( :氏名, :住所 )
000000     END-EXEC.

9.5.9 カーソルのオープン(OPEN)

カーソル宣言で指定したSELECT文の実行をする。

書き方

EXEC SQL

OPEN カーソル名-1

END-EXEC.

一般規則

OPEN文は、カーソル名-1の宣言文に記載されているSELECT文を実行し、結果を一旦作業用の領域に保存する。
カーソル名-1の宣言文がプログラム中になければならない。
OPEN文を実行する前に、CONNECT文が実行されていて成功していなければならない。
オープン済のカーソルをクローズせずに、再度オープンしても副作用はない。
OPEN文が成功した場合は、SQLCODEに0が、失敗した場合には、-1が設定される。

9.5.10 カーソルのクローズ(CLOSE)

カーソルを閉じる。

書き方

EXEC SQL

CLOSE カーソル名-1

END-EXEC.

一般規則

CLOSE文は、カーソル名-1に関連している作業用の領域に保存されているデータを開放する。
CLOSE文を実行する前に、OPEN文が実行されていて成功していなければならない。
CLOSE文が成功した場合は、SQLCODEに0が、失敗した場合には、-1が設定される。

9.5.11 カーソルからの読み込み(FETCH)

カーソルに関連づいている作業用領域からデータを１件取得する。

書き方

EXEC SQL

FETCH カーソル名-1 INTO :ホスト変数名-1 [ , :ホスト変数名-2 ] …

END-EXEC.

構文規則

ホスト変数の個数は、カーソルに関連づいている作業用領域中のデータの列数と同じでなければならない。

一般規則

FETCH文は、カーソル名-1で指定したカーソルに関連づいている作業用領域からデータを1つ取得する。
FETCH文の直前は、OPEN文もしくはFETCH文が実行され成功していなければならない。
FETCH文が成功したときは、SQLCODEに0が、これ以上データがない場合には、100が、失敗した場合には、-1が設定される。

書き方例

cur1が、住所の中に東京が含まれるデータを選択するカーソルとした場合、選択されたデータから１つのだけデータを読み込む。

000000     EXEC SQL
000000         DECLARE cur1 CURSOR FOR 
000000           SELECT name, address FROM address_book WHERE address LIKE '%東京%'
000000     END-EXEC.
000000     :
000000     EXEC SQL
000000         OPEN cur1
000000     END-EXEC.
000000     :
000000     EXEC SQL
000000         FETCH cur1 INTO :氏名, :住所
000000     END-EXEC.

9.5.12 カーソルに関連付いている行を削除(DELETE文[位置付け])

FETCH文で読み込んだ行を削除する

書き方

EXEC SQL

DELETE FROM 表名-1 WHERE CURRENT OF カーソル名-1

END-EXEC.

一般規則

カーソル名-1に関連付いている作業領域からFETCHした行を削除する。
DELETE文の直前は、FETCH文が実行され成功していなければならない。
DELETE文が成功したときは、SQLCODEに0が、失敗した場合には、-1が設定される。

書き方例

FETCHした行を削除する。

000000     EXEC SQL
000000         FETCH cur1 INTO :氏名, :住所
000000     END-EXEC.
000000     EXEC-SQL
000000         DELETE FROM address_book WHERE CURRENT OF cur1
000000     END-EXEC.

9.5.13 複数の行を削除(DELETE文[探索])

DELETE文(探索)は、探索条件を満たす表の行を削除する。

書き方

EXEC SQL

DELETE FROM 表名-1 [ WHERE 探索条件-1 ]

END-EXEC.

一般規則

DELETE文は、表名-1から、探索条件-1を満たす表の行を削除する。
探索条件を指定した場合、探索条件が真となる行を削除する。
探索条件を省略した場合、表のすべてを削除する。
探索条件は、表の行を削除する前に評価される。
探索条件-1の書き方については、「行の選択(SELECT文)」を参照のこと。
DELETE文が成功したときは、SQLCODEに0が、失敗した場合には、-1が設定される。

書き方例

住所録(address_book)から、住所の中に’東京’を含んでいるデータをすべて削除する。

000000     EXEC SQL
000000       DELETE address_book WHERE address LIKE '%東京%'
000000     END-EXEC.

9.5.14 カーソルに関連付いている行を更新(UPDATE文[位置付け])

FETCH文で読み込んだ行を更新する

書き方

EXEC SQL

UPDATE 表名-1 SET 列名-1 = 値-1 [ , 列名-2 = 値-2 ] …

WHERE CURRENT OF カーソル名-1

END-EXEC.

一般規則

UPDATE文(位置付け)の結果、カーソルの導出元の表の行の値が更新される。
列名-1, 列名-2の値を“＝”の右側に指定されている値-1, 値-2に変更する。
値-1, 値-2の値は、UPDATE文(位置付け)で変更される前の値である。
UPDATE文が成功したときは、SQLCODEに0が、失敗した場合には、-1が設定される。

書き方例

住所録のFETCHした行を更新する。名前(name)のみ更新。

000000     EXEC SQL
000000         FETCH cur1 INTO :氏名, :住所
000000     END-EXEC.
000000     EXEC-SQL
000000         UPDATE FROM address_book SET name = '田中'
000000         WHERE CURRENT OF cur1
000000     END-EXEC.

9.5.15 複数の行を更新(UPDATE文[探索])

UPDATE文(探索)は、探索条件を満たす表の行を更新する。

書き方

EXEC SQL

UPDATE 表名-1 SET 列名-1 = 値-1 [ , 列名-2 = 値-2 ] …

[ WHERE 探索条件-1 ]

END-EXEC.

一般規則

UPDATE文は、表名-1から、探索条件-1を満たす表の行を更新する。
WHERE句を指定した場合、WHERE句の探索条件が真になる行が変更される。
WHERE句を省略した場合、表のすべての行が変更される。
探索条件-1の書き方については、「行の選択(SELECT文)」を参照のこと。
列名-1, 列名-2の値を“＝”の右側に指定されている値-1, 値-2に変更する。
値-1, 値-2の値は、UPDATE文(探索)により変更される前の値である。
UPDATE文が成功したときは、SQLCODEに0が、失敗した場合には、-1が設定される。

書き方例

住所録(address_book)から、住所の中に’東京’を含んでいるデータをすべて更新する(住所をすべて神奈川にする)。

000000     EXEC SQL
000000       UPDATE address_book SET address = '神奈川'
000000       WHERE address LIKE '%東京%'
000000     END-EXEC.

9.5.16 行の選択(SELECT文)

SELECT文には行の選択を行う。カーソル宣言において使用することができる。

書き方

SELECT [ DISTINCT ] 列名-1 [, 列名-2]… FROM 表名-1

[ ORDER BY 列名-3 [ ASC DESC ] ]

[ LIMIT n [ OFFSET m] ]

[ GROUP BY 列名-4]

[ WHERE 探索条件-1]

説明

列名には、次の集合関数等も記載できる。

集合関数：COUNT(*), MAX(列名), MIN(列名), SUM(列名), AVG(列名)
その他の関数：LENGTH(列名), SUBSTRING(列名, 開始位置, 長さ), ROUNDED(列名, 位置), POWER(列名, n)

ORDER BY句は、問い合わせた結果を列名-3の値の昇順(ASC)または降順(DESC)に並べることを指定する。
LIMIT/OFFSET句は、問い合わせた結果のうち、m番目(0オリジン)からn個と範囲を指定する。
GROUP BY句は、列名-4の列で集計等を行うことを指定する。
探索条件-1は、問い合わせる条件を指定する。次のような記述ができ、またそれらをAND/ORで連結させることもできる。

列名-1 { = > >= < <= <> } 列名-2
列名-1 [ NOT ] BETWEEN 値-1 AND 値-2
列名-1 [ NOT ] IN (値-1 [, 値-2 ] …)
列名-1 LIKE ‘%…’ (または、 ‘…%’, ’…%…’など)
列名-1 IS [ NOT ] NULL

文法について詳細な説明は、SQL文の解説書に譲ることとし、ここでは、代表的な機能要素のみを説明する。説明を容易にするため、次のような列名を持つ、従業員表を元に説明を行ってゆく。

000000     EXEC SQL
000000       CREATE TABLE 従業員 (
000000           氏名              TEXT,
000000           給与              INTEGER,
000000           所属              TEXT,
000000           住所              TEXT,
000000           出身都道府県      TEXT
000000       )
000000     END-EXEC.

以下、SELECT文のみを記載する。

(1) 集合関数

COUNT(), MAX(), MIN(), AVG(), SUM() が使用できる。

全従業員数を問い合わせる。

000000       SELECT COUNT(*) FROM 従業員

所属単位の従業員数と、給与の平均を問い合わせる。

000000       SELECT 所属, COUNT(*), AVG(給与) FROM 従業員 GROUP BY 所属

(2) 比較述語

所属が’開発部’である従業員の氏名と出身都道府県を問い合わせる。

000000       SELECT 氏名, 出身都道府県 FROM 従業員 WHERE 所属 = '開発部'

出身県が東京である人の給与の平均より、給与の多い人の氏名と給与を問い合わせる。

000000       SELECT 氏名, 給与 FROM 従業員 WHERE 給与 >
000000           (SELECT AVG(給与) FROM 従業員 WHERE 出身都道府県 = '東京')

(3) BETWEEN述語

給与が20万円から30万円の人の氏名と給与を問い合わせる。

000000       SELECT 氏名, 給与 FROM 従業員 
000000             WHERE 給与 BETWEEN 200000 AND 300000

給与が20万円未満、30万円よい多い人の氏名と給与を問い合わせる。

000000       SELECT 氏名, 給与 FROM 従業員 
000000             WHERE 給与 NOT BETWEEN 200000 AND 300000

(4) IN述語

出身都道府県が大阪でも京都でもない人の氏名と出身都道府県を問い合わせる。

000000       SELECT 氏名, 出身都道府県 FROM 従業員 
000000            WHERE 出身都道府県 NOT IN ('大阪', '京都')

出身都道府県が’東京’である人の所属と同じ所属をもつ人の氏名と所属を問い合わせる。

000000       SELECT 氏名, 所属 FROM 従業員 
000000            WHERE 出身都道府県 
000000                   IN (SELECT 所属 FROM 従業員 WHERE 出身都道府県 = '東京')

(5) LIKE述語

氏名の中に’田’をもつ人の氏名を問い合わせる。

000000       SELECT 氏名 FROM 従業員 WHERE 氏名 LIKE '%田%'

(参考)
‘%’は、0文字以上の文字列を表す。例えば、田から始まる氏名を求めるときは LIKE ’田%’、田で終わる氏名を求めるときは LIKE ‘%田’ と指定する。
また、始まりが’A’で、終わりが’C’の文字列を指定するのであれば、 LIKE ’A%C’と指定する。

’_‘は、任意の1文字を表す。例えば、LIKE’_田’と指定すると、‘中田’、’須田’は一致するが、’萩生田’は一致しない。

なお、‘%’や’_‘を通常の文字として検索したい場合は、ESCAPE文字を付与することができる。例えば、 LIKE’#%ABC%’ ESCAPE ‘#’ とすることで、エスケープ文字’#‘の直後の’%‘は通常文字として扱われ、’%ABCDEF’などに一致させることができる。

(6) NULL述語

所属がNULL値である人の氏名を問い合わせる。

000000       SELECT 氏名 FROM 従業員 WHERE 所属 IS NULL

(7) GROUP BY句

所属ごとに最大の給与を問い合わせる。

000000       SELECT 所属, MAX(給与) FROM 従業員 
000000               GROUP BY 所属

(8) ORDER BY句

所属ごとの人数を、人数の昇順に並べた結果を問い合わせる

000000       SELECT 所属, COUNT(*) FROM 従業員 
000000               GROUP BY 所属 ORDER BY 2 ASC

(参考)
ORDER BY 2 の 2は、結果の列番号を指定している。すなわち、1列名は所属、2列目は人数(COUNT(*))であり、人数の昇順に並べるために、2を指定している。
もし、所属の順であれば、ORDER BY 所属でよい。

昇順は’ASC’、降順は、’DESC’を指定する。

(9) DISTINCT

重複するデータを除外して取得する。

全従業員の給与を問い合わせる。ただし重複値がないようにする。

000000       SELECT DISTINCT 給与 FROM 従業員

(10) LIMIT/OFFSET

LIMIT n OFFSET m と指定することで、m行目からn個のデータを取り出せる。

給与の高い順に並べ、3番目の人から10人分の氏名を問い合わせる。

000000       SELECT 氏名, 給与 FROM 従業員
000000               LIMIT 10 OFFSET 2
000000               ORDER BY 給与 DESC

(参考)
OFFSETは、0オリジンなので、1番目が0、2番目が1、3番目が2である。したがって、’OFFSET 2’は3番目の行を指している。

(11) 限定述語

SIT COBOLは、次のような限定述語は使用することができない。(SQLITE3が未対応のため)

(例) 従業員表の中から所属が’開発’であるすべての人より給与が多い人の氏名と給与を問い合わせる。

000000     SELECT 氏名, 給与 FROM 従業員 WHERE 給与 > ALL
000000        (SELECT 給与 FROM 従業員 WHERE 所属 = '開発')

(例) 従業員表の中から所属が’開発’である人の誰かより給料の少ない人の氏名と給料を問い合わせる。

000000     SELECT 氏名, 給与 FROM 従業員 WHERE 給与 < ANY
000000        (SELECT 給与 FROM 従業員 所属 = '開発')

9.6 動的SQL

SIT COBOLは、PERPARE文、EXECUTE文に代表される動的SQLをサポートしていない。(サポート予定)

第9章 データベース(SQL)操作

9.1 概要

データベース

表(テーブル)

埋込みSQL

ホスト変数

9.2 SQLの使い方例

9.2.1 例１：データベースと表の作成

(1) データベースの接続

(2) 表(テーブル)の作成

(3) データベースとの接続解除

(4) 実行結果

9.2.2 例２：表へのデータ挿入

(1) データベースへの接続

(2) データの挿入

(3) コミット処理

(4) データベースの接続解除

(5) プログラムの実行結果

9.2.3 例３：埋込み例外宣言

(1) 埋込み例外宣言の追加

(2) プログラムの実行結果

9.2.4 例４：カーソルの利用

(1) カーソルの宣言

(2) カーソルのオープン

(3) カーソル位置のデータの読み込み

(4) プログラムの実行結果

9.2.5 例５：データの検索

(1) カーソル宣言

(2) プログラムの実行結果

9.2.6 データの更新

9.2.7 データの削除

9.2.8 表の削除

9.3 埋込みSQLの正書法

9.3.1 全般規定

9.3.2 行のつなぎ

9.3.3 COBOLの注記行および行内注記

9.4 データ部

9.4.1 埋込みSQL宣言

書き方

一般規則

9.4.2 ホスト変数定義

書き方

9.4.3 ホスト変数の参照

9.4.4 SQLCODE

9.4.5 SQLMSG

9.4.6 SQLIMAGE

9.5 手続き部

9.5.1 埋込みSQLの名前

表名

カーソル名

列名

9.5.2 カーソル宣言

書き方

構文規則

一般規則

書き方例

9.5.3 埋込み例外宣言

書き方

一般規則

9.5.4 データベースへの接続(CONNECT文)

書き方

構文規則

一般規則

書き方例1

書き方例2

9.5.5 データベースへの接続解除(DISCONNECT文)

書き方

構文規則

一般規則

書き方例1

書き方例2

9.5.6 表の作成(CREATE)

書き方

構文規則

一般規則

書き方例1

書き方例2

9.5.7 表の削除(DROP)

書き方

一般規則

第9章データベース(SQL)操作