Windows Server System Center

PL/SQLの理解と利用に関するシリーズ記事のパート12

Oracle PL/SQL言語のおもな目的は、データベース内の表の内容をできる限り簡単かつ効率的に問い合わせて、変更できるようにすることです。言うまでもなく、表にアクセスするにはSQL言語を使用する必要があります。そして、表にアクセスするたびに、カーソルを使用して仕事を最後までやり遂げます。カーソルとは、SELECT文またはデータ操作言語（DML）文（INSERT、UPDATE、DELETE、MERGE）の処理に関する情報を保管したプライベートなSQL領域へのポインタです。DML文のカーソルの管理はOracle Databaseによって行われますが、PL/SQLではSELECT文を実行するためのカーソルを、さまざまな方法で定義して操作できます。この記事では、プログラマーがPL/SQLでSELECT文を実行する一般的な方法について説明します。具体的には次のとおりです。 

• SELECT-INTO文の使用

• 明示カーソルからのフェッチ

• カーソルによるFORループの使用

• EXECUTE IMMEDIATE INTOを使用した動的問合せ

• カーソル変数の使用 

この記事の最後に、各シナリオに対してどの方法を使用すべきかを判断するための簡単なヒントを示します。

SELECT-INTO

SELECT-INTOは、SELECT文から単一行をフェッチするためのもっとも速く簡単な方法です。この文の構文は次のようになります。

PL/SQL Challengeの正解

前号の"コードを整然としたパッケージにまとめる"で出題されたPL/SQL Challengeの質問は、パッケージレベルの変数に代入された値がセッション内でどのように存続するかに関するもので、「次のうち、実行後に"3"と表示されるものはどれですか」という内容でした。この記事の印刷版では、aのみが正解とされていました。このオンライン版（ PL/SQL Challenge のクイズと同様）では、aとbの両方が正解となります。このクイズの正解の詳しい説明については、PL/SQL Challengeにアクセスしてください。  

SELECT select_list INTO variable_list FROM remainder_of_query;  

remainder_of_queryには表またはビューのリスト、WHERE句、および問合せのその他の句が含まれます。variable_listの要素の個数とタイプは、select_listの要素の個数とタイプに一致する必要があります。

SELECT文でフェッチすべき行が複数見つかった場合は、Oracle DatabaseのTOO_MANY_ROWS例外が発生します。また、SELECT文でフェッチすべき行が見つからなかった場合は、Oracle DatabaseのNO_DATA_FOUND例外が発生します。

SELECT-INTOの使用例を示します。

次の例では、特定の従業員ID（employees表の主キー）に対応する従業員の姓を取得します。 

DECLARE
  l_last_name  employees.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO l_last_name
    FROM employees
   WHERE employee_id = 138;

  DBMS_OUTPUT.put_line (
     l_last_name);
END;

employees表にID 138の行が存在する場合は、このブロックの実行によって、その従業員の姓が表示されます。そのような行が存在しない場合は、未処理のNO_DATA_FOUND例外により、このブロックの実行が失敗します。employee_id列に一意索引が定義されていると仮定すると、このブロックでTOO_MANY_ROWS例外が発生することはありません。

次の例では、employees表から、特定の従業員IDに対応する行全体をフェッチします。 

DECLARE
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees
    WHERE employee_id = 138;

   DBMS_OUTPUT.put_line (
      l_employee.last_name);
END;

1つ前の例と同様に、そのIDの従業員が存在する場合に、姓が表示されます。ただし、この例の場合は、employees表に基づくレコードを宣言し、指定した行のすべての列をフェッチして（SELECT *を使用）、そのレコードに格納しています。

次の例では、異なる複数の表から列をフェッチします。 

DECLARE
  l_last_name         
     employees.last_name%TYPE;
  l_department_name   
     departments.department_name%TYPE;
BEGIN
  SELECT last_name, department_name
    INTO l_last_name, l_department_name
    FROM employees e, departments d
   WHERE e.department_id=d.department_id
         AND e.employee_id=138;

  DBMS_OUTPUT.put_line (
     l_last_name || 
     ' in ' || 
     l_department_name);
END;

この例では、表の一方または両方について複数の列の値が必要ですが、すべての列の値は必要ではありません。そのため、2つの変数を宣言し、2つの列の値をフェッチしてそれらの変数に格納しています。

INTO句の変数のリストが問合せのSELECT構文のリストと一致しない場合はどうなるでしょうか。その場合は、表1に示すエラー・メッセージのいずれかが表示されます。

表1：INTO句のリストとSELECT構文のリストが一致しない場合に起こりうるエラー・メッセージ

明示カーソルからのフェッチ

SELECT-INTOは暗黙的な問合せとも呼ばれます。Oracle Databaseが暗黙的にSELECT文のカーソルをオープンし、行をフェッチし、処理の完了時に（または例外の発生時に）カーソルをクローズするからです。

これとは別に、開発者が明示的にカーソルを宣言して、オープン操作、フェッチ操作、クローズ操作を実行することもできます。

たとえば、給与の低い順に従業員をフェッチし、次のようなヘッダーを持つassign_bonusプロシージャをコールして、総資金プールから賞与を割り当てるブロックを記述する必要があるとします。 

PROCEDURE assign_bonus (     employee_id_in IN             employees.employee_id%TYPE,     bonus_pool_io  IN OUT INTEGER)  

assign_bonusはコールされるたびに総資金から割り当てる賞与を減算し、その減算後の総資金を返します。この賞与プールが底をついたときに、フェッチを停止し、すべての変更をコミットします。

リスト1に、明示カーソルを使用してこのロジックを実装したブロックを示し、さらにこのブロック内の具体的な行番号の各操作について説明します。

コード・リスト1：明示カーソルを実装したブロックとその説明 

1  DECLARE
 2     l_total       INTEGER := 10000;
 3
 4     CURSOR employee_id_cur
 5     IS
 6          SELECT employee_id
 7            FROM plch_employees
 8        ORDER BY salary ASC;
 9
10     l_employee_id   employee_id_cur%ROWTYPE;
11  BEGIN
12     OPEN employee_id_cur;
13
14     LOOP
15        FETCH employee_id_cur INTO l_employee_id;
16        EXIT WHEN employee_id_cur%NOTFOUND;
17
18        assign_bonus (l_employee_id, l_total);
19        EXIT WHEN l_total <= 0;
20     END LOOP;
21
22     CLOSE employees_cur;
23  END;

行 説明

明示カーソルを操作する際の注意点は次のとおりです。

• 問合せで1行も見つからなかった場合に、Oracle DatabaseのNO_DATA_FOUNDは発生しない。代わりに、cursor_name%NOTFOUND属性がTRUEを返す。

• 問合せで複数行を返すこともでき、Oracle DatabaseのTOO_MANY_ROWS例外は発生しない。

• パッケージ内でカーソルを宣言し（パッケージのサブプログラム内ではない）、カーソルをオープンした場合、明示的にクローズするかセッションが終了するまでは、カーソルはオープン状態のままである。

• カーソルを宣言セクション（パッケージ内ではない）で宣言している場合は、宣言されたブロックが終了したときにカーソルが自動的にクローズされる。ただし、カーソルを明示的にクローズすることは良いアイデアである。このカーソルをパッケージに移動した場合に必要となるCLOSEがすでに記述されていることになる。また、ローカルのカーソルの場合でも、CLOSE文を記述しておくことで、他の開発者やマネージャーに対して、自分が慎重に開発していることを示すことができる。 

カーソルによるFORループの使用

カーソルによるFORループは、PL/SQLの数値によるFORループを明確かつ自然に拡張した形式です。数値によるFORループでは、範囲内に指定した最小値から最高値までのすべての整数値に対して、1回ずつループの本体が実行されます。一方、カーソルによるFORループでは、問合せから返された行ごとにループの本体が実行されます。

次のブロックでは、カーソルによるFORループを使用して、部門10に所属するすべての従業員の姓を表示します。 

BEGIN
   FOR employee_rec IN (
        SELECT *
          FROM employees
         WHERE department_id = 10)
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;

また、明示的にカーソルを宣言して、カーソルによるFORループを使用することもできます。 

DECLARE
   CURSOR employees_in_10_cur
   IS
      SELECT *
        FROM employees
       WHERE department_id = 10;
BEGIN
   FOR employee_rec 
   IN employees_in_10_cur
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;

カーソルによるFORループの優れた点は、Oracle Databaseがカーソルをオープンし、そのカーソルに対して%ROWTYPEを使用してレコードを宣言し、各行をフェッチしてレコードに格納し、すべての行のフェッチ終了時に（またはその他の理由によるループ終了時に）ループをクローズすることです。

特に、Oracle DatabaseはカーソルによるFORループを自動的に最適化して、BULK COLLECT問合せと同様のことを実行します（BULK COLLECTについては、Oracle Magazine 2012年9/10月号の"BULK COLLECTとFORALLによるバルク処理"を参照）。そのため、コード上は一度に1行ずつフェッチしているように見えても、実際にはOracle Databaseにより一度に100行ずつフェッチされ、その各行を開発者が個々に操作できます。

EXECUTE IMMEDIATEを使用した動的問合せ

動的SQLは、コードの記述時（およびコンパイル時）にSQL文の解析に必要となるすべての情報がなく、SQL文の完成、解析、実行のために実行時まで待つ必要がある場合に使用します。

Oracle Databaseでは、EXECUTE IMMEDIATE文を使用することで、SQL文（およびPL/SQLブロック）の動的な実行を簡単に行うことができます。そして、データの問合せは、あらゆる操作の中でもっとも簡単な動的SQL操作です。

フェッチする対象は単一行でも複数行でもかまいません。次に、指定したWHERE句の条件に一致する、任意の表内の特定の数値列の値をフェッチする汎用ファンクションを示します。 

CREATE OR REPLACE FUNCTION 
single_number_value (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
   RETURN NUMBER
IS
   l_return   NUMBER;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      INTO l_return;
   RETURN l_return;
END;

この例では、SELECT-INTOの代わりにEXECUTE IMMEDIATE-INTOを使用し、ファンクションに渡された引数に基づいてSELECT文を構成しています。このファンクションのコール例は次のとおりです。 

BEGIN
   DBMS_OUTPUT.put_line (
      single_number_value (
                'employees',
                'salary',
                'employee_id=138'));
END;

SELECT-INTOと同様に、EXECUTE IMMEDIATE-INTOでも行が見つからない場合はNO_DATA_FOUNDが発生し、複数の行が見つかった場合はTOO_MANY_ROWSが発生します。

EXECUTE IMMEDIATEを使用して複数行のデータをフェッチすることもできます。この際には、コレクションのデータを設定することになるため、BULK COLLECTを使用する必要があります。次に、WHERE句に指定されたすべての行について、すべての数値列の値を表示するプロシージャを示します。 

CREATE OR REPLACE PROCEDURE 
show_number_values (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
IS
   TYPE values_t IS TABLE OF NUMBER;

   l_values   values_t;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      BULK COLLECT INTO l_values;

   FOR indx IN 1 .. l_values.COUNT
   LOOP
      DBMS_OUTPUT.put_line 
      (l_values (indx));
   END LOOP;
END;

このプロシージャを標準的なemployees表に対してコールしてみましょう。 

BEGIN
   show_number_values (
      'employees',
      'salary',
      'department_id = 10 
       order by salary desc');
END;

その結果、次のような2行が出力されます。 

4400
3200

動的SQL、特にこの記事で示した例については一般的な注意点があります。テキストを連結して動的実行文を実行するときには、かならずSQLインジェクションのリスクが伴います。SQLインジェクションは、悪意のあるユーザーがSQL文に、そのSQL文の動作を変えるようなコードを"インジェクション"する（挿入する）場合に発生します。

SQLインジェクションによるセキュリティ侵害を避けるためのアドバイスについては、"インジェクションに負けないPL/SQLを記述するには(PDF)"を参照してください。

カーソル変数

カーソル変数とは、その名前からも分かるように、カーソルまたは結果セットをポイントする変数のことです。明示カーソルを使用する場合とは異なり、プロシージャまたはファンクションの引数としてカーソル変数を渡すことができます。カーソル変数については、次のような優れたユースケースがいくつかあります。 

• プログラム・ユニットをコールしたホスト環境にカーソル変数を返す。ホスト環境では表示やその他の処理のために結果セットを利用できる。

• ファンクション内部で結果セットを構成し、その結果セットへのカーソル変数を返す。これは特に、SQLに加えてPL/SQLを使用して結果セットを作成する必要がある場合に便利である。

• 強力で非常に高度な最適化技術であるパイプライン・テーブル・ファンクションに対してカーソル変数を渡す。強いREF CURSOR型と弱いREF CURSOR型の違いなど、カーソル変数の詳細についてはこの記事では割愛する。

• 代わりに、この記事ではカーソル変数の操作に関する基本構文を説明し、この機能の利用を検討すべき状況を明らかにする。

カーソル変数は、埋込みSQL（静的SQL）と動的SQLのいずれかで使用できます。リスト2にnames_forファンクションを示します。このファンクションは、ファンクションに渡された引数に応じて従業員名または部門名をフェッチするカーソル変数を返します。

コード・リスト2：カーソル変数を返すnames_forファンクションのブロックとその説明 

1  CREATE OR REPLACE FUNCTION names_for (
 2        name_type_in IN VARCHAR2)
 3     RETURN SYS_REFCURSOR
 4  IS
 5     l_return   SYS_REFCURSOR;
 6  BEGIN
 7     CASE name_type_in
 8        WHEN 'EMP'
 9        THEN
10           OPEN l_return FOR
11                SELECT last_name
12                  FROM employees
13              ORDER BY employee_id;
14        WHEN 'DEPT'
15        THEN
16           OPEN l_return FOR
17                SELECT department_name
18                  FROM departments
19              ORDER BY department_id;
20     END CASE;
21
22     RETURN l_return;
23  END names_for;

行 説明

次のブロックでは、names_forファンクションを使用してdepartments表のすべての名前を表示します。 

DECLARE
   l_names   SYS_REFCURSOR;
   l_name    VARCHAR2 (32767);
BEGIN
   l_names := names_for ('DEPT');

   LOOP
      FETCH l_names INTO l_name;

      EXIT WHEN l_names%NOTFOUND;
      DBMS_OUTPUT.put_line (l_name);
   END LOOP;

   CLOSE l_names;
END;

  この例から分かるように、オープンする問合せに関するすべての情報がファンクション・ヘッダーの裏に"隠されます"。開発者がすべきことは、特定の表に関する名前情報（"names for"）の取得を依頼することだけです。このファンクションは適切なSELECT文を選択し、その文のカーソル変数をオープンして、その結果セットをポイントした変数を返します。

カーソル変数がオープンされブロックに返された後は、明示カーソルを使用する場合と同じコードをカーソル変数とともに使用します。 

• カーソル（変数）からフェッチし、1つ以上の変数に格納します（FETCH-BULK COLLECT INTOをカーソル変数とともに使用して、複数行を含むコレクションのデータを設定することもできます）。

• カーソル変数の%NOTFOUND属性をチェックし、すべての行のフェッチが完了したかを確認します。

• 完了後は、カーソル変数をクローズします。 

OPEN-FOR文はカーソル変数固有の機能で、動的SQLに切り替えなくても、カーソル変数によってフェッチするデータセットを実行時に指定できます。

次のステップ 

ダウンロード

Oracle Database 11g

 テスト  PL/SQLの知識

 その他の記事 PL/SQLの基礎、パート1～11  

詳細情報

BULK COLLECTとFORALL

SQLインジェクション (PDF)

ただし、OPEN-FORを動的SELECT文とともに使用することも可能です。次に、非常にシンプルな例を示します。 

CREATE OR REPLACE FUNCTION 
numbers_from (
      query_in IN VARCHAR2)
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;
BEGIN
   OPEN l_return FOR query_in;

   RETURN l_return;
END numbers_from;

また、次のブロックは、部門10に所属する従業員のすべての給与を表示します。このブロックは、前述のnames_forをコールするブロックとほぼ同じです。 

DECLARE
  l_salaries   SYS_REFCURSOR;
  l_salary     NUMBER;
BEGIN
  l_salaries :=
    numbers_from (
      'select salary 
        from employees 
       where department_id = 10');

  LOOP
    FETCH l_salaries INTO l_salary;

    EXIT WHEN l_salaries%NOTFOUND;
    DBMS_OUTPUT.put_line (l_salary);
  END LOOP;

  CLOSE l_salaries;
END;

正しい問合せ方法の選択

この記事では、SELECT-INTOを使用したもっとも単純な暗黙的な問合せから、もっと複雑なカーソル変数まで、カーソルを使用してリレーショナル表からデータをフェッチし、ローカル変数に格納するためのPL/SQL言語を使用したさまざまな方法を説明してきました。

どの方法を使用すべきかを判断するためのガイドラインは次のとおりです。 

• 単一行をフェッチする場合は、SELECT-INTOまたはEXECUTE IMMEDIATE-INTO（動的問合せの場合）を使用する。明示カーソルやカーソルによるFORループは使用しない。

• 問合せからすべての行をフェッチする場合は、ループの本体で1つ以上のDML文（INSERT、UPDATE、DELETE、MERGE）を実行しない限り、カーソルによるFORループを使用する。ループの本体でDML文を実行する場合は、BULK COLLECTおよびFORALLに切り替える。

• BULK COLLECTを使用してフェッチするが、各フェッチで返される行数を制限する必要がある場合は、明示カーソルを使用する。

• 複数行をフェッチするが、すべての行をフェッチする前に条件によって終了する可能性がある場合は、明示カーソルを使用する。

• フェッチする問合せが実行時に変わる（ただしかならずしも動的ではない）場合、特にPL/SQL以外のホスト環境に結果を返す必要がある場合は、カーソル変数を使用する。

• コードの記述時にSELECT文を完全に構成できない場合に限り、EXECUTE IMMEDIATEを使用してデータの問合せを行う。 

SELECT-INTOのファンクション内への移動

PL/SQL開発者は頻繁に、（通常）主キー値を指定して表の単一行のデータを取得する必要があります。そのため、主キーに基づいて検索する同じようなコードを何度も記述することになります。もっと良いアプローチは、SELECT-INTO問合せのそれぞれを、リクエストされた行の返却だけを目的とした1つのファンクション内に移動することです。そのため、次のようなコードを記述するのではなく、  

DECLARE
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees
    WHERE employee_id = 138;

   DBMS_OUTPUT.put_line (
      l_employee.last_name);
END;

まず次のようなファンクションを作成します。

CREATE OR REPLACE FUNCTION row_for_employee_id (
   employee_id_in IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees e
    WHERE e.employee_id = 
       row_for_employee_id.employee_id_in;

   RETURN l_employee;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;

これを利用すると、主キーに基づいた検索のための無名ブロックは次のようになります。

DECLARE l_employee employees%ROWTYPE; BEGIN l_employee := row_for_employee_id (138); DBMS_OUTPUT.put_line ( l_employee.last_name); END;

特に優れているのは、次にこのemployees表から特定のIDに一致する行を取得する必要がある場合に、このファンクションをコールするだけで済むことです。

このアプローチには、2つの大きなメリットがあります。

• 記述するコード量が減少し、ビルド済み、テスト済み、再利用可能なプログラムを使用できるため、生産性が向上します。

• その単一行の検索方法を変更する必要がある場合に、変更を1箇所（"唯一の定義ポイント"）で行うことで、そのファンクションをコールするすべてのプログラムで、改良版が即座に利用されます。

このファンクション内には、NO_DATA_FOUNDを捕捉後、単純にNULLレコードを返す例外ハンドラが含まれています。SELECT-INTOの実行中、データが存在しないことが実際にはエラーではなく単なるデータの条件の1つであることもよくあります。そのため、例外を捕捉して、行が見つからなかったことを示すインジケータを返すことは非常によくあることです（NULLは通常、このような状態を示すインジケータとして適していますが、適していない場合もあります）。ファンクションをコールしたプログラマーが、NO_DATA_FOUND条件への対処方法を決定することになります。

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは以下の他、 PL/SQL Challenge にも掲載されます。PL/SQL Challengeは、PL/SQL言語やSQL、Oracle Application Expressに関するオンライン・クイズを提供するWebサイトです。  

この記事のクイズ：

plch_employees表にはemployee_idという主キーの単一列があります。次のヘッダーを含むファンクションを記述する必要があります。FUNCTION plch_one_employee ( 

employee_id_in IN PLS_INTEGER)
   RETURN plch_employees%ROWTYPE

  このファンクションは、指定した主キーの行の情報を含むレコードを返します。この要件を満たすPLCH_ONE_EMPLOYEEの実装を表している選択肢はどれですか。

a.  

IS
   l_return   plch_employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN l_return;
END plch_one_employee;

b.

IS
   CURSOR one_emp_cur
   IS
      SELECT *
        FROM plch_employees
       WHERE employee_id = employee_id_in;

   l_return   one_emp_cur%ROWTYPE;
BEGIN
   OPEN one_emp_cur;
   FETCH one_emp_cur INTO l_return;
   CLOSE one_emp_cur;

   RETURN l_return;
END plch_one_employee;

c.

IS
   l_return   plch_employees%ROWTYPE;
BEGIN
   FOR rec IN (SELECT *
                 FROM plch_employees
                WHERE employee_id = employee_id_in)
   LOOP
      l_return := rec;
   END LOOP;

   RETURN l_return;
END plch_one_employee;

d.

IS
   l_cursor   SYS_REFCURSOR;

   l_return   plch_employees%ROWTYPE;
BEGIN
   OPEN l_cursor FOR
      SELECT *
        FROM plch_employees
       WHERE employee_id = employee_id_in;

   FETCH l_cursor INTO l_return;

   CLOSE l_cursor;

   RETURN l_return;
END plch_one_employee;

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください