Top > PLSQL プログラミング入門（１） > バルクインサート

PL/SQL によるバルクインサート処理 Oracle 9i

バルクインサート処理 (BULK INSERT:FORALL 構文) は 1 回のコンテキストスイッチで大量のレコードを処理（バルク処理）することができる。 このコンテキストスイッチ（PL/SQL エンジンと SQL エンジンの間で発生する処理のやり取り） の回数を減らすことによって大量データの処理時のレスポンスを向上することが可能になる。

このバルクインサート例ではバルクフェッチ（取り出し）とバルクインサートに レコード型 の コレクション を 使用している。バルクインサートに対してレコード型を使用できるのは Oracle 9i 以降となる。

Oracle 8i 以前でのバルクインサート処理
Oracle 8i 以前ではレコード型によるバルクインサートには対応していない。 フィールド数が多いとやや面倒になるが、バルクインサート処理自体は Oracle 8i でも記述できるが、単一データ型によるコレクションを複数個用いることで実装することが可能。

バルクインサートに使う表定義

表定義は SQL 入門 のテーブル定義を流用して行なう。さらにインサート処理用に以下の作業を行なう。

CREATE TABLE RIVUS.USER_MASTER_COPY AS SELECT * FROM RIVUS.USER_MASTER WHERE 1=0;
ALTER TABLE RIVUS.USER_MASTER_COPY ADD PRIMARY KEY (USER_ID);

バルクインサートの例 （FOR ALL）

バルクフェッチ を行なった コレクション をバルクインサートする。
バルク処理の途中で発生したエラーは eBulkProcessNotComplete で例外処理し(※)継続処理する 。（SAVE EXCEPTIONS オプションによって終了しない）

(※) 例外処理部の実装は行なっていない。暗黙カーソル SQL%BULK_EXCEPTIONS 配列を使用することで例外の発生した要素番号とエラーコードが取得できる。

  1. CREATE OR REPLACE PROCEDURE RIVUS.BULK_FETCH_INSERT2
  2. IS
  3.   BULK_SIZE CONSTANT PLS_INTEGER := 4;  -- コンスタントでなくても良い
  4.  
  5.   CURSOR cIDName IS
  6.     SELECT * FROM USER_MASTER ORDER BY 1 ;
  7.   TYPE tIDNAMES IS TABLE OF cIDName%ROWTYPE INDEX BY BINARY_INTEGER;
  8.   --  ↑ カーソル定義によるレコード型のコレクション（結合配列）
  9.   vIDName tIDNAMES;
  10.  
  11.   -- エラーハンドラ用 ORA-24381: DML配列にエラーがあります。
  12.   vBulkErrors  PLS_INTEGER := 0;
  13.   eBulkProcessNotComplete EXCEPTION;
  14.   PRAGMA EXCEPTION_INIT(eBulkProcessNotComplete, -24381);
  15. BEGIN
  16. --  再試行とエラーテスト用
  17. --  DELETE FROM USER_MASTER_COPY;
  18. --  COMMIT;
  19.  
  20.   OPEN cIDName;
  21.   LOOP
  22.     FETCH cIDName BULK COLLECT INTO vIDName LIMIT BULK_SIZE;
  23.     -- BULK FETCH 処理
  24.     EXIT WHEN vIDName.COUNT = 0;
  25.     DBMS_OUTPUT.PUT_LINE('バルクインサート対象件数:' || vIDName.COUNT);
  26.     BEGIN
  27.       -- BULK INSERT 処理
  28.       FORALL i in 1..vIDName.COUNT SAVE EXCEPTIONS  -- ← エラーが発生しても継続
  29.         INSERT INTO USER_MASTER_COPY VALUES vIDName(i);
  30.         --   Oracle 9i から使用できる記述 ↑
  31.       COMMIT;
  32.     EXCEPTION
  33.       WHEN eBulkProcessNotComplete THEN
  34.         vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
  35.         -- PROC_BULK_ERROR_HANDLER;
  36.     END;
  37.   END LOOP;
  38.   DBMS_OUTPUT.PUT_LINE('処理件数:' || cIDName%ROWCOUNT);
  39.   DBMS_OUTPUT.PUT_LINE('エラー数:' || vBulkErrors);
  40.   CLOSE cIDName;
  41. END;
  42. /

索引が不連続なコレクションに対するバルクインサート Oracle 10g

FORALL 構文（FOR と ALL にスペースなし）を使用するには連続した索引をもつ コレクション型 であることが条件であったが Oracle 10g からコレクションの一部を DELETE した索引が不連続なコレクションでも索引を再構築せずに バルクインサートが可能になっている。

FORALL index IN INDICES OF collection ... / FORALL index IN VALUES OF collection ... 構文

INDICES OF*1 は索引部を VALUES OF はコンテンツ部を取り出すための述語となり、上のサンプル例のバルク処理部を書き換えて少しアレンジを加えると、以下のようになる。

  1.       .....
  2.       -- BULK INSERT 処理 
  3.       FORALL i in INDICES OF vIDName BETWEEN 5 AND 10 SAVE EXCEPTIONS
  4.       --          ↑↑ IN INDICES OF .. [BETWEEN n AND m] [SAVE EXCEPTIONS]
  5.         INSERT INTO USER_MASTER_COPY VALUES vIDName(i);
  6.       .....
 

関連事項

Table of Contents

バルクインサートの関連トピックス

  1. PL/SQL コレクション型
  2. PL/SQL プログラミング入門（２）
  3. CASE 文
  4. IF 文
  5. PL/SQL から DDL を実行する
  6. PL/SQL による INSERT
  7. PL/SQL による SELECT
  8. PL/SQL ブロック、コメント
  9. カーソル・ループ処理
  10. ストアドプロシージャ、パッケージとは
  11. 二次元配列の定義
  12. 位置表記法と名前表記法
  13. GOTO、NULL、ラベル
  14. RAISE
  15. RAISE_APPLICATION_ERROR
  16. プロシージャのコンパイル
  17. ユーザー定義例外
  18. ローカル・サブプログラム

SHIFT the Oracle 最近のトピックス

  1. 各国語対応可能な祝日、休日一覧を作成する
  2. STANDARD_HASH
  3. CSV 出力を１０倍高速にする方法
  4. V$SESSION（詳細）
  5. DBMS_CRYPTO 使用方法 〜暗号化〜
  6. DBMS_CRYPTO 使用方法 〜ハッシュ〜
日本オラクル
■ 日本オラクル 株式会社
Oracle Japan
■ オラクルマスター資格 （オラクルマスターとは）
Oracle University
■ Oracle のライセンスがわからない…
Oracle Direct （ネットで聞いても最後はここで要確認）
*1 Oracle では 複数の INDEX を INDEXES として用いていることが多いので少々紛らわしいかもしれない。