バルクSQLおよびバルク・バインド

バルクSQLは、PL/SQLとSQL間の通信のパフォーマンス・オーバーヘッドを最小化します。バルクSQLを構成するPL/SQL機能は、FORALL文およびBULK COLLECT句です。SQL文に指定されたPL/SQL変数に値を代入することを、バインドと呼びます。

PL/SQLとSQLは、次のように通信します。PL/SQLエンジンは、SELECT INTO文またはDML文を実行するために、SQLエンジンに問合せまたはDML文を送信します。SQLエンジンは、問合せまたはDML文を実行し、結果をPL/SQLエンジンに戻します。

FORALL文は、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。BULK COLLECT句は、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。問合せまたはDML文が4つ以上のデータベース行に影響する場合は、バルクSQLでパフォーマンスを大幅に向上できます。

ノート:

リモート表では、バルクSQLは実行できません。

PL/SQLバインド操作は、次のカテゴリに分類されます。

バインド・カテゴリ	このバインドが実行される場合
インバインド	`INSERT`文、`UPDATE`文または`MERGE`によってPL/SQL変数またはホスト変数がデータベースに格納される場合
アウトバインド	`INSERT`文、`UPDATE`文、`MERGE`文または`DELETE`文の`RETURNING` `INTO`句によってPL/SQL変数またはホスト変数にデータベースの値が代入される場合
`DEFINE`	`SELECT`文または`FETCH`文によってPL/SQL変数またはホスト変数にデータベースの値が代入される場合

インバインドおよびアウトバインドでは、バルクSQLはバルク・バインドを使用します(つまり、値のコレクション全体を一度にバインドします)。n個の要素があるコレクションの場合、バルクSQLは単一の操作で、n回分のSELECT INTO文またはDML文に相当する処理を実行できます。バルクSQLを使用する問合せでは、行ごとにFETCH文を使用することなく、任意の数の行を戻すことができます。

ノート:

パラレルDMLは、バルクSQLを使用すると無効になります。

ここでのトピック

FORALL文

FORALL文は、バルクSQLの機能であり、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。

FORALL文を理解するため、最初に例13-7のFOR LOOP文について考えてみます。これらのDML文は、PL/SQLからSQLに1文ずつ送信されます。

DELETE FROM employees_temp WHERE department_id = 10;
DELETE FROM employees_temp WHERE department_id = 30;
DELETE FROM employees_temp WHERE department_id = 70;

次に、例13-8のFORALL文について考えてみます。3つの同じDML文がPL/SQLからSQLにバッチとして送信されます。

通常、FORALL文は、同等のFOR LOOP文よりはるかに高速です。ただし、FOR LOOP文には複数のDML文を含めることができますが、FORALL文に含めることができるDML文は1つのみです。FORALL文によってSQLに送信されるDML文のバッチは、VALUES句とWHERE句のみが異なります。これらの句の値は、データが移入された既存のコレクションから取得される必要があります。

ノート:

FORALL文のDML文では、複数のコレクションを参照できますが、パフォーマンス上のメリットは、FORALLの索引変数を索引として使用するコレクション参照に対してのみ適用されます。

例13-9では、2つのデータベース表に同じコレクション要素を挿入します(1番目の表にはFOR LOOP文を使用し、2番目の表にはFORALL文を使用して、各文の実行にかかる時間を表示します)。(時間は実行ごとに異なります。)

例13-10では、FORALL文がコレクションのサブセットに適用されます。

ここでのトピック

疎コレクションに対するFORALL文の使用

FORALL文の境界句で疎コレクションを参照する場合、INDICES OF句またはVALUES OF句を使用して既存の索引値のみを指定します。

INDICES OFは、文字列で索引付けされている連想配列を除く任意のコレクションに使用できます。VALUES OFは、PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションにのみ使用できます。

PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションには、索引コレクションを使用できます。索引コレクションは、他のコレクションの要素に対するポインタのコレクションです(索引付けされたコレクション)。

