テクノロジー：PL/SQL

コレクションの操作

Steven Feuerstein著

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

このシリーズの前回の記事では、PL/SQLレコードの操作方法を説明しました。PL/SQLレコードは、1つ以上のフィールドにより構成されるコンポジット・データ型です。この記事では、もう1つのコンポジット・データ型であるコレクションについて詳しく見ていきます。Oracle PL/SQLコレクションは1次元配列であり、インデックス値を使用してアクセスできる1つ以上の要素により構成されます。

コレクションは、次のようなPL/SQLのもっとも重要なパフォーマンス最適化機能の一部で使用されます。

  • BULK COLLECT：1回のフェッチで複数の行を取得するSELECT文。データ取得が高速化されます。
  • FORALL：コレクションを使用してデータの複数行をすばやく変更する挿入、更新、削除。
  • テーブル・ファンクション：コレクションを返すPL/SQLファンクション。SELECT文のFROM句でコールできます。

 また、データベース表に保存されていないデータのリストをプログラム内で操作するためにコレクションを使用することもできます。

この記事では、コレクションの概要を説明し、コレクションの構文と機能について基礎を身に付けます。

コレクションの概念と用語

コレクションについて詳しく見ていく前に、次の用語を含むコレクションの一般的なボキャブラリについて知っておくと今後役に立つでしょう。

インデックス値：コレクション内のデータの位置。インデックス値は通常は整数ですが、ある特定のコレクション型では文字列を使用することもできます。

要素：コレクション内の特定のインデックス値に保管されているデータ。コレクション内のデータは常に同じ型になります（すべてのデータは文字列、日付、またはレコードです）。PL/SQLのコレクションは同種です。

疎：最小の定義済みインデックス値から最大の定義済みインデックス値までの間に1つ以上の未定義のインデックス値がある場合、コレクションは疎です。たとえば、ある要素がインデックス値1に割り当てられ、別の要素がインデックス値10に割り当てられているけれども、その間に要素がないものは疎コレクションです。疎コレクションの逆は密コレクションです。

メソッド：コレクション・メソッドとは、コレクションに関する情報を提供する、あるいはコレクションのデータを変更するプロシージャまたはファンクションのことです。メソッドはドット表記法（オブジェクト指向の構文）によりコレクション変数に関連付けられます（例：my_collection.FIRST）。

コレクション型

コレクションは最初にOracle7 Serverで導入され、長年の間、Oracle Databaseの各バージョンにわたって複数の方法で強化されてきました。現在は3つのコレクション型から選択できます。それぞれの型に独自の特性があり、もっとも適した状況もそれぞれ異なります。

連想配列：PL/SQLで最初に使用できるようになったコレクション型で、当初は"PL/SQL表"と呼ばれていました。連想配列を使用できるのはPL/SQLブロック内のみです。連想配列は疎コレクションでも密コレクションでもよく、整数または文字列のインデックスを設定できます。

ネストした表：Oracle8 Databaseで追加されたコレクション型です。ネストした表はPL/SQLブロック内とSQL文で使用でき、表の列のデータ型としても使用できます。ネストした表は疎でも問題ありませんが、大部分は密になります。整数のインデックスのみを設定できます。MULTISET演算子を使用すれば、ネストした表に対して集合演算や等価比較を実行できます。

VARRAY：Oracle8 Databaseで追加されたコレクション型です。VARRAY（可変サイズの配列）はPL/SQLブロック内とSQL文で使用でき、表の列のデータ型としても使用できます。VARRAYは常に密であり、整数のインデックスのみを設定できます。VARRAY型を定義する場合は、その型を使用して宣言したコレクション内で許可される最大の要素数を指定する必要があります。

VARRAYの必要性はほとんどありません（コレクション内で定義する最大の要素数を前もって把握していることはほとんどないでしょう）。もっとも頻繁に使用するコレクション型は連想配列です。しかし、ネストした表にも、コレクションを使用するために記述する必要のあるコードをシンプルにできる強力な独自機能（MULTISET演算子など）があります。

ネストした表の例

