【PL/SQL】バルク・バインド
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結果の最大件数を指定できます。
下記の例だと、最大２件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; --最大２件をFETCH
CLOSE csrTSTBL;
DB_TSTBL.DELETE; --配列解放
EXCEPTION
WHEN OTHERS THEN
IF csrTSTBL%ISOPEN THEN
CLOSE csrTSTBL;
END IF;
END;最後に、Oracleのメモリを圧迫するので、配列に大量のデータを取り出すのには最善の注意を払って下さい。
SELECTで条件をしっかり絞る、「配列.DELETE」で配列内容をクリア等、しっかり対応して下さい。