索引コレクションは、異なるFORALL文で同じコレクションの異なるサブセットを処理する場合に便利です。時間とメモリーを大量に費やす可能性のあるサブセットを表す新しいコレクションに元のコレクションの要素をコピーするかわりに、索引コレクションを使用して各サブセットを表し、各索引コレクションを異なるFORALL文のVALUES OF句で使用します。

FORALL文での未処理例外

SAVE EXCEPTIONS句なしのFORALL文で、いずれかのDML文によって未処理例外が呼び出されると、PL/SQLによってFORALL文が停止され、前のDML文で行われたすべての変更がロールバックされます。

たとえば、例13-8のFORALL文は、次のDML文を(そのいずれかによって未処理例外が呼び出されないかぎり)記載された順序で処理します。

DELETE FROM employees_temp WHERE department_id = depts(10);
DELETE FROM employees_temp WHERE department_id = depts(30);
DELETE FROM employees_temp WHERE department_id = depts(70);

3番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目と2番目の文による変更がロールバックされます。2番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目の文による変更がロールバックされ、3番目の文は実行されません。

FORALL文で呼び出される例外は、次のいずれかの方法で処理できます。

各例外が呼び出された時点で(「FORALL例外の即座の処理」を参照)
FORALL文の実行が完了した後、SAVE EXCEPTIONS句を含めることによって(「FORALL文完了後のFORALL例外の処理」を参照)

FORALL例外の即時処理

FORALL文で呼び出された例外を即座に処理するには、SAVE EXCEPTIONS句を省略して適切な例外ハンドラを記述します。

1つのDML文で処理済例外が発生した場合、PL/SQLはその文によって行われた変更をロールバックしますが、それより前のDML文で行われた変更はロールバックしません。

例13-12では、FORALL文が3つのUPDATE文を実行するように設計されています。ただし、2番目の文によって例外が呼び出されます。例外は例外ハンドラによって処理され、エラー・メッセージの表示と1番目のUPDATE文による変更のコミットが行われます。3番目のUPDATE文は実行されません。

例外処理の詳細は、「PL/SQLのエラー処理」を参照してください。

例13-12 FORALL例外の即時処理

DROP TABLE emp_temp;
CREATE TABLE emp_temp (
  deptno NUMBER(2),
  job VARCHAR2(18)
);
 
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  TYPE NumList IS TABLE OF NUMBER;
 
  depts          NumList := NumList(10, 20, 30);
  error_message  VARCHAR2(100);
 
BEGIN
  -- Populate table:
 
  INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
  INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
  INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
  COMMIT;
 
  -- Append 9-character string to each job:
 
  FORALL j IN depts.FIRST..depts.LAST
    UPDATE emp_temp SET job = job || ' (Senior)'
    WHERE deptno = depts(j);
 
EXCEPTION
  WHEN OTHERS THEN
    error_message := SQLERRM;
    DBMS_OUTPUT.PUT_LINE (error_message);
 
    COMMIT;  -- Commit results of successful updates
    RAISE;
END;
/

結果:

Procedure created.

プロシージャの起動:

BEGIN
  p;
END;
/

結果:

ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,
maximum: 18)
BEGIN
*
ERROR at line 1:
ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,
maximum: 18)
ORA-06512: at "HR.P", line 27
ORA-06512: at line 2

問合せ:

SELECT * FROM emp_temp;

結果:

    DEPTNO JOB
---------- ------------------
        10 Clerk (Senior)
        20 Bookkeeper
        30 Analyst
 
3 rows selected.

FORALL文が完了した後のFORALL例外の処理

一部のDML文が失敗した場合でもFORALL文の処理を継続するには、SAVE EXCEPTIONS句を含めます。DML文が失敗しても、PL/SQLによって例外は呼び出されず、かわりに、その失敗に関する情報が保存されます。FORALL文の完了後、PL/SQLによって、FORALL文に対して1つの例外(ORA-24381)が呼び出されます。

ORA-24381の例外ハンドラでは、暗黙カーソル属性SQL%BULK_EXCEPTIONSから個々のDML文の失敗に関する情報を取得できます。

