問合せ結果セットの処理

PL/SQLでは、従来のデータベース・プログラミングと同様に、カーソルを使用して問合せ結果セットを処理できます。ただし、PL/SQLでは、暗黙カーソルまたは明示カーソルを使用できます。

前者ではコードが削減されますが、後者ではより柔軟な処理が可能です。たとえば、明示カーソルはパラメータを受け取ることができます。

次のPL/SQL文では、PL/SQLによって自動で定義および管理される暗黙カーソルを使用します。

• SELECT INTO

• 暗黙カーソルFOR LOOP

次のPL/SQL文では、明示カーソルを使用します。

• 明示カーソルFOR LOOP

  ユーザーが明示カーソルを定義しますが、文の実行中はPL/SQLが管理します。

• OPEN、FETCHおよびCLOSE

  ユーザーが明示カーソルを定義および管理します。

ノート:

問合せによって行が戻されない場合、PL/SQLによって例外NO_DATA_FOUNDが呼び出されます。

ここでのトピック

• SELECT INTO文による問合せ結果セットの処理

• カーソルFOR LOOP文による問合せ結果セットの処理

• 明示カーソル、OPEN、FETCHおよびCLOSEによる問合せ結果セットの処理

• 副問合せによる問合せ結果セットの処理

関連項目:

• 「パラメータを受け入れる明示カーソル」

• クライアントへの結果セットの戻しの詳細は、『Oracle Database開発ガイド』を参照してください。

• 例外処理の詳細は、「例外ハンドラ」を参照してください

SELECT INTO文による問合せ結果セットの処理

暗黙カーソルを使用して、SELECT INTO文は、1つ以上のデータベース表から値を取り出し(この処理はSQLのSELECT文と同様です)、それらの値を変数に格納します(この処理はSQLのSELECT文では実行されません)。

ここでのトピック

• 単一行の結果セットの処理

• 多数の複数行の結果セットの処理

関連項目:

SELECT INTO文の構文およびセマンティクスの詳細は、「SELECT INTO文」を参照してください

単一行の結果セットの処理

問合せで1つの行のみが戻されると予想される場合は、SELECT INTO文を使用して、その行の値を1つ以上のスカラー変数または1つのレコード変数に格納します。

問合せで複数の行が戻される可能性があるが、n番目の行のみを処理すればよい場合、WHERE ROWNUM=n句を使用して結果セットを目的の行に制限します。

関連項目:

• 「SELECT INTO文を使用した変数への値の代入」

• 「SELECT INTOを使用した行のレコード変数への代入」

• ROWNUM疑似列の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

多数の複数行の結果セットの処理

大量の表データを変数に代入する必要がある場合、BULK COLLECT句付きでSELECT INTO文を使用することをお薦めします。

この文は、結果セット全体を1つ以上のコレクション変数に取り出します。

詳細は、「BULK COLLECT句を使用したSELECT INTO文」を参照してください。

カーソルFOR LOOP文による問合せ結果セットの処理

カーソルFOR LOOP文では、SELECT文を実行してから、すぐに結果セットの行をループできます。

この文では、暗黙カーソルまたは明示カーソルを使用できます(ただしカーソル変数は使用できません)。

カーソルFOR LOOP文でのみSELECT文を使用する場合は、そのSELECT文をカーソルFOR LOOP文の内側で指定します。この形式のカーソルFOR LOOP文は、暗黙カーソルを使用するため、暗黙カーソルのFOR LOOP文と呼ばれます。暗黙カーソルは文に対して内部的であるため、SQLという名前で参照することはできません。

同じPL/SQLユニット内でSELECT文を複数回使用する場合は、そのユニットで明示カーソルを定義して、そのカーソルをカーソルFOR LOOP文で指定します。この形式のカーソルFOR LOOP文は、明示カーソルのFOR LOOP文と呼ばれます。同じPL/SQLユニット内の別の場所でも同じ明示カーソルを使用できます。

カーソルFOR LOOP文は、そのカーソルが戻す型の%ROWTYPEレコード変数として暗黙的にループ索引を宣言します。このレコードは、ループに対してローカルで、ループの実行中のみ存在します。ループの内側の文は、レコードとそのフィールドを参照できます。仮想列は別名でのみ参照できます。

ループ索引のレコード変数の宣言後、FOR LOOP文によって指定したカーソルがオープンされます。ループが反復されるたびに、FOR LOOP文によって結果セットから行がフェッチされ、レコードに格納されます。フェッチする行がなくなると、カーソルFOR LOOP文によってカーソルがクローズされます。また、ループの内側の文によって制御がループの外側に移された場合や、PL/SQLによって例外が呼び出された場合も、カーソルがクローズされます。

関連項目:

カーソルFOR LOOP文の構文およびセマンティクスの詳細は、「カーソルFOR LOOP文」を参照してください

ノート:

カーソルFOR LOOP文の内側で例外が呼び出されると、カーソルは、例外ハンドラが実行される前にクローズされます。したがって、ハンドラでは明示カーソルの属性の値を参照できません。

例7-18 暗黙カーソルFOR LOOP文

この例では、暗黙カーソルFOR LOOP文で、担当マネージャのIDが120より大きいすべての事務員の名前および職種IDを出力します。

BEGIN
  FOR item IN (
    SELECT last_name, job_id
    FROM employees
    WHERE job_id LIKE '%CLERK%'
    AND manager_id > 120
    ORDER BY last_name
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

結果:

Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK

例7-19 明示カーソルFOR LOOP文

この例は例7-18と似ていますが、明示カーソルFOR LOOP文を使用しています。

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE job_id LIKE '%CLERK%' AND manager_id > 120
    ORDER BY last_name;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

結果:

Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK

例7-20 明示カーソルFOR LOOP文へのパラメータの引渡し

この例では、2つのパラメータを受け入れる明示カーソルを宣言および定義し、明示カーソルFOR LOOP文でそのカーソルを使用して、指定された部門内で給与が指定額を超える従業員の給与を示します。

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
    WHERE job_id = job
    AND salary > max_wage;
BEGIN
  FOR person IN c1('ST_CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE (
      'Name = ' || person.last_name || ', salary = ' ||
      person.salary || ', Job Id = ' || person.job_id
    );
  END LOOP;
END;
/

結果:

Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
Name = Mallin, salary = 3300, Job Id = ST_CLERK
Name = Ladwig, salary = 3600, Job Id = ST_CLERK
Name = Stiles, salary = 3200, Job Id = ST_CLERK
Name = Rajs, salary = 3500, Job Id = ST_CLERK
Name = Davies, salary = 3100, Job Id = ST_CLERK

例7-21 カーソルFOR LOOPによる仮想列の参照

この例では、暗黙カーソルFOR LOOPで、仮想列をその別名full_nameおよびdream_salaryで参照しています。

BEGIN
  FOR item IN (
    SELECT first_name || ' ' || last_name AS full_name,
           salary * 10                    AS dream_salary 
    FROM employees
    WHERE ROWNUM <= 5
    ORDER BY dream_salary DESC, last_name ASC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE
      (item.full_name || ' dreams of making ' || item.dream_salary);
  END LOOP;
END;
/

結果:

Stephen King dreams of making 240000
Lex Garcia dreams of making 170000
Neena Yang dreams of making 170000
Alexander James dreams of making 90000
Bruce Miller dreams of making 60000

明示カーソル、OPEN、FETCHおよびCLOSEによる問合せ結果セットの処理

問合せ結果セットの処理を完全に制御するには、明示カーソルを宣言し、それらのカーソルをOPEN文、FETCH文およびCLOSE文を使用して管理します。

この結果セットの処理方法は、他の方法より複雑ですが、より柔軟な処理が可能です。たとえば、次のことが可能です。

• 複数のカーソルを使用して、複数の結果セットをパラレルに処理できます。

• 1回のループで複数の行を処理したり、行をスキップしたり、処理を複数のループに分割することができます。

• あるPL/SQLユニットで問合せを指定し、別のユニットで行を取り出すことができます。

詳細および例は、「明示カーソル」を参照してください。

副問合せによる問合せ結果セットの処理

問合せ結果セットを処理する際に、その結果セットをループして行ごとに別の問合せを実行する場合、ループの内側から2番目の問合せを削除し、その問合せを最初の問合せの副問合せにすると、パフォーマンスを向上できます。

通常の副問合せが表ごとに評価されるのに対し、相関副問合せは行ごとに評価されます。

副問合せの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

例7-22 親問合せのFROM句内の副問合せ

この例では、FROM句に副問合せを含む問合せを使用して明示カーソルc1を定義しています。

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, department_name, staff
    FROM departments t1,
         ( SELECT department_id, COUNT(*) AS staff
           FROM employees
           GROUP BY department_id
         ) t2
    WHERE (t1.department_id = t2.department_id) AND staff >= 5
    ORDER BY staff;

BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Department = '
       || dept.department_name || ', staff = ' || dept.staff);
   END LOOP;
END;
/

結果:

Department = IT, staff = 5
Department = Finance, staff = 6
Department = Purchasing, staff = 6
Department = Sales, staff = 34
Department = Shipping, staff = 45

例7-23 相関副問合せ

この例では、給与が部門平均を上回っている従業員の名前と給与を戻しています。相関副問合せでは、表の各行について、対応する部門の平均給与を計算します。

DECLARE
  CURSOR c1 IS
    SELECT department_id, last_name, salary
    FROM employees t
    WHERE salary > ( SELECT AVG(salary)
                     FROM employees
                     WHERE t.department_id = department_id
                   )
    ORDER BY department_id, last_name;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
  END LOOP;
END;
/

結果:

Making above-average salary = Martinez
Making above-average salary = Li
Making above-average salary = Bell
...
Making above-average salary = Higgins