PL/SQL プログラミング入門（１） > バルクインサート

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 NUMBER := 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.     DBMS_OUTPUT.PUT_LINE('バルクインサート対象件数:' || vIDName.COUNT);
  25.     BEGIN
  26.       -- BULK INSERT 処理
  27.       FORALL i in 1..vIDName.COUNT SAVE EXCEPTIONS  -- ← エラーが発生しても継続
  28.         INSERT INTO USER_MASTER_COPY VALUES vIDName(i);
  29.         --   Oracle 9i から使用できる記述 ↑
  30.       COMMIT;
  31.     EXCEPTION
  32.       WHEN eBulkProcessNotComplete THEN
  33.         vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
  34.         -- PROC_BULK_ERROR_HANDLER;
  35.     END;
  36.     EXIT WHEN cIDName%NOTFOUND;
  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. CASE 文
  3. FOR、WHILE、LOOP
  4. PL/SQL から DDL を実行する
  5. PL/SQL の例外処理
  6. PL/SQL ブロック、コメント
  7. カーソル・ループ処理
  8. ストアドプロシージャ
  9. ストアドプロシージャ、パッケージとは
  10. バルクフェッチ
  11. 位置表記法と名前表記法
  12. 二次元配列の定義
  13. RAISE
  14. ローカル・サブプログラム
  15. 暗黙カーソル

ITエンジニア キャリアアップ・転職・求人情報

■ 開発・設計経験豊富な上級管理職の採用

スタッフサービスがエンジニア正社員を束ねる【マネージャー】候補を募集
スタッフサービス

■ フリーランスから独立開業、会社設立へ

企業に雇われないプロフェッショナルたち
組織に属さず自分の得意分野で働くプロワーカーたちの実態レポート
アントレnet

Oracle & IT技術者 専門書

■絵で見てわかるOS/ストレージ/ネットワーク データベースはこう�
 価格: 2,310 円 （税込）
新品/ユーズド価格: 2,310 円 より
リリース: 2008-04-22
ユーザーレビュー: 2 件

■ロジカル・ライティング (BEST SOLUTION―LOGICAL COMMUNICATION
 価格: 2,310 円 （税込）
新品/ユーズド価格: 1,700 円 より
リリース: 2006-03-24
ユーザーレビュー: 11 件

■オラクルマスター教科書Gold Oracle Database 10g【DBA10g】編 (
 価格: 4,725 円 （税込）
新品/ユーズド価格: 3,720 円 より
リリース: 2005-11-23
ユーザーレビュー: 4 件

日本オラクル
■ 日本オラクル 株式会社
Oracle Japan
■ オラクルマスター資格 （オラクルマスターとは）
Oracle University
■ OTN Software Kit (開発者ライセンス)
OTN 有償プログラム FAQ (Standard/Professional)
Google
*1 Oracle では 複数の INDEX を INDEXES として用いていることが多いので少々紛らわしいかもしれない。