SQL%BULK_EXCEPTIONSは、直前のFORALL文の実行中に失敗したDML文に関する情報の連想配列とほぼ同じです。

SQL%BULK_EXCEPTIONS.COUNTは、失敗したDML文の数です。SQL%BULK_EXCEPTIONS.COUNTが0(ゼロ)でない場合、1からSQL%BULK_EXCEPTIONS. COUNTまでの各索引値iに対して、次のようになります。

SQL%BULK_EXCEPTIONS(i).ERROR_INDEXは、失敗したDML文の数です。
SQL%BULK_EXCEPTIONS(i).ERROR_CODEは、失敗に関するOracle Databaseエラー・コードです。

たとえば、FORALL SAVE EXCEPTIONS文で100個のDML文を実行し、10番目と64番目の文がそれぞれエラー・コードORA-12899およびORA-19278を戻して失敗した場合、次のようになります。

SQL%BULK_EXCEPTIONS.COUNT = 2
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278

ノート:

SAVE EXCEPTIONS句なしのFORALL文によって例外が呼び出されると、SQL%BULK_EXCEPTIONS.COUNT = 1になります。

エラー・コードを使用して、次のようにSQLERRMファンクション(「SQLERRMファンクション」を参照)で関連するエラー・メッセージを取得できます。

SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))

ただし、SQLERRMによって戻されるエラー・メッセージには、置換引数は含まれません(例13-12と例13-13のエラー・メッセージを比較してください)。

例13-13は、例13-12とほぼ同じですが、次の点が異なります。

FORALL文に、SAVE EXCEPTIONS句が含まれます。
例外処理部に、ORA-24381(バルク操作が例外を呼び出して保存したときにPL/SQLによって暗黙的に呼び出される内部的に定義された例外)に対応する例外ハンドラが含まれます。この例では、ORA-24381にユーザー定義の名前dml_errorsを割り当てています。
dml_errorsの例外ハンドラは、SQL%BULK_EXCEPTIONSとSQLERRM(およびいくつかのローカル変数)を使用して、エラー・メッセージと、エラーの原因となった文、コレクション項目および文字列を表示します。

例13-13 FORALL文が完了した後のFORALL例外の処理

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  TYPE NumList IS TABLE OF NUMBER;
  depts        NumList := NumList(10, 20, 30);
 
  error_message  VARCHAR2(100);
  bad_stmt_no    PLS_INTEGER;
  bad_deptno     emp_temp.deptno%TYPE;
  bad_job        emp_temp.job%TYPE;
 
  dml_errors  EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
  -- Populate table:
 
  INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
  INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
  INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
  COMMIT;
 
  -- Append 9-character string to each job:
 
  FORALL j IN depts.FIRST..depts.LAST SAVE EXCEPTIONS
    UPDATE emp_temp SET job = job || ' (Senior)'
    WHERE deptno = depts(j); 
 
EXCEPTION
  WHEN dml_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      DBMS_OUTPUT.PUT_LINE (error_message);
 
      bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
      DBMS_OUTPUT.PUT_LINE('Bad statement #: ' || bad_stmt_no);
 
      bad_deptno := depts(bad_stmt_no);
      DBMS_OUTPUT.PUT_LINE('Bad department #: ' || bad_deptno);
 
      SELECT job INTO bad_job FROM emp_temp WHERE deptno = bad_deptno;
 
      DBMS_OUTPUT.PUT_LINE('Bad job: ' || bad_job);
    END LOOP;
 
    COMMIT;  -- Commit results of successful updates

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unrecognized error.');
      RAISE;
END;
/

結果:

Procedure created.

プロシージャの起動:

BEGIN
  p;
END;
/

結果:

ORA-12899: value too large for column  (actual: , maximum: )
Bad statement #: 2
Bad department #: 20
Bad job: Bookkeeper
 
PL/SQL procedure successfully completed.

問合せ:

SELECT * FROM emp_temp;

結果:

    DEPTNO JOB
