Oracle PL/SQL BULK COLLECT: FORALL の例

一括収集とは何ですか?

BULK COLLECT は、SQL と PL/SQL エンジンの間のコンテキストの切り替えを減らし、SQL エンジンがレコードを一度にフェッチできるようにします。

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

構文:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

上記の構文では、「SELECT」および「FETCH」ステートメントからデータを収集するために BULK COLLECT が使用されています。

FORALL 句

FORALLは、データに対してDML操作を一括して実行することを可能にします。これは、FORループ文と似ていますが、 FOR ループ 物事はレコードレベルで発生しますが、FORALL には LOOP の概念がありません。 代わりに、指定された範囲内に存在するデータ全体が同時に処理されます。

構文:

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 には、n で影響を受ける行数を返す %BULK_ROWCOUNT(n) があります。th FORALL ステートメントの DML ステートメント。つまり、コレクション変数の単一値ごとに、FORALL ステートメントで影響を受けるレコードの数が表示されます。 「n」という用語は、行数が必要となるコレクション内の値のシーケンスを示します。

: この例では、BULK COLLECT を使用して emp テーブルからすべての従業員名を射影し、FORALL を使用して全従業員の給与を 5000 増加させます。

一括収集属性

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 salaiy=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

コードの説明:

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