Array DML (大量レコード一括INSERT/UPDATE/DELETE)

ここではArray DML（バルク・バインド）の利用法を説明します。

大量のレコードを一度に更新するのに、ループの内部で1件更新のDML（INSERT文やUPDATE文など）を呼び出すのは、クライアントとOracleサーバ間での通信が何往復も発生してしまい非効率です。

1000件のデータをテーブルに挿入する処理の方法について、以下に3つの方法を比較しますが、DOAでは、Oracle8i以降で利用可能な
Array DML（バルク・バインド）を利用することができます。

1. ループの中で1件挿入のSQLを実行する方法＝最も遅い方法です
2. 1000件分のINSERT文を、一つのPL/SQL無名ブロックに記述して一度に実行する方法
3. Array DML を用いて、一括更新を行う方法（Oracle8i以降で有効）

1.ループの中で1件づつ挿入実行

以下の方法だと、TOracleQuery.Executeが1000回実行され、その都度、Oracleサーバとの通信が発生するため、パフォーマンス上は大変に不利です。大量のデータに対しては避けるべき方法でしょう。

  procedure TForm1.Button1Click(Sender: TObject);
  // OracleQuery1.SQL = 
  //    INSERT INTO TableA
  //    VALUES ( :KEY, :DATA )
  var
    i: integer;
  begin
    for i := 0 to 999 do begin
    with OracleQuery1 do begin
        SetVariable('KEY', KeyValues[i]);
          SetVariable('DATA', DataValues[i]);
        Execute;
      end;
    end;
  end;

2.PL/SQL無名ブロックを使う方法

上記の方法と比べて、Executeは一回ですみますが、型変換などでSQLの組み立てには苦労するはずです。特に、文字列データ中に引用符が含まれる可能性がある場合は、思わぬバグが混入する可能性があり、おすすめできません

また、サーバ上でPL/SQLエンジンからSQLエンジンへの呼び出しが1000回発生するので、ベストのパフォーマンスは得られません

  procedure TForm1.Button1Click(Sender: TObject);

  var
  S: string;
    i: integer;
  begin
    S := 'BEGIN' + #13#10;
    for i := 0 to 999 do begin
      S := S + 'INSERT INTO TableA Values(' + 
                    IntToStr(KeyValues[i]) + ',' + AnsiQuotedString(DataValues[i]) +
                 ');' +#13#10;
    end;
    S := S + 'END;';

  with OracleQuery1 do begin
      SQL.Text := S;
      Execute;
    end;
  end;

3.Array DML(バルク・バインド）の利用

Oracle8i以降では、PL/SQL文中で、FORALLステートメントを利用し、配列（PL/SQLテーブル）からのデータを使ってデータベースを一括更新することができます。（詳しくはPL/SQLリファレンスを、FORALLやバルク・バインドをキーワードに参照してください）

DOAでは、以下のように、変数に配列データを割り当てるだけで、（FORALL句を含むPL/SQLを記述することもなく）簡単に、この機能を使うことができます。

  procedure TForm1.Button1Click(Sender: TObject);
  // OracleQuery1.SQL = 
  //    INSERT INTO TableA
  //    VALUES ( :KEY, :DATA )
  var
  KeyValueArray: Variant;
    DataValueArray: Variant;
    i: integer;
  begin
    KeyValueArray :=  VarArrayCreate([0, 999], varVariant);
    DataValueArray := VarArrayCreate([0, 999], varVariant);

    for i := 0 to 999 do begin
      KeyValueArray[i] := KeyValues[i];
        DataValueArray[i] := DataValues[i];
    end;

  with OracleQuery1 do begin
    SetVariable('KEY', KeyValueArray);
      SetVariable('DATA', DataValueArray);
      Execute;
    end;
  end;

• TOracleQuery
  • SetVariable: Variant配列を変数に割り付けることができます。
  • Execute:
    SetVariableで割り付けられた変数がVariant配列であった場合は、ArrayDMLとして、配列の各要素について同じSQLを実行します
  • OnArrayError イベント:
    ArrayDMLによるデータ更新途中の例外を処理します。エラー個所とエラー内容を検出でき、必要に応じて処理の中断・続行をコントロールできます。
  • ExecuteArray(Index,Count):
    与えられたIndexとCountに基づいて、配列で与えられた複数件のデータ中の一部について、一括更新を行います。（全件について更新する場合はExecuteでよいので、あえてExecuteArrayを使う必要はありません）