---------- ------------------
        10 Clerk (Senior)
        20 Bookkeeper
        30 Analyst (Senior)
 
3 rows selected.

疎コレクションおよびSQL%BULK_EXCEPTIONS

FORALL文の境界句で疎コレクションを参照する場合に、DML文が失敗する原因となったコレクション要素を検出するには、索引がSQL%BULK_EXCEPTIONS(i).ERROR_INDEXである要素を検出するまで、各要素を1つずつ確認する必要があります。FORALL文で、VALUES OF句を使用して別のコレクションに対するポインタのコレクションを参照する場合、他のコレクションの要素で索引がSQL%BULK_EXCEPTIONS(i).ERROR_INDEXであるものを検出する必要があります。

FORALL文の影響を受ける行の数の取得

FORALL文の完了後、各DML文によって影響を受けた行の数を、暗黙カーソル属性SQL%BULK_ROWCOUNTから取得できます。

FORALL文の影響を受けた行の合計数を取得するには、「SQL%ROWCOUNT属性: 影響を受けた行数」で説明されている、暗黙カーソル属性のSQL%ROWCOUNTを使用します。

SQL%BULK_ROWCOUNTは、i番目の要素が、直前に完了したFORALL文のi番目のDML文によって影響を受けた行の数に相当する連想配列とほぼ同じです。要素のデータ型はINTEGERです。

ノート:

サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11gリリース2以前(またはその逆)の場合、SQL%BULK_ROWCOUNTが戻す最大値は4,294,967,295です。

例13-14では、SQL%BULK_ROWCOUNTを使用してFORALL文の各DELETE文で削除された行の数を表示し、SQL%ROWCOUNTを使用して削除された行の合計数を表示します。

例13-15では、SQL%BULK_ROWCOUNTを使用してFORALL文の各INSERT SELECT構文で挿入された行の数を表示し、SQL%ROWCOUNTを使用して挿入された行の合計数を表示します。

例13-14 FORALLの各DELETEで影響を受けた行の数の表示

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts NumList := NumList(30, 50, 60);
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j);

  FOR i IN depts.FIRST..depts.LAST LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Statement #' || i || ' deleted ' ||
      SQL%BULK_ROWCOUNT(i) || ' rows.'
    );
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/

結果:

Statement #1 deleted 6 rows.
Statement #2 deleted 45 rows.
Statement #3 deleted 5 rows.
Total rows deleted: 56

例13-15 FORALLの各INSERT SELECTで影響を受けた行の数の表示

DROP TABLE emp_by_dept;
CREATE TABLE emp_by_dept AS
  SELECT employee_id, department_id
  FROM employees
  WHERE 1 = 0;

DECLARE
  TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
  deptnums  dept_tab;
BEGIN
  SELECT department_id BULK COLLECT INTO deptnums FROM departments;

  FORALL i IN 1..deptnums.COUNT
    INSERT INTO emp_by_dept (employee_id, department_id)
      SELECT employee_id, department_id
      FROM employees
      WHERE department_id = deptnums(i)
      ORDER BY department_id, employee_id;

  FOR i IN 1..deptnums.COUNT LOOP
    -- Count how many rows were inserted for each department; that is,
    -- how many employees are in each department.
    DBMS_OUTPUT.PUT_LINE (
      'Dept '||deptnums(i)||': inserted '||
      SQL%BULK_ROWCOUNT(i)||' records'
    );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);
END;
/

結果:

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Dept 280: inserted 0 records
Total records inserted: 106

BULK COLLECT句

BULK COLLECT句は、バルクSQLの機能であり、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。

BULK COLLECT句は、次の場所に使用できます。

SELECT INTO文
FETCH文
次の文のRETURNING INTO句:
- DELETE文
- INSERT文
- UPDATE文
- MERGE文
- EXECUTE IMMEDIATE文

BULK COLLECT句を使用して、前述の各文で結果セット全体を取り出し、それを単一の操作で1つ以上のコレクション変数に格納します(一度に1つの結果行を取り出すループ文を使用するより効率的な操作です)。