リスト1のシンプルな例を見てみましょう。この例では、この記事の後半で詳しく説明する、コレクションのさまざまな側面を紹介しています。

コード・リスト1：ネストした表の例



1  DECLARE
  2     TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
  3
  4     happyfamily   list_of_names_t := list_of_names_t ();
  5     children      list_of_names_t := list_of_names_t ();
  6     parents       list_of_names_t := list_of_names_t ();
  7  BEGIN
  8     happyfamily.EXTEND (4);
  9     happyfamily (1) := ‘Veva’;
 10     happyfamily (2) := ‘Chris’;
 11     happyfamily (3) := ‘Eli’;
 12     happyfamily (4) := ‘Steven’;
 13
 14     children.EXTEND;
 15     children (children.LAST) := ‘Chris’;
 16     children.EXTEND;
 17     children (children.LAST) := ‘Eli’;
 18
 19     parents := happyfamily MULTISET EXCEPT children;
 20
 21     FOR l_row IN 1 .. parents.COUNT
 22     LOOP
 23        DBMS_OUTPUT.put_line (parents (l_row));
 24     END LOOP;
 25  END;
行 説明
2 新しいネストした表型を宣言しています。この型を使用して宣言したコレクションの各要素は、最大長が100の文字列です。
4–6 新しく宣言したコレクション型に基づいて、happyfamily、children、parentsという3つのネストした表を宣言しています。また、型と同じ名前のコンストラクタ・ファンクションをコールして、各変数にデフォルト値を代入しています。
8 happyfamilyのEXTENDメソッドをコールして、4つの要素を格納するための"スペースを作成"しています。
9-12 家族（妻のVeva、2人の息子ChrisとEli、私）の名前を代入しています。通常の1次配列構文であるarray_name (index_value)という形式を使用して、配列内の要素を識別しています。
14-17 childrenというネストした表に、息子の名前のみを設定しています。ここでは、8行目のように"バルク"拡張を実行するのではなく、1回ずつインデックス値を拡張しています。その後、LASTメソッドをコールして、追加したばかりのインデックス値に名前を代入しています。このメソッドにより、コレクション内の定義済みの最大インデックス値が返されます。必要となる要素数を前もって把握していないのであれば、このように1行拡張して最新の最大インデックス値に値を代入するアプローチが便利です。
19 子供は両方とも成人で、先祖代々の実家からはすでに引越ししています。では、寝室が余るこの場所には、誰が残されているでしょうか。まずhappyfamilyを扱い、そこから子供を（MULTISET EXCEPT演算子を使用して）抜きます。このセット演算子の結果をparentsコレクションに代入します。これで、VevaとStevenだけになるはずです。
21-24 MULTISET演算子の結果は、空か、インデックス値1から始まる密コレクションになります。ここでは、このコレクションのすべての要素に対して、1からCOUNT（コレクション内に定義された要素数）までの繰り返し処理を実行し、各インデックス値で見つかった要素を表示します。

リスト1のブロックを実行すると、次の出力が表示されます。

  Veva  Steven

リスト1には、コード・ブロック内の行への参照や、これらの行がネストした表の例にどのように関わるかの説明も記載しています。

コレクション型と変数の宣言

コレクション変数を宣言して使用するには、変数が基づく型を定義しておく必要があります。Oracle Databaseには、DBMS_SQLやDBMS_UTILITYなど、提供されるパッケージ内に複数の事前定義コレクション型があります。そのため、たとえば最大長が32767の文字列の連想配列を宣言する必要がある場合、次のように記述できます。

l_names DBMS_UTILITY.maxname_array;

しかし、ほとんどの場合は、独自のアプリケーション固有コレクション型を宣言することになります。さまざまなコレクション型の宣言例を次に示します。

  1. 整数のインデックスを設定した数値の連想配列を宣言します。 
    TYPE numbers_aat IS TABLE OF NUMBER      INDEX BY PLS_INTEGER;
  2. 文字列のインデックスを設定した数値の連想配列を宣言します。 
    TYPE numbers_aat IS TABLE OF NUMBER      INDEX BY VARCHAR2(100);
  3. 数値のネストした表を宣言します。 
      TYPE numbers_nt IS TABLE OF NUMBER;
  4. 数値のVARRAYを宣言します。 
    TYPE numbers_vat IS VARRAY(10)   OF NUMBER;

