FORALLとBULK COLLECT

バルクバインディング(Bulk Binding)は、PL/SQLとSQLの間でデータを一括転送する仕組みであり、行ごとのFETCH、INSERT、UPDATE、DELETEに伴うオーバーヘッドを削減できます。OceanBaseのOracleモードでは、2つのコア構文がサポートされています：FORALLステートメント（DMLの一括実行）とBULK COLLECT句（クエリ結果をコレクションに一括取得するために使用されます）。一度の操作で複数行を扱う場合、パフォーマンスが大幅に向上します。

使用テーブル

以下の例はすべて、同じテーブルに基づいています。各例を実行する前に、まずテーブル作成ステートメントを実行してください。

obclient> CREATE TABLE employee_info (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER
);

FORALLステートメント

FORALLは、変数を一括してバインドしてDMLを実行するもので、SQLエンジンの配列バインディングに依存しており、FORループで1件ずつ実行するよりも効率的です。

設定要件

パラメータ ob_enable_batched_multi_statement を true に設定します。false の場合は FOR ループ実行にダウングレードされます。

使用上の制限

以下の場合は FOR ループ実行にダウングレードされます：

• 動的SQLで IN OUT を使用して変数をバインドした場合。
• 動的SQLがサポートしていないDML（INSERT、UPDATE、DELETE、MERGE INTO）の場合。
• 一部の不正なインデックス使用に対するエラーメッセージがOracleと異なる場合。

構文

FORALL index IN { lower_bound .. upper_bound
                | INDICES OF collection [ BETWEEN lower_bound AND upper_bound ]
                | VALUES OF index_collection }
  [ SAVE EXCEPTIONS ]
  dml_statement

使用例

• 一括挿入

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE emp_name_array IS TABLE OF VARCHAR2(50);
      TYPE salary_array IS TABLE OF NUMBER;
      emp_ids emp_id_array := emp_id_array(1, 2, 3, 4, 5);
      emp_names emp_name_array := emp_name_array('Alice', 'Bob', 'Charlie', 'David', 'Eve');
      salaries salary_array := salary_array(5000, 6000, 7000, 8000, 9000);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT
          INSERT INTO employee_info VALUES (emp_ids(i), emp_names(i), salaries(i));
      DBMS_OUTPUT.PUT_LINE('合計 ' || SQL%ROWCOUNT || ' 条を挿入しました');
  END;
  /
  

• 一括更新（SQL%BULK_ROWCOUNT(i) を使用して、i 回目の DML が影響した行数を確認できます）

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE salary_array IS TABLE OF NUMBER;
      emp_ids emp_id_array := emp_id_array(1, 2, 3);
      new_salaries salary_array := salary_array(5500, 6500, 7500);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT
          UPDATE employee_info SET salary = new_salaries(i) WHERE emp_id = emp_ids(i);
      DBMS_OUTPUT.PUT_LINE('合計 ' || SQL%ROWCOUNT || ' 条を更新しました');
  END;
  /
  

• 一括削除

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      emp_ids emp_id_array := emp_id_array(1, 2, 3);
  BEGIN
      FORALL i IN emp_ids.FIRST..emp_ids.LAST
          DELETE FROM employee_info WHERE emp_id = emp_ids(i);
      DBMS_OUTPUT.PUT_LINE('合計 ' || SQL%ROWCOUNT || ' 条を削除しました');
  END;
  /
  

• INDICES OF（疎集合）：集合に空要素がある場合、値があるインデックスに対してのみ DML を実行します。

  obclient> DECLARE
      TYPE num_tab IS TABLE OF NUMBER;
      v_tb num_tab := num_tab(10, 20, 30);
      v_int NUMBER := 1;
  BEGIN
      v_tb.DELETE(2);  -- 疎集合：インデックス 1、3 のみ保持
      FORALL i IN INDICES OF v_tb BETWEEN 1 AND 3
          INSERT INTO employee_info (emp_id, emp_name, salary) VALUES (v_tb(i), 'X', v_int);
      DBMS_OUTPUT.PUT_LINE('合計 ' || SQL%ROWCOUNT || ' 条を挿入しました');
  END;
  /
  

