PL/SQLのコレクションとレコード

5 PL/SQLのコレクションとレコード

PL/SQLでは、コレクションとレコードという、2種類のコンポジット・データ型を定義できます。

コンポジット・データ型には、内部コンポーネントを持つ値が格納されます。コンポジット変数全体をサブプログラムにパラメータとして渡すことや、コンポジット変数の内部コンポーネントに個別にアクセスすることができます。内部コンポーネントは、スカラーまたはコンポジットのいずれかです。スカラー・コンポーネントは、スカラー変数を使用できるすべての場所で使用できます。コンポジット・コンポーネントは、同じ型のコンポジット変数を使用できるすべての場所で使用できます。

ノート:

コンポジット変数をパラメータとしてリモート・サブプログラムに渡した場合、冗長なループバックDATABASE LINKを作成する必要がありますが、これにより、リモート・サブプログラムがコンパイルされたときに、ソースを検証するタイプ・チェッカが、実行者の使用しているものと同じユーザー定義のコンポジット変数型定義を使用するようになります。

collectionの内部コンポーネントは、常に同じデータ型であり、要素と呼ばれます。コレクション変数の各要素には、このvariable_name(index)という構文を使用して、その一意の索引によってアクセスできます。コレクション変数を作成するには、コレクション型を定義してからその型の変数を作成するか、%TYPEを使用します。

レコードの内部コンポーネントは、データ型が異なる場合があり、フィールドと呼ばれます。レコード変数の各フィールドには、variable_name.field_nameという構文を使用して、その名前によってアクセスできます。レコード変数を作成するには、RECORD型を定義してからその型の変数を作成するか、%ROWTYPEまたは%TYPEを使用します。

レコードのコレクション、およびコレクションを含むレコードを作成できます。

コレクションに関するトピック

関連項目:

CREATE DATABASE LINK文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
コレクションの問合せ
コレクションへの問合せ結果の取出しの詳細は、「BULK COLLECT句」を参照してください
コレクション型の定義およびコレクション変数の宣言の構文とセマンティクスは、「コレクション変数の宣言」を参照してください

レコードに関するトピック

ノート:

明示的にリストされたコンポジット・データ構造のコンポーネント(コレクション・コンストラクタまたはレコード・イニシャライザなど)はどの順序でも評価できます。プログラムにより評価の順序が決定される場合は、プログラムで決定が行われる時点で動作は定義されません。

5.1 コレクション型

PL/SQLには、連想配列、VARRAY (可変サイズの配列)およびネストした表の3つのコレクション型があります。

表5-1に、それぞれの類似点と相違点の概要を示します。

表5-1 PL/SQLコレクション型

コレクション型	要素の数	索引タイプ	密か疎か	未初期化時のステータス	定義される場所	ADT属性のデータ型として使用可能かどうか
連想配列(または索引付き表)	指定なし	文字列または`PLS_INTEGER`	密または疎のいずれか	なし	PL/SQLブロック内またはパッケージ内	不可
`VARRAY`(可変サイズの配列)	指定されている	整数	常に密	Null	PL/SQLブロック内、パッケージ内またはスキーマ・レベル	スキーマ・レベルで定義されている場合のみ
ネストした表	指定なし	整数	密で始まり、疎になる可能性あり	Null	PL/SQLブロック内、パッケージ内またはスキーマ・レベル	スキーマ・レベルで定義されている場合のみ

要素の数

要素の数が指定されている場合、その数がコレクション内の要素の最大数です。要素の数が指定されていない場合は、索引タイプの上限がコレクション内の要素の最大数になります。

密か疎か

dense collectionとは、要素間に欠損がないコレクションのことで、最初と最後の要素の間にあるすべての要素が定義され、値を含んでいます(要素にNOT NULL制約が指定されていない場合にかぎり、値にNULLを使用できます)。疎コレクションは、要素間に欠損があります。

未初期化時のステータス

空のコレクションは、要素を持たずに存在するコレクションです。空のコレクションに要素を追加するには、EXTENDメソッド(「EXTENDコレクション・メソッド」を参照)を起動します。

null collection(atomically null collectionとも呼ばれる)は存在しません。NULLのコレクションを存在するコレクションに変えるには、空にするかNULL以外の値を代入してコレクションを初期化する必要があります(詳細は「コレクションのコンストラクタ」および「コレクション変数への値の代入」を参照してください)。EXTENDメソッドを使用して、NULLのコレクションを初期化することはできません。

定義される場所

PL/SQLブロック内に定義されるコレクション型はローカル型です。ブロック内でのみ使用可能であり、スタンドアロン・サブプログラムまたはパッケージ・サブプログラム内にブロックがある場合にのみ、データベースに格納されます。(スタンドアロン・サブプログラムおよびパッケージ・サブプログラムの詳細は、ネストしたサブプログラム、パッケージ・サブプログラムおよびスタンドアロン・サブプログラムを参照してください。)

パッケージ仕様部に定義されるコレクション型はパブリック項目です。パッケージ名(package_name.type_name)で修飾することで、パッケージの外から参照できます。パッケージを削除するまでデータベースに格納されます。(パッケージについては、「PL/SQLパッケージ」を参照してください。)

スキーマ・レベルで定義されるコレクション型は、スタンドアロン型です。「CREATE TYPE文」を使用して作成します。「DROP TYPE文」を使用して削除するまでデータベースに格納されます。

ノート:

パッケージ仕様部で定義されたコレクション型は、同一定義のローカル・コレクション型またはスタンドアロン・コレクション型と互換性がありません(例5-33および例5-34を参照)。

ADT属性のデータ型として使用可能かどうか

ADT属性のデータ型にするには、コレクション型がスタンドアロン・コレクション型である必要があります。その他の制限は、「datatypeの制限」を参照してください。

非PL/SQLコンポジット型からPL/SQLコンポジット型への変換

他の言語を使用するコードまたはビジネス・ロジックが存在する場合、通常は配列を変換して、その言語の型をPL/SQLのコレクション型に直接設定できます。たとえば:

非PL/SQLコンポジット型	等価なPL/SQLコンポジット型
ハッシュ表	連想配列
順序付けされていない表	連想配列
集合	ネストした表
バッグ	ネストした表
配列	`VARRAY`

関連項目:

あるSQLデータ型またはコレクション型の値を別のSQLデータ型またはコレクション型の値に変換するCASTファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

5.2 連想配列

連想配列(旧称はPL/SQL表または索引付き表)は、キーと値のペアのセットです。各キーは一意の索引であり、構文variable_name(index)を使用して、関連する値を検索するために使用されます。

索引のデータ型には、文字列型(VARCHAR2、VARCHAR、STRINGまたはLONG)またはPLS_INTEGERを使用できます。索引は、作成された順序ではなく、ソートされた順序で格納されます。文字列型の場合、ソートの順序は初期化パラメータNLS_SORTおよびNLS_COMPで決定されます。

データベース表と同様に、連想配列には次の特性があります。

移入するまで空である(ただし、NULLではない)
数が指定されていない要素を保持できる(要素の位置を知らなくてもアクセスできる)

連想配列には、データベース表とは異なる次の特性があります。

ディスク領域またはネットワーク操作が不要
DML文では操作できない

ここでのトピック

関連項目:

表5-1に、結合配列の特性の概要を示します。
結合配列型の定義の構文は、「assoc_array_type_def ::=」を参照してください

例5-1 文字列で索引付けされている連想配列

この例では、文字列で索引付けされる連想配列型を定義し、この型の変数を宣言して3つの要素を変数に移入し、1つの要素の値を変更した後に値を(作成された順序ではなくソートされた順序で)出力します。(FIRSTおよびNEXTはコレクション・メソッドです。詳細は「コレクション・メソッド」を参照してください。)

Live SQL:

この例は、Oracle Live SQLの「文字列で索引付けされている連想配列」で表示および実行できます

DECLARE
  -- Associative array indexed by string:
  
  TYPE population IS TABLE OF NUMBER  -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by string
  
  city_population  population;        -- Associative array variable
  i  VARCHAR2(64);                    -- Scalar variable
  
BEGIN
  -- Add elements (key-value pairs) to associative array:
 
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
 
  -- Change value associated with key 'Smallville':
 
  city_population('Smallville') := 2001;
 
  -- Print associative array:
 
  i := city_population.FIRST;  -- Get first element of array
 
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Population of ' || i || ' is ' || city_population(i));
    i := city_population.NEXT(i);  -- Get next element of array
  END LOOP;
END;
/

結果:

Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

例5-2 PLS_INTEGERで索引付けされている連想配列を戻すファンクション

この例では、PLS_INTEGERで索引付けされている連想配列型と、その型の連想配列を戻すファンクションを定義します。

Live SQL:

この例は、Oracle Live SQLの「PLS_INTEGERで索引付けされている連想配列を戻すファンクション」で表示および実行できます

DECLARE
  TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  sn PLS_INTEGER := 10;  -- number of multiples to sum
  m  PLS_INTEGER := 3;   -- multiple

  FUNCTION get_sum_multiples (
    multiple IN PLS_INTEGER,
    num      IN PLS_INTEGER
  ) RETURN sum_multiples
  IS
    s sum_multiples;
  BEGIN
    FOR i IN 1..num LOOP
      s(i) := multiple * ((i * (i + 1)) / 2);  -- sum of multiples
    END LOOP;
    RETURN s;
  END get_sum_multiples;

BEGIN
  DBMS_OUTPUT.PUT_LINE (
    'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
    TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
  );
END;
/

結果:

Sum of the first 5 multiples of 3 is 45

5.2.1 連想配列の定数の宣言

連想配列の定数を宣言する場合、初期値を連想配列に移入するファンクションを作成して、定数宣言でそのファンクションを起動する必要があります。

コンストラクタの詳細は、「コレクションのコンストラクタ」を参照してください。

例5-3 連想配列の定数の宣言

この例では、コンストラクタがVARRAYまたはネストした表に対して行う操作を、ファンクションが連想配列に対して行います。

Live SQL:

この例は、Oracle Live SQLの「連想配列の定数の宣言」で表示および実行できます

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER IS
  TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  FUNCTION Init_My_AA RETURN My_AA;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types IS
  FUNCTION Init_My_AA RETURN My_AA IS
    Ret My_AA;
  BEGIN
    Ret(-10) := '-ten';
    Ret(0) := 'zero';
    Ret(1) := 'one';
    Ret(2) := 'two';
    Ret(3) := 'three';
    Ret(4) := 'four';
    Ret(9) := 'nine';
    RETURN Ret;
  END Init_My_AA;
END My_Types;
/
DECLARE
  v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();
BEGIN
  DECLARE
    Idx PLS_INTEGER := v.FIRST();
  BEGIN
    WHILE Idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));
      Idx := v.NEXT(Idx);
    END LOOP;
  END;
END;
/

結果:

-10   -ten
0   zero
1    one
2    two
3  three
4   four
9   nine
 
PL/SQL procedure successfully completed.

5.2.2 文字列で索引付けされている結合配列に影響を与えるNLSパラメータ値

文字列で索引付けされている連想配列は、NLS_SORT、NLS_COMP、NLS_DATE_FORMATなどの各国語サポート(NLS)のパラメータによって影響を受けます。

ここでのトピック

関連項目:

言語ソート・パラメータの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

5.2.2.1 結合配列に移入した後のNLSパラメータ値の変更

連想配列の文字列索引の格納順序は、初期化パラメータNLS_SORTおよびNLS_COMPで決まります。

文字列で索引付けされている連想配列にデータを移入した後で、いずれかのパラメータ値を変更すると、コレクション・メソッドFIRST、LAST、NEXTおよびPRIORから予期しない値が戻されたり例外が呼び出される場合があります。セッション中にこれらのパラメータ値を変更する必要がある場合は、文字列で索引付けされている連想配列に対して操作を実行する前に元の値をリストアしてください。

関連項目:

FIRST、LAST、NEXTおよびPRIORの詳細は、「コレクション・メソッド」を参照してください

5.2.2.2 VARCHAR2以外のデータ型の索引

文字列で索引付けされている連想配列の宣言では、文字列型をVARCHAR2またはそのサブタイプの1つにする必要があります。

ただし、連想配列への移入では、TO_CHARファンクションでVARCHAR2に変換できる任意のデータ型の索引を使用できます。

VARCHAR2およびそのサブタイプ以外のデータ型の索引を使用する場合は、初期化パラメータの値が変更されても索引の一貫性および一意性が保持されることを確認してください。たとえば:

TO_CHAR(SYSDATE)は索引として使用しないでください。

NLS_DATE_FORMATの値が変更された場合は、(TO_CHAR(SYSDATE))も変更される可能性があります。
それぞれ異なるNVARCHAR2の索引でも、同じVARCHAR2値に変換される可能性のある場合は使用しないでください。
大/小文字、アクセント記号またはデリミタ文字のみが異なるCHARまたはVARCHAR2の索引を使用しないでください。