注：接尾辞として、_aat（連想配列型）、_nt（ネストした表型）、_vat（VARRAY型）をそれぞれ使用しています。



PL/SQL Challengeの正解

前号の"PL/SQLでのレコードの操作"で出題されたPL/SQL Challenge
の質問では、表またはカーソルに基づいてレコード変数を宣言する方法について、知識をテストしました。質問の内容は、
問題の表の値（"Keyboard"）が表示するために問題のコード・ブロックで使用できるコードは次のうちどれか、というものでした。
以下にすべての選択肢を示します。このうち、正しいのは(c)と(d)のみです。

a.

 
l_part   plch_parts%TYPE;
 

b.
 

l_part   plch_parts;
 

c.
 

l_part   plch_parts%ROWTYPE;
 

d. 

 
CURSOR parts_cur
      IS
      SELECT * FROM plch_parts;

  l_part   parts_cur%ROWTYPE;

コレクション型を定義するための構文でcollectionという語を使用しないのはなぜかと疑問を持たれるかもしれません。これは、Oracle7 Serverで最初にIS TABLE OF構文が導入されたからです。当時はPL/SQL表という1つのコレクション型しかありませんでした。

上記の例から、コレクション型について次のような結論を導くことができます。

  • TYPE文にINDEX BY句が含まれている場合は、コレクション型は連想配列である。
  • TYPE文にVARRAYキーワードが含まれている場合は、コレクション型はVARRAYである。
  • TYPE文にINDEX BY句もVARRAYキーワードも含まれていない場合は、コレクション型はネストした表である。
  • インデックスのデータ型を選択できるのは、連想配列だけである。ネストした表とVARRAYでは、常に整数によるインデックスを設定する。
  • VARRAY型を定義した場合は、その型のコレクションに定義可能な最大の要素数を指定する。

コレクション型を宣言した後は、他の種類の変数を宣言するのと同じように、このコレクション型を使用してコレクション変数を宣言できます。



DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER; 
   l_numbers numbers_nt;

コレクションの初期化

ネストした表およびVARRAYを操作するときには、使用前にコレクション変数を初期化する必要があります。初期化するには、その型のコンストラクタ・ファンクションをコールします。このファンクションは、型の宣言時にOracle Databaseによって自動的に作成されます。コンストラクタ・ファンクションは、ファンクションに関連付けられた型のインスタンスを構成します。このファンクションは引数なしでコールできます。また、コレクションの要素と同じ型の式を1つ以上渡すこともできます。その場合、渡した式がコレクションに挿入されます。

3つの要素（1、2、3）を持つ数値で構成されるネストした表の初期化の例を次に示します。



DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER; 
   l_numbers numbers_nt;
BEGIN
   l_numbers := numbers_nt (1, 2, 3);
END;

コレクションの初期化を忘れた場合、そのコレクションの使用時にOracle Databaseのエラーが発生します。



SQL> DECLARE
  2    TYPE numbers_nt IS TABLE OF NUMBER;
  3    l_numbers numbers_nt;
  4  BEGIN
  5    l_numbers.EXTEND;
  6    l_numbers(1) := 1;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-06531:Reference to uninitialized collection
ORA-06512: at line 5

連想配列の場合は、値を代入する前に初期化する必要はありません。

コレクションのデータの設定

コレクション内の要素には、さまざまな方法で値を代入できます。

  • コンストラクタ・ファンクションをコールする（ネストした表およびVARRAYの場合）
  • 代入演算子を使用する（1つの要素に対してもコレクション全体に対しても可能）
  • OUTパラメータまたはIN OUTパラメータとしてサブプログラムにコレクションを渡して、サブプログラム内部で値を代入する
  • BULK COLLECT問合せを使用する

前項では、コンストラクタ・ファンクションを使用する例を紹介しました。その他のアプローチについて、次に例を示します。