• SAVE EXCEPTIONS：一部の DML が失敗した場合でも実行を続け、終了後に SQL%BULK_EXCEPTIONS を使用して失敗したインデックスとエラーコードを確認できます。

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE emp_name_array IS TABLE OF VARCHAR2(50);
      emp_ids emp_id_array := emp_id_array(1, 2, 3, 4, 5);
      emp_names emp_name_array := emp_name_array('A', 'B', 'C', 'D', 'E');
      dml_errors EXCEPTION;
      PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT SAVE EXCEPTIONS
          INSERT INTO employee_info VALUES (emp_ids(i), emp_names(i), 5000);
  EXCEPTION
      WHEN dml_errors THEN
          FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE('失敗したインデックス: ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ', エラーコード: ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
          END LOOP;
  END;
  /
  

• 動的 SQL 内の FORALL：動的ブロック内では、集合を自ら宣言する必要があり、外部変数を直接参照することはできません。

  obclient> BEGIN
      EXECUTE IMMEDIATE 'DECLARE TYPE my_type IS TABLE OF VARCHAR2(10); var my_type := my_type(''1'', ''2'', ''3''); BEGIN FORALL i IN 1..var.COUNT INSERT INTO employee_info(emp_id, emp_name, salary) VALUES(var(i), ''X'', 1000); END;';
      DBMS_OUTPUT.PUT_LINE('一括挿入が完了しました');
  END;
  /
  

  注意

  動的 SQL 内の FORALL は IN OUT バインド変数をサポートしていません。

BULK COLLECT句

BULK COLLECTは、SQLが返す複数行を一度にPL/SQLコレクションに取り込むもので、SELECT INTO、FETCH、RETURNING INTOに使用できます。

BULK COLLECTの使用例

• SELECT INTO + BULK COLLECT：複数列をバッチで複数のコレクションに格納します。

  obclient> DECLARE
      TYPE num_tab IS TABLE OF employee_info.emp_id%TYPE;
      TYPE name_tab IS TABLE OF employee_info.emp_name%TYPE;
      v_ids   num_tab;
      v_names name_tab;
  BEGIN
      SELECT emp_id, emp_name BULK COLLECT INTO v_ids, v_names FROM employee_info ORDER BY emp_id;
      FOR i IN 1..v_ids.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': ' || v_names(i));
      END LOOP;
  END;
  /
  

• FETCH + BULK COLLECT：カーソルが一度に複数行をコレクションに取り込みます。

  obclient> DECLARE
      CURSOR c IS SELECT emp_id, emp_name, salary FROM employee_info ORDER BY emp_id;
      TYPE emp_tab IS TABLE OF c%ROWTYPE;
      v_emps emp_tab;
  BEGIN
      OPEN c;
      FETCH c BULK COLLECT INTO v_emps;
      CLOSE c;
      FOR i IN 1..v_emps.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(v_emps(i).emp_id || ' ' || v_emps(i).emp_name || ' ' || v_emps(i).salary);
      END LOOP;
  END;
  /
  

• RETURNING INTO + BULK COLLECT：DMLのバッチ処理で返された列をコレクションに書き込みます。

  obclient> DECLARE
      TYPE id_tab IS TABLE OF employee_info.emp_id%TYPE;
      v_ids id_tab;
  BEGIN
      DELETE FROM employee_info WHERE salary < 6000 RETURNING emp_id BULK COLLECT INTO v_ids;
      DBMS_OUTPUT.PUT_LINE('Deleted ' || v_ids.COUNT || ' rows.');
  END;
  /
  

FORALLとBULK COLLECTの併用

まず BULK COLLECT でキー値をコレクションに取り込み、その後 FORALL でコレクションごとにDMLをバッチ実行します。

obclient> DECLARE
    TYPE id_tab IS TABLE OF employee_info.emp_id%TYPE;
    v_emp_ids id_tab;
BEGIN
    SELECT emp_id BULK COLLECT INTO v_emp_ids FROM employee_info WHERE salary < 8000;
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employee_info SET salary = salary * 1.1 WHERE emp_id = v_emp_ids(i);
    DBMS_OUTPUT.PUT_LINE('合計更新 ' || SQL%ROWCOUNT || ' 条');
END;
/