ノート:

PL/SQLは、LOOP文の内側でFETCH文を処理する場合と同様の方法で、BULK COLLECT句を処理します。PL/SQLは、BULK COLLECT句付きの文が行を戻さない場合でも、例外を呼び出しません。例13-22に示すように、ターゲット・コレクションが空かどうかを確認する必要があります。

ここでのトピック

BULK COLLECT句を使用したSELECT INTO文

BULK COLLECT句付きのSELECT INTO文(SELECT BULK COLLECT INTO文とも呼ばれます)は、結果セット全体を選択して1つ以上のコレクション変数に入れます。

詳細は、「SELECT INTO文」を参照してください。

注意:

SELECT BULK COLLECT INTO文は、エイリアシングに対して不安定であるため、予期しない結果が発生する可能性があります。詳細は、「SELECT BULK COLLECT INTO文およびエイリアシング」を参照してください。

例13-16では、SELECT BULK COLLECT INTO文を使用して、データベースの2つの列を選択して2つのコレクション(ネストした表)に入れています。

例13-17では、SELECT BULK COLLECT INTO文を使用して、結果セットを選択してレコードのネストした表に入れています。

ここでのトピック

例13-16 2つのネストした表に対するデータベースの2つの列のバルク選択

DECLARE
  TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
 
  enums NumTab;
  names NameTab;
 
  PROCEDURE print_first_n (n POSITIVE) IS
  BEGIN
    IF enums.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
 
      FOR i IN 1 .. n LOOP
        DBMS_OUTPUT.PUT_LINE (
          '  Employee #' || enums(i) || ': ' || names(i));
      END LOOP;
    END IF;
  END;
 
BEGIN
  SELECT employee_id, last_name
  BULK COLLECT INTO enums, names
  FROM employees
  ORDER BY employee_id;
 
  print_first_n(3);
  print_first_n(6);
END;
/

結果:

First 3 employees:
Employee #100: King
Employee #101: Yang
Employee #102: Garcia
First 6 employees:
Employee #100: King
Employee #101: Yang
Employee #102: Garcia
Employee #103: James
Employee #104: Miller
Employee #105: Williams

例13-17 レコードのネストした表に対するバルク選択

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
 
  stock_managers  NameSet;  -- nested table of records
 
BEGIN 
  -- Assign values to nested table of records:
 
  SELECT first_name, last_name, hire_date
    BULK COLLECT INTO stock_managers
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

結果:

01-MAY-13 Kaufling, Payam
18-JUL-14 Weiss, Matthew
10-APR-15 Fripp, Adam
10-OCT-15 Vollman, Shanta
16-NOV-17 Mourgos, Kevin

SELECT BULK COLLECT INTO文およびエイリアシング

次の書式の文について説明します。

SELECT column BULK COLLECT INTO collection FROM table ...

columnおよびcollectionは、それぞれIN NOCOPYおよびOUT NOCOPYサブプログラム・パラメータに類似しており、PL/SQLによって参照方式で渡されます。参照方式で渡されるサブプログラム・パラメータと同様に、エイリアシングによって予期しない結果が発生する場合があります。

SELECT BULK COLLECT INTO文の行の制限

大量の行を戻すSELECT BULK COLLECT INTO文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、次のいずれかを使用します。

ROWNUM疑似列(『Oracle Database SQL言語リファレンス』を参照)
SAMPLE句(『Oracle Database SQL言語リファレンス』を参照)
FETCH FIRST句(『Oracle Database SQL言語リファレンス』を参照)

例13-21は、SELECT BULK COLLECT INTO文により戻される行数を制限するいくつかの方法を示しています。

例13-21 ROWNUM、SAMPLEおよびFETCH FIRSTによるバルク選択の制限

DECLARE
  TYPE SalList IS TABLE OF employees.salary%TYPE;
  sals SalList;
