| Oracle Databaseオブジェクト・リレーショナル開発者ガイド 11gリリース1（11.1） E05671-02 |
|
 戻る |
 次へ |
この章では、コレクション型（VARRAYおよびネストした表）を作成して管理する方法について説明します。
|
関連項目 コレクションの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
内容は次のとおりです。
Oracleでは、VARRAYとネストした表のコレクション・データ型がサポートされます。
VARRAYとは、順序付けられた要素の集合です。
ネストした表には、要素をいくつでも入れることができます。
一定数の項目のみ格納する必要がある場合、順序に従って要素をループする必要がある場合、またはコレクション全体を1つの値として取り出して操作する必要がある場合は、VARRAYを使用してください。
コレクションについての問合せを効率的に実行する必要がある場合は、任意の数の要素を扱うか、大量の挿入、更新または削除を行ってから、ネストした表を使用してください。「コレクションの設計上の考慮点」を参照してください。
この項の内容は次のとおりです。
この項では、ネストした表型の作成方法を示します。VARRAY型の作成については、「VARRAY」で説明します。
例5-1では、person_typオブジェクトと、person_typオブジェクトのネストした表型としてのpeople_typを作成します。どちらも、この章の後続の例で使用します。
例5-1 後続の例で使用するCREATE TYPE person_typ
CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), MAP MEMBER FUNCTION get_idno RETURN NUMBER, MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) ); / CREATE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN idno; END; MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS BEGIN -- use the put_line procedure of the DBMS_OUTPUT package to display details DBMS_OUTPUT.put_line(TO_CHAR(idno) || ' - ' || name || ' - ' || phone); END; END; / CREATE TYPE people_typ AS TABLE OF person_typ; -- nested table type /
コレクション型のインスタンスは、他のオブジェクト型のインスタンスを作成する場合と同じ方法、つまり型のコンストラクタ・メソッドをコールして作成します。コンストラクタ・メソッドの名前は、型の名前です。コレクションの各要素は、カンマで区切ったメソッドの引数のリストとして指定します。次に例を示します。
( person_typ(1, 'John Smith', '1-650-555-0135'),
空のリストでコンストラクタ・メソッドをコールすると、その型の空のコレクションが作成されます。空のコレクションとは、偶然に空になっている実際のコレクションで、NULLコレクションとは異なる点に注意してください。ネストした表の使用方法の詳細は、「ネストした表の設計上の考慮点」を参照してください。
例5-2では、最初にネストした表型people_typのインスタンスが含まれる表（people_column）を作成し、次にSQL文でコンストラクタ・メソッドを使用して値をpeople_typに挿入する方法を示します。この例では、コンストラクタ・メソッドのリテラル起動を使用します。
例5-2 ネストした表に値を挿入するためのコンストラクタ・メソッドの使用
-- Requires Ex. 5-1
CREATE TABLE people_tab (
group_no NUMBER,
people_column people_typ ) -- an instance of nested table
NESTED TABLE people_column STORE AS people_column_nt; -- storage table for NT
INSERT INTO people_tab VALUES (
100,
people_typ( person_typ(1, 'John Smith', '1-650-555-0135'),
person_typ(2, 'Diane Smith', NULL)));
表の列をオブジェクト型またはコレクション型として宣言する場合、DEFAULT句を含めることができます。明示的に列に値を指定しない場合には、このDEFAULT句で指定した値が使用されます。DEFAULT句には、そのオブジェクトまたはコレクションのコンストラクタ・メソッドのリテラル起動を含める必要があります。
例5-3に、コンストラクタ・メソッドのリテラル起動を使用してオブジェクトperson_typおよびネストした表people_typのデフォルトを指定する方法を示します。
例5-3 DEFAULT句を使用したdepartment_persons表の作成
-- requires Ex. 5-1 CREATE TABLE department_persons ( dept_no NUMBER PRIMARY KEY, dept_name CHAR(20), dept_mgr person_typ DEFAULT person_typ(10,'John Doe',NULL), dept_emps people_typ DEFAULT people_typ() ) -- instance of nested table type NESTED TABLE dept_emps STORE AS dept_emps_tab; INSERT INTO department_persons VALUES ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-650-555-0125'), people_typ( person_typ(1, 'John Smith', '1-650-555-0135'), person_typ(2, 'Diane Smith', NULL) ) ); INSERT INTO department_persons VALUES ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-415-555-0101'), people_typ() ); -- an empty people_typ table
people_typ()は、空のネストした表people_typのコンストラクタ・メソッドのリテラル起動であることに注意してください。
VARRAYとは、順序付けられたデータ要素の集合です。任意のVARRAYのすべての要素は、同じデータ型または宣言されたデータ型のサブタイプの要素です。各要素は、索引を持ちますが、これは配列における要素の位置に対応する番号です。索引番号は、特定の要素にアクセスするために使用されます。
VARRAYを定義するときは、ここに格納できる要素の最大数を指定しますが、この値は後で変更できます。配列における要素の数は、配列のサイズです。配列を可変サイズにでき、この配列をVARRAYといいます。
次の文では、それぞれがVARCHAR2(80)データ型の最大10個の要素を持つ配列型email_list_arrを作成します。
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); /
例5-4では、オブジェクト型の配列としてのVARRAY型を作成します。phone_varray_typ VARRAY型は、dept_phone_list表の列のデータ型として使用されます。このINSERT文は、VARRAY phone_varray_typとオブジェクトphone_typのコンストラクタを起動してphone_varray_typに値を挿入する方法を示しています。
例5-4 VARRAYデータ型の作成と移入
CREATE TYPE phone_typ AS OBJECT (
country_code VARCHAR2(2),
area_code VARCHAR2(3),
ph_number VARCHAR2(7));
/
CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
/
CREATE TABLE dept_phone_list (
dept_no NUMBER(5),
phone_list phone_varray_typ);
INSERT INTO dept_phone_list VALUES (
100,
phone_varray_typ( phone_typ ('01', '650', '5550123'),
phone_typ ('01', '650', '5550148'),
phone_typ ('01', '650', '5550192')));
配列型を作成しても、SQLオブジェクト型と同様、領域は割り当てらません。データ型が定義されます。このデータ型の用途は、次のとおりです。
リレーショナル表の列のデータ型
オブジェクト型属性
PL/SQL変数、パラメータまたはファンクション戻り値の型
通常、VARRAYはインラインに、つまり、その行にある他のデータと同じ表領域に格納されます。VARRAYのサイズが十分であれば、BLOBとして格納されます。「VARRAYの記憶域上の考慮点」を参照してください。
PL/SQLまたはビュー問合せなど、手順上の目的でXMLTypeのVARRAY型またはLOB型を作成できます。ただし、これらの型のVARRAYのデータベース記憶域はサポートされていません。このため、XMLTypeのVARRAY型またはLOB型のオブジェクト表またはオブジェクト型列は作成できません。
|
関連項目 CREATE TABLE文のSTORE AS LOB句の詳細および例は、『Oracle Database SQL言語リファレンス』を参照してください。 |
ネストした表は順序付けされていないデータ要素の集合で、これらの要素はすべて同じデータ型を持ちます。表の定義に、最大値の指定はありません。また、要素の順序は維持されません。通常の表を扱う場合と同様に、TABLE式を使用して、ネストした表でも選択、挿入、削除および更新の操作ができます。
ネストした表は単一列を持ち、この列の型は組込み型またはオブジェクト型です。ネストした表の列がオブジェクト型であれば、オブジェクト型の各属性に対して列を持つ複数列表としても、表を表示できます。
ネストした表型を宣言するには、CREATE TYPE ... AS TABLE OF文を使用します。次に例を示します。
CREATE TYPE people_typ AS TABLE OF person_typ;
表型の定義では、領域は割り当てられません。型が定義されます。この型の用途は、次のとおりです。
リレーショナル表の列のデータ型
オブジェクト型属性
PL/SQL変数、パラメータまたはファンクション戻り型
ネストした表の要素は、実際には個別の記憶表に格納されます。
Oracleでは、ネストした表のデータは、リレーショナル表の列またはオブジェクト表の属性である両方のネストした表型に対応するオブジェクト表に関連付けられた1つの記憶表に格納されます。この記憶表には、ネストした表の各要素が属する親表の行やオブジェクトを識別する列が含まれています。図9-2「ネストした表の記憶域」を参照してください。
NESTED TABLE..STORE AS句は、ネストした表の記憶域名を指定します。記憶域名は、ネストした表に索引を作成するときに使用します。
例5-5では、ネストした表を作成して移入し、例5-1で定義したオブジェクトperson_typおよびネストした表people_typを使用して、ネストした表記憶域を指定します。
例5-5 単純なネストした表の作成および移入
-- Requires 5-1 CREATE TABLE students ( graduation DATE, math_majors people_typ, -- nested tables (empty) chem_majors people_typ, physics_majors people_typ) NESTED TABLE math_majors STORE AS math_majors_nt -- storage tables NESTED TABLE chem_majors STORE AS chem_majors_nt NESTED TABLE physics_majors STORE AS physics_majors_nt; CREATE INDEX math_idno_idx ON math_majors_nt(idno); CREATE INDEX chem_idno_idx ON chem_majors_nt(idno); CREATE INDEX physics_idno_idx ON physics_majors_nt(idno); INSERT INTO students (graduation) VALUES ('01-JUN-03'); UPDATE students SET math_majors = people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), person_typ(31, 'Sarah Chen', '415-555-0120'), person_typ(45, 'Chris Woods', '415-555-0124')), chem_majors = people_typ (person_typ(51, 'Joe Lane', '650-555-0140'), person_typ(31, 'Sarah Chen', '415-555-0120'), person_typ(52, 'Kim Patel', '650-555-0135')), physics_majors = people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), person_typ(45, 'Chris Woods', '415-555-0124')) WHERE graduation = '01-JUN-03'; SELECT m.idno math_id, c.idno chem_id, p.idno physics_id FROM students s, TABLE(s.math_majors) m, TABLE(s.chem_majors) c, TABLE(s.physics_majors) p;
|
注意: 通常と異なる構文:NESTED TABLE..STORE AS句がセミコロン（;）で終わっていません。 |
ネストしたカーソルまたはTABLEファンクションを使用すると、ネストした表の個々の要素に簡単にアクセスできます。「コレクションの問合せ」を参照してください。
ネストした表は、親表と異なる表領域に格納できます。例5-6では、ネストした表はsystem表領域に格納されます。
例5-6 ネストした表を格納するための別の表領域の指定
-- Requires 5-1
CREATE TABLE people_tab (
people_column people_typ )
NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE system);
TABLESPACE句が指定されていない場合、ネストした表の記憶表は、親表が作成された表領域に作成されます。マルチレベルのネストした表の場合、子表を、直系の親表と同じ表領域に作成します。
ALTER TABLE.. MOVE文を発行して、表を別の表領域に移動できます。ネストした表列を持つ表に対してこの文を発行した場合、親表のみ移動され、ネストした表の記憶表に対してはアクションは実行されません。ネストした表の記憶表を別の表領域に移動するには、記憶表に対してALTER TABLE.. MOVEを発行します。次に例を示します。
ALTER TABLE people_tab MOVE TABLESPACE system; -- moving table ALTER TABLE people_column_nt MOVE TABLESPACE example; -- moving storage table
people_tab表はsystem表領域に格納され、ネストした表記憶域はexample表領域に格納されました。
VARRAY型の要素型またはネストした表型が可変文字、RAW型または数値型の場合、可変文字またはRAW型のサイズを大きくするか、数値型の精度を向上できます。VARRAY型またはネストした表型の新しい型バージョンが生成されます。
これらの変更は、ALTER TYPE..MODIFY文を使用して行います。この文には次のオプションがあります。
INVALIDATE: すべての依存オブジェクトを無効とします。
CASCADE: 変更をその型および表の依存オブジェクトに伝播します。
例5-7では、VARRAYおよびネストした表の要素の型のサイズを大きくします。
ALTER TYPE ...MODIFY LIMIT構文を使用して、VARRAY型の要素の数を増加できます。数が増加すると、VARRAY型の新しい型バージョンが生成され、型変更履歴の一部となります。
ALTER TYPE ...MODIFY LIMIT文には次のオプションがあります。
INVALIDATE: すべての依存オブジェクトを無効とします。
CASCADE: 変更をその型および表の依存オブジェクトに伝播します。
例5-8 VARRAYの制限サイズの増加
-- if you have already creating following types, drop them.
DROP TYPE email_list_tab FORCE;
DROP TYPE email_list_arr FORCE;
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
CREATE TYPE email_list_typ AS OBJECT (
section_no NUMBER,
emails email_list_arr);
/
CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;
/
ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE;
VARRAY型が変更されると、その変更は依存表に伝播されます。「VARRAYのサイズ変更の伝播」を参照してください。
LOB参照のVARRAYを作成するには、まずREF email_list_typ型のVARRAY型を定義します。注意: email_list_typは例5-8で定義したものです。次に、表dept_email_listを作成し、その中に配列型の列email_addrsを定義します。
マルチレベル・コレクション型とは、要素自体が直接的または間接的に別のコレクション型であるコレクション型です。使用可能なマルチレベル・コレクション型は、次のとおりです。
ネストした表型のネストした表
VARRAY型のネストした表
ネストした表型のVARRAY
VARRAY型のVARRAY
ネストした表またはVARRAY型である属性を持つユーザー定義型のネストした表またはVARRAY
シングルレベル・コレクション型と同様に、マルチレベル・コレクション型には次の特徴があります。
リレーショナル表の列として、またはオブジェクト表のオブジェクト属性とともに使用できます。
代入では、ソースとターゲットにいずれも同じデータ型が宣言されている必要があります。
この項の内容は次のとおりです。
「ネストした表の要素の格納」で説明したように、ネストした表型の列またはオブジェクト表属性には、ネストした表すべての行を格納するための記憶表が必要になります。ネストした表のマルチレベル・コレクションの場合、ネストした表の内側のセットと外側のセットのいずれについても、ネストした表の記憶域の句を指定する必要があります。
例5-10では、ネストした表のネストした表であるマルチレベル・コレクション型nt_country_typを作成します。この例では、それぞれの地域に国のネストした表のコレクションが存在し、それぞれの国にその所在地のネストした表のコレクションが存在する企業の地域体系をモデル化します。この例では、Oracle HRサンプル・スキーマのregions、countriesおよびlocations表が必要です。
|
関連項目 サンプル・スキーマの使用方法の詳細は、『Oracle Databaseサンプル・スキーマ』を参照してください。 |
例5-10のSQL文は、型がマルチレベル・コレクション（nt_country_typ）である列countriesが含まれる表region_tabを作成します。このマルチレベル・コレクションは、ネストした表属性locationsを持つオブジェクト型のネストした表です。外側のネストした表countriesと内側のネストした表locationsに対し、別々のネストした表句が用意されています。
例5-10 マルチレベルのネストした表の記憶域
-- Requires the HR sample schema CREATE TYPE location_typ AS OBJECT ( location_id NUMBER(4), street_address VARCHAR2(40), postal_code VARCHAR2(12), city VARCHAR2(30), state_province VARCHAR2(25)); / CREATE TYPE nt_location_typ AS TABLE OF location_typ; -- nested table type / CREATE TYPE country_typ AS OBJECT ( country_id CHAR(2), country_name VARCHAR2(40), locations nt_location_typ); -- inner nested table / CREATE TYPE nt_country_typ AS TABLE OF country_typ; -- multilevel collection type / CREATE TABLE region_tab ( region_id NUMBER, region_name VARCHAR2(25), countries nt_country_typ) -- outer nested table NESTED TABLE countries STORE AS nt_countries_tab (NESTED TABLE locations STORE AS nt_locations_tab);
例5-10では、このネストした表はオブジェクトの名前付き属性になっているため、内側のネストした表locationsを名前で参照できます。ただし、内側のネストした表がオブジェクトの属性でない場合には、名前はありません。この場合には、キーワードCOLUMN_VALUEが用意されています。
例5-11では、内側のネストした表に対して名前のかわりにキーワードCOLUMN_VALUEを使用しています。
例5-11 COLUMN_VALUEキーワードを使用したマルチレベルのネストした表の記憶域
CREATE TYPE inner_table AS TABLE OF NUMBER; / CREATE TYPE outer_table AS TABLE OF inner_table; / CREATE TABLE tab1 ( col1 NUMBER, -- inner nested table, unnamed col2 outer_table) NESTED TABLE col2 STORE AS col2_ntab (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);
例5-12に、ネストした表の句の中で記憶表の物理属性を指定する方法を示します。
例5-12 ネストした表の記憶域の物理属性の指定
-- Requires Ex. 5-10 -- drop the following if you have previously created it DROP TABLE region_tab FORCE; CREATE TABLE region_tab ( region_id NUMBER, region_name VARCHAR2(25), countries nt_country_typ) NESTED TABLE countries STORE AS nt_countries_tab ( (PRIMARY KEY (NESTED_TABLE_ID, country_id)) ORGANIZATION INDEX COMPRESS NESTED TABLE locations STORE AS nt_locations_tab);
ネストした表のどの記憶表も、NESTED_TABLE_IDで参照可能な列を含んでいます。この列は、記憶表内の行を、親表内の関連する行と対応付けます。それ自体がネストした表である親表には、システムから提供されたID列が2つあります。
システムから提供された、NESTED_TABLE_IDにより参照可能なID列。これにより、その行が親表の行に再び関連付けられます。
システムから提供された、ネストした表の子の中のNESTED_TABLE_ID列により隠蔽および参照されるID列。
例5-12では、ネストした表countriesにORGANIZATION INDEX句を追加し、ネストした表に1列目がNESTED_TABLE_IDになっている主キーを割り当て、この表を索引構成表（IOT）にしています。この列には、記憶表の行が関連付けられた親表内の行のIDが入っています。NESTED_TABLE_IDを使用して主キーを1列目として指定し、表の索引構成を行うと、同じ親である行に属するネストした表の行すべてが、物理的にクラスタ化され、さらに効率的にアクセスできるようになります。
ネストした表のそれぞれが、専用の表記憶域句を必要とします。したがって、ネストした表の記憶域句はコレクションに存在するネストした表のレベルと同数用意する必要があります。「ネストした表の記憶域」を参照してください。
マルチレベルVARRAYの格納方法は、VARRAYが複数個あるVARRAYの1つである場合と、ネストした表の1つのVARRAYである場合とでは異なります。
複数個あるVARRAYの中の1つのVARRAYの場合、そのサイズが約4000バイト以上の場合、またはLOB記憶域が明示的に指定されている場合を除き、VARRRAY全体がインラインで行に格納されます。
ネストした表のVARRAYの場合、VARRAY全体がLOBに格納されます。この場合、LOBロケータのみが行に格納されます。VARRAYのネストした表要素と関連付けられた記憶表は、存在しません。
VARRAYについては、LOB記憶域を明示的に指定できます。次の例では、VARRAY要素のネストした表に対してこの指定を行います。
例5-13 VARRAYのネストした表に使用するLOB記憶域の指定
-- drop the following types if you have created them
DROP TYPE email_list_typ FORCE;
DROP TABLE dept_email_list FORCE;
DROP TYPE email_list_arr FORCE;
CREATE TYPE email_list_typ AS VARRAY(10) OF VARCHAR2(80);
/
CREATE TYPE email_list_typ AS TABLE OF email_list_arr;
/
CREATE TABLE dept_email_list (
dept_no NUMBER,
email_addrs email_list_typ)
NESTED TABLE email_addrs STORE AS email_addrs_nt
(VARRAY COLUMN_VALUE STORE AS LOB dept_emails_lob);
例5-13では、VARRAYとともにCOLUMN_VALUEキーワードを使用しています。このキーワードの説明、およびこれをネストした表に対して使用する方法は、例5-11を参照してください。
例5-14に、VARRAY型のVARRAYについて指定された明示的なLOB記憶域を示します。
例5-14 VARRAY型のVARRAYに使用するLOB記憶域の指定
CREATE TYPE email_list_typ2 AS OBJECT (
section_no NUMBER,
emails email_list_arr);
/
CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2;
/
CREATE TABLE dept_email_list2 (
dept_no NUMBER,
email_addrs email_varray_typ)
VARRAY email_addrs STORE AS LOB dept_emails_lob2;
「VARRAYの記憶域上の考慮点」を参照してください。『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』も参照してください。
マルチレベル・コレクション型は、シングルレベル・コレクションやその他のオブジェクト型と同様、個々の型のコンストラクタをコールして作成します。マルチレベル・コレクション型のコンストラクタはシステム定義ファンクションの1つで、型と同じ名前を持ち、その型の新しいインスタンスを戻します。コンストラクタ・パラメータは、オブジェクト型の属性の名前と型を持ちます。
例5-15に、マルチレベル・コレクション型nt_country_typのコンストラクタ・コールを示します。nt_country_typコンストラクタがcountry_typコンストラクタをコールし、country_typコンストラクタがnt_location_typコンストラクタをコールし、nt_location_typコンストラクタがlocation_typコンストラクタをコールします。
|
注意: nt_country_typは、別のネストした表が属性として含まれるネストした表であるため、マルチレベル・コレクションです。 |
例5-15 マルチレベル・コレクションのコンストラクタの使用
-- Requires 5-10 and HR sample schema INSERT INTO region_tab VALUES(1, 'Europe', nt_country_typ( country_typ( 'IT', 'Italy', nt_location_typ ( location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''), location_typ(1100, '93091 Calle della Testa','10934','Venice','') ) ), country_typ( 'CH', 'Switzerland', nt_location_typ ( location_typ(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve'), location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') ) ), country_typ( 'UK', 'United Kingdom', nt_location_typ ( location_typ(2400, '8204 Arthur St', '', 'London', 'London'), location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford'), location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester') ) ) ) );
この項では、コレクション・データ型に対する操作について説明します。
この項の内容は次のとおりです。
列または属性としてコレクション型が含まれる表を問い合せるには、次の2つの一般的な方法があります。
コレクションが含まれる結果行の中にコレクションをネストします。
それぞれのコレクション要素が単独で1行に現れるように、コレクションを分散またはネスト解除します。
次の各問合せでは、例5-3に示したdepartment_persons表を使用します。列dept_empsは、person_typ型のネストした表コレクションです。dept_empsコレクション列は、通常のスカラー列としてSELECT構文のリストに現れます。この方法でSELECT構文のリスト内のコレクション列を問い合せると、コレクションと関連付けられた結果行の中のコレクションの各要素がネスト化されます。
例5-16の問合せでは、従業員のネストしたコレクションが取り出されます。
例5-16 コレクション問合せの結果のネスト化
-- Requires Ex. 5-1 and Ex. 5-3 SELECT d.dept_emps FROM department_persons d;
DEPT_EMPS(IDNO, NAME, PHONE)-------------------------------------------------------------PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-650-555-0135'),PERSON_TYP(2, 'Diane Smith', '1-650-555-0135'))SELECT構文のリスト内のオブジェクト型列にコレクション属性が入っている場合は、結果もネストされます。SELECT構文のリストに、このコレクションが明示的にリストされていなくても、結果はネストされます。たとえば、SELECT * FROM department_personsという問合せを行うと、ネストされた結果が作成されます。
すべてのツールやアプリケーションが、ネストされた形式の結果を処理できるとはかぎりません。従来のフォーマットを必要とするツールを使用してOracleコレクション・データを参照するには、1つの行のコレクション属性をネスト解除またはフラット化して1つ以上のリレーショナル行にする必要があります。そのためには、コレクションとともにTABLE式を使用します。TABLE式を使用すると、表と同様の方法で、FROM句でコレクションを問い合せることができます。つまり、ネストした表が入っている行とネストした表を結合する操作をします。
TABLE式は、変数やパラメータなどの一時値を含むコレクション値式の問合せに使用できます。
|
関連項目 TABLE式およびコレクションのネスト解除の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
例5-17の問合せでは、例5-16と同様に従業員のコレクションが取り出されますが、コレクションはネスト解除されます。
例5-17 コレクション問合せの結果のネスト解除
-- Requires Ex. 5-1 and 5-3 SELECT e.* FROM department_persons d, TABLE(d.dept_emps) e;
IDNO NAME PHONE---------- ------------------------------ ---------------1 John Smith 1-650-555-01352 Diane Smith 1-650-555-0135例5-17では、TABLE式がそれ自身の表別名を持つことができることがわかります。TABLE式の表別名は、TABLE式によって戻される列を選択するために、SELECT構文のリストの中にあります。
TABLE式は、別の表別名を使用して、TABLE式が参照するコレクション列が含まれている表を指定します。TABLE(d.dept_emps)式は、dept_empsコレクション列が含まれている表としてdepartment_persons表を指定します。表列を参照するには、TABLE式でFROM句の式の左側にある表の表別名を使用できます。これを左相関といいます。
次の例では、使用するTABLE式の表別名を指定するために、FROM句にdepartment_persons表がリストされています。結果として生成されるdepartment_persons表の列は、TABLE式によって参照される列にかぎられます。
次の例は、従業員のいる部門の行のみを取り出します。
SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) e;
従業員のいない部門の行も取り出すには、外部結合構文を使用します。
SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) (+) e;
(+)は、department_personsとe.dept_empsとの間の依存結合を、NULLで拡張することを示しています。つまり、department_personsからは、e.dept_empsがNULLまたは空である行も問合せ結果に含められます。それらの行のe.dept_empsに対応する列には、NULL値が設定されます。
「コレクション問合せの結果のネスト解除」の例は、コレクションの名前が入っているTABLE式を示しています。もう1つの方法は、TABLE式に、コレクションの副問合せを含めるというものです。
例5-18では、部門番号が101である従業員のコレクションが戻されます。
例5-18 コレクションの副問合せが含まれているTABLE式の使用
-- Requires Ex. 5-1 and 5-3
SELECT *
FROM TABLE(SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101);
TABLE式の中の副問合せには、次の制限があります。
副問合せでは、コレクション型を戻す必要があります。
副問合せのSELECT構文のリストには、1つの項目を入れる必要があります。
副問合せでは、1つのコレクションのみを戻す必要があります。複数行についてのコレクションを戻すことはできません。たとえば、副問合せSELECT dept_emps FROM department_personsは、department_persons表に1つの行のみ入っている場合にかぎり、TABLE式で成功します。表に複数行が含まれている場合、副問合せはエラーを戻します。
例5-19に、CURSOR式に埋め込まれているSELECTのFROM句に使用されているTABLE式を示します。
問合せのネスト解除は、VARRAYとネストした表のどちらについても、マルチレベル・コレクションでも使用できます。例5-20は、ネストした表のマルチレベルのネストした表コレクションに対して行う問合せのネスト解除を示しています。この問合せでは、それぞれの地域がcountriesのネストした表を持ち、それぞれの国がlocationsのネストした表を持つregion_tab表から、すべてのregions、countriesおよびlocationsの名前が戻されます。
例5-20 TABLEファンクションによるマルチレベル・コレクションを伴う問合せのネスト解除
-- Requires Ex. 5-10 and 5-15 SELECT r.region_name, c.country_name, l.location_id FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l; -- the following query is optimized to run against the locations table SELECT l.location_id, l.city FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
出力は次のようになります。
REGION_NAME COUNTRY_NAME LOCATION_ID ------------------------- ---------------------------------------- ----------- Europe Italy 1000 Europe Italy 1100 Europe Switzerland 2900 Europe Switzerland 3000 Europe United Kingdom 2400 Europe United Kingdom 2500 Europe United Kingdom 2600 7 rows selected.
LOCATION_ID CITY
----------- ------------------------------
1000 Roma
1100 Venice
2900 Geneva
3000 Bern
2400 London
2500 Oxford
2600 Stretford
7 rows selected.
実表region_tabの列は第2のSELECT構文のリストには表示されないため、問合せは、直接locations記憶表に対して実行されるように最適化されます。
マルチレベル・コレクションの問合せでは、外部結合構文も使用できます。「ネストを解除する問合せを使用したリレーショナル形式でのオブジェクト・データの表示」を参照してください。
Oracleは、コレクションを対象とした次のDML操作をサポートします。
コレクション全体に対して新しい値を提供する挿入および更新
ネストした表およびマルチレベルのネストした表に対する個別的またはピース単位の更新（要素の挿入、削除および更新を含む）
VARRAY列を対象としたピース単位の更新はサポートされません。ただし、VARRAY列を基本単位として挿入または更新することはできます。この項の内容は次のとおりです。
ネストした表列に対してピース単位の操作を実行する場合、TABLE式を使用します。
TABLE式では、副問合せを使用してネストした表を抽出できます。これによりINSERT文、UPDATE文またはDELETE文を最上位表でなくネストした表に適用できます。
また、CAST演算子も有用です。CAST演算子を使用すると、ネストした表を実際にデータベースに格納することなく、ネストした表に対する設定操作をSQL表記法により実行できます。
例5-21のDML文は、ネストした表列を対象としたピース単位操作を示しています。
例5-21 コレクションを対象としたピース単位操作
-- Requires Ex. 5-1 and 5-3
INSERT INTO TABLE(SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101)
VALUES (5, 'Kevin Taylor', '1-408-555-0199');
UPDATE TABLE(SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101) e
SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-408-555-0199')
WHERE e.idno = 5;
DELETE FROM TABLE(SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101) e
WHERE e.idno = 5;
例5-22は、更新の目的でオブジェクト・インスタンス行を戻すためにVALUEを使用しています。
ピース単位のDMLは、マルチレベルのネストした表に対してのみ実行できます。マルチレベルのVARRAYに対しては実行できません。「基本データ単位としてのコレクション」で説明するように、DML操作は、VARRAYとネストした表のいずれのマルチレベル・コレクションに対しても基本単位として実行できます。
例5-23は、ネストした表のネストした表countriesに対して行うピース単位挿入操作を示しています。この例は、新しい国にlocation_typのネストした表を付けて挿入します。
例5-23 マルチレベル・コレクションに対するピース単位INSERT
-- Requires Ex. 5-10 and 5-15
INSERT INTO TABLE( SELECT countries FROM region_tab r WHERE r.region_id = 2)
VALUES ( 'CA', 'Canada', nt_location_typ(
location_typ(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario')));
例5-24では、ピース単位挿入操作を実行して、内側のネストした表を挿入し、国の所在地を追加します。この例も、前述の例と同様、内側のネストした表を選択して、挿入のターゲットを指定する副問合せが含まれているTABLE式を使用します。
例5-24 内側のネストした表に対するピース単位INSERT
-- Requires Ex. 5-10 and 5-15 INSERT INTO TABLE( SELECT c.locations FROM TABLE( SELECT r.countries FROM region_tab r WHERE r.region_id = 2) c WHERE c.country_id = 'US') VALUES (1700, '2004 Lakeview Rd', '98199', 'Seattle', 'Washington'); SELECT r.region_name, c.country_name, l.location_id FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
この項では、ネストした表およびVARRAYに対するアトミック変更について説明します。
注意: ネストした表はピース単位での変更も可能ですが、VARRAYはピース単位で変更することはできません。
例5-25は、SQLのVARRAYオブジェクト型をPL/SQL文で操作する方法を示しています。この例では、VARRAYをPL/SQL変数とSQL表の間で転送しています。この例のようにして、コレクションを含む行を挿入したり、行を更新してコレクションを置き換えたり、コレクションをPL/SQL変数内で選択したりできます。
ただし、VARRAYの個々の要素をSQLで直接更新したり削除することはできません。表からVARRAYを選択し、PL/SQLで変更した後、新しいVARRAYを含むように表を更新する必要があります。この操作はネストした表でも行うことができますが、ネストした表には、ピース単位の更新および削除を行うこともできます。
例5-25 VARRAYに対するINSERT文、UPDATE文、DELETE文およびSELECT文の使用
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30); / CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var); BEGIN INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance')); INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping')); INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales')); COMMIT; END; / DECLARE new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 'Executive', 'Marketing'); some_dnames dnames_var; BEGIN UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe'; COMMIT; SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe'; FOR i IN some_dnames.FIRST .. some_dnames.LAST LOOP DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i)); END LOOP; END; /
「マルチレベル・コレクションのコンストラクタ」の項で、INSERT文を使用してマルチレベル・コレクション全体を挿入する例を示しました。マルチレベル・コレクション（VARRAYまたはネストした表にかかわらず）は基本単位として更新することもできます。たとえば、v_countryがcountries のネストした表型nt_country_typとして宣言された変数であるとします。
例5-26では、countriesコレクションを1つの単位としてv_countryの値に設定することで、region_tabを更新します。
例5-26 UPDATEを使用したマルチレベル・コレクション全体の挿入
-- Requires Ex. 5-10 and 5-15
INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');
DECLARE
v_country nt_country_typ;
BEGIN
v_country := nt_country_typ( country_typ(
'US', 'United States of America', nt_location_typ (
location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
UPDATE region_tab r
SET r.countries = v_country WHERE r.region_id = 2;
END;
/
-- Invocation:
SELECT r.region_name, c.country_name, l.location_id
FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l
WHERE r.region_id = 2;
PL/SQL句BULK COLLECTは、処理に時間のかかるDML文を使用しない方法の1つです。結果セット全部を1つの操作で戻すことができます。
例5-27では、オブジェクト型を含むマルチレベル・コレクションに対してBULK COLLECTを使用しています。
例5-27 コレクションに対するBULK COLLECTの使用
-- unrelated to other examples in this chapter
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/
DECLARE
TYPE dnames_tab IS TABLE OF dnames_var;
v_depts dnames_tab;
BEGIN
SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/
この項で示す条件を使用すると、ネストした表（マルチレベルのネストした表を含む）を比較できます。VARRAYを比較するためのメカニズムはありません。この項のSQLの例では、例5-5で作成したネストした表を使用します。また、例5-1で作成したオブジェクトが含まれています。
等価（=）および非等価（<>）条件は、入力されたネストした表が同一であるかどうかを判断し、結果をブール値として戻します。
同一の名付けられた型およびカーディナリティを持ち、要素が等価である場合、2つのネストした表は等価です。要素が等価であるかどうかは、マップ・メソッドを必要とするオブジェクト型を除き、要素自体の等価性定義によって等価であるかどうかに依存します。
例5-28 ネストした表への等価比較の使用
-- Requires Ex. 5-1 and 5-5 SELECT p.name FROM students, TABLE(physics_majors) p WHERE math_majors = physics_majors;
例5-28では、ネストした表には、関連付けられたマップ・メソッドを持つperson_typオブジェクトが含まれています。例5-1を参照してください。
SUBMULTISET [OF]条件は、特定のネストした表が、別のネストした表のサブセットであるかどうかを確認し、結果をブール値として戻します。OFキーワードはオプションで、SUBMULTISETの機能を変更しません。
この条件は、ネストした表のみに実装されます。
MEMBER [OF]またはNOT MEMBER [OF]条件は、特定の要素がネストした表のメンバーであるかどうかを確認し、結果をブール値として戻します。OFキーワードはオプションで、出力に影響を与えません。
例5-31で、person_typは、ネストした表math_majorsの要素と同じ型の要素です。
例5-31 ネストした表へのMEMBER OFの使用
-- Requires Ex. 5-1 and 5-5 SELECT graduation FROM students WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') MEMBER OF math_majors;
例5-32では、MEMBER OF条件を使用しないアプローチを示します。サイズの大きいコレクションの場合、この方法のほうが実行効率が高くなります。
この項では、ネストした表に対する多重集合操作について説明します。VARRAYには多重集合操作を実行できません。
この項のSQLの例では、例5-5で作成したネストした表と、例5-1で作成したオブジェクトを使用します。
|
関連項目
|
CARDINALITYファンクションは、ネストした表に含まれている要素の数を戻します。戻り型はNUMBERです。ネストした表がNULLコレクションの場合、NULLが戻されます。
例5-35 ネストした表のCARDINALITYの判別
-- Requires Ex. 5-1 and 5-5 SELECT CARDINALITY(math_majors) FROM students;
CARDINALITYファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
COLLECTファンクションは集計関数で、要素のセットから多重集合を作成します。入力として要素型の列を取り、選択された行から多重集合を作成します。このファンクションの結果を取得するには、COLLECTの出力型を指定するために、CASTファンクション内で使用する必要があります。COLLECTファンクションの例は、「CAST」を参照してください。
COLLECTファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
MULTISET EXCEPT演算子は、2つのネストした表を入力として取り、最初のネストした表にのみ含まれ、2番目のネストした表には含まれていない要素のネストした表を戻します。入力のネストした表と出力のネストした表は、すべて型の名前が等価です。つまり、型のuser.nameは同じですが、構造は同じでなくてもかまいません。
この演算子とともに、ALLまたはDISTINCTオプションを使用できます。デフォルトは、ALLです。
ALLオプションを使用すると、ntab1 MULTISET EXCEPT ALL ntab2の場合、ntab2に含まれている要素を除くntab1のすべての要素が、結果に含まれます。特定の要素が、ntab1ではm回、ntab2ではn回出現する場合、mがnより大きければ、結果にはこの要素が(m - n)回出現します。そうでない場合、この要素は0回出現します。
DISTINCTオプションを使用すると、ntab1に存在し、ntab2にも存在する要素は、出現回数に関係なく削除されます。
例5-36 ネストした表に対するMULTISET EXCEPT操作の使用
-- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET EXCEPT physics_majors FROM students WHERE graduation = '01-JUN-03';
MULTISET EXCEPT演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
MULTISET INTERSECT演算子は、入力された2つのネストした表に共通する値で構成されるネストした表を戻します。入力のネストした表と出力のネストした表は、すべて、型の名前が等価です。
この演算子には、ALLまたはDISTINCTオプションが関連付けられています。デフォルトは、ALLです。ALLオプションを使用すると、特定の値が、ntab1ではm回、ntab2ではn回出現する場合、結果にはこの要素がMIN(m, n)回出現します。DISTINCTオプションの場合、結果から重複する要素は削除されます（NULL値が重複する場合はこれも含まれます）。
例5-37 ネストした表に対するMULTISET INTERSECT操作の使用
-- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET INTERSECT physics_majors FROM students WHERE graduation = '01-JUN-03';
MULTISET INTERSECT演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
MULTISET UNION演算子は、入力された2つのネストした表の値で構成されるネストした表を戻します。入力のネストした表と出力のネストした表は、すべて、型の名前が等価です。
この演算子には、ALLまたはDISTINCTオプションが関連付けられています。デフォルトは、ALLです。ALLオプションを使用すると、ntab1およびntab2に含まれるすべての要素が、NULLのすべてのコピーを含め、結果に含まれます。特定の要素がntab1ではm回、ntab2ではn回出現する場合、結果にはこの要素が(m + n)回出現します。DISTINCTオプションの場合、結果から重複する要素は削除されます（NULL値が重複する場合はこれも含まれます）。
例5-38 ネストした表に対するMULTISET UNION操作の使用
-- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET UNION DISTINCT physics_majors FROM students WHERE graduation = '01-JUN-03';
PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'), PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'), PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))-- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET UNION ALL physics_majors FROM students WHERE graduation = '01-JUN-03';
PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'), PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'), PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'), PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'), PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))MULTISET UNION演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
POWERMULTISETファンクションは、指定された多重集合から、すべての空でないサブ多重集合を生成します。POWERMULTISETファンクションの入力には、多重集合と評価されるすべての式を使用できます。多重集合引数のカーディナリティの制限は32です。
例5-39 多重集合に対するPOWERMULTISET操作の使用
-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET( people_typ (
person_typ(12, 'Bob Jones', '1-650-555-0130'),
person_typ(31, 'Sarah Chen', '1-415-555-0120'),
person_typ(45, 'Chris Woods', '1-415-555-0124'))));
POWERMULTISETファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
POWERMULTISET_BY_CARDINALITYファンクションは、指定されたカーディナリティのネストした表の空でないサブ多重集合をすべて戻します。出力は、ネストした表の行です。
POWERMULTISET_BY_CARDINALITY(x, l)はTABLE(POWERMULTISET(x)) pと等価です。CARDINALITY(value(p)) = l、xは多重集合で、1は指定されたカーディナリティです。
POWERMULTISET_BY_CARDINALITYの最初の入力パラメータには、ネストした表と評価されるすべての式を使用できます。長さのパラメータには正の整数を使用します。そうでない場合、エラーが戻されます。ネストした表引数のカーディナリティの制限は32です。
例5-40 POWERMULTISET_BY_CARDINALITYファンクションの使用
-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ (
person_typ(12, 'Bob Jones', '1-650-555-0130'),
person_typ(31, 'Sarah Chen', '1-415-555-0120'),
person_typ(45, 'Chris Woods', '1-415-555-0124')),2));
POWERMULTISET_BY_CARDINALITYファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
パーティション化は、大規模な表や索引を、パーティションと呼ばれる小さく管理が容易な単位に分解することで、大規模な表や索引のサポート上の主要な問題が解決できます。Oracleでは、オブジェクト、REF、VARRAYおよびネストした表を含む表をパーティション化できるように、パーティション化の機能を拡張しています。LOBに格納されたVARRAYは、LOBと同様の方法で同一レベル・パーティション化されます。『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』も参照してください。
例5-42では、列オブジェクトShipToAddrの属性であるzipコード（ToZip）に従って、発注書の表をパーティション化します。この例の目的上、パーティション化されたVARRAYの記憶域を示すために、ネストした表LineItemListはVARRAYにされています。
これで、ネストした表が、最上位の基本表のパーティション化に従ってパーティション化されます。最上位の表へのメンテナンス操作は、関連付けられたネストした表にカスケードされます。
例5-42では、付録A「オブジェクト・リレーショナル機能を使用したサンプル・アプリケーション」の例A-14、例A-16、例A-17、例A-18および例A-19でオブジェクトを作成したことを前提としています。
例5-42 オブジェクトを持つ表のパーティション化
CREATE TYPE LineItemList_vartyp as varray(10000) of LineItem_objtyp;
/
CREATE TYPE PurchaseOrder_typ AS OBJECT (
PONo NUMBER,
Cust_ref REF Customer_objtyp,
OrderDate DATE,
ShipDate DATE,
OrderForm BLOB,
LineItemList LineItemList_vartyp,
ShipToAddr Address_objtyp,
MAP MEMBER FUNCTION
ret_value RETURN NUMBER,
MEMBER FUNCTION
total_value RETURN NUMBER);
/
CREATE TABLE PurchaseOrders_tab of PurchaseOrder_typ
LOB (OrderForm) store as (nocache logging)
PARTITION BY RANGE (ShipToAddr.zip)
(PARTITION PurOrderZone1_part
VALUES LESS THAN ('59999')
LOB (OrderForm) store as (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))
VARRAY LineItemList store as LOB (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),
PARTITION PurOrderZone6_part
VALUES LESS THAN ('79999')
LOB (OrderForm) store as (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))
VARRAY LineItemList store as LOB (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),
PARTITION PurOrderZoneO_part
VALUES LESS THAN ('99999')
LOB (OrderForm) store as (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))
VARRAY LineItemList store as LOB (
storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)));
