質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.78%

Oracle ストアドプロシージャでのBULK INSERT

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,110

shirobara_

score 11

前提・実現したいこと

oralceにて、他システムで保持しているユーザー情報の移行が必要となり、
件数が膨大(数百万件)である為ストアドを作成し移行作業を行おうとしています。
概要としましては移行用のcsvファイルをloaderで移行会員TBL(MIG_CUSTOMER)に読み込み、
それを加工し複数のTBLにデータを挿入するという流れです。(CUSTOMER,ADDRESS)
移行用TBLへの読み込みは完了しています。

カーソルを作成しFOR LOOPで回し、加工して1件ずつ挿入という流れを取ったところ、
処理時間が200万件のデータで10時間を超えてしまい、チューニングに困っています。
そこでBULK INSERTなる物を使用せんと調べて実装しているのですが、
TBL間の直接のデータの移行には成功しましたがデータの加工方法がわからず苦戦しています。

下記ソースコードはコンパイルエラーとなり、FORALL文の後にはINSERT等を続けなければいけない旨はわかりましたが、
データの加工方法をご教示いただけますと幸いです。
(そもそもやろうとしていることが本当に可能なのかどうかから不明です)

よろしくお願い致します

発生している問題・エラーメッセージ

Error(50,3): PLS-00103: 記号"CUSTOMER_REC"が見つかりました。 次のうちの1つが入るとき:     . ( * @ % & - + / at mod remainder rem select update with    <an exponent (**)> delete insert || execute multiset save    merge 記号"." は続行のために"CUSTOMER_REC"に代わりました。 

該当のソースコード

-- CREATE文の一部を記載します
CREATE TABLE M_MIG_CUSTOMER_BTR
(
    CUSTOMER_CODE                   NUMBER(12,0)

)
/
CREATE INDEX M_CUSTOMER_IDX2
    ON M_MIG_CUSTOMER_BTR (CUSTOMER_CODE)

---

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID                     VARCHAR2(12) NOT NULL,
    OLD_CUSTOMER_ID                 VARCHAR2(13)
)
/
ALTER TABLE CUSTOMER
    ADD(CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID) USING INDEX)

---

CREATE OR REPLACE PROCEDURE BULK_INSERT
IS
  BULK_SIZE PLS_INTEGER := 1000;

  CURSOR CSR_CUSTOMER IS
    SELECT * FROM MIG_CUSTOMER
    ORDER BY CUSTOMER_CODE ASC ;

  TYPE CSR_CUSTOMER_REC IS TABLE OF CSR_CUSTOMER%ROWTYPE;
  TYPE CUSTOMER_REC IS TABLE OF CUSTOMER%ROWTYPE;

  V_CSR_CUSTOMER_REC CSR_CUSTOMER_REC;
  V_CUSTOMER_REC CUSTOMER_REC;

  -- エラーハンドラ用
  vBulkErrors  PLS_INTEGER := 0;
  eBulkProcessNotComplete EXCEPTION;
  PRAGMA EXCEPTION_INIT(eBulkProcessNotComplete, -24381);
BEGIN

  OPEN CSR_CUSTOMER;
  LOOP
    FETCH CSR_CUSTOMER BULK COLLECT INTO V_CSR_CUSTOMER_REC LIMIT BULK_SIZE;
    -- BULK FETCH 処理
    EXIT WHEN V_CSR_CUSTOMER_REC.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('バルクインサート対象件数:' || V_CSR_CUSTOMER_REC.COUNT);
    BEGIN
      -- BULK INSERT 処理
      FORALL i in 1..V_CSR_CUSTOMER_REC.COUNT SAVE EXCEPTIONS
      -- 会員情報セット(ここの処理の記述方法が不明)
      CUSTOMER_REC(i).CUSTOMER_ID                     := '9999999999';
      CUSTOMER_REC(i).OLD_CUSTOMER_ID                 := LPAD(V_CSR_CUSTOMER_REC(i).CUSTOMER_CODE,10,'0');

      INSERT INTO CUSTOMER VALUES V_CSR_CUSTOMER_REC(i);

      COMMIT;
    EXCEPTION
      WHEN eBulkProcessNotComplete THEN
        vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
        -- PROC_BULK_ERROR_HANDLER;
    END;
  END LOOP;
  CLOSE CSR_CUSTOMER;
END;

試したこと

加工せずそのままの値を移植するように記述した際は正常に動作確認ができました。

補足情報(FW/ツールのバージョンなど)

実際に行いたい加工の項目は100を超えるため割愛しています。

CUSTOMER.CUSTOMER_IDは本来主キーですが、動作確認の為固定値を入れています。
(余分な処理は記載せず、一意制約エラーまで行くのを確認したい為)
ADDRESSへの挿入は割愛していますが、同様にMIG_CUSTOMERから取得した物を加工して挿入したいです。
複数TBLへのINSERT処理を同時に行いたいと思っています。
(CUSOTOMER_RECに情報を詰めた後はADDRESS_RECに情報を詰め、連続してINSERTするイメージ)

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

VALUES の内容はinsertする形に合わせましょう。
即ち、カーソル内で会員情報セットなどの整形は済ませておくことです。

追記

一つのテーブルから複数のテーブルに纏めて追加したいとのことですので、INSERT ALLの方が良いかと思います。
フェッチしながら、複数件数をある程度纏めてからSQL発行してコミット。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/15 15:16 編集

    > カーソルでとってきたデータを加工して複数テーブルに挿入するのは無理ということ
    そんな事は一言も云っていません。
    質問のSQLの内容について述べているだけです。

    INSERT ALLに関して追記しました。

    キャンセル

  • 2019/05/15 15:31

    元々の処理が、全件COMMITで行っているようなら、一定件数毎にCOMMITするようにしてみるとか。

    キャンセル

  • 2019/05/16 11:28

    話し合った結果、差分移行とする方向になりました。
    ご指摘いただいた内容は今後の学習に役立てようと思います。
    私のような初学者へのアドバイス、ありがとうございました。

    キャンセル

+1

質問にCREATE TABLE文も載せてください。

とりあえず、

-- 宣言
  TYPE CSR_CUSTOMER_REC IS TABLE OF CSR_CUSTOMER%ROWTYPE;

  V_CSR_CUSTOMER_REC CSR_CUSTOMER_REC;
      CUSTOMER_REC(i).CUSTOMER_ID                     := '9999999999';
      CUSTOMER_REC(i).OLD_CUSTOMER_ID                 := LPAD(V_CSR_CUSTOMER_REC(i).CUSTOMER_CODE,10


と、CUSTOMER_REC が定義されてません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/15 13:18

    コード修正致しました。
    変数についてはすみません、
    コピペしてきた箇所といろいろ試しているうちに変更した箇所とでわかりにくくなってしまっていますね。
    変更を考えてみます。

    キャンセル

  • 2019/05/15 13:24 編集

    >Oracle Database 11g Express Edition
    最初に質問に書くべきです。パラレル処理は DBMS_PARALLEL_EXECUTE
    https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_parallel_ex.htm#CHDIJACH
    が必要なのでとらえず保留。
    パラレル処理なしで insert ... select ... でやってみては?
    200万件あるデータでExpress Edition って選択がそもそも間違いです。

    キャンセル

  • 2019/05/15 14:45

    oracleにバージョン以外の差異があることも知らぬ素人でして、申し訳ありません。
    BULKではなくinsert selectでの処理を試してみようかと思います。
    Editionは業務システムであるため如何ともしがたく・・・
    ともあれ、アドバイス頂きありがとうございます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.78%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る