NLS_SORTの値の末尾が_CI(大/小文字を区別しない比較)または_AI(アクセント記号の有無および大/小文字を区別しない比較)である場合、大/小文字、アクセント記号またはデリミタ文字のみが異なる索引は同じ値に変換される可能性があります。

関連項目:

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

5.2.2.3 リモート・データベースへの結合配列の受渡し

連想配列をパラメータとしてリモート・データベースに渡す場合に、ローカルとリモートのデータベースのNLS_SORT値またはNLS_COMP値が異なると、次のようになります。

コレクション・メソッドFIRST、LAST、NEXTまたはPRIOR(「コレクション・メソッド」を参照)から予期しない値が戻されたり例外が呼び出される場合があります。
ローカル・データベース上では一意の索引でも、リモート・データベース上では一意でない可能性があり、一意でない場合は事前定義の例外VALUE_ERRORが呼び出されます。

5.2.3 結合配列の適切な使用方法

連想配列は、次のような場合に適切です。

表が宣言されているサブプログラムの起動またはパッケージの初期化のたびにメモリー内に構成できる比較的小さい参照表
データベース・サーバーとの間のコレクションの受渡し

連想配列型の仮サブプログラム・パラメータを宣言します。Oracle Call Interface(OCI)またはOracleプリコンパイラを使用すると、対応する実パラメータにホスト配列がバインドされます。PL/SQLでは、PLS_INTEGERで索引付けされているホスト配列と連想配列との間で自動的に変換が行われます。

ノート:

VARCHARで索引付けされている連想配列はバインドできません。

ノート:

スキーマ・レベルでは結合配列型を宣言できません。そのため、連想配列の変数をパラメータとしてスタンドアロン・サブプログラムに渡すには、その変数の型をパッケージ仕様部で宣言する必要があります。このようにすることで、起動されるサブプログラム(結合配列型の仮パラメータを宣言する側)と起動元のサブプログラムまたは無名ブロック(結合配列型の変数を宣言して受け渡す側)の両方で結合配列型を使用できます。例10-2を参照してください。

ヒント:

データベース・サーバーとの間でのコレクションの受渡しには、連想配列をFORALL文またはBULK COLLECT句とともに使用することが、最も効率的な方法です。詳細は、「FORALL文」および「BULK COLLECT句」を参照してください。

連想配列は、一時的なデータの格納に使用されます。連想配列をデータベース・セッションの期間中持続させるには、パッケージ仕様部で連想配列を宣言し、パッケージ本体で移入します。

5.3 VARRAY (可変サイズの配列)

VARRAY(可変サイズの配列)とは、0個(空の場合)から宣言されている最大サイズまで要素の数を変更できる配列のことです。

VARRAY変数の要素にアクセスするには、構文variable_name(index)を使用します。indexの下限は1で、上限は現行の要素の数です。上限は、要素の追加または削除に合わせて変化しますが、最大サイズを超えることはできません。VARRAYをデータベースに格納したりデータベースから取り出しても、その索引と要素の順序は変わりません。

図5-1 は、Gradesという名前のVARRAY変数を示していて、最大サイズは10個で、7個の要素が格納されています。Grades(n)は、Gradesのn番目の要素を参照します。Gradesの上限は7で、10を超えることはできません。

図5-1 7個の要素を持つ最大サイズが10個のVARRAY

「図5-1 7個の要素を持つ最大サイズが10個のVARRAY」の説明

VARRAY変数は単一オブジェクトとしてデータベースに格納されます。VARRAY変数が4KB未満の場合は、そのVARRAY変数が列になっている表内に配置され、それ以外の場合は、表の外側に配置されますが、表領域は同じです。

初期化されていないVARRAY変数は、NULLのコレクションです。空にするかNULL以外の値を代入して初期化する必要があります。詳細は、「コレクションのコンストラクタ」および「コレクション変数への値の代入」を参照してください。

ここでのトピック

VARRAYの適切な使用方法

関連項目:

表5-1に、VARRAYの特性の概要を示します。
VARRAY型の定義の構文は、「varray_type_def ::=」を参照してください
スタンドアロンVARRAY方の作成の詳細は、「CREATE TYPE文」を参照してください
VARRAYの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

例5-4 VARRAY(可変サイズの配列)

この例では、ローカルのVARRAY型を定義した後に、この型の変数を宣言(コンストラクタを使用して初期化)し、このVARRAYを出力するプロシージャを定義します。この例では、このプロシージャを3回起動します(変数を初期化した後、2つの要素の値をそれぞれ変更した後、およびコンストラクタを使用してすべての要素の値を変更した後に1回ずつ)。(NULLまたは空の可能性があるVARRAYを出力するプロシージャの例は、例5-26を参照してください。)

Live SQL:

この例は、Oracle Live SQLの「VARRAY(可変サイズの配列)」で表示および実行できます

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
 
  -- varray variable initialized with constructor:
 
  team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
 
  PROCEDURE print_team (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    FOR i IN 1..4 LOOP
      DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('---'); 
  END;
  
BEGIN 
  print_team('2001 Team:');
 
  team(3) := 'Pierre';  -- Change values of two elements
  team(4) := 'Yvonne';
  print_team('2005 Team:');
 
  -- Invoke constructor to assign new values to varray variable:
 
  team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
  print_team('2009 Team:');
END;
/

結果:

2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
---
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
---

5.3.1 VARRAYの適切な使用方法

VARRAYは、次のような場合に適切です。

要素の最大数がわかっている。
通常は要素に順番にアクセスする。

VARRAYはすべての要素を同時に格納または取得する必要があるため、要素の数が多い場合は現実的ではないことがあります。

5.4 ネストした表

データベース内では、ネストした表は、特に順序を付けずに数が指定されていない行を格納する列型になります。

ネストした表の値をデータベースから取り出してPL/SQLのネストした表の変数に入れると、PL/SQLにより1から始まる連続した索引が行に付けられます。これらの索引を使用して、ネストした表の変数の個々の行にアクセスできます。構文はvariable_name(index)です。ネストした表の索引と行の順序は、ネストした表をデータベースに格納したりデータベースから取り出したときに変わる可能性があります。

ネストした表の変数に占有されるメモリー量は、要素の追加または削除に応じて動的に増減します。

初期化されていないネストした表の変数は、NULLのコレクションです。空にするかNULL以外の値を代入して初期化する必要があります。詳細は、「コレクションのコンストラクタ」および「コレクション変数への値の代入」を参照してください。

ノート:

nt_typeおよびprint_ntは、例5-19、例5-21および例5-22で再利用します。

ここでのトピック

関連項目:

表5-1に、ネストした表の特性の概要を示します。
ネストした表型の定義の構文は、「nested_table_type_def ::=」を参照してください
スタンドアロン型のネストした表型の作成の詳細は、「CREATE TYPE文」を参照してください
ネストした表のビューの列を更新するトリガーの詳細は、「INSTEAD OF DMLトリガー」を参照してください
ネストした表の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

例5-5 ローカル型のネストした表

この例では、ローカルのネストした表型を定義した後に、この型の変数を宣言(コンストラクタを使用して初期化)し、このネストした表を出力するプロシージャを定義します。(このプロシージャでは、コレクション・メソッドFIRSTおよびLASTを使用しています。詳細は「コレクション・メソッド」を参照してください。)この例では、このプロシージャを3回起動します(変数を初期化した後、1つの要素の値を変更した後、コンストラクタを使用してすべての要素の値を変更した後に1回ずつ)。2回目にコンストラクタを起動した後は、ネストした表の要素が2つのみになります。要素3を参照すると、エラーORA-06533が発生します。

Live SQL:

この例は、Oracle Live SQLの「ローカル型のネストした表」で表示および実行できます

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);  -- nested table type
 
  -- nested table variable initialized with constructor:
 
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
 
  PROCEDURE print_names (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    FOR i IN names.FIRST .. names.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('---');
  END;
  
BEGIN 
  print_names('Initial Values:');
 
  names(3) := 'P Perez';  -- Change value of one element
  print_names('Current Values:');
 
  names := Roster('A Jansen', 'B Gupta');  -- Change entire table
  print_names('Current Values:');
END;
/

結果:

Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta

例5-6 スタンドアロン型のネストした表

この例では、スタンドアロン型のネストした表型nt_typeを定義し、この型の変数を出力するスタンドアロン・プロシージャprint_ntを定義します。無名ブロックで型nt_typeの変数を宣言し、この変数をコンストラクタで空に初期化してから、print_ntを2回起動します(変数を初期化した後、およびコンストラクタですべての要素の値を変更した後に1回ずつ)。

Live SQL:

この例は、Oracle Live SQLの「スタンドアロン型のネストした表」で表示および実行できます

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER IS
  i  NUMBER;
BEGIN
  i := nt.FIRST;
 
  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('nt is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('nt.(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL'));
      i := nt.NEXT(i);
    END LOOP;
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('---');
END print_nt;
/
DECLARE
  nt nt_type := nt_type();  -- nested table variable initialized to empty
BEGIN
  print_nt(nt);
  nt := nt_type(90, 9, 29, 58);
  print_nt(nt);
END;
/

結果:

nt is empty
---
nt.(1) = 90
nt.(2) = 9
nt.(3) = 29
nt.(4) = 58
---

5.4.1 ネストした表と配列の重要な相違点

概念上、ネストした表は任意の数の要素がある1次元配列に似ています。ただし、ネストした表と配列には次の重要な相違点があります。

配列では要素の数が宣言されますが、ネストした表では宣言されません。ネストした表のサイズは動的に増やすことができます。
配列は常に密です。ネストした表は、最初は密であっても、要素を削除できるため、疎になる可能性があります。

図5-2は、ネストした表と配列の重要な相違点を示しています。

図5-2 配列およびネストした表

「図5-2 配列およびネストした表」の説明

5.4.2 ネストした表の適切な使用方法

ネストした表は、次のような場合に適切です。

要素の数が設定されていない。
索引の値が連続していない。
すべての要素を同時にではなく、一部の要素を削除または更新する必要がある。

ネストした表のデータは、別の記憶域表(システム生成によるデータベース表)に格納されます。ネストした表にアクセスすると、データベースによってその表がデータベースの記憶域表に結合されます。この記憶域表によって、ネストした表は、コレクションの一部の要素にのみ影響を与える問合せと更新に適した内容になります。
メインの表の各行に複数のエントリを含む個別の参照表を作成し、結合問合せを介してその表にアクセスする。

5.5 コレクションのコンストラクタ

コレクションのコンストラクタ(コンストラクタ)は、コレクション型と同じ名前のシステム定義ファンクションです(この型のコレクションを戻します)。

ノート:

このトピックの内容は、VARRAYおよびネストした表にのみ適用されます。このトピックでは、コレクションはVARRAYまたはネストした表のことを意味します。連想配列は、修飾式と集計(修飾式の概要を参照)を使用します。

次にコンストラクタを起動する構文を示します。

collection_type ( [ value [, value ]... ] )

パラメータ・リストが空の場合は、コンストラクタから空のコレクションが戻されます。それ以外の場合は、指定した値を含むコレクションが戻されます。セマンティクスの詳細は、「collection_constructor」を参照してください。

戻されたコレクションは、変数の宣言およびブロックの実行部で(同じ型の)コレクション変数に代入できます。

例5-7 コレクション(VARRAY)変数の空への初期化

この例では、コンストラクタを2回起動します(変数の宣言でVARRAY変数teamを空に初期化するとき、およびブロックの実行部で新しい値を指定するときに1回ずつ)。プロシージャprint_teamは、teamの初期値と最終的な値を表示します。print_teamでは、teamが空であるかどうかを判断するために、コレクション・メソッドCOUNTを使用しています(詳細は「コレクション・メソッド」を参照)。(NULLの可能性があるVARRAYを出力するプロシージャの例は、例5-26を参照してください。)

Live SQL:

この例は、Oracle Live SQLの「コレクション(VARRAY)変数の空への初期化」で表示および実行できます

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
  team Foursome := Foursome();  -- initialize to empty
 
  PROCEDURE print_team (heading VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    IF team.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE 
      FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('---'); 
  END;
 
BEGIN
  print_team('Team:');
  team := Foursome('John', 'Mary', 'Alberto', 'Juanita');
  print_team('Team:');
END;
/

結果:

Team:
Empty
---
Team:
1.John
2.Mary
3.Alberto
4.Juanita
---

5.6 修飾式の概要

修飾式を使用すると、複雑な値を簡潔な形式で、値が必要になった箇所で宣言および定義できるため、プログラムが明確になり開発者の生産性も向上します。

修飾式は式の要素を結合して、RECORD型または連想配列型の値を作成します。

修飾式は明示的な型の指定を使用することで修飾項目の型を指定します。この明示的な指定はtypemarkと呼ばれるものです。

修飾式の構文は次のとおりです。

qualified_expression ::= typemark ( aggregate )

aggregate ::= [ positional_choice_list ] [explicit_choice_list]

positional_choice_list ::= (expr )+

explicit_choice_list ::= named_choice_list | indexed_choice_list

named_choice_list ::= identifier => expr [,]+

indexed_choice_list ::= expr => expr [,] +

構文およびセマンティクスの詳細は、qualified_expression ::=を参照してください。

例5-8 修飾式を使用したRECORD型変数への値の代入

この例は、RECORD型変数の宣言、初期化および定義を示しています。パッケージpkg内でrec_t型が定義されていて、一部が初期化されています。その型を使用して変数v_rec1が宣言され、位置表記法を使用して初期値が代入されています。その型を使用して変数v_rec2が宣言され、名前関連付け構文を使用して初期値が代入されています。変数v_rec3にはNULL値が代入されています。プロシージャprint_recはローカル変数v_rec1の値を表示し、続いてプロシージャ・パラメータpi_rec変数値を表示します。プロシージャにパラメータが渡されない場合は、プロシージャ定義に設定されている初期値が表示されます。

Live SQL:

この例は、18c 修飾式を使用したRECORD型変数への値の代入のOracle Live SQLで表示および実行できます

CREATE PACKAGE pkg IS
  TYPE rec_t IS RECORD
   (year PLS_INTEGER := 2,
    name VARCHAR2 (100) );
END;

DECLARE
  v_rec1 pkg.rec_t := pkg.rec_t(1847,'ONE EIGHT FOUR SEVEN');
  v_rec2 pkg.rec_t := pkg.rec_t(year => 1, name => 'ONE');
  v_rec3 pkg.rec_t := pkg.rec_t(NULL,NULL);

PROCEDURE print_rec ( pi_rec pkg.rec_t := pkg.rec_t(1847+1,  'a'||'b')) IS
  v_rec1 pkg.rec_t := pkg.rec_t(2847,'TWO EIGHT FOUR SEVEN');
BEGIN
  DBMS_OUTPUT.PUT_LINE(NVL(v_rec1.year,0) ||' ' ||NVL(v_rec1.name,'N/A'));
  DBMS_OUTPUT.PUT_LINE(NVL(pi_rec.year,0) ||' ' ||NVL(pi_rec.name,'N/A'));
END;
BEGIN
  print_rec(v_rec1);
  print_rec(v_rec2);
  print_rec(v_rec3);
  print_rec();
END;

2847 TWO EIGHT FOUR SEVEN
1847 ONE EIGHT FOUR SEVEN
2847 TWO EIGHT FOUR SEVEN
1 ONE
2847 TWO EIGHT FOUR SEVEN
0 N/A
2847 TWO EIGHT FOUR SEVEN
1848 ab

例5-9 修飾式を使用した連想配列型変数への値の代入

この例では、ファンクションを使用してBOOLEANの表の値を表示します。

Live SQL:

この例は、18c 修飾式を使用したRECORD型変数への値の代入のOracle Live SQLで表示および実行できます

CREATE FUNCTION print_bool (v IN BOOLEAN)
  RETURN VARCHAR2
IS
  v_rtn VARCHAR2(10);
BEGIN
  CASE v
  WHEN TRUE THEN
    v_rtn := 'TRUE';
  WHEN FALSE THEN
    v_rtn := 'FALSE';
  ELSE
    v_rtn := 'NULL';
  END CASE;
  RETURN v_rtn;
END print_bool;

変数v_aa1は索引キーと値のペアを使用して初期化されます。


DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;

FALSE
TRUE
NULL

5.7 コレクション変数への値の代入

コレクション変数には次の方法で値を代入できます。

コンストラクタを起動してコレクションを作成し、それをコレクション変数に代入します。
代入文を使用して、別の既存のコレクション変数の値を代入します。
OUTパラメータまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で値を代入する方法。
修飾式を使用して、連想配列に値を代入します(例5-9を参照)。

コレクション変数のスカラー要素に値を代入するには、collection_variable_name(index)として要素を参照し、値を代入します。

ここでのトピック

関連項目:

コレクションのコンストラクタ
「代入文」の構文図
コレクション変数のスカラー要素に値を代入する方法については、「変数への値の代入」を参照してください
BULK COLLECT句

5.7.1 データ型の互換性

あるコレクションをコレクション変数に代入できるのは、両者のデータ型が同じ場合のみです。要素型が同じであることのみでは不十分です。

例5-10 コレクション代入のデータ型の互換性

この例では、VARRAY型のtripletとtrioが同じ要素型VARCHAR(15)になっています。コレクション変数group1とgroup2のデータ型は同じtripletですが、コレクション変数group3のデータ型はtrioです。group1からgroup2への代入は正常に行われますが、group1からgroup3への代入は失敗します。

Live SQL:

この例は、Oracle Live SQLの「コレクション代入のデータ型の互換性」で表示および実行できます

DECLARE
  TYPE triplet IS VARRAY(3) OF VARCHAR2(15);
  TYPE trio    IS VARRAY(3) OF VARCHAR2(15);
 
  group1 triplet := triplet('Jones', 'Wong', 'Marceau');
  group2 triplet;
  group3 trio;
BEGIN
  group2 := group1;  -- succeeds
  group3 := group1;  -- fails
END;
/

結果:

ORA-06550: line 10, column 13:
PLS-00382: expression is of wrong type

5.7.2 VARRAYまたはネストした表の変数へのNULL値の代入

VARRAYまたはネストした表の変数には、値NULLまたは同じデータ型のNULLのコレクションを代入できます。いずれの代入でも、変数はNULLになります。

例5-11では、ネストした表の変数dept_namesをNULL以外の値に初期化します。次に、NULLのコレクションを代入して変数をNULLにした後、別のNULL以外の値に再初期化します。

例5-11 ネストした表の変数へのNULL値の代入

Live SQL:

この例は、Oracle Live SQLの「ネストした表の変数へのNULL値の代入」で表示および実行できます

DECLARE
  TYPE dnames_tab IS TABLE OF VARCHAR2(30);
 
  dept_names dnames_tab := dnames_tab(
    'Shipping','Sales','Finance','Payroll');  -- Initialized to non-null value
 
  empty_set dnames_tab;  -- Not initialized, therefore null
 
  PROCEDURE print_dept_names_status IS
  BEGIN
    IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('dept_names is null.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('dept_names is not null.');
    END IF;
  END  print_dept_names_status;
 
BEGIN
  print_dept_names_status;
  dept_names := empty_set;  -- Assign null collection to dept_names.
  print_dept_names_status;
  dept_names := dnames_tab (
    'Shipping','Sales','Finance','Payroll');  -- Re-initialize dept_names
  print_dept_names_status;
END;
/

結果:

dept_names is not null.
dept_names is null.
dept_names is not null.

5.7.3 ネストした表の変数へのSET演算結果の代入

ネストした表の変数には、SQL MULTISET演算またはSQL SETファンクションの起動結果を代入できます。

SQLのMULTISET演算子は、2つのネストした表を1つのネストした表に統合します。2つのネストした表の要素は、同等のデータ型を持っている必要があります。MULTISET演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

SQL SETファンクションはネストした表を引数に取り、別個の要素を含む同じデータ型のネストした表を戻します(重複する要素は結果から削除されます)。SETファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

例5-12 ネストした表の変数へのSET演算結果の代入

この例では、複数のMULTISET演算と1つのSETファンクションの起動結果をネストした表の変数answerに代入し、代入するたびにプロシージャprint_nested_tableを使用してanswerを出力します。このプロシージャでは、コレクション・メソッドFIRSTおよびLASTを使用しています。詳細は「コレクション・メソッド」を参照してください。

Live SQL:

この例は、Oracle Live SQLの「ネストした表の変数へのSET演算結果の代入」で表示および実行できます

DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
 
  nt1    nested_typ := nested_typ(1,2,3);
  nt2    nested_typ := nested_typ(3,2,1);
  nt3    nested_typ := nested_typ(2,3,1,3);
  nt4    nested_typ := nested_typ(1,2,4);
  answer nested_typ;
 
  PROCEDURE print_nested_table (nt nested_typ) IS
    output VARCHAR2(128);
  BEGIN
    IF nt IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Result: null set');
    ELSIF nt.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Result: empty set');
    ELSE
      FOR i IN nt.FIRST .. nt.LAST LOOP  -- For first to last element
        output := output || nt(i) || ' ';
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Result: ' || output);
    END IF;
  END print_nested_table;
 
BEGIN
  answer := nt1 MULTISET UNION nt4;
  print_nested_table(answer);
  answer := nt1 MULTISET UNION nt3;
  print_nested_table(answer);
  answer := nt1 MULTISET UNION DISTINCT nt3;
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT nt3;
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT DISTINCT nt3;
  print_nested_table(answer);
  answer := SET(nt3);
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT nt2;
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT DISTINCT nt2;
  print_nested_table(answer);
END;
/

結果:

Result: 1 2 3 1 2 4
Result: 1 2 3 2 3 1 3
Result: 1 2 3
Result: 3 2 1
Result: 3 2 1
Result: 2 3 1
Result: 3
Result: empty set

5.8 多次元コレクション

コレクションは1次元のみですが、コレクションを要素に持つコレクションを使用して、多次元コレクションのモデルを作成できます。

例5-13 2次元のVARRAY (VARRAYのVARRAY)

この例のnvaは、2次元のVARRAY(整数のVARRAYのVARRAY)です。

Live SQL:

この例は、Oracle Live SQLの「2次元のVARRAY(VARRAYのVARRAY)」で表示および実行できます

DECLARE
  TYPE t1 IS VARRAY(10) OF INTEGER;  -- varray of integer
  va t1 := t1(2,3,5);

  TYPE nt1 IS VARRAY(10) OF t1;      -- varray of varray of integer
  nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);

  i INTEGER;
  va1 t1;
BEGIN
  i := nva(2)(3);
  DBMS_OUTPUT.PUT_LINE('i = ' || i);

  nva.EXTEND;
  nva(5) := t1(56, 32);          -- replace inner varray elements
  nva(4) := t1(45,43,67,43345);  -- replace an inner integer element
  nva(4)(4) := 1;                -- replace 43345 with 1

  nva(4).EXTEND;    -- add element to 4th varray element
  nva(4)(5) := 89;  -- store integer 89 there
END;
/

結果:

i = 73

例5-14 ネストした表のネストした表と整数のVARRAYのネストした表

この例のntb1は文字列のネストした表のネストした表で、ntb2は整数のVARRAYのネストした表です。

Live SQL:

この例は、Oracle Live SQLの「ネストした表のネストした表と整数のVARRAYのネストした表」で表示および実行できます

DECLARE
  TYPE tb1 IS TABLE OF VARCHAR2(20);  -- nested table of strings
  vtb1 tb1 := tb1('one', 'three');

  TYPE ntb1 IS TABLE OF tb1; -- nested table of nested tables of strings
  vntb1 ntb1 := ntb1(vtb1);

  TYPE tv1 IS VARRAY(10) OF INTEGER;  -- varray of integers
  TYPE ntb2 IS TABLE OF tv1;          -- nested table of varrays of integers
  vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));

BEGIN
  vntb1.EXTEND;
  vntb1(2) := vntb1(1);
  vntb1.DELETE(1);     -- delete first element of vntb1
  vntb1(2).DELETE(1);  -- delete first string from second table in nested table
END;
/

例5-15 結合配列のネストした表と文字列のVARRAYのネストした表

この例のaa1は連想配列の連想配列で、ntb2は文字列のVARRAYのネストした表です。

Live SQL:

この例は、Oracle Live SQLの「連想配列のネストした表と文字列のVARRAYのネストした表」で表示および実行できます

DECLARE
  TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;  -- associative arrays
  v4 tb1;
  v5 tb1;

  TYPE aa1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;  -- associative array of
  v2 aa1;                                         --  associative arrays

  TYPE va1 IS VARRAY(10) OF VARCHAR2(20);  -- varray of strings
  v1 va1 := va1('hello', 'world');

  TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;  -- associative array of varrays
  v3 ntb2;

BEGIN
  v4(1)   := 34;     -- populate associative array
  v4(2)   := 46456;
  v4(456) := 343;

  v2(23) := v4;  -- populate associative array of associative arrays

  v3(34) := va1(33, 456, 656, 343);  -- populate associative array varrays

  v2(35) := v5;      -- assign empty associative array to v2(35)
  v2(35)(2) := 78;
END;
/

5.9 コレクションの比較

たとえば、あるコレクション変数が別のコレクション変数より小さいかどうかを判別するには、そのコンテキストで「より小さい」の意味を定義して、TRUEまたはFALSEを戻すファンクションを記述する必要があります。

連想配列の変数と値NULLを比較したり、連想配列を互いに比較することはできません。

「ネストした表が等しいかどうかの比較」を除き、関係演算子を使用して2つのコレクション変数をそのまま比較はできません。この制限は、暗黙的な比較にも適用されます。たとえば、コレクション変数はDISTINCT、GROUP BYまたはORDER BY句には使用できません。

ここでのトピック

関連項目:

表2-5
機能の記述の詳細は、「PL/SQLサブプログラム」を参照してください

5.9.1 VARRAYおよびネストした表の変数とNULLの比較

NULL値と比較するときは、IS[NOT] NULL演算子を使用します。

VARRAYおよびネストした表の変数は、「IS [NOT] NULL演算子」を使用して値NULLと比較できますが、関係演算子の等価演算子(=)および不等価演算子(<>、!=、~=または^=)を使用して比較できません。

例5-16 VARRAYおよびネストした表の変数とNULLの比較

この例では、VARRAYおよびネストした表の変数とNULLを正しく比較しています。

Live SQL:

この例は、Oracle Live SQLの「VARRAYおよびネストした表の変数とNULLの比較」で表示および実行できます

DECLARE  
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
  team Foursome;                               -- varray variable
  
  TYPE Roster IS TABLE OF VARCHAR2(15);        -- nested table type
  names Roster := Roster('Adams', 'Patel');    -- nested table variable
  
BEGIN
  IF team IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('team IS NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('team IS NOT NULL');
  END IF;
 
  IF names IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('names IS NOT NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('names IS NULL');
  END IF;
END;
/

結果:

team IS NULL
names IS NOT NULL

5.9.2 ネストした表が等しいかどうかの比較

2つのネストした表の変数は、同じ要素のセット(順不同)を含む場合のみ等価です。

2つのネストした表の変数が同じネストした表型を持ち、ネストした表型にレコード型の要素が含まれていない場合は、関係演算子の等価演算子(=)および不等価演算子(<>、!=、~=、 ^=)を使用して、2つの変数が等しいかどうかを比較できます。

関連項目:

レコードの比較

例5-17 ネストした表が等しいかどうかの比較

この例では、ネストした表の変数が等しいか等しくないかを関係演算子で比較しています。

Live SQL:

この例は、Oracle Live SQLの「ネストした表が等しいかどうかの比較」で表示および実行できます

DECLARE
  TYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record type

  dept_names1 dnames_tab :=
    dnames_tab('Shipping','Sales','Finance','Payroll');

  dept_names2 dnames_tab :=
    dnames_tab('Sales','Finance','Shipping','Payroll');

  dept_names3 dnames_tab :=
    dnames_tab('Sales','Finance','Payroll');

BEGIN
  IF dept_names1 = dept_names2 THEN
    DBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2');
  END IF;

  IF dept_names2 != dept_names3 THEN
    DBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3');
  END IF;
END;
/

結果:

dept_names1 = dept_names2
dept_names2 != dept_names3

5.9.3 ネストした表のSQL MULTISET条件による比較

ネストした表の変数を比較し、そのプロパティの一部をSQL MULTISET条件でテストできます。

関連項目:

MULTISET条件の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
CARDINALITY構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
SET構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

例5-18 ネストした表のSQL MULTISET条件による比較

この例では、SQL MULTISET条件と、ネストした表の変数引数CARDINALITYおよびSETを使用する2つのSQLファンクションを使用します。

Live SQL:

この例は、Oracle Live SQLの「ネストした表のSQL MULTISET条件による比較」で表示および実行できます

DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1 nested_typ := nested_typ(1,2,3);
  nt2 nested_typ := nested_typ(3,2,1);
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
 
  PROCEDURE testify (
    truth BOOLEAN := NULL,
    quantity NUMBER := NULL
  ) IS
  BEGIN
    IF truth IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE (
        CASE truth
           WHEN TRUE THEN 'True'
           WHEN FALSE THEN 'False'
        END
      );
    END IF;
    IF quantity IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(quantity);
    END IF;
  END;
BEGIN
  testify(truth => (nt1 IN (nt2,nt3,nt4)));        -- condition
  testify(truth => (nt1 SUBMULTISET OF nt3));      -- condition
  testify(truth => (nt1 NOT SUBMULTISET OF nt4));  -- condition
  testify(truth => (4 MEMBER OF nt1));             -- condition
  testify(truth => (nt3 IS A SET));                -- condition
  testify(truth => (nt3 IS NOT A SET));            -- condition
  testify(truth => (nt1 IS EMPTY));                -- condition
  testify(quantity => (CARDINALITY(nt3)));         -- function
  testify(quantity => (CARDINALITY(SET(nt3))));    -- 2 functions
END;
/

結果:

True
True
True
False
False
True
False
4
3

5.10 コレクション・メソッド

コレクション・メソッドはPL/SQLサブプログラムであり、コレクションに関する情報を戻すファンクションまたはコレクションに対する操作を実行するプロシージャのいずれかです。コレクション・メソッドを使用すると、コレクションの使用およびアプリケーションのメンテナンスが簡単になります。

表5-2に、コレクション・メソッドの概要を示します。

ノート:

NULLのコレクションで使用する場合に、事前定義の例外COLLECTION_IS_NULLが発生しないコレクション・メソッドはEXISTSのみです。

表5-2 コレクション・メソッド

メソッド	型	説明
`DELETE`	プロシージャ	コレクションから要素を削除します。
`TRIM`	プロシージャ	VARRAYまたはネストした表の末尾から要素を削除します。
`EXTEND`	プロシージャ	VARRAYまたはネストした表の末尾に要素を追加します。
`EXISTS`	ファンクション	VARRAYまたはネストした表の指定された要素が存在する場合のみ`TRUE`を戻します。
`FIRST`	ファンクション	コレクションの最初の索引を戻します。
`LAST`	ファンクション	コレクションの最後の索引を戻します。
`COUNT`	ファンクション	コレクション内の要素の数を戻します。
`LIMIT`	ファンクション	コレクションに格納できる要素の最大数を戻します。
`PRIOR`	ファンクション	指定された索引の前の索引を戻します。
`NEXT`	ファンクション	指定された索引の後の索引を戻します。

コレクション・メソッドを起動する基本構文は、次のとおりです。

collection_name.method

構文の詳細は、「コレクション・メソッドの起動」を参照してください。

コレクション・メソッドの起動は、その型(ファンクションまたはプロシージャ)のPL/SQLサブプログラムの起動を指定できるすべての場所(SQL文を除く)で使用できます。(PL/SQLサブプログラムの概要は、「PL/SQLサブプログラム」を参照してください。)

サブプログラム内で、コレクション・パラメータは引数のプロパティがバインドされていることを前提にしています。コレクション・メソッドは、そのようなパラメータに適用できます。VARRAYパラメータの場合、パラメータ・モードに関係なく、LIMITの値は常にパラメータの型定義から導出されます。

ここでのトピック

5.10.1 DELETEコレクション・メソッド

DELETEは、コレクションから要素を削除するプロシージャです。

このメソッドには次の形式があります。

DELETEはすべての型のコレクションからすべての要素を削除します。

この操作を実行すると、削除された要素に割り当てられていたメモリーは即座に解放されます。
連想配列またはネストされた表(VARRAYではない)から削除する場合は次のとおりです。
- DELETE(n)は、nの索引を持つ要素が存在する場合にその要素を削除します。それ以外の場合は何も実行しません。
- DELETE(m,n)は、mとnの両方が存在し、m <= nの場合に、mからnの範囲の索引を持つすべての要素を削除します。それ以外の場合は何も実行しません。
これら2つのDELETE形式を使用する場合、PL/SQLは削除された要素のプレースホルダを保持します。したがって、削除された要素はコレクションの内部サイズに含まれるため、削除された要素に有効な値を代入してリストアすることができます。

例5-19 ネストした表でのDELETEメソッド

この例では、ネストした表の変数を宣言し、6つの要素を使用して初期化した後で、2番目の要素を削除してからリストアし、次に、要素の範囲を削除し、削除した要素の1つをリストアしてから、すべての要素を削除します。リストアされた要素は、それに対応する削除された要素と同じメモリー量を占有します。プロシージャprint_ntは、初期化の後および各DELETE操作の後に、ネストした表の変数を出力します。型nt_typeおよびプロシージャprint_ntは、例5-6で定義しています。

DECLARE
  nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
  print_nt(nt);
 
  nt.DELETE(2);     -- Delete second element
  print_nt(nt);
 
  nt(2) := 2222;    -- Restore second element
  print_nt(nt);
 
  nt.DELETE(2, 4);  -- Delete range of elements
  print_nt(nt);
 
  nt(3) := 3333;    -- Restore third element
  print_nt(nt);
 
  nt.DELETE;        -- Delete all elements
  print_nt(nt);
END;
/

結果:

nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 2222
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 3333
nt.(5) = 55
nt.(6) = 66
---
nt is empty
---

例5-20 文字列で索引付けされている連想配列でのDELETEメソッド

この例では、文字列で索引付けされている連想配列に要素を移入してからすべての要素を削除し、これにより、要素に割り当てられていたメモリーは解放されます。次に、削除した要素を置き換えます(つまり、削除した要素と同じ索引を持つ新しい要素を追加します)。新たに置き換えた要素は、それに対応する削除された要素と同じメモリー量を占有しません。最後に、1つの要素を削除してから、要素の範囲を削除します。プロシージャprint_aa_strは、各操作の効果を表示します。

DECLARE
  TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  aa_str  aa_type_str;
 
  PROCEDURE print_aa_str IS
    i  VARCHAR2(10);
  BEGIN
    i := aa_str.FIRST;
 
    IF i IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('aa_str is empty');
    ELSE
      WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT('aa_str.(' || i || ') = ');
        DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa_str(i)), 'NULL'));
        i := aa_str.NEXT(i);
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('---');
  END print_aa_str;
 
BEGIN
  aa_str('M') := 13;
  aa_str('Z') := 26;
  aa_str('C') := 3;
  print_aa_str;
 
  aa_str.DELETE;  -- Delete all elements
  print_aa_str;
 
  aa_str('M') := 13;   -- Replace deleted element with same value
  aa_str('Z') := 260;  -- Replace deleted element with new value
  aa_str('C') := 30;   -- Replace deleted element with new value
  aa_str('W') := 23;   -- Add new element
  aa_str('J') := 10;   -- Add new element
  aa_str('N') := 14;   -- Add new element
  aa_str('P') := 16;   -- Add new element
  aa_str('W') := 23;   -- Add new element
  aa_str('J') := 10;   -- Add new element
  print_aa_str;
 
  aa_str.DELETE('C');      -- Delete one element
  print_aa_str;
 
  aa_str.DELETE('N','W');  -- Delete range of elements
  print_aa_str;
 
  aa_str.DELETE('Z','M');  -- Does nothing
  print_aa_str;
END;
/

結果:

aa_str.(C) = 3
aa_str.(M) = 13
aa_str.(Z) = 26
---
aa_str is empty
---
aa_str.(C) = 30
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---

5.10.2 TRIMコレクション・メソッド

TRIMは、VARRAYまたはネストした表の末尾から要素を削除するプロシージャです。

このメソッドには次の形式があります。

TRIMは、コレクションに1つ以上の要素が含まれる場合に、末尾から1つの要素を削除します。それ以外の場合は、事前定義の例外SUBSCRIPT_BEYOND_COUNTを呼び出します。
TRIM(n)は、コレクションの末尾にn個以上の要素が含まれる場合に、末尾からn個の要素を削除します。それ以外の場合は、事前定義の例外SUBSCRIPT_BEYOND_COUNTを呼び出します。

TRIMは、コレクションの内部サイズを操作します。つまり、DELETEで要素が削除されてもその要素のプレースホルダが保持されている場合、TRIMはその要素が存在するものとみなします。したがって、削除された要素をTRIMで削除することができます。

PL/SQLは切り捨てられた要素のプレースホルダを保持しません。したがって、切り捨てられた要素はコレクションの内部サイズに含まれないため、切り捨てられた要素に有効な値を代入して要素をリストアすることはできません。

注意:

TRIMとDELETEの間の相互作用には依存しないでください。ネストした表は、固定サイズの配列のように扱う(DELETEのみを使用する)か、またはスタックのように扱う(TRIMとEXTENDのみを使用する)ようにしてください。

例5-21 ネストした表でのTRIMメソッド

この例では、ネストした表の変数を宣言し、6つの要素で初期化した後で、末尾の要素を切り捨て、4番目の要素を削除してから、末尾の2つの要素を切り捨てます(切り捨てる要素の1つは、削除した4番目の要素です)。プロシージャprint_ntは、初期化の後とTRIM操作およびDELETE操作の後に、ネストした表の変数を出力します。型nt_typeおよびプロシージャprint_ntは、例5-6で定義しています。

DECLARE
  nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
  print_nt(nt);

  nt.TRIM;       -- Trim last element
  print_nt(nt);

  nt.DELETE(4);  -- Delete fourth element
  print_nt(nt);

  nt.TRIM(2);    -- Trim last two elements
  print_nt(nt);
END;
/

結果:

nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---

5.10.3 EXTENDコレクション・メソッド

EXTENDは、VARRAYまたはネストした表の末尾に要素を追加するプロシージャです。

コレクションは空でもかまいませんが、NULLは許可されません。(コレクションを空にしたりNULLのコレクションに要素を追加するには、コンストラクタを使用します。詳細は、「コレクションのコンストラクタ」を参照してください。)

EXTENDメソッドには次の形式があります。

EXTENDは、コレクションに1つのNULL要素を追加します。
EXTEND(n)は、コレクションにn個のNULL要素を追加します。
EXTEND(n,i)は、コレクションにi番目の要素のコピーをn個追加します。

ノート:

NOT NULL制約が含まれている要素を持つコレクションに使用できる形式は、EXTEND(n,i)のみです。

EXTENDは、コレクションの内部サイズを操作します。つまり、DELETEで要素が削除されてもその要素のプレースホルダが保持されている場合、EXTENDはその要素が存在するものとみなします。

例5-22 ネストした表でのEXTENDメソッド

この例では、ネストした表の変数を宣言し、3つの要素を使用して初期化した後で、最初の要素のコピーを2つ追加し、次に、5番目(最後)の要素を削除してから、NULL要素を1つ追加します。EXTENDでは、削除された5番目の要素が存在するものとみなされるため、追加されたNULL要素は6番目の要素になります。プロシージャprint_ntは、初期化の後とEXTEND操作およびDELETE操作の後に、ネストした表の変数を出力します。型nt_typeおよびプロシージャprint_ntは、例5-6で定義しています。

DECLARE
  nt nt_type := nt_type(11, 22, 33);
BEGIN
  print_nt(nt);
 
  nt.EXTEND(2,1);  -- Append two copies of first element
  print_nt(nt);
 
  nt.DELETE(5);    -- Delete fifth element
  print_nt(nt);
 
  nt.EXTEND;       -- Append one null element
  print_nt(nt);
END;
/

結果:

nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(5) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(6) = NULL
---

5.10.4 EXISTSコレクション・メソッド

EXISTSは、VARRAYまたはネストした表の指定された要素が存在するかどうかを表示するファンクションです。

EXISTS(n)は、コレクションにn番目の要素が存在する場合にTRUEを戻し、それ以外の場合はFALSEを戻します。nが範囲外の場合、EXISTSは事前定義の例外SUBSCRIPT_OUTSIDE_LIMITを呼び出さずに、FALSEを戻します。

削除された要素の場合、DELETEによりその要素のプレースホルダが保持されていても、EXISTS(n)はFALSEを戻します。

例5-23 ネストした表でのEXISTSメソッド

この例では、ネストした表を4つの要素で初期化した後、2番目の要素を削除して、1番目から6番目の要素の値またはステータスのいずれかを出力します。

DECLARE
  TYPE NumList IS TABLE OF INTEGER;
  n NumList := NumList(1,3,5,7);
BEGIN
  n.DELETE(2); -- Delete second element
 
  FOR i IN 1..6 LOOP
    IF n.EXISTS(i) THEN
      DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
    ELSE
      DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
    END IF;
  END LOOP;
END;
/

結果:

n(1) = 1
n(2) does not exist
n(3) = 5
n(4) = 7
n(5) does not exist
n(6) does not exist

5.10.5 FIRSTおよびLASTコレクション・メソッド

FIRSTおよびLASTはファンクションです。

1つ以上の要素を持つコレクションの場合、FIRSTおよびLASTはそれぞれ最初と最後の要素の索引を戻します(削除された要素のプレースホルダがDELETEにより保持されていても、それらの要素は無視されます)。コレクションの要素が1つのみの場合、FIRSTおよびLASTは同じ索引を戻します。コレクションが空の場合、FIRSTとLASTはNULLを戻します。

ここでのトピック

5.10.5.1 結合配列に対するFIRSTおよびLASTメソッド

PLS_INTEGERで索引付けされている連想配列の場合、最初の要素および最後の要素は、それぞれ最小および最大の索引を持つ要素です。文字列で索引付けされている連想配列の場合、最初の要素および最後の要素は、それぞれ最小および最大のキー値を持つ要素です。

キー値の順序はソートされています(詳細は、「文字列で索引付けされている連想配列に影響を与えるNLSパラメータ値」を参照してください)。

例5-24 PLS_INTEGERで索引付けされている連想配列のFIRSTとLASTの値

この例では、PLS_INTEGERで索引付けされている連想配列のFIRSTとLASTの値を表示してから最初と最後の要素を削除し、再度、FIRSTとLASTの値を表示します。

DECLARE
  TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  aa_int  aa_type_int;
 
  PROCEDURE print_first_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
    DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
  END print_first_and_last;
 
BEGIN
  aa_int(1) := 3;
  aa_int(2) := 6;
  aa_int(3) := 9;
  aa_int(4) := 12;
 
  DBMS_OUTPUT.PUT_LINE('Before deletions:');
  print_first_and_last;
 
  aa_int.DELETE(1);
  aa_int.DELETE(4);
 
  DBMS_OUTPUT.PUT_LINE('After deletions:');
  print_first_and_last;
END;
/

結果:

Before deletions:
FIRST = 1
LAST = 4
After deletions:
FIRST = 2
LAST = 3

例5-25 文字列で索引付けされている連想配列のFIRSTとLASTの値

この例では、文字列で索引付けされている連想配列のFIRSTとLASTの値を表示してから最初と最後の要素を削除し、再度、FIRSTとLASTの値を表示します。

DECLARE
  TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  aa_str  aa_type_str;
 
  PROCEDURE print_first_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST);
    DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST);
  END print_first_and_last;
 