BEGIN
  SELECT salary BULK COLLECT INTO sals FROM employees
    WHERE ROWNUM <= 50;
 
  SELECT salary BULK COLLECT INTO sals FROM employees
    SAMPLE (10);
 
  SELECT salary BULK COLLECT INTO sals FROM employees
    FETCH FIRST 50 ROWS ONLY;
END;
/

コレクションのループのガイドライン

結果セットをコレクションに格納する場合、行をループして異なる列を参照することは簡単です。この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合、次のことに注意してください。

結果セットを1回ループするには、カーソルFOR LOOPを使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。

この方法を使用すると、結果セットのコピーを格納する際のメモリーのオーバーヘッドを回避できます。
結果セットをループして特定の値を検索したり、結果をフィルタリングしてより小さい結果セットにするかわりに、SELECT INTO文の問合せで検索またはフィルタリングを行います。

たとえば、単純な問合せではWHERE句を使用し、複数の結果セットを比較する問合せでは、INTERSECTやMINUSなどの集合演算子を使用します。集合演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
結果セットをループして各結果行に対して別の問合せを実行するかわりに、SELECT INTO文の問合せで副問合せを使用します(「副問合せによる問合せ結果セットの処理」を参照)。
結果セットをループして各結果行に対して別のDML文を実行するかわりに、FORALL文を使用します(「FORALL文」を参照)。

BULK COLLECT句を使用したFETCH文

BULK COLLECT句付きのFETCH文(FETCH BULK COLLECT文とも呼ばれます)は、結果セット全体をフェッチして1つ以上のコレクション変数に入れます。

詳細は、「FETCH文」を参照してください。

例13-22では、FETCH BULK COLLECT文を使用し、結果セット全体をフェッチして2つのコレクション(ネストした表)に入れます。

例13-23では、FETCH BULK COLLECT文を使用し、結果セットをフェッチしてレコードのコレクション(ネストした表)に入れます。

例13-22 2つのネストした表へのバルク・フェッチ

DECLARE
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  TYPE SalList IS TABLE OF employees.salary%TYPE;

  CURSOR c1 IS
    SELECT last_name, salary
    FROM employees
    WHERE salary > 10000
    ORDER BY last_name;

  names  NameList;
  sals   SalList;

  TYPE RecList IS TABLE OF c1%ROWTYPE;
  recs RecList;

  v_limit PLS_INTEGER := 10;

  PROCEDURE print_results IS
  BEGIN
    -- Check if collections are empty:

    IF names IS NULL OR names.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No results!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Result: ');
      FOR i IN names.FIRST .. names.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE('  Employee ' || names(i) || ': $' || sals(i));
      END LOOP;
    END IF;
  END;

BEGIN
  DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO names, sals;
  CLOSE c1;
  print_results();
  DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---');
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
    EXIT WHEN names.COUNT = 0;
    print_results();
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO recs;
  FOR i IN recs.FIRST .. recs.LAST
  LOOP
    -- Now all columns from result set come from one record
    DBMS_OUTPUT.PUT_LINE (
      '  Employee ' || recs(i).last_name || ': $' || recs(i).salary
    );
  END LOOP;
END;
/

結果:

--- Processing all results simultaneously ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee Errazuriz: $12000
Employee Garcia: $17000
Employee Gruenberg: $12008
Employee Higgins: $12008
Employee King: $24000
Employee Li: $11000
Employee Martinez: $13000
Employee Ozer: $11500
Employee Partners: $13500
Employee Singh: $14000
Employee Vishney: $10500
Employee Yang: $17000
Employee Zlotkey: $10500
--- Processing 10 rows at a time ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee Errazuriz: $12000
Employee Garcia: $17000
Employee Gruenberg: $12008
Employee Higgins: $12008
Employee King: $24000
Employee Li: $11000
Employee Martinez: $13000
Employee Ozer: $11500
Result:
Employee Partners: $13500
Employee Singh: $14000
Employee Vishney: $10500
Employee Yang: $17000
Employee Zlotkey: $10500
--- Fetching records rather than columns ---
Employee Abel: $11000
Employee Cambrault: $11000
Employee Errazuriz: $12000
Employee Garcia: $17000
Employee Gruenberg: $12008
Employee Higgins: $12008
Employee King: $24000
Employee Li: $11000
Employee Martinez: $13000
Employee Ozer: $11500
Employee Partners: $13500
Employee Singh: $14000
Employee Vishney: $10500
Employee Yang: $17000
Employee Zlotkey: $10500

