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: トランザクションをコミットしています。