BEGIN
  aa_str('Z') := 26;
  aa_str('A') := 1;
  aa_str('K') := 11;
  aa_str('R') := 18;
 
  DBMS_OUTPUT.PUT_LINE('Before deletions:');
  print_first_and_last;
 
  aa_str.DELETE('A');
  aa_str.DELETE('Z');
 
  DBMS_OUTPUT.PUT_LINE('After deletions:');
  print_first_and_last;
END;
/

結果:

Before deletions:
FIRST = A
LAST = Z
After deletions:
FIRST = K
LAST = R

5.10.5.2 VARRAYに対するFIRSTおよびLASTメソッド

空ではないVARRAYの場合、FIRSTは常に1を戻します。すべてのVARRAYで、LASTは常にCOUNTに等しくなります。

例5-26 FOR LOOP内のFIRSTおよびLASTによるVARRAYの出力

この例では、team.FIRSTおよびteam.LASTの境界を持つFOR LOOP文を使用して、VARRAYのteamを出力します。VARRAYは常に密であるため、ループの内側のteam(i)は常に存在します。

DECLARE
  TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
  team team_type;
 
  PROCEDURE print_team (heading VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    IF team IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Does not exist');
    ELSIF team.FIRST IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Has no members');
    ELSE
      FOR i IN team.FIRST..team.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('---'); 
  END;
  
BEGIN 
  print_team('Team Status:');
 
  team := team_type();  -- Team is funded, but nobody is on it.
  print_team('Team Status:');
 
  team := team_type('John', 'Mary');  -- Put 2 members on team.
  print_team('Initial Team:');
 
  team := team_type('Arun', 'Amitha', 'Allan', 'Mae');  -- Change team.
  print_team('New Team:');
END;
/

結果:

Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. John
2. Mary
---
New Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---

5.10.5.3 ネストした表に対するFIRSTおよびLASTメソッド

ネストした表の場合、LASTがCOUNTより大きい場合は、中から要素を削除しないかぎり、LASTはCOUNTに等しくなります。

例5-27 FOR LOOP内のFIRSTおよびLASTによるネストした表の出力

この例では、team.FIRSTおよびteam.LASTの境界を持つFOR LOOP文を使用して、ネストした表のteamを出力します。ネストした表は疎の場合があるため、team.EXISTS(i)がTRUEの場合のみ、FOR LOOP文はteam(i)を出力します。

DECLARE
  TYPE team_type IS TABLE OF VARCHAR2(15);
  team team_type;
 
  PROCEDURE print_team (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    IF team IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Does not exist');
    ELSIF team.FIRST IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Has no members');
    ELSE
      FOR i IN team.FIRST..team.LAST LOOP
        DBMS_OUTPUT.PUT(i || '. ');
        IF team.EXISTS(i) THEN
          DBMS_OUTPUT.PUT_LINE(team(i));
        ELSE
          DBMS_OUTPUT.PUT_LINE('(to be hired)');
        END IF;
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('---'); 
  END;
  
BEGIN 
  print_team('Team Status:');
 
  team := team_type();  -- Team is funded, but nobody is on it.
  print_team('Team Status:');
 
  team := team_type('Arun', 'Amitha', 'Allan', 'Mae');  -- Add members.
  print_team('Initial Team:');
 
  team.DELETE(2,3);  -- Remove 2nd and 3rd members.
  print_team('Current Team:');
END;
/

結果:

Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---
Current Team:
1. Arun
2. (to be hired)
3. (to be hired)
4. Mae
---

5.10.6 COUNTコレクション・メソッド

COUNTは、コレクション内の要素の数を戻すファンクションです(削除された要素がDELETEにより保持されていても、それらは無視されます)。

ここでのトピック

5.10.6.1 VARRAYに対するCOUNTメソッド

VARRAYの場合、COUNTは常にLASTと同じです。VARRAYのサイズを(EXTENDまたはTRIMメソッドを使用して)増減させると、COUNTの値が変更されます。

例5-28 VARRAYのCOUNTとLASTの値

この例では、4つの要素を使用して初期化し、EXTEND(3)に続いてTRIM(5)を実行してから、VARRAYのCOUNTとLASTの値を表示します。

DECLARE
  TYPE NumList IS VARRAY(10) OF INTEGER;
  n NumList := NumList(1,3,5,7);
 
  PROCEDURE print_count_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
    DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
  END  print_count_and_last;
 
BEGIN
  print_count_and_last;
 
  n.EXTEND(3);
  print_count_and_last;
 
  n.TRIM(5);
  print_count_and_last;
END;
/

結果:

n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2

5.10.6.2 ネストした表に対するCOUNTメソッド

ネストした表の場合、COUNTはLASTと同じです。ただし、ネストした表の中から要素を削除すると、COUNTはLASTより小さくなります。

例5-29 ネストした表のCOUNTとLASTの値

この例では、4つの要素を使用して初期化し、3番目の要素を削除した後で末尾にNULL要素を2つ追加してから、ネストした表のCOUNTとLASTの値を表示します。最後に、1番目から8番目の要素のステータスを出力します。

DECLARE
  TYPE NumList IS TABLE OF INTEGER;
  n NumList := NumList(1,3,5,7);
 
  PROCEDURE print_count_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
    DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
  END  print_count_and_last;
 
BEGIN
  print_count_and_last;
 
  n.DELETE(3);  -- Delete third element
  print_count_and_last;
 
  n.EXTEND(2);  -- Add two null elements to end
  print_count_and_last;
 
  FOR i IN 1..8 LOOP
    IF n.EXISTS(i) THEN
      IF n(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
      ELSE
        DBMS_OUTPUT.PUT_LINE('n(' || i || ') = NULL');
      END IF;
    ELSE
      DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
    END IF;
  END LOOP;
END;
/

結果:

n.COUNT = 4, n.LAST = 4
n.COUNT = 3, n.LAST = 4
n.COUNT = 5, n.LAST = 6
n(1) = 1
n(2) = 3
n(3) does not exist
n(4) = 7
n(5) = NULL
n(6) = NULL
n(7) does not exist
n(8) does not exist

5.10.7 LIMITコレクション・メソッド

LIMITは、コレクションに格納可能な要素の最大数を戻すファンクションです。コレクションに要素の最大数がない場合、LIMITはNULLを戻します。最大サイズがあるのはVARRAYのみです。

例5-30 様々なコレクション型のLIMITとCOUNTの値

この例では、4つの要素を持つ連想配列、2つの要素を持つVARRAY、3つの要素を持つネストした表について、LIMITとCOUNTの値を表示します。

DECLARE
  TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  aa aa_type;                          -- associative array
 
  TYPE va_type IS VARRAY(4) OF INTEGER;
  va  va_type := va_type(2,4);   -- varray
 
  TYPE nt_type IS TABLE OF INTEGER;
  nt  nt_type := nt_type(1,3,5);  -- nested table
 
BEGIN
  aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12;
 
  DBMS_OUTPUT.PUT('aa.COUNT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa.COUNT), 'NULL'));
 
  DBMS_OUTPUT.PUT('aa.LIMIT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa.LIMIT), 'NULL'));
 
  DBMS_OUTPUT.PUT('va.COUNT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(va.COUNT), 'NULL'));
 
  DBMS_OUTPUT.PUT('va.LIMIT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(va.LIMIT), 'NULL'));
 
  DBMS_OUTPUT.PUT('nt.COUNT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.COUNT), 'NULL'));
 
  DBMS_OUTPUT.PUT('nt.LIMIT = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.LIMIT), 'NULL'));
END;
/

結果:

aa.COUNT = 4
aa.LIMIT = NULL
va.COUNT = 2
va.LIMIT = 4
nt.COUNT = 3
nt.LIMIT = NULL

5.10.8 PRIORおよびNEXTコレクション・メソッド

PRIORおよびNEXTは、コレクション内を前後に移動できるファンクションです(削除された要素がDELETEにより保持されていても、それらは無視されます)。このメソッドは、疎コレクション内の移動に便利です。

索引を指定すると次のようになります。

先行する要素が存在する場合、PRIORはそのコレクション要素の索引を戻します。存在しない場合、PRIORはNULLを戻します。

任意のコレクションcの場合、c.PRIOR(c.FIRST)はNULLを戻します。
後続の要素が存在する場合、NEXTはそのコレクション要素の索引を戻します。存在しない場合、NEXTはNULLを戻します。

任意のコレクションcの場合、c.NEXT(c.LAST)はNULLを戻します。

指定された索引が存在していなくてもかまいません。ただし、コレクションcがVARRAYで、索引がc.LIMITより大きい場合は次のようになります。

c.PRIOR(index)はc.LASTを戻します。
c.NEXT(index)はNULLを戻します。

たとえば:

DECLARE
  TYPE Arr_Type IS VARRAY(10) OF NUMBER;
  v_Numbers Arr_Type := Arr_Type();
BEGIN
  v_Numbers.EXTEND(4);
 
  v_Numbers (1) := 10;
  v_Numbers (2) := 20;
  v_Numbers (3) := 30;
  v_Numbers (4) := 40;
 
  DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.prior (3400), -1));
  DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.next (3400), -1));
END;
/

結果:

4
-1

文字列で索引付けされている連想配列の場合、前の索引および次の索引はキー値で決まり、キー値の順序はソートされています(詳細は、「文字列で索引付けされている連想配列に影響を与えるNLSパラメータ値」を参照してください)。例5-1では、FIRST、NEXTおよびWHILE LOOP文を使用して連想配列の要素を出力しました。

例5-31 PRIORおよびNEXTメソッド

この例では、ネストした表を6つの要素で初期化した後、4番目の要素を削除して、1番目から7番目の要素のPRIORとNEXTの値を表示します。4番目と7番目の要素は存在しません。値はNULLですが、2番目の要素は存在します。

DECLARE
  TYPE nt_type IS TABLE OF NUMBER;
  nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);
 
BEGIN
  nt.DELETE(4);
  DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.');
 
  FOR i IN 1..7 LOOP
    DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = ');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.PRIOR(i)), 'NULL'));

    DBMS_OUTPUT.PUT('nt.NEXT(' || i || ')  = ');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.NEXT(i)), 'NULL'));
  END LOOP;
END;
/

結果:

nt(4) was deleted.
nt.PRIOR(1) = NULL
nt.NEXT(1)  = 2
nt.PRIOR(2) = 1
nt.NEXT(2)  = 3
nt.PRIOR(3) = 2
nt.NEXT(3)  = 5
nt.PRIOR(4) = 3
nt.NEXT(4)  = 5
nt.PRIOR(5) = 3
nt.NEXT(5)  = 6
nt.PRIOR(6) = 5
nt.NEXT(6)  = NULL
nt.PRIOR(7) = 6
nt.NEXT(7)  = NULL

例5-32 疎であるネストした表の要素の出力

この例では、疎であるネストした表の要素を、FIRSTとNEXTを使用して最初から最後まで出力し、LASTとPRIORを使用して最後から最初まで出力します。

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
  idx INTEGER;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('First to last:');
  idx := n.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT('n(' || idx || ') = ');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(n(idx)), 'NULL'));
    idx := n.NEXT(idx);
  END LOOP;
    
  DBMS_OUTPUT.PUT_LINE('--------------');
 
  DBMS_OUTPUT.PUT_LINE('Last to first:');
  idx := n.LAST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT('n(' || idx || ') = ');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(n(idx)), 'NULL'));
    idx := n.PRIOR(idx);
  END LOOP;
