PL/SQLで大量データを扱う場合、処理速度が問題になることがあります。通常のループ処理では1件ごとにSQLエンジンとPL/SQLエンジンを行き来するため、パフォーマンスが大きく低下してしまいます。そんなときに活用したいのが「バルク処理(Bulk Processing)」です。
この記事では、PL/SQLでバルク処理を行うためのBULK COLLECTとFORALLについて、実例を交えてわかりやすく解説します。
BULK COLLECTとは
BULK COLLECTは、SELECT文で複数の行を一括でコレクションに取り込む構文です。通常のFETCHループよりもはるかに高速に処理できます。
DECLARE
TYPE emp_name_table IS TABLE OF employees.last_name%TYPE;
l_names emp_name_table;
BEGIN
SELECT last_name BULK COLLECT INTO l_names
FROM employees
WHERE department_id = 30;
FOR i IN 1 .. l_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_names(i));
END LOOP;
END;
このようにBULK COLLECTを使うことで、SELECT文の結果を一括で配列に取り込むことができます。
FORALLとは
FORALLは、配列の内容を元にINSERT、UPDATE、DELETEなどのDML操作を一括で実行する構文です。通常のFORループによるDMLと比べて、非常に高速に処理できます。
DECLARE
TYPE id_table IS TABLE OF employees.employee_id%TYPE;
l_ids id_table := id_table(100, 101, 102);
BEGIN
FORALL i IN 1 .. l_ids.COUNT
DELETE FROM employees WHERE employee_id = l_ids(i);
END;
この例では、従業員IDのリストを一括でDELETE文に渡しています。FORループ内で個別にDELETEするよりも効率的です。
FORALLとBULK COLLECTの連携例
実際の処理では、まずBULK COLLECTでデータを取得し、その結果をFORALLで処理するパターンがよく使われます。
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
l_emp_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO l_emp_ids
FROM employees
WHERE department_id = 50;
FORALL i IN 1 .. l_emp_ids.COUNT
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = l_emp_ids(i);
END;
このように、データの取得から処理までを一括で行うことで、大量データ処理を高速に実行できます。
SAVE EXCEPTIONSによるエラー対応
FORALLで一括処理を行う際、途中でエラーが発生すると処理全体が停止してしまいます。それを回避するにはSAVE EXCEPTIONS
を使って、エラーが発生しても他の処理を継続できるようにします。
DECLARE
TYPE id_table IS TABLE OF employees.employee_id%TYPE;
l_ids id_table := id_table(200, 201, 999); -- 999は存在しないID
BEGIN
FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
DELETE FROM employees WHERE employee_id = l_ids(i);
EXCEPTION
WHEN OTHERS THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('行 ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
' でエラー発生: ' || SQLERRM(SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
END LOOP;
END;
このようにすれば、一部にエラーがあっても他のレコードの処理を継続できます。
まとめ
PL/SQLで大量のデータを効率よく処理したい場合は、BULK COLLECTとFORALLを組み合わせたバルク処理が非常に有効です。
- BULK COLLECT:複数行を一括取得