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属性のデータ型として使用可能かどうか |
---|---|---|---|---|---|---|
連想配列(または索引付き表) |
指定なし |
文字列または |
密または疎のいずれか |
なし |
PL/SQLブロック内またはパッケージ内 |
不可 |
|
指定されている |
整数 |
常に密 |
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文」を使用して削除するまでデータベースに格納されます。
ADT属性のデータ型として使用可能かどうか
ADT属性のデータ型にするには、コレクション型がスタンドアロン・コレクション型である必要があります。その他の制限は、「datatypeの制限」を参照してください。
非PL/SQLコンポジット型からPL/SQLコンポジット型への変換
他の言語を使用するコードまたはビジネス・ロジックが存在する場合、通常は配列を変換して、その言語の型をPL/SQLのコレクション型に直接設定できます。たとえば:
非PL/SQLコンポジット型 | 等価なPL/SQLコンポジット型 |
---|---|
ハッシュ表 |
連想配列 |
順序付けされていない表 |
連想配列 |
集合 |
ネストした表 |
バッグ |
ネストした表 |
配列 |
|
関連項目:
ある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
から予期しない値が戻されたり例外が呼び出される場合があります。セッション中にこれらのパラメータ値を変更する必要がある場合は、文字列で索引付けされている連想配列に対して操作を実行する前に元の値をリストアしてください。
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
(アクセント記号の有無および大/小文字を区別しない比較)である場合、大/小文字、アクセント記号またはデリミタ文字のみが異なる索引は同じ値に変換される可能性があります。
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を超えることはできません。
VARRAY変数は単一オブジェクトとしてデータベースに格納されます。VARRAY変数が4KB未満の場合は、そのVARRAY変数が列になっている表内に配置され、それ以外の場合は、表の外側に配置されますが、表領域は同じです。
初期化されていないVARRAY変数は、NULLのコレクションです。空にするかNULL
以外の値を代入して初期化する必要があります。詳細は、「コレクションのコンストラクタ」および「コレクション変数への値の代入」を参照してください。
ここでのトピック
関連項目:
-
表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.4 ネストした表
データベース内では、ネストした表は、特に順序を付けずに数が指定されていない行を格納する列型になります。
ネストした表の値をデータベースから取り出してPL/SQLのネストした表の変数に入れると、PL/SQLにより1から始まる連続した索引が行に付けられます。これらの索引を使用して、ネストした表の変数の個々の行にアクセスできます。構文はvariable_name
(
index
)
です。ネストした表の索引と行の順序は、ネストした表をデータベースに格納したりデータベースから取り出したときに変わる可能性があります。
ネストした表の変数に占有されるメモリー量は、要素の追加または削除に応じて動的に増減します。
初期化されていないネストした表の変数は、NULLのコレクションです。空にするかNULL
以外の値を代入して初期化する必要があります。詳細は、「コレクションのコンストラクタ」および「コレクション変数への値の代入」を参照してください。
ここでのトピック
関連項目:
-
表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.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
)
として要素を参照し、値を代入します。
ここでのトピック
関連項目:
-
「代入文」の構文図
-
コレクション変数のスカラー要素に値を代入する方法については、「変数への値の代入」を参照してください
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
句には使用できません。
ここでのトピック
関連項目:
-
機能の記述の詳細は、「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 コレクション・メソッド
メソッド | 型 | 説明 |
---|---|---|
|
プロシージャ |
コレクションから要素を削除します。 |
|
プロシージャ |
VARRAYまたはネストした表の末尾から要素を削除します。 |
|
プロシージャ |
VARRAYまたはネストした表の末尾に要素を追加します。 |
|
ファンクション |
VARRAYまたはネストした表の指定された要素が存在する場合のみ |
|
ファンクション |
コレクションの最初の索引を戻します。 |
|
ファンクション |
コレクションの最後の索引を戻します。 |
|
ファンクション |
コレクション内の要素の数を戻します。 |
|
ファンクション |
コレクションに格納できる要素の最大数を戻します。 |
|
ファンクション |
指定された索引の前の索引を戻します。 |
|
ファンクション |
指定された索引の後の索引を戻します。 |
コレクション・メソッドを起動する基本構文は、次のとおりです。
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;
カーソルは問合せに関連付けられています。問合せにより選択されるすべての列に対して、レコード変数の中には、対応する型互換のフィールドが存在している必要があります。カーソルは、明示カーソルまたは強いカーソル変数のいずれかである必要があります。
例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
文を使用したレコードの挿入および更新
-