END;
/

結果:

First to last:
n(1) = 1
n(2) = 2
n(3) = NULL
n(4) = NULL
n(5) = 5
n(6) = NULL
n(7) = 7
n(8) = 8
n(9) = 9
n(10) = NULL
--------------
Last to first:
n(10) = NULL
n(9) = 9
n(8) = 8
n(7) = 7
n(6) = NULL
n(5) = 5
n(4) = NULL
n(3) = NULL
n(2) = 2
n(1) = 1

5.11 パッケージ仕様部で定義されたコレクション型

パッケージ仕様部で定義されたコレクション型は、同一定義のローカル・コレクション型またはスタンドアロン・コレクション型と互換性がありません。

ノート:

このトピックの例ではパッケージとプロシージャを定義しています。詳細は、「PL/SQLパッケージ」と「PL/SQLサブプログラム」をそれぞれ参照してください。

例5-33 同一定義のパッケージ・コレクション型とローカル・コレクション型

この例では、パッケージ仕様部と無名ブロックで、同じコレクション型NumListを定義します。パッケージでは、NumListのパラメータを取るプロシージャprint_numlistを定義します。また、無名ブロックでは、型pkg.NumList(パッケージで定義した型)の変数n1と、型NumList(このブロックで定義した型)の変数n2を宣言します。無名ブロックは、n1をprint_numlistに渡せますが、n2をprint_numlistに渡すことはできません。