単一のインデックス値に数値を代入します。連想配列を使用する場合は、EXTENDを使用する必要はなく、インデックス値1から開始する必要もありません。



DECLARE
   TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER; 
   l_numbers numbers_aat;

BEGIN
   l_numbers (100) := 12345;
END;

あるコレクションに別のコレクションを代入します。同じ型を使用して両方のコレクションを宣言している限り、コレクションレベルの代入を実行できます。



DECLARE
   TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER; 
   l_numbers1 numbers_aat;
   l_numbers2 numbers_aat;
BEGIN
   l_numbers1 (100) := 12345;
   l_numbers2       := l_numbers1;
END;

コレクションをIN OUT引数として渡して、そのコレクションからすべての要素を削除します。



DECLARE
   TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;
   l_numbers   numbers_aat;

   PROCEDURE empty_collection (
      numbers_io IN OUT numbers_aat)
   IS
   BEGIN
      numbers_io.delete;
   END;
BEGIN
   l_numbers (100) := 123;
   empty_collection (l_numbers);
END;

BULK COLLECTを使用した問合せによって、コレクションにデータを直接設定します（BULK COLLECTについては、このシリーズの次回の記事で詳しく取り上げます）。



DECLARE
   TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   l_numbers   numbers_aat;
BEGIN
     SELECT employee_id
       BULK COLLECT INTO l_numbers
       FROM employees
   ORDER BY last_name;
END;

コレクションに対する繰り返し処理

非常によく使用するコレクションの操作として、コレクションのすべての要素に対する繰り返し処理が挙げられます。"コレクションの全体スキャン"を実行する理由には、コレクションの情報表示、要素内のデータを使用したデータ操作言語（DML）文の実行、特定のデータの検索などがあります。

コレクションに対する繰り返し処理のためにどのような種類のコードを記述するかは、操作するコレクション型と、コレクション型へのデータの設定方法によって決まります。一般的には、数値によるFORループとWHILEループから選択します。

数値によるFORループは次の状況で使用します。

  • コレクションが密にデータ設定されている（最小値から最大値までのすべてのインデックス値が定義されている）
  • 何らかの条件を満たした場合にスキャンを終了するのではなく、コレクション全体をスキャンしたい

逆に、WHILEループは次の状況で使用します。

  • コレクションが疎である可能性がある
  • コレクション内のすべての要素に対して繰り返し処理を実行せずにループを終了する可能性がある

密コレクションに数値によるFORループを使用することで、NO_DATA_FOUND例外の発生を防ぐことができます。一方、コレクション内で未定義のインデックス値の要素を"読み取ろうと"した場合には、Oracle DatabaseでNO_DATA_FOUND例外が発生します。

たとえば、次のブロックではNO_DATA_FOUND例外が発生します。



DECLARE
  TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER; 
  l_numbers numbers_aat;
BEGIN
  DBMS_OUTPUT.PUT_LINE (l_numbers (100));
END;

しかし、コレクションが現在もこれからも常に密にデータ設定されると確信できる場合は、FORによるループが目的を達成するもっともシンプルなコードになります。たとえば、リスト2のプロシージャは、DBMS_UTILITYパッケージに定義されている型のコレクションに含まれる文字列をすべて表示します。

コード・リスト2：コレクション内のすべての文字列の表示



CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
BEGIN
   FOR indx IN names_in.FIRST .. names_in.LAST
   LOOP
      DBMS_OUTPUT.put_line (names_in (indx));
   END LOOP;
END;
/

このプロシージャは、FIRSTとLASTという2つのメソッドをコールしています。FIRSTはコレクション内の最小の定義済みインデックス値を返し、LASTはコレクション内の最大の定義済みインデックス値を返します。

次のブロックは、3人の芸術家の名前を表示します。インデックス値を1から開始する必要はない点に注意してください。



DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (100) := ‘Picasso’;
   l_names (101) := ‘O’’Keefe’; 
   l_names (102) := ‘Dali’;=
   show_contents (l_names);
END;
/