例13-23 レコードのネストした表へのバルク・フェッチ

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
  stock_managers  NameSet;  -- nested table of records
 
  TYPE cursor_var_type is REF CURSOR;
  cv cursor_var_type;
 
BEGIN 
  -- Assign values to nested table of records:
 
  OPEN cv FOR
    SELECT first_name, last_name, hire_date
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  FETCH cv BULK COLLECT INTO stock_managers;
  CLOSE cv;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

結果:

01-MAY-13 Kaufling, Payam
18-JUL-14 Weiss, Matthew
10-APR-15 Fripp, Adam
10-OCT-15 Vollman, Shanta
16-NOV-17 Mourgos, Kevin

FETCH BULK COLLECT文の行の制限

大量の行を戻すFETCH BULK COLLECT文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、LIMIT句を使用します。

例13-24では、LOOP文が繰り返されるたびに、FETCH文によって10行(またはそれ以下)が連想配列empidsにフェッチされます(前の値は上書きされます)。LOOP文の終了条件に注意してください。

例13-24 LIMITによるバルクFETCHの制限

DECLARE
  TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  CURSOR c1 IS
    SELECT employee_id
    FROM employees
    WHERE department_id = 80
    ORDER BY employee_id;

  empids  numtab;
BEGIN
  OPEN c1;
  LOOP  -- Fetch 10 rows or fewer in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT 10;
    DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));
    END LOOP;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;
/

結果:

------- Results from One Bulk Fetch --------
Employee Id: 145
Employee Id: 146
Employee Id: 147
Employee Id: 148
Employee Id: 149
Employee Id: 150
Employee Id: 151
Employee Id: 152
Employee Id: 153
Employee Id: 154
------- Results from One Bulk Fetch --------
Employee Id: 155
Employee Id: 156
Employee Id: 157
Employee Id: 158
Employee Id: 159
Employee Id: 160
Employee Id: 161
Employee Id: 162
Employee Id: 163
Employee Id: 164
------- Results from One Bulk Fetch --------
Employee Id: 165
Employee Id: 166
Employee Id: 167
Employee Id: 168
Employee Id: 169
Employee Id: 170
Employee Id: 171
Employee Id: 172
Employee Id: 173
Employee Id: 174
------- Results from One Bulk Fetch --------
Employee Id: 175
Employee Id: 176
Employee Id: 177
Employee Id: 179

BULK COLLECT句を使用したRETURNING INTO句

BULK COLLECT句付きのRETURNING INTO句(RETURNING BULK COLLECT INTO句とも呼ばれます)は、INSERT文、UPDATE文、MERGE文、DELETE文またはEXECUTE IMMEDIATE文で使用できます。RETURNING BULK COLLECT INTO句を使用すると、文によってその結果セットが1つ以上のコレクションに格納されます。

詳細は、「RETURNING INTO句」を参照してください。

例13-25では、RETURNING BULK COLLECT INTO句付きのDELETE文を使用して、表から行を削除し、それらの行を2つのコレクション(ネストした表)に戻します。

例13-26では、キーワードOLDおよびNEWを使用して、RETURNING BULK COLLECT INTO句を指定したUPDATE文の前および後の従業員給与の値を返します。

例13-25 2つのネストした表に対する削除行の戻し

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id;

DECLARE
  TYPE NumList IS TABLE OF employees.employee_id%TYPE;
  enums  NumList;
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  names  NameList;
BEGIN
  DELETE FROM emp_temp
  WHERE department_id = 30
  RETURNING employee_id, last_name
  BULK COLLECT INTO enums, names;

  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN enums.FIRST .. enums.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i));
  END LOOP;
END;
/

結果:

Deleted 6 rows:
Employee #114: Li
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares

例13-26 更新された行のNEWおよびOLD値の戻し

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id;

DECLARE
  TYPE SalList IS TABLE OF employees.salary%TYPE;
  old_sals SalList;
  new_sals SalList;
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  names NameList;
BEGIN
  UPDATE emp_temp SET salary = salary * 1.15
  WHERE salary < 2500
  RETURNING OLD salary, NEW salary, last_name 
  BULK COLLECT INTO old_sals, new_sals, names; 

  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows: ');
  FOR i IN old_sals.FIRST .. old_sals.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(names(i) || ': Old Salary $' || old_sals(i) || 
            ', New Salary $' || new_sals(i));
  END LOOP;
END;
/

結果:

Landry: Old Salary $2400, New Salary $2760
Markle: Old Salary $2200, New Salary $2530
Olson: Old Salary $2100, New Salary $2415
Gee: Old Salary $2400, New Salary $2760
Philtanker: Old Salary $2200, New Salary $2530

FORALL文とBULK COLLECT句の併用

FORALL文では、DML文にRETURNING BULK COLLECT INTO句を含めることができます。FORALL文が繰り返されるたびに、DML文は前の値を上書きすることなく、指定された値を指定されたコレクションに格納します(この動作は、FOR LOOP文における同じDML文の処理と同様です)。

例13-27では、FORALL文で、RETURNING BULK COLLECT INTO句が含まれるDELETE文を実行します。FORALL文が繰り返されるたびに、DELETE文は、削除された行のemployee_idおよびdepartment_idの値をそれぞれコレクションe_idsおよびd_idsに格納します。

例13-28は、例13-27と類似していますが、FORALL文のかわりにFOR LOOP文を使用しています。

例13-27 FORALL文でのRETURN BULK COLLECT INTO付きのDELETE

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id, department_id;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts  NumList := NumList(10,20,30);

  TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  e_ids  enum_t;

  TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  d_ids  dept_t;

BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp
    WHERE department_id = depts(j)
    RETURNING employee_id, department_id
    BULK COLLECT INTO e_ids, d_ids;

  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
    );
  END LOOP;
END;
/

結果:

Deleted 9 rows:
Employee #200 from dept #10
Employee #201 from dept #20
Employee #202 from dept #20
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

例13-28 FOR LOOP文でのRETURN BULK COLLECT INTO付きのDELETE

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id, department_id;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts  NumList := NumList(10,20,30);
 
  TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  e_ids  enum_t;
 
  TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  d_ids  dept_t;
 
BEGIN
  FOR j IN depts.FIRST..depts.LAST LOOP
    DELETE FROM emp_temp
    WHERE department_id = depts(j)
    RETURNING employee_id, department_id
    BULK COLLECT INTO e_ids, d_ids;
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
 
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
    );
  END LOOP;
END;
/

結果:

Deleted 6 rows:
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

ホスト配列のクライアント・バルク・バインド

クライアント・プログラム(OCIプログラムやPro*Cプログラムなど)は、PL/SQLの無名ブロックを使用してホスト配列をバルク・バインド入出力できます。これは、コレクションをデータベース・サーバーとの間でやり取りするのに最も効率的な方法です。

クライアント・プログラムで、値を宣言し、無名ブロックで参照されるホスト変数にそれらの値を代入します。無名ブロックでは、各ホスト変数名にコロン(:)を接頭辞として付け、PL/SQLのコレクション変数名と区別します。クライアント・プログラムが実行されると、データベース・サーバーでPL/SQLの無名ブロックが実行されます。

例13-29では、無名ブロックでFORALL文を使用してホスト入力配列をバルク・バインドします。FORALL文において、DELETE文は、スカラーlower、upper、emp_idおよび配列deptsという4つのホスト変数を参照します。

例13-29 無名ブロックによる入力ホスト配列のバルク・バインド

BEGIN
  FORALL i IN :lower..:upper
    DELETE FROM employees
    WHERE department_id = :depts(i);
END;
/