Live SQL:

この例は、Oracle Live SQLの「同一定義のパッケージ・コレクション型とローカル・コレクション型」で表示および実行できます

CREATE OR REPLACE PACKAGE pkg AS
  TYPE NumList IS TABLE OF NUMBER;
  PROCEDURE print_numlist (nums NumList);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_numlist (nums NumList) IS
  BEGIN
    FOR i IN nums.FIRST..nums.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(nums(i));
    END LOOP;
  END;
END pkg;
/
DECLARE
  TYPE NumList IS TABLE OF NUMBER;  -- local type identical to package type
  n1 pkg.NumList := pkg.NumList(2,4);  -- package type
  n2     NumList :=     NumList(6,8);  -- local type
BEGIN
  pkg.print_numlist(n1);  -- succeeds
  pkg.print_numlist(n2);  -- fails
END;
/

結果:

  pkg.print_numlist(n2);  -- fails
  *
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_NUMLIST'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored

例5-34 同一定義のパッケージ・コレクション型とスタンドアロン・コレクション型

この例では、例5-33のパッケージ仕様部で定義したコレクション型NumListと同じスタンドアロン・コレクション型NumListを定義します。また、無名ブロックでは、型pkg.NumList(パッケージで定義した型)の変数n1と、スタンドアロン型NumListのn2を宣言します。無名ブロックは、n1をprint_numlistに渡せますが、n2をprint_numlistに渡すことはできません。

Live SQL:

この例は、Oracle Live SQLの「同一定義のパッケージ・コレクション型とスタンドアロン・コレクション型」で表示および実行できます

CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;
  -- standalone collection type identical to package type
/
DECLARE
  n1 pkg.NumList := pkg.NumList(2,4);  -- package type
  n2     NumList :=     NumList(6,8);  -- standalone type
 
BEGIN
  pkg.print_numlist(n1);  -- succeeds
  pkg.print_numlist(n2);  -- fails
END;
/

結果:

  pkg.print_numlist(n2);  -- fails
  *
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_NUMLIST'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored

5.12 レコード変数

次のいずれかの方法でレコード変数を作成できます。

RECORD型を定義し、次にその型の変数を宣言します。
データベースの表またはビュー内の行の全体または一部を表すレコード変数を宣言するには、%ROWTYPEを使用します。
事前に宣言されているレコード変数と同じ型のレコード変数を宣言するには、%TYPEを使用します。

(構文およびセマンティクスの詳細は、「レコード変数の宣言」を参照してください。)

ここでのトピック

5.12.1 レコード変数の初期値

RECORD型のレコード変数の場合、型を定義するときに別の初期値を指定しないかぎり、各フィールドの初期値はNULLになります。

%ROWTYPEまたは%TYPEを使用して宣言したレコード変数の場合、各フィールドの初期値はNULLになります。この変数は、参照先項目の初期値を継承しません。

5.12.2 レコード定数の宣言

レコード定数を宣言する場合、初期値をレコードに移入するファンクションを作成して、定数宣言でそのファンクションを起動する必要があります。

例5-35 レコード定数の宣言

この例では、初期値をレコードに移入するファンクションを作成して、定数宣言でそのファンクションを起動します。

Live SQL:

この例は、Oracle Live SQLの「レコード定数の宣言」で表示および実行できます

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER IS
  TYPE My_Rec IS RECORD (a NUMBER, b NUMBER);
  FUNCTION Init_My_Rec RETURN My_Rec;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types IS
  FUNCTION Init_My_Rec RETURN My_Rec IS
    Rec My_Rec;
  BEGIN
    Rec.a := 0;
    Rec.b := 1;
    RETURN Rec;
  END Init_My_Rec;
END My_Types;
/
DECLARE
  r CONSTANT My_Types.My_Rec := My_Types.Init_My_Rec();
BEGIN
  DBMS_OUTPUT.PUT_LINE('r.a = ' || r.a);
  DBMS_OUTPUT.PUT_LINE('r.b = ' || r.b);
END;
/

結果:

r.a = 0
r.b = 1
 
PL/SQL procedure successfully completed.

5.12.3 RECORD型

PL/SQLブロック内に定義されるRECORD型はローカル型です。ブロック内でのみ使用可能であり、スタンドアロン・サブプログラムまたはパッケージ・サブプログラム内にブロックがある場合にのみ、データベースに格納されます。

パッケージ仕様部に定義されるRECORD型はパブリック項目です。パッケージ名(package_name.type_name)で修飾することで、パッケージの外から参照できます。パブリック項目は、DROP PACKAGE文を使用してパッケージを削除するまで、データベースに格納されます。

スキーマ・レベルではRECORD型を宣言できません。したがって、RECORD型はADT属性のデータ型にできません。

RECORD型を定義するには、型の名前とフィールドを指定します。フィールドを定義するには、フィールド名とデータ型を指定します。デフォルトでは、フィールドの初期値はNULLです。フィールドにNOT NULL制約を指定できますが、指定する場合は、初期値にNULL以外の値を指定する必要があります。NOT NULL制約がない場合、NULL以外の初期値はオプションです。

パッケージ仕様部で定義されたRECORD型は、同一定義のローカルRECORD型と互換性がありません。

関連項目:

例5-36 RECORD型の定義および変数の宣言

この例では、DeptRecTypという名前のRECORD型を定義し、各フィールドに初期値を指定します。その後、dept_recという名前でその型の変数を宣言し、変数のフィールドを出力します。

Live SQL:

この例は、Oracle Live SQLの「RECORD型の定義および変数の宣言」で表示および実行できます

DECLARE
  TYPE DeptRecTyp IS RECORD (
    dept_id    NUMBER(4) NOT NULL := 10,
    dept_name  VARCHAR2(30) NOT NULL := 'Administration',
    mgr_id     NUMBER(6) := 200,
    loc_id     NUMBER(4) := 1700
  );
 
  dept_rec DeptRecTyp;
BEGIN
  DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.dept_id);
  DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
  DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec.mgr_id);
  DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec.loc_id);
END;
/

結果:

dept_id:   10
dept_name: Administration
mgr_id:    200
loc_id:    1700

例5-37 RECORDフィールドを持つRECORD型(ネストされたレコード)

この例では、name_recおよびcontactという2つのRECORD型を定義します。型contactには型name_recのフィールドを含めます。

Live SQL:

この例は、Oracle Live SQLの「RECORDフィールドを持つRECORD型(ネストされたレコード)」で表示および実行できます

DECLARE
  TYPE name_rec IS RECORD (
    first  employees.first_name%TYPE,
    last   employees.last_name%TYPE
  );
 
  TYPE contact IS RECORD (
    name  name_rec,                    -- nested record
    phone employees.phone_number%TYPE
  );
 
  friend contact;
BEGIN
  friend.name.first := 'John';
  friend.name.last := 'Smith';
  friend.phone := '1-650-555-1234';
  
  DBMS_OUTPUT.PUT_LINE (
    friend.name.first  || ' ' ||
    friend.name.last   || ', ' ||
    friend.phone
  );
END;
/

結果:

John Smith, 1-650-555-1234

例5-38 VARRAYフィールド持つRECORD型

この例では、full_nameというVARRAY型と、contactというRECORD型を定義します。型contactには型full_nameのフィールドを含めます。

Live SQL:

この例は、Oracle Live SQLの「VARRAYフィールド持つRECORD型」で表示および実行できます

DECLARE
  TYPE full_name IS VARRAY(2) OF VARCHAR2(20);
 
  TYPE contact IS RECORD (
    name  full_name := full_name('John', 'Smith'),  -- varray field
    phone employees.phone_number%TYPE
  );
 
  friend contact;