コレクションが疎である場合、またはループを条件により終了したい場合は、WHILEループが適しています。リスト3のプロシージャに、このアプローチを示します。

コード・リスト3：コレクションに対するWHILEを使用した繰り返し処理



CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
   l_index   PLS_INTEGER := names_in.FIRST;
BEGIN
   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (names_in (l_index));
      l_index := names_in.NEXT (l_index);
   END LOOP;
END;
/

このプロシージャでは、最初にイテレータ（l_index）に最小の定義済みインデックス値を設定しています。コレクションが空の場合は、FIRSTもLASTもNULLを返します。l_indexがNULLの場合はWHILEループが終了します。その後、現在のインデックス値の名前を表示し、NEXTメソッドをコールして、次に定義されているl_indexよりも大きいインデックス値を取得しています。より大きいインデックス値がない場合は、このファンクションはNULLを返します。

データが順次的に設定されていないコレクションを使用して、このプロシージャを次のブロックでコールしましょう。そうすると、NO_DATA_FOUNDが発生せずに、3人の名前が表示されます。



DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (-150) := 'Picasso';
   l_names (0) := 'O''Keefe'; 
   l_names (307) := 'Dali';
   show_contents (l_names);
END;
/

また、コレクションの内容を逆方向にスキャンすることもできます。この場合は、リスト4のようにLASTより開始し、PRIORメソッドを使用します。

コード・リスト4：コレクションの逆方向スキャン



CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
   l_index   PLS_INTEGER := names_in.LAST;
BEGIN
   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (names_in (l_index));
      l_index := names_in.PRIOR (l_index);
   END LOOP;
END;
/

次のステップ 

Feuersteinによるその他の情報

コレクションの要素の削除

PL/SQLが提供するDELETEメソッドを使用して、コレクションのすべての要素、1つの要素、または一部の要素を削除できます。次にいくつかの例を挙げます。

コレクションからすべての要素を削除します。引数を指定せずにDELETEメソッドを使用します。このDELETEの形式は、3つのコレクション型のすべてで機能します。

l_names.DELETE;

コレクション内の最初の要素を削除します。1つの要素を削除するには、DELETEにそのインデックス値を渡します。このDELETEの形式は、連想配列またはネストした表でのみ使用できます。

l_names.DELETE (l_names.FIRST);

指定した最小インデックス値から最大インデックス値までのすべての要素を削除します。このDELETEの形式は、連想配列またはネストした表でのみ使用できます。

l_names.DELETE (100, 200);

未定義のインデックス値を指定した場合、Oracle Databaseのエラーは発生しません。

VARRAYとネストした表では、TRIMメソッドを使用してコレクションの最後から要素を削除することもできます。トリムは、1つの要素でも複数の要素でも実行できます。

l_names.TRIM;  l_names.TRIM (3);

コレクションを使用して便利に

コレクションを使用しなければ、一部の強力な機能を含め、PL/SQLを最大限活かすことはできません。この記事では、コレクションを操作するための基礎を身に付けましたが、文字列によるインデックス、ネストしたコレクションなどのいくつかの高度な機能についてはまだ詳しく見ていません。これらの機能については今後の記事で取り上げます。

このPL/SQLの基礎に関するシリーズの次回の記事では、もっとも重要なPL/SQLのパフォーマンス関連機能である、FORALLとBULK COLLECTによるコレクションの利用方法を説明します。



クイズにチャレンジ

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

質問
次のブロックのうち、実行後に以下の3行を出力するものはどれですか。

 


Strawberry
Raspberry
Blackberry
 

a.
 

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

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

b.

 

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   indx := l_names.FIRST;

   WHILE (indx IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
      indx := l_names.NEXT (indx);
   END LOOP;
END;
/
 

c.

 

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   DECLARE
      indx   PLS_INTEGER := l_names.FIRST;
   BEGIN

      WHILE (indx IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line (l_names (indx));
         indx := l_names.NEXT (indx);
      END LOOP;
   END;
END;
/
 

d.

 

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   FOR indx IN l_names.FIRST .. l_names.LAST
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
   END LOOP;
END;
/
ご意見ご感想をお寄せください。

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