6.4.3 一括INSERT

一括INSERTは、埋め込みSQL(ECPG)で、複数行のデータを一括挿入する機能です。

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  ]  [, ...]]

FOR 句

FOR句にはnumber_of_rowsまたはARRAY_SIZEを使って、挿入する回数を指定します。FOR句はINSERT文にのみ指定可能であり、他の更新文には指定できません。

number_of_rows、ARRAY_SIZE

指定された回数だけ、挿入処理が実行されます。ただし、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句の指定例を以下に示します。

int  number_of_rows = 10;
int id[25];
char name[25][10]; 

EXEC SQL FOR :number_of_rows	/* will process 10 rows */
INSERT INTO prod (name, id) VALUES (:name, :id); 

EXEC SQL FOR ARRAY_SIZE   		/* will process 25 rows */ 
INSERT INTO prod (name, id) VALUES (:name, :id);

expression

テーブルに挿入する値を指定します。配列ホスト変数、ホスト変数定数、文字列、ポインター変数を指定できます。構造体型の配列やポインター変数の配列は指定できません。

ポインター変数とARRAY_SIZEを同時に使用しないでください。ポインター変数が指す領域にある要素数を判断できないためです。

query: queryが返却する行数は1行でなければなりません。2行以上返却された場合にはエラーになります。ARRAY_SIZEと同時には使用できません。

output_host_var、indicator_val: 配列ホスト変数、またはポインター変数でなければなりません。

注意

一括INSERTでは、WITH句による副問い合わせの指定はできません。
エラーが発生した場合には、一括INSERTのすべての処置がロールバックされるので、一行も挿入されていない状態になります。ただし、RETURNING句を使用し、かつ、挿入に成功した後の行の取得でエラーがあった場合には、挿入処理はロールバックされません。

エラーメッセージ

一括INSERT使用時にエラーとなった場合、以下のメッセージが出力されます。

number_of_rowsに不当な値が指定されています

エラーメッセージ: FOR句の値は正の整数でなければなりません

原因: number_of_rowsに、0以下の値が指定されています。

対処: number_of_rowsには、1以上の値を指定してください。

ARRAY_SIZEに不当な値が指定されています

エラーメッセージ: FOR句にARRAY_SIZEを指定する場合は、配列ホスト変数を使用する必要があります

原因: FOR句にARRAY_SIZEが指定されていますが、VALUES句に配列ホスト変数が指定されていません。

対処: ARRAY_SIZEを指定する場合、 VALUES句には、配列ホスト変数を1つ以上指定してください

SELECT... INTOで返却される結果が不当です

エラーメッセージ: 行番号%dにおいて、SELECT..INTO文が返却する行が多すぎます

原因: INSERT文中の“SELECT ... INTO”で2行以上のデータが返却されています。

対処: number_of_rowsが2以上の場合、INSERT文中の“SELECT ... INTO”で返却可能な行の最大数は、1行です。

注意事項

一括INSERT使用時の注意事項を説明します。

VALUES句には、構造体型の配列は指定できません。
VALUES句には、ポインター変数の配列は指定できません。
一括INSERTでは、WITH句は使用できません。
埋め込みSQLでは、ポインター変数のサイズを計算しません。複数要素が含まれるポインター変数を使用している場合、number_of_rowsに、要素数以下の値を設定する必要があります。

使用例

一括INSERTの使用例を示します。

基本的な一括INSERT

int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1);

挿入する行数がFOR句で“3”と指定されているので、配列の各要素のデータのうち、先頭から3要素がテーブルに挿入されます。targetテーブルは、以下のようになります。

 f1 
----
  1
  2
  3
(3 rows)

また、挿入する行数をホスト変数として、FOR句に指定することができます。この場合も、上記と同様の実行結果となります。

int num = 3;
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR :num INSERT INTO target (f1) VALUES (:in_f1);

定数値の挿入

挿入値として定数値を指定します。

EXEC SQL FOR 3 INSERT INTO target (f1,f2) VALUES (DEFAULT,'hello');

DEFAULTは、列“f1”に“0”を設定とした場合、targetテーブルは、以下のようになります。

f1 |  f2   
---+-------
 0 | hello
 0 | hello
 0 | hello
(3 rows)

ARRAY_SIZEを指定する

ARRAY_SIZEは、配列のすべての要素を挿入する場合に指定します。

	int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1) VALUES (:in_f1);

上記の例では、targetテーブルに4行のデータが挿入されます。

VALUES句に、複数の配列のホスト変数が指定された場合、要素数が最も小さい配列の要素数が指定されたものとして、その行数分の値が挿入されます。以下に例を示します。

int in_f1[4] = {1,2,3,4};
char in_f3[3][10] = {"one", "two", "three"};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1,f3) VALUES (:in_f1,:in_f3);

上記の例では、配列の要素数は“4”と“3”ですが、ARRAY_SIZEには、最小の値である“3”が設定されるため、targetテーブルに3行のデータが挿入されます。

f1 |  f3   
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

ポインター変数の値を指定する

挿入値に、複数の要素を含むポインター変数の値を指定します。

int *in_pf1 = NULL;
in_pf1 = (int*)malloc(4*sizeof(int));
in_pf1[0]=1; 
in_pf1[1]=2; 
in_pf1[2]=3; 
in_pf1[3]=4; 
...
EXEC SQL FOR 4 INSERT INTO target (f1) values (:in_pf1);

上記の例では、targetテーブルに4行挿入されます。

問い合わせ(SELECT文)の結果を指定する

挿入値に、問い合わせ(SELECT文)の結果を指定します。

EXEC SQL FOR 4 INSERT INTO target(f1) SELECT age FROM source WHERE name LIKE 'foo';

上記の例で、問い合わせの結果、1行が返却されたと仮定すると、targetテーブルには、同じ行が4回挿入されます。

FOR句に“2”以上を指定した場合、問い合わせの結果が2行以上の場合、INSERT文はエラーとなります。

FOR句に“1”を指定した場合、SELECT文で返却されるすべての行がテーブルに挿入されます。

EXEC SQL FOR 1 INSERT INTO target(f1) SELECT age FROM source;

この場合、FOR句に指定された“1”は、返却されたすべての行を指します。

RETURNING句を使用する

一括INSERTでは、通常のINSERT文と同様にRETURNING句を使用できます。以下に例を示します。

int out_f1[4];
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1) RETURNING f1 INTO :out_f1;

INSERT文の実行後、配列out_f1には、“1”、“2”および“3”の3つの要素が格納されます。