BEGIN
  friend.phone := '1-650-555-1234';
  
  DBMS_OUTPUT.PUT_LINE (
    friend.name(1) || ' ' ||
    friend.name(2) || ', ' ||
    friend.phone
  );
END;
/

結果:

John Smith, 1-650-555-1234

例5-39 同一定義のパッケージRECORD型とローカルRECORD型

この例では、パッケージpkgと無名ブロックで、同じRECORD型rec_typeを定義します。パッケージでは、rec_typeのパラメータを取るプロシージャprint_rec_typeを定義します。無名ブロックでは、パッケージ型(pkg.rec_type)の変数r1と、ローカル型(rec_type)の変数r2を宣言します。無名ブロックは、r1をprint_rec_typeに渡せますが、r2をprint_rec_typeに渡すことはできません。

Live SQL:

この例は、Oracle Live SQLの「同一定義のパッケージRECORD型とローカルRECORD型」で表示および実行できます

CREATE OR REPLACE PACKAGE pkg AS
  TYPE rec_type IS RECORD (       -- package RECORD type
    f1 INTEGER,
    f2 VARCHAR2(4)
  );
  PROCEDURE print_rec_type (rec rec_type);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_rec_type (rec rec_type) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(rec.f1);
    DBMS_OUTPUT.PUT_LINE(rec.f2);
  END; 
END pkg;
/
DECLARE
  TYPE rec_type IS RECORD (       -- local RECORD type
    f1 INTEGER,
    f2 VARCHAR2(4)
  );
  r1 pkg.rec_type;                -- package type
  r2     rec_type;                -- local type
 
BEGIN
  r1.f1 := 10; r1.f2 := 'abcd';
  r2.f1 := 25; r2.f2 := 'wxyz';
 
  pkg.print_rec_type(r1);  -- succeeds
  pkg.print_rec_type(r2);  -- fails
END;
/

結果:

  pkg.print_rec_type(r2);  -- fails
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_REC_TYPE'

5.12.4 %ROWTYPE属性を使用した項目の宣言

%ROWTYPE属性を使用すると、データベースの表またはビュー内の行の全体または一部を表すレコード変数を宣言できます。

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

ここでのトピック

5.12.4.1 常に行の全体を表すレコード変数の宣言

常にデータベースの表またはビュー内の行の全体を表すレコード変数を宣言するには、次の構文を使用します。

variable_name table_or_view_name%ROWTYPE;

このレコードは、表またはビューのすべての列に対して、同じ名前とデータ型のフィールドを持ちます。

関連項目:

%ROWTYPEの詳細は、「%ROWTYPE属性」を参照してください

例5-40 データベース表の行全体を表す%ROWTYPE変数

この例では、表departmentsの行を表すレコード変数を宣言し、フィールドに値を代入してからフィールドを出力します。この例を例5-36と比較してください。

Live SQL:

この例は、Oracle Live SQLの「データベース表の行全体を表す%ROWTYPE変数」で表示および実行できます

DECLARE
  dept_rec departments%ROWTYPE;
BEGIN
  -- Assign values to fields:
  
  dept_rec.department_id   := 10;
  dept_rec.department_name := 'Administration';
  dept_rec.manager_id      := 200;
  dept_rec.location_id     := 1700;
 
  -- Print fields:
 
  DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.department_id);
  DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name);
  DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec.manager_id);
  DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec.location_id);
END;
/

結果:

dept_id:   10
dept_name: Administration
mgr_id:    200
loc_id:    1700

例5-41 初期値または制約を継承しない%ROWTYPE変数

この例では、2つの列を持つ表を作成し、それぞれの列に初期値とNOT NULL制約を指定します。次に、この表の行を表すレコード変数を宣言してからフィールドを出力し、初期値またはNOT NULL制約が変数に継承されなかったことを示します。

Live SQL:

この例は、Oracle Live SQLの「初期値または制約を継承しない%ROWTYPE変数」で表示および実行できます

DROP TABLE t1;
CREATE TABLE t1 (
  c1 INTEGER DEFAULT 0 NOT NULL,
  c2 INTEGER DEFAULT 1 NOT NULL
);
 
DECLARE
  t1_row t1%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT('t1.c1 = ');
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t1_row.c1), 'NULL'));

  DBMS_OUTPUT.PUT('t1.c2 = '); print(t1_row.c2);
  DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t1_row.c2), 'NULL'));
END;
/

結果:

t1.c1 = NULL
t1.c2 = NULL

5.12.4.2 行の一部を表すことができるレコード変数の宣言

データベースの表またはビュー内の行の一部を表すことができるレコード変数を宣言するには、次の構文を使用します。

variable_name cursor%ROWTYPE;

カーソルは問合せに関連付けられています。問合せにより選択されるすべての列に対して、レコード変数の中には、対応する型互換のフィールドが存在している必要があります。問合せにより表またはビュー内のすべての列が選択される場合、変数は行の全体を表しますが、それ以外の場合は、行の一部を表します。カーソルは、明示カーソルまたは強いカーソル変数のいずれかである必要があります。

関連項目:

構文の詳細は、「FETCH文」を参照してください
カーソルの詳細は、「カーソルの概要」を参照してください
明示カーソルの詳細は、「明示カーソル」を参照してください
カーソル変数の詳細は、「カーソル変数」を参照してください
結合の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

例5-42 データベース表の行の一部を表す%ROWTYPE変数

この例では、サンプル・スキーマHRのemployees表から列first_name、last_nameおよびphone_numberのみを選択する問合せが指定された明示カーソルを宣言します。次に、カーソルにより選択される各列に対応するフィールドを持つレコード変数を宣言します。この変数はemployeesの行の一部を表します。この例を例5-37と比較してください。

Live SQL:

この例は、Oracle Live SQLの「データベース表の行の一部を表す%ROWTYPE変数」で表示および実行できます

DECLARE
  CURSOR c IS
    SELECT first_name, last_name, phone_number
    FROM employees;
 
  friend c%ROWTYPE;
BEGIN
  friend.first_name   := 'John';
  friend.last_name    := 'Smith';
  friend.phone_number := '1-650-555-1234';
  
  DBMS_OUTPUT.PUT_LINE (
    friend.first_name  || ' ' ||
    friend.last_name   || ', ' ||
    friend.phone_number
  );
END;
/

結果:

John Smith, 1-650-555-1234

例5-43 結合行を表す%ROWTYPE変数

この例では、結合問合せが指定された明示カーソルを定義した後、このカーソルにより選択される各列に対応するフィールドを持つレコード変数を宣言します。

Live SQL:

この例は、Oracle Live SQLの「結合行を表す%ROWTYPE変数」で表示および実行できます

DECLARE
  CURSOR c2 IS
    SELECT employee_id, email, employees.manager_id, location_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
  
  join_rec c2%ROWTYPE;  -- includes columns from two tables
  
BEGIN
  NULL;
END;
/

5.12.4.3 %ROWTYPE属性と仮想列

%ROWTYPE属性を使用して、仮想列を持つ表の行全体を表すレコード変数を定義する場合、そのレコードを表に挿入できません。かわりに、仮想列以外の各レコード・フィールドを表に挿入する必要があります。

例5-44 %ROWTYPEレコードの表への挿入(間違った例)

この例では、仮想列を持つ表の行全体を表すレコード変数を作成し、レコードに移入してから、そのレコードを表に挿入した結果、ORA-54013エラーが発生します。

DROP TABLE plch_departure;
 
CREATE TABLE plch_departure (
  destination    VARCHAR2(100),
  departure_time DATE,
  delay          NUMBER(10),
  expected       GENERATED ALWAYS AS (departure_time + delay/24/60/60)
);
 
 
DECLARE
 dep_rec plch_departure%ROWTYPE;
BEGIN
  dep_rec.destination := 'X'; 
  dep_rec.departure_time := SYSDATE;
  dep_rec.delay := 1500;
 
  INSERT INTO plch_departure VALUES dep_rec;
END;
/

結果:

DECLARE
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at line 8

例5-45 %ROWTYPEレコードの表への挿入(正しい例)

これは、仮想列以外の各レコード・フィールドを表に挿入することで、例5-44の問題を解決しています。

DECLARE
  dep_rec plch_departure%rowtype;
BEGIN
  dep_rec.destination := 'X';
  dep_rec.departure_time := SYSDATE;
  dep_rec.delay := 1500;
 
  INSERT INTO plch_departure (destination, departure_time, delay)
  VALUES (dep_rec.destination, dep_rec.departure_time, dep_rec.delay);
end;
/

結果:

PL/SQL procedure successfully completed.

5.12.4.4 %ROWTYPE属性と非表示列

%ROWTYPE属性を使用して、非表示列を持つ表の行を表すレコード変数を定義しており、その後、非表示列を表示列に変更するとします。

「行の一部を表すことができるレコード変数の宣言」に示すように、カーソルとともにレコード変数を定義し、その後、非表示列を表示列に変更しても、レコード変数の構造は変わりません。

ただし、常に行の全体を表すレコード変数の宣言に示すようにレコード変数を定義し、SELECT * INTO文を使用してレコードに値を代入した後、非表示列を表示列に変更すると、レコードの構造が変わります。例5-46を参照してください。

関連項目:

非表示列の概要は、『Oracle Database SQL言語リファレンス』を参照してください。

例5-46 非表示列の表示列への変更により影響を受ける%ROWTYPE

CREATE TABLE t (a INT, b INT, c INT INVISIBLE);
INSERT INTO t (a, b, c) VALUES (1, 2, 3);
COMMIT;

DECLARE
  t_rec t%ROWTYPE;  -- t_rec has fields a and b, but not c
BEGIN
  SELECT * INTO t_rec FROM t WHERE ROWNUM < 2;  -- t_rec(a)=1, t_rec(b)=2
  DBMS_OUTPUT.PUT_LINE('c = ' || t_rec.c);
END;
/

結果:

  DBMS_OUTPUT.PUT_LINE('c = ' || t_rec.c);
                                       *
ERROR at line 5:
ORA-06550: line 5, column 40:
PLS-00302: component 'C' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

非表示列を表示列に変更:

ALTER TABLE t MODIFY (c VISIBLE);

結果:

Table altered.

前述の無名ブロックを繰り返す:

DECLARE
  t_rec t%ROWTYPE;  -- t_rec has fields a, b, and c
BEGIN
  SELECT * INTO t_rec FROM t WHERE ROWNUM < 2;  -- t_rec(a)=1, t_rec(b)=2,
                                                  -- t_rec(c)=3
  DBMS_OUTPUT.PUT_LINE('c = ' || t_rec.c);
END;
/

結果:

c = 3
 
PL/SQL procedure successfully completed.

5.13 レコード変数への値の代入

レコード変数とは、レコード変数またはコンポジット変数のレコード・コンポーネントのいずれかを意味します。

どのレコード変数にも、各フィールドには個別に値を代入できます。

修飾式(例5-8を参照)を使用して値を代入できます。

場合によっては、あるレコード変数の値を別のレコード変数に代入できます。

データベースの表またはビュー内の行の全体または一部を表すレコード変数の場合は、対象となる行をレコード変数に代入できます。

ここでのトピック

5.13.1 あるレコード変数から別のレコード変数への代入

次の場合にかぎり、あるレコード変数の値を別のレコード変数に代入できます。

2つの変数は同じRECORD型を持ちます。
代入先の変数がRECORD型で、代入元の変数が%ROWTYPEでそれぞれ宣言され、両者のフィールドの数と順序が一致し、対応するフィールドのデータ型が同じである場合。

コンポジット変数のレコード・コンポーネントの場合は、コンポジット変数の型が一致している必要があります。

例5-47 同じRECORD型のレコードから別のレコードへの代入

この例では、name1とname2が同じRECORD型であるため、name1の値をname2に代入できます。

DECLARE
  TYPE name_rec IS RECORD (
    first  employees.first_name%TYPE DEFAULT 'John',
    last   employees.last_name%TYPE DEFAULT 'Doe'
  );
 
  name1 name_rec;
  name2 name_rec;
 
BEGIN
  name1.first := 'Jane'; name1.last := 'Smith'; 
  DBMS_OUTPUT.PUT_LINE('name1: ' || name1.first || ' ' || name1.last);
  name2 := name1;
  DBMS_OUTPUT.PUT_LINE('name2: ' || name2.first || ' ' || name2.last); 
END;
/

結果:

name1: Jane Smith
name2: Jane Smith

例5-48 RECORD型のレコードへの%ROWTYPEレコードの代入

この例では、代入先の変数がRECORD型、代入元の変数が%ROWTYPEでそれぞれ宣言され、両者のフィールドの数と順序が一致し、対応するフィールドのデータ型が同じです。

DECLARE
  TYPE name_rec IS RECORD (
    first  employees.first_name%TYPE DEFAULT 'John',
    last   employees.last_name%TYPE DEFAULT 'Doe'
  );
 
  CURSOR c IS
    SELECT first_name, last_name
    FROM employees;
 
  target name_rec;
  source c%ROWTYPE;
 
