Oracle PL/SQL BULK COLLECT: FORALL の例

一括収集とは何ですか?

一括収集により、コンテキストスイッチが削減されます。 SQL PL/SQLエンジンと連携し、SQLエンジンがレコードを一度に取得できるようにします。

Oracle PL / SQLの レコードを1つずつ取得するのではなく、まとめて取得する機能を提供します。このBULK COLLECTは、SELECTステートメントで使用してレコードをまとめて入力したり、 カーソル 一括で取得します。BULK COLLECTはレコードを一括で取得するため、INTO句には必ずコレクション型の変数を指定する必要があります。BULK COLLECTを使用する主な利点は、データベースとPL/SQLエンジン間のやり取りを減らすことでパフォーマンスが向上することです。

構文：

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

上記の構文では、BULK COLLECT を使用して SELECT 文と FETCH 文からデータを収集しています。

FORALL 句

FORALL ステートメントは、 DML操作 大量のデータに対して実行されます。FORループ文に似ていますが、FORループではレコードレベルで処理が行われるのに対し、FORALLにはループの概念がありません。代わりに、指定された範囲内のすべてのデータが同時に処理されます。

構文：

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

上記の構文では、指定されたDML操作は、下限値と上限値の間に存在するすべてのデータに対して実行されます。

LIMIT条項

バルクコレクトの概念では、データ全体を一度にターゲットコレクション変数にロードします。つまり、データ全体が一度にコレクション変数に格納されます。しかし、ロードする必要のあるレコードの総数が非常に多い場合は、この方法は推奨されません。PL/SQLがデータ全体をロードしようとすると、セッションメモリをより多く消費するためです。したがって、バルクコレクト操作のサイズは常に制限するのが良いでしょう。

このサイズ制限は、SELECT文にROWNUM条件を導入することで容易に実現できますが、カーソルの場合はこれは不可能です。

これを克服するために、 Oracle 一括処理に含めるレコード数を定義するLIMIT句が提供されています。

構文：

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

上記の構文では、カーソルフェッチステートメントは、LIMIT句とともにBULK COLLECTステートメントを使用しています。

一括収集属性

カーソル属性と同様に、BULK COLLECT には %BULK_ROWCOUNT(n) があり、これは FORALL ステートメントの n 番目の DML ステートメントで影響を受けた行数を返します。つまり、コレクション変数の各値に対して FORALL ステートメントで影響を受けたレコード数を返します。「n」は、行数が必要なコレクション内の値のシーケンスを示します。

例1： この例では、BULK COLLECT を使用して emp テーブルからすべての従業員名を抽出し、FORALL を使用してすべての従業員の給与を 5000 増やします。

以下のスクリーンショットは、この BULK COLLECT と FORALL の例とその出力を示しています。 Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

出力

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code 説明：

• Code 2行目： ステートメント「SELECT emp_name FROM emp」に対してカーソルguru99_detを宣言します。
• Code 3行目： lv_emp_name_tbl を VARCHAR2(50) 型のテーブルとして宣言します。
• Code 4行目： lv_emp_nameをlv_emp_name_tbl型として宣言します。
• Code 6行目： カーソルを開く。
• Code 7行目： BULK COLLECTを使用してカーソルを取得し、LIMITサイズを5000としてlv_emp_name変数に格納します。
• Code 8行目～11行目： コレクション lv_emp_name 内のすべてのレコードを出力するための FOR ループを設定します。
• Code 12行目： FORALLを使用して、全従業員の給与を5000ずつ更新します。
• Code 14行目： コミットする トランザクション.

よくあるご質問