PL/SQLで大量データ移行する際のメモ

PL/SQLで大量データ移行する際のメモ

数十万～数億件のデータ移行でバルクFETCHからのINSERTが有効です。
（ダイレクトパスINSERTはまたの機会に)
BULK FETCH から INSERT / UPDATE をなるべく簡単に書く方法をすっかり忘れていたのでメモメモ。

これを忘れてしまい検索に手間取ったUPDATEの書式。「ROW=」のところがミソ。
これを使うとレコード毎UPDATEできて便利。

BULK_UPDATE

UPDATE <テーブル名>
   SET ROW = <レコード型コレクション>
 WHERE <主キー>;

INSERTもこんな感じで簡単に書ける。

BULK_INSERT

INSERT INTO <テーブル名> VALUE <レコード型コレクション>;

BULK FETCH からの INSERT / UPDATE を行うとこんな感じになる
1万件をガバッ　と読んで、　変更データをコレクションにため込み 一気に UPDATE/INSERT して効率を高めます。
（便宜上、中でSELECT文を入れていますが UPDATEも例に入れたかったので^^;)

BULK_FETCH_SAMPLE

CREATE OR REPLACE PROCEDURE BULK_FETCH AS 

    BULK_SIZE CONSTANT BINARY_INTEGER := 10000;  
 
    CURSOR curEMP(p_dept_id VARCHAR2) IS
        SELECT EMP_ID, DEPT_ID, EMP_NAME
          FROM EMP
         WHERE DEPT_ID = p_dept_id
         ORDER BY EMP_ID ASC;
    
    TYPE typCurEmp IS TABLE OF curEMP%ROWTYPE ;
    TYPE typEmp2 IS TABLE OF emp2%ROWTYPE ;
 
    rdEmp typCurEmp;
    insEmp typEmp2 := typEmp2();
    updEmp typEmp2 := typEmp2();
    num number(10);
    
BEGIN

    OPEN curEMP('1000');

    LOOP
    
        FETCH curEMP BULK COLLECT INTO rdEmp LIMIT BULK_SIZE;
        EXIT WHEN rdEmp.COUNT = 0;
    
        insEmp.delete;
        updEmp.delete;
    
        FOR i in 1..rdEmp.COUNT LOOP
    
            SELECT count(1) 
              INTO num
              FROM emp2
             WHERE emp_id = rdEmp(i).emp_id; 
       
            IF num > 0 THEN
                updEmp.EXTEND;
                updEmp(updEmp.COUNT).emp_id := rdEmp(i).emp_id;
                updEmp(updEmp.COUNT).dept_id := rdEmp(i).dept_id;
                updEmp(updEmp.COUNT).emp_name := rdEmp(i).emp_name;
            ELSE
                insEmp.EXTEND;
                insEmp(insEmp.COUNT).emp_id := rdEmp(i).emp_id;
                insEmp(insEmp.COUNT).dept_id := rdEmp(i).dept_id;
                insEmp(insEmp.COUNT).emp_name := rdEmp(i).emp_name;
            END IF;
        END LOOP;

        FORALL i in 1..insEmp.COUNT 
            INSERT INTO emp2 values insEmp(i);
        
        FORALL i in  1..updEmp.COUNT
            UPDATE emp2 SET ROW = updEmp(i) Where emp_id = updEmp(i).emp_id;
        
        COMMIT;

    END LOOP;
    CLOSE curEMP;

END BULK_FETCH;

これで大量データの移行データも楽ちんチン！