BEGIN
  source.first_name := 'Jane'; source.last_name := 'Smith';
 
  DBMS_OUTPUT.PUT_LINE (
    'source: ' || source.first_name || ' ' || source.last_name
  );
 
 target := source;
 
 DBMS_OUTPUT.PUT_LINE (
   'target: ' || target.first || ' ' || target.last
 );
END;
/

結果:

source: Jane Smith
target: Jane Smith

例5-49 同じRECORD型のネストしたレコードから別のレコードへの代入

この例では、あるネストしたレコードの値を別のネストしたレコードに代入します。これらのネストしたレコードは同じRECORD型を持ちますが、これらがネストされているレコードは同じではありません。

DECLARE
  TYPE name_rec IS RECORD (
    first  employees.first_name%TYPE,
    last   employees.last_name%TYPE
  );
 
  TYPE phone_rec IS RECORD (
    name  name_rec,                    -- nested record
    phone employees.phone_number%TYPE
  );
 
  TYPE email_rec IS RECORD (
    name  name_rec,                    -- nested record
    email employees.email%TYPE
  );
 
  phone_contact phone_rec;
  email_contact email_rec;
 
BEGIN
  phone_contact.name.first := 'John';
  phone_contact.name.last := 'Smith';
  phone_contact.phone := '1-650-555-1234';
 
  email_contact.name := phone_contact.name;
  email_contact.email := (
    email_contact.name.first || '.' ||
    email_contact.name.last  || '@' ||
    'example.com' 
  );
 
  DBMS_OUTPUT.PUT_LINE (email_contact.email);
END;
/

結果:

John.Smith@example.com

5.13.2 レコード変数への行の全体または一部の代入

データベースの表またはビュー内の行の全体または一部を表すレコード変数の場合は、対象となる行をレコード変数に代入できます。

ここでのトピック

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

単純なSELECT INTO文の構文は次のとおりです。

SELECT select_list INTO record_variable_name FROM table_or_view_name;

select_listの各列に対して、レコード変数の中には、対応する型互換のフィールドが存在している必要があります。select_list内の列は、レコード・フィールドと同じ順序で並んでいる必要があります。

関連項目:

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

例5-50 SELECT INTOを使用したレコード変数への値の代入

この例では、レコード変数rec1は、employees表の行の一部(列last_nameおよびemployee_id)を表しています。SELECT INTO文は、job_idが'AD_PRES'の行をemployeesから選択し、選択した行の列last_nameおよびemployee_idの値をrec1の対応するフィールドに代入しています。

DECLARE
  TYPE RecordTyp IS RECORD (
    last employees.last_name%TYPE,
    id   employees.employee_id%TYPE
  );
  rec1 RecordTyp;
BEGIN
  SELECT last_name, employee_id INTO rec1
  FROM employees
  WHERE job_id = 'AD_PRES';

  DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

結果:

Employee #100 = King

5.13.2.2 FETCHを使用した行のレコード変数への代入

単純なFETCH文の構文は次のとおりです。

FETCH cursor INTO record_variable_name;

カーソルは問合せに関連付けられています。問合せにより選択されるすべての列に対して、レコード変数の中には、対応する型互換のフィールドが存在している必要があります。カーソルは、明示カーソルまたは強いカーソル変数のいずれかである必要があります。

関連項目:

構文の詳細は、「FETCH文」を参照してください
すべてのカーソルの詳細は、「カーソルの概要」を参照してください
明示カーソルの詳細は、「明示カーソル」を参照してください
カーソル変数の詳細は、「カーソル変数」を参照してください

例5-51 ファンクションが戻すレコードへのFETCHによる値の代入

この例では、RECORD型EmpRecTypの各変数は、employees表の行の一部(列employee_idおよびsalary)を表しています。カーソルおよびファンクションの両方は、型EmpRecTypの値を戻します。このファンクションでは、列employee_idおよびsalaryの値がFETCH文により型EmpRecTypのローカル変数の対応するフィールドに代入されます。

DECLARE
  TYPE EmpRecTyp IS RECORD (
    emp_id  employees.employee_id%TYPE,
    salary  employees.salary%TYPE
  );
 
  CURSOR desc_salary RETURN EmpRecTyp IS
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC;
 
  highest_paid_emp       EmpRecTyp;
  next_highest_paid_emp  EmpRecTyp;
 
  FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
    emp_rec  EmpRecTyp;
  BEGIN
    OPEN desc_salary;
    FOR i IN 1..n LOOP
      FETCH desc_salary INTO emp_rec;
    END LOOP;
    CLOSE desc_salary;
    RETURN emp_rec;
  END nth_highest_salary;
 
BEGIN
  highest_paid_emp := nth_highest_salary(1);
  next_highest_paid_emp := nth_highest_salary(2);
 
  DBMS_OUTPUT.PUT_LINE(
    'Highest Paid: #' ||
    highest_paid_emp.emp_id || ', $' ||
    highest_paid_emp.salary 
  );
  DBMS_OUTPUT.PUT_LINE(
    'Next Highest Paid: #' ||
    next_highest_paid_emp.emp_id || ', $' ||
    next_highest_paid_emp.salary
  );
END;
/

結果:

Highest Paid: #100, $24000
Next Highest Paid: #101, $17000

5.13.2.3 PL/SQLレコード変数に行を戻すSQL文の使用

SQLのINSERT文、UPDATE文およびDELETE文には、影響のある行をPL/SQLレコード変数に戻すことができる、オプションのRETURNING INTO句があります。

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

例5-52 UPDATE文を使用したレコード変数への値の代入

この例では、従業員の給与をUPDATE文で更新し、従業員の名前および新しい給与をレコード変数に戻します。

DECLARE
  TYPE EmpRec IS RECORD (
    last_name  employees.last_name%TYPE,
    salary     employees.salary%TYPE
  );
  emp_info    EmpRec;
  old_salary  employees.salary%TYPE;
BEGIN
  SELECT salary INTO old_salary
   FROM employees
   WHERE employee_id = 100;
 
  UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id = 100
    RETURNING last_name, salary INTO emp_info;
 
  DBMS_OUTPUT.PUT_LINE (
    'Salary of ' || emp_info.last_name || ' raised from ' ||
    old_salary || ' to ' || emp_info.salary
  );
END;
/

結果:

Salary of King raised from 24000 to 26400

5.13.3 レコード変数へのNULLの代入

値NULLをレコード変数に代入すると、その各フィールドに値NULLが代入されます。

この代入は再帰的です(つまり、フィールドがレコードの場合は、それに属するフィールドにも値NULLが代入されます)。

例5-53 レコード変数へのNULLの代入

この例では、レコード変数にNULLを代入する前後に、レコード変数のフィールド(そのうちの1つがレコード)を出力します。

DECLARE
  TYPE age_rec IS RECORD (
    years  INTEGER DEFAULT 35,
    months INTEGER DEFAULT 6
  );
 
  TYPE name_rec IS RECORD (
    first  employees.first_name%TYPE DEFAULT 'John',
    last   employees.last_name%TYPE DEFAULT 'Doe',
    age    age_rec
  );
 
  name name_rec;
 
  PROCEDURE print_name AS
  BEGIN
    DBMS_OUTPUT.PUT(NVL(name.first, 'NULL') || ' '); 
    DBMS_OUTPUT.PUT(NVL(name.last,  'NULL') || ', ');
    DBMS_OUTPUT.PUT(NVL(TO_CHAR(name.age.years), 'NULL') || ' yrs ');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(name.age.months), 'NULL') || ' mos');
  END;
 
BEGIN
  print_name;
  name := NULL;
  print_name;
END;
/

結果:

John Doe, 35 yrs 6 mos
NULL NULL, NULL yrs NULL mos

5.14 レコードの比較

レコードがNULLかどうか、または等しいかどうかは、そのままテストできません。

次のBOOLEAN式は違反となります。

My_Record IS NULL
My_Record_1 = My_Record_2
My_Record_1 > My_Record_2

このようなテストは、独自のファンクションを記述して実装する必要があります。ファンクションの記述の詳細は、「PL/SQLサブプログラム」を参照してください。

5.15 表へのレコードの挿入

SQLのINSERT文に対するPL/SQLの拡張機能によって、レコードを表に挿入できます。

レコードは、表の行を表している必要があります。詳細は、「INSERT文の拡張機能」を参照してください。表へのレコードの挿入の制限については、「レコードの挿入/更新に関する制限」を参照してください。

レコードのコレクションを効率的に表に挿入するには、INSERT文をFORALL文内に置きます。FORALL文の詳細は、「FORALL文」を参照してください。

例5-54 デフォルト値のレコードの挿入による表の初期化

この例では、表scheduleを作成してから、デフォルト値をレコードに入力し、毎週そのレコードを表に挿入して表を初期化しています。(COLUMN書式設定コマンドはSQL*Plusのコマンドです。)

DROP TABLE schedule;
CREATE TABLE schedule (
  week  NUMBER,
  Mon   VARCHAR2(10),
  Tue   VARCHAR2(10),
  Wed   VARCHAR2(10),
  Thu   VARCHAR2(10),
  Fri   VARCHAR2(10),
  Sat   VARCHAR2(10),
  Sun   VARCHAR2(10)
);
 
DECLARE
  default_week  schedule%ROWTYPE;
  i             NUMBER;
BEGIN
  default_week.Mon := '0800-1700';
  default_week.Tue := '0800-1700';
  default_week.Wed := '0800-1700';
  default_week.Thu := '0800-1700';
  default_week.Fri := '0800-1700';
  default_week.Sat := 'Day Off';
  default_week.Sun := 'Day Off';
 
  FOR i IN 1..6 LOOP
    default_week.week    := i;
    
    INSERT INTO schedule VALUES default_week;
  END LOOP;
END;
/
 
COLUMN week FORMAT 99
COLUMN Mon  FORMAT A9
COLUMN Tue  FORMAT A9
COLUMN Wed  FORMAT A9
COLUMN Thu  FORMAT A9
COLUMN Fri  FORMAT A9
COLUMN Sat  FORMAT A9
COLUMN Sun  FORMAT A9
 
SELECT * FROM schedule;

結果:

WEEK MON       TUE       WED       THU       FRI       SAT       SUN
---- --------- --------- --------- --------- --------- --------- ---------
   1 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   2 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   3 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off

5.16 レコードを使用した行の更新

SQLのUPDATE文に対するPL/SQLの拡張機能によって、レコードを使用して1つ以上の表の行を更新できます。

レコードは、表の行を表している必要があります。詳細は、「UPDATE文の拡張機能」を参照してください。

レコードを使用した表の行の更新に関する制限については、「レコードの挿入/更新に関する制限」を参照してください。

レコードのコレクションを使用して行セットを更新するには、UPDATE文をFORALL文内に置きます。FORALL文の詳細は、「FORALL文」を参照してください。

例5-55 レコードを使用した行の更新

この例では、新しい値をレコードに入力し、そのレコードを使用して表の最初の3つの行を更新することによって、(例5-54で定義した)表scheduleの最初の3週間のデータを更新しています。

DECLARE
  default_week  schedule%ROWTYPE;
BEGIN
  default_week.Mon := 'Day Off';
  default_week.Tue := '0900-1800';
  default_week.Wed := '0900-1800';
  default_week.Thu := '0900-1800';
  default_week.Fri := '0900-1800';
  default_week.Sat := '0900-1800';
  default_week.Sun := 'Day Off';
 
  FOR i IN 1..3 LOOP
    default_week.week    := i;
  
    UPDATE schedule
    SET ROW = default_week
    WHERE week = i;
  END LOOP;
END;
/
 
SELECT * FROM schedule;

結果:

WEEK MON       TUE       WED       THU       FRI       SAT       SUN
---- --------- --------- --------- --------- --------- --------- ---------
   1 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   2 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   3 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off

5.17 レコードの挿入/更新に関する制限

レコードの挿入および更新には、次の制限があります。

レコード変数が使用できるのは、次の位置に限定されます。
- UPDATE文のSET句の右側
- INSERT文のVALUES句の中
- RETURNING句のINTO副次句の中
レコード変数は、SELECTリスト、WHERE句、GROUP BY句またはORDER BY句では使用できません。
キーワードROWを指定できる位置は、SET句の左側のみです。また、ROWと副問合せは一緒に使用できません。
UPDATE文では、ROWが使用されている場合、許可されるSET句は1つのみです。
INSERT文のVALUES句にレコード変数が含まれている場合は、その句の中で他の変数または値を使用することはできません。
RETURNING句のINTO副次句にレコード変数が含まれている場合は、その副次句の中で他の変数または値を使用することはできません。
次の内容はサポートされません。
- ネストされたRECORD型
- RECORD型を戻すファンクション
- EXECUTE IMMEDIATE文を使用したレコードの挿入および更新