【PL/SQL】バルク・バインド

2020年8月6日

PL/SQLで処理効率を上げる方法に、「バルク・バインド」と言うものがあります。
一言でいうと、テーブルとのデータのやり取りを配列を使って一気にドンとやってしまう方法です。
少し詳しい事を言うと、PL/SQLとSQLは別の部分で処理されていて、その間のやり取りには結構付加がかかります。(SELECTだとFETCH部分)
例をあげて説明して行きます。

静的SQLのSELECT から INSERT %ROWTYPE配列

FETCH部分の「BULK COLLECT」命令でSELECT結果を配列に入れます。
「FORALL」命令でその直後に記述したSQLの配列をまとめて処理します。

DECLARE
    TYPE TYP_TSTBL IS TABLE OF TSTBL%ROWTYPE INDEX BY PLS_INTEGER; --配列型宣言
 
    DB_TSTBL TYP_TSTBL; --配列
 
    CURSOR csrTSTBL IS
        SELECT *
          FROM TSTBL;
BEGIN
    OPEN csrTSTBL;
    FETCH csrTSTBL BULK COLLECT INTO DB_TSTBL; --SELECT結果を配列にFETCH
    CLOSE csrTSTBL;
 
    IF DB_TSTBL.COUNT > 0 THEN
        FORALL i IN DB_TSTBL.FIRST..DB_TSTBL.LAST
            INSERT INTO ATBL VALUES DB_TSTBL(i); --FORALL命令で、配列の値をまとめてINSERT
 
        DB_TSTBL.DELETE; --配列解放
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE csrTSTBL;
END;

静的SQLのSELECT から INSERT 項目個別配列

FETCH部分の「BULK COLLECT」命令でSELECT結果を配列に入れます。
「FORALL」命令でその直後に記述したSQLの配列をまとめて処理します。
項目を個別に記述した例となります。

DECLARE
    TYPE TYP_AAA IS TABLE OF TSTBL.AAA%TYPE INDEX BY PLS_INTEGER; --配列型宣言
    TYPE TYP_BBB IS TABLE OF TSTBL.BBB%TYPE INDEX BY PLS_INTEGER; --配列型宣言
 
    DB_AAA TYP_AAA; --配列
    DB_AAA TYP_BBB; --配列
 
    CURSOR csrTSTBL IS
        SELECT AAA, BBB
          FROM TSTBL;
BEGIN
    OPEN csrTSTBL;
    FETCH csrTSTBL BULK COLLECT INTO DB_AAA, DB_BBB; --SELECT結果を配列にFETCH
    CLOSE csrTSTBL;
  
    IF DB_TSTBL.COUNT > 0 THEN
        FORALL i IN DB_AAA.FIRST..DB_AAA.LAST --どちらかの配列を代表して記述
            INSERT INTO ATBL(AAA, BBB) VALUES DB_AAA(i), DB_BBB(i);
 
        DB_TSTBL.DELETE; --配列解放
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE csrTSTBL;
END;

動的SQLのSELECT から INSERT %ROWTYPE配列

FETCH部分の「BULK COLLECT」命令でSELECT結果を配列に入れます。
「FORALL」命令でその直後に記述したSQLの配列をまとめて処理します。
静的SQLの例と処理は同じです。

DECLARE
    TYPE T_csrTSTBL IS REF CURSOR;
 
    csrTSTBL T_csrTSTBL;
 
    stmt VARCHAR2(4000);
 
    TYPE TYP_TSTBL IS TABLE OF TSTBL%ROWTYPE INDEX BY PLS_INTEGER; --配列型宣言
 
    DB_TSTBL TYP_TSTBL; --配列
BEGIN
    stmt := 'SELECT * ' ||
              'FROM TSTBL';

    OPEN csrTSTBL FOR stmt;
    FETCH csrTSTBL BULK COLLECT INTO DB_TSTBL; --SELECT結果を配列にFETCH
    CLOSE csrTSTBL;
 
    IF DB_TSTBL.COUNT > 0 THEN
        FORALL i IN DB_TSTBL.FIRST..DB_TSTBL.LAST
            INSERT INTO ATBL VALUES DB_TSTBL(i); --FORALL命令で、配列の値をまとめてINSERT
 
        DB_TSTBL.DELETE; --配列解放
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE csrTSTBL;
END;

動的SQLのSELECT から INSERT 個別項目配列

FETCH部分の「BULK COLLECT」命令でSELECT結果を配列に入れます。
「FORALL」命令でその直後に記述したSQLの配列をまとめて処理します。
静的SQLの例と処理は同じです。

DECLARE
    TYPE T_csrTSTBL IS REF CURSOR;
 
    csrTSTBL T_csrTSTBL;
 
    stmt VARCHAR2(4000);
 
    TYPE TYP_AAA IS TABLE OF TSTBL.AAA%TYPE INDEX BY PLS_INTEGER; --配列型宣言
    TYPE TYP_BBB IS TABLE OF TSTBL.BBB%TYPE INDEX BY PLS_INTEGER; --配列型宣言
 
    DB_AAA TYP_AAA; --配列
    DB_AAA TYP_BBB; --配列
BEGIN
    stmt := 'SELECT AAA, BBB ' ||
              'FROM TSTBL';
 
    OPEN csrTSTBL FOR stmt;
    FETCH csrTSTBL BULK COLLECT INTO DB_AAA, DB_BBB; --SELECT結果を配列にFETCH
    CLOSE csrTSTBL;
  
    IF DB_TSTBL.COUNT > 0 THEN
        FORALL i IN DB_AAA.FIRST..DB_AAA.LAST --どちらかの配列を代表して記述
            INSERT INTO ATBL(AAA, BBB) VALUES DB_AAA(i), DB_BBB(i);
 
        DB_TSTBL.DELETE; --配列解放
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE csrTSTBL;
END;

静的SQL・動的SQL共通 SELECT LIMIT付き

LIMITの例です。静的SQL・動的SQLで同じ動作となります。
「BULK COLLECT」命令に、配列に取り出すFETCH結果の最大件数を指定できます。
下記の例だと、最大2件FETCHします。

DECLARE
    TYPE TYP_TSTBL IS TABLE OF TSTBL%ROWTYPE INDEX BY PLS_INTEGER; --配列型宣言
 
    DB_TSTBL TYP_TSTBL; --配列
 
    CURSOR csrTSTBL IS
        SELECT *
          FROM TSTBL;
BEGIN
    OPEN csrTSTBL;
    FETCH csrTSTBL BULK COLLECT INTO DB_TSTBL LIMIT 2; --最大2件をFETCH
    CLOSE csrTSTBL;
  
    DB_TSTBL.DELETE; --配列解放
 EXCEPTION
    WHEN OTHERS THEN
        IF csrTSTBL%ISOPEN THEN
            CLOSE csrTSTBL;
        END IF;
END;

最後に、Oracleのメモリを圧迫するので、配列に大量のデータを取り出すのには最善の注意を払って下さい。
SELECTで条件をしっかり絞る、「配列.DELETE」で配列内容をクリア等、対応して下さい。

記事を読む >> 【PL/SQL】動的SQLのコーデイング方法

【PL/SQL】メニュー

サイトマップ

2020年8月6日Oracle,PL/SQL,データベース,プログラム動的SQL

Posted by こっぷ