一括INSERTは、埋め込みSQL(ECOBPG)で、複数行のデータを一括挿入する機能です。
INSERT文のVALUES句にデータを格納した配列ホスト変数を指定することで、配列の各要素のデータを一括で挿入できます。INSERT文の直前にFOR句で挿入回数を指定して本機能を利用します。
記述形式
EXEC SQL [ AT connection ] [ FOR {number_of_rows|ARRAY_SIZE}]
INSERT INTO table_name [ ( column_name [, ...] ) ]
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...]
INTO output_host_var [ [ INDICATOR ] indicator_var ] [, ...]] END-EXEC
FOR句にはnumber_of_rowsまたはARRAY_SIZEを使って、挿入する回数を指定します。FOR句はINSERT文にのみ指定可能であり、他の更新文には指定できません。
指定された回数だけ、挿入処理が実行されます。ただし、1の場合には、アプリケーションの実行時にFOR句が省略されたものとみなします。この場合は、PostgreSQL文書のINSERTの仕様に従います。
FOR句は、整数型ホスト変数またはリテラルで指定します。
配列のすべての要素をテーブルに挿入する場合は、ARRAY_SIZEを指定します。ARRAY_SIZEを指定する場
合には、expressionに1個以上の配列を指定してください。
expressionに2個以上の配列を指定した場合には、ARRAY_SIZEは配列の中の要素数が最小の配列の要素数
とみなされます。
number_of_rowsまたはARRAY_SIZEは、expression、output_host_var、indicator_valで指定されたすべての配列のうちの最小の要素数を超える値でなければなりません。
FOR句の指定例を以下に示します。
01 NUMBER-OF-ROWS PIC S9(9) COMP VALUE 10. 01 GROUP-ITEM. 05 ID1 PIC S9(9) OCCURS 25. 05 NAME PIC X(10) OCCURS 25. * will process 10 rows EXEC SQL FOR :NUMBER-OF-ROWS INSERT INTO prod (name, id) VALUES (:NAME, :ID1) END-EXEC * will process 25 rows EXEC SQL FOR ARRAY_SIZE INSERT INTO prod (name, id) VALUES (:NAME, :ID1) END-EXEC
テーブルに挿入する値を指定します。配列ホスト変数、ホスト変数定数、文字列、ポインター変数を指定できます。構造体型の配列やポインター変数の配列は指定できません。
ポインター変数とARRAY_SIZEを同時に使用しないでください。ポインター変数が指す領域にある要素数を判断できないためです。
queryが返却する行数は1行でなければなりません。2行以上返却された場合にはエラーになります。ARRAY_SIZEと同時には使用できません。
配列ホスト変数、またはポインター変数でなければなりません。
注意
一括INSERTでは、WITH句による副問い合わせの指定はできません。
エラーが発生した場合には、一括INSERTのすべての処置がロールバックされるので、一行も挿入されていない状態になります。ただし、RETURNING句を使用し、かつ、挿入に成功した後の行の取得でエラーがあった場合には、挿入処理はロールバックされません。
エラーメッセージ
一括INSERT使用時にエラーとなった場合、以下のメッセージが出力されます。
FOR句の値は正の整数でなければなりません
number_of_rowsに、0以下の値が指定されています。
number_of_rowsには、1以上の値を指定してください。
FOR句にARRAY_SIZEを指定する場合は、配列ホスト変数を使用する必要があります
FOR句にARRAY_SIZEが指定されていますが、VALUES句に配列ホスト変数が指定されていません。
ARRAY_SIZEを指定する場合、 VALUES句には、配列ホスト変数を1つ以上指定してください
行番号%dにおいて、SELECT..INTO文が返却する行が多すぎます
INSERT文中の“SELECT ... INTO”で2行以上のデータが返却されています。
number_of_rowsが2以上の場合、INSERT文中の“SELECT ... INTO”で返却可能な行の最大数は、1行です。
注意事項
一括INSERT使用時の注意事項を説明します。
VALUES句には、構造体型の配列は指定できません。
VALUES句には、ポインター変数の配列は指定できません。
ECOBPGは、一つのINSERT文にWITH句の使用をサポートします。一括INSERTでは、WITH句は使用できません。
使用例
一括INSERTの使用例を示します。
01 GROUP-ITEM. 05 IN-F1 PIC S9(9) OCCURS 4. MOVE 1 TO IN-F1(1) MOVE 2 TO IN-F1(2) MOVE 3 TO IN-F1(3) MOVE 4 TO IN-F1(4) ... EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:IN-F1) END-EXEC
挿入する行数がFOR句で“3”と指定されているので、配列の各要素のデータのうち、先頭から3要素がテーブルに挿入されます。targetテーブルは、以下のようになります。
f1 ---- 1 2 3 (3 rows)
また、挿入する行数をホスト変数として、FOR句に指定することができます。この場合も、上記と同様の実行結果となります。
01 NUM PIC S9(9) COMP VALUE 3. 01 GROUP-ITEM. 05 IN-F1 PIC S9(9) OCCURS 4. MOVE 1 TO IN-F1(1) MOVE 2 TO IN-F1(2) MOVE 3 TO IN-F1(3) MOVE 4 TO IN-F1(4) ... EXEC SQL FOR :NUM INSERT INTO target (f1) VALUES (:IN-F1) END-EXEC
挿入値として定数値を指定します。
EXEC SQL FOR 3 INSERT INTO target (f1,f2) VALUES (DEFAULT,'hello') END-EXEC
DEFAULTは、列“f1”に“0”を設定とした場合、targetテーブルは、以下のようになります。
f1 | f2 ---+------- 0 | hello 0 | hello 0 | hello (3 rows)
ARRAY_SIZEは、サイズを指定しないで、テーブルに配列のすべての要素を挿入する場合に指定します。
01 GROUP-ITEM. 05 IN-F1 PIC S9(9) OCCURS 4. MOVE 1 TO IN-F1(1) MOVE 2 TO IN-F1(2) MOVE 3 TO IN-F1(3) MOVE 4 TO IN-F1(4) ... EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1) VALUES (:IN-F1) END-EXEC
注意
複数の配列のホスト変数入力値が与えられた場合、挿入する行の数は配列のサイズの最小値と同じになります。以下に例を示します。
01 GROUP-ITEM. 05 IN-F1 PIC S9(9) OCCURS 4. 05 IN-F3 PIC X(10) OCCURS 3. MOVE 1 TO IN-F1(1) MOVE 2 TO IN-F1(2) MOVE 3 TO IN-F1(3) MOVE 4 TO IN-F1(4) MOVE "one" TO IN-F3(1) MOVE "two" TO IN-F3(2) MOVE "three" TO IN-F3(3) ... EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1,f3) VALUES (:IN-F1,:IN-F3) END-EXEC
上記の例では、配列の要素数は“4”と“3”です。しかし、ARRAY_SIZEには、最小の値である“3”が設定されるため、targetテーブルに3行のデータが挿入されます。したがって、テーブルの内容は以下のようになります。
f1 | f3 ---+------- 1 | one 2 | two 3 | three (3 rows)
挿入値に、問い合わせ(SELECT文)の結果を指定します。
EXEC SQL FOR 4 INSERT INTO target(f1) SELECT age FROM source WHERE name LIKE 'foo' END-EXEC
上記の例で、問い合わせの結果、1行が返却されたと仮定すると、targetテーブルには、同じ行が4回挿入されます。
注意
FOR句に“2”以上を指定した場合、問い合わせの結果が2行以上の場合、INSERT文はエラーとなります。
FOR句に“1”を指定した場合、SELECT文で返却されるすべての行がテーブルに挿入されます。
EXEC SQL FOR 1 INSERT INTO target(f1) SELECT age FROM source END-EXEC
この場合、FOR句に指定された“1”は、返却されたすべての行を指します。
一括INSERTでは、通常のINSERT文と同様にRETURNING句を使用できます。以下に例を示します。
01 GROUP-ITEM. 05 IN-F1 PIC S9(9) OCCURS 4. 05 OUT-F1 PIC S9(9) OCCURS 4. MOVE 1 TO IN-F1(1) MOVE 2 TO IN-F1(2) MOVE 3 TO IN-F1(3) MOVE 4 TO IN-F1(4) ... EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:IN-F1) RETURNING f1 INTO :OUT-F1 END-EXEC
上記のINSERT文を実行後、配列out_f1には、“1”、“2”および“3”の3つの要素が格納されます。