タイプ付き表は、CREATE TYPE ステートメントを使用して特性が定義されているオブジェクトのインスタンスを実際に保管するのに使用されます。変形した CREATE TABLE ステートメントを使用して、タイプ付き表を作成することができます。構造型の階層を基にしたタイプ付き表の階層を作成することもできます。サブタイプのインスタンスをデータベース表に保管するには、対応する表階層を作成する必要があります。
次の例は、図 9 に示されているタイプ階層に基づく表階層の作成方法を示しています。
BusinessUnit タイプ付き表を作成する SQL は、次のとおりです。
CREATE TABLE BusinessUnit OF BusinessUnit_t (REF IS Oid USER GENERATED);
Person 表階層に表を作成する SQL は、次のとおりです。
CREATE TABLE Person OF Person_t (REF IS Oid USER GENERATED); CREATE TABLE Employee OF Employee_t UNDER Person INHERIT SELECT PRIVILEGES (SerialNum WITH OPTIONS NOT NULL, Dept WITH OPTIONS SCOPE BusinessUnit ); CREATE TABLE Student OF Student_t UNDER Person INHERIT SELECT PRIVILEGES; CREATE TABLE Manager OF Manager_t UNDER Employee INHERIT SELECT PRIVILEGES; CREATE TABLE Architect OF Architect_t UNDER Employee INHERIT SELECT PRIVILEGES;
前述の例で最初に作成されるタイプ付き表は、BusinessUnit です。この表は、BusinessUnit_t のタイプとして定義されているので、このタイプのインスタンスを保持することになります。この表は、構造型 BusinessUnit_t のそれぞれの属性に対応する列と、オブジェクト識別子列 という 1 つの追加の列を持つようになるということです。
タイプ付き表には他のオブジェクトが参照できるオブジェクトが入っているので、すべてのタイプ付き表の最初の列はオブジェクト識別子 列になっています。この例では、オブジェクト識別子列のタイプは REF(BusinessUnit_t) です。 REF IS ... USER GENERATED 文節を使用して、オブジェクト識別子列に名前を付けることができます。この例では、Oid という名前が付けられています。 REF IS 文節の USER GENERATED 部分は、新たに挿入されるすべての行のオブジェクト識別子列の初期値を与える必要があることを示しています。オブジェクト識別子の挿入後は、オブジェクト識別子の値を更新することはできません。オブジェクト識別子を自動的に生成するよう DB2 を構成する方法の詳細については、 システム生成オブジェクト識別子の定義を参照してください。
Person タイプ付き表のタイプは Person_t です。従業員と生徒のサブタイプのインスタンスを保管するには、 Person 表の副表である Employee と Student を作成する必要があります。 Employee_t の 2 つの追加のサブタイプにも表が必要です。これらの副表の名前は、Manager と Architect になります。サブタイプがスーパータイプの属性を継承するのと同様に、副表もオブジェクト識別子列を含めたスーパー表の列を継承します。
注: | 副表はスーパー表と同じスキーマに常駐していなければなりません。 |
したがって、Employee 副表の中の行の列は、 Oid、Name、Age、Address、 SerialNum、Salary、および Dept の合計 7 つになります。
スーパー表に対して作用する SELECT、UPDATE、または DELETE ステートメントは、すべての副表に対しても自動的に作用します。たとえば、Employee に対する UPDATE ステートメントは、 Employee、Manager、および Architect 表の中の行に影響を与えますが、 Manager 表に対する UPDATE ステートメントは、 Manager 行だけにしか影響を与えません。
SELECT、INSERT、または DELETE ステートメントのアクションを特定の表に制限する場合は、 ONLY を使用して特定のタイプのオブジェクトを戻すで説明されている ONLY オプションを使用します。
CREATE TABLE ステートメントの INHERIT SELECT PRIVILEGES 文節は、 Employee などの結果副表が、 Person などのスーパー表 (結果表は UNDER 文節を使用してここから作成される) と同じユーザーおよびグループによって、最初はアクセス可能であることを指定しています。スーパー表に対して現在 SELECT 特権を保持しているユーザーまたはグループには、新しく作成された副表に対する SELECT 特権が授与されます。副表の作成者が、SELECT 特権の授与者になります。副表に対する DELETE や UPDATE などの特権を指定するには、正規表に対する特権を指定するのに使用するのと同じ明示的 GRANT または REVOKE ステートメントを発行する必要があります。 INHERIT SELECT PRIVILEGES の詳細については、 SQL 解説書 を参照してください。
特権は、表階層のすべてのレベルで別々に授与したり取り消したりすることができます。副表を作成する場合は、その副表に対する継承された SELECT 特権を取り消すこともできます。継承された SELECT 特権を副表から取り消すと、スーパー表に対する SELECT 特権を持つユーザーは、副表だけに表示される列を見ることができなくなります。継承された SELECT 特権を副表から取り消すと、スーパー表に対する SELECT 特権しか持っていないユーザーは、副表の行のスーパー表の列だけを見ることができます。副表に対する必要な特権を保持しているユーザーが直接操作できるのは、副表だけです。したがって、ユーザーが副表の管理者の賞与を選択できないようにするには、その副表に対する SELECT 特権を取り消して、この情報が必要なユーザーだけに SELECT 特権を授与するようにします。
WITH OPTIONS 文節を使用すると、タイプ付き表の中の個々の列に適用されるオプションを定義することができます。 WITH OPTIONS の形式は次のとおりです。
column-name WITH OPTIONS column-options
column-name は CREATE TABLE または ALTER TABLE ステートメントの中の列の名前を表し、 column-options は列に定義されているオプションを表しています。
たとえば、ユーザーが SerialNum 列にヌルを挿入できないようにするには、次のようにして NOT NULL 列オプションを指定します。
(SerialNum WITH OPTIONS NOT NULL)
WITH OPTIONS の別の使用法は、列の効力範囲を指定するという方法です。たとえば、Employee 表とその副表の中では、
Dept WITH OPTIONS SCOPE BusinessUnit
という文節は、この表とその副表の Dept 列の効力範囲 が、 BusinessUnit であると宣言しています。これは、Employee 表のこの列の中の参照値は、 BusinessUnit 表の中のオブジェクトを参照することになっているということです。
たとえば、Employee 表に対する次の照会では、逆参照演算子が使用されていて、 Dept 列から BusinessUnit 表へパスをたどるよう DB2 に伝えています。逆参照演算子は、Name 列の値を戻します。
SELECT Name, Salary, Dept->Name FROM Employee;
参照および参照の効力範囲の指定の詳細については、参照タイプの使用を参照してください。
前述の例で構造型を作成した後、また対応する表と副表を作成した後のデータベースの構造は、 図 11 のようになります。
![]() |
階層を設定すれば、INSERT ステートメントを通常どおりに使用して表にデータを挿入することができます。唯一の相違点は、オブジェクト識別子列にデータを必ず挿入しなければならないこと、またそれぞれの表または副表の中のオブジェクトの追加の属性に任意でデータを挿入することです。オブジェクト識別子列のタイプは REF (大文字で入力される) であるので、構造型を作成した時にシステムによって生成された cast 関数を使用して、ユーザー提供のオブジェクト識別子の値をキャストする必要があります。
INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(1), 'Toy', 15); INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(2), 'shoe', 10); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('a'), 'Andrew', 20); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('b'), 'Bob', 30); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('c'), 'Cathy', 25); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, BusinessUnit_t(1)); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('e'), 'Eva', 31, 83, 45000, BusinessUnit_t(2)); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('f'), 'Franky', 28, 214, 39000, BusinessUnit_t(2)); INSERT INTO Student (Oid, Name, Age, SerialNum, GPA) VALUES(Student_t('g'), 'Gordon', 19, '10245', 4.7); INSERT INTO Student (Oid, Name, Age, SerialNum, GPA) VALUES(Student_t('h'), 'Helen', 20, '10357', 3.5); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('i'), 'Iris', 35, 251, 55000, BusinessUnit_t(1), 12000); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('j'), 'Christina', 10, 317, 85000, BusinessUnit_t(1), 25000); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('k'), 'Ken', 55, 482, 105000, BusinessUnit_t(2), 48000); INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption) VALUES(Architect_t('l'), 'Leo', 35, 661, 92000, BusinessUnit_t(2), 20000);
前述の例では、住所は挿入されていません。列に構造型を挿入する方法の詳細については、 構造型値が入っている行の挿入を参照してください。
タイプ付き表に行を挿入する時は、挿入されたそれぞれの行の最初の値は、表に挿入されるデータのオブジェクト識別子になっていなければなりません。また、非タイプ付き表の場合と同様に、 NOT NULL として定義されているすべての列にデータを入れなければなりません。最後に、参照値が指定されているふさわしいタイプの式であればどれでも、参照属性を初期化するのに使用することができる点に注意してください。前述の例では、従業員の Dept 参照は、ふさわしくタイプ・キャストされた定数としての入力です。しかし、次の例で示されているように、副照会を使用して参照を入手することもできます。
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption) VALUES(Architect_t('m'), 'Brian', 7, 882, 112000, (SELECT Oid FROM BusinessUnit WHERE name = 'Toy'), 30000);
それぞれの構造型について、DB2 は対応する参照タイプをサポートしています。たとえば、Person_t タイプを作成すると、 DB2 は REF(Person_t) というタイプを自動的に作成します。 REF(Person_t) タイプの表示タイプ (および Person_t のすべてのサブタイプの REF タイプ)は、デフォルトで VARCHAR (16) FOR BIT DATA になっていますが、 CREATE TYPE ステートメントに REF USING 文節を使用して、別の表示タイプを選択することができます。この参照タイプは、構造型のインスタンスを保管するために作成するタイプ付き表のオブジェクト識別子列の基盤となります。たとえば、参照タイプのデフォルトの表示タイプを使用して、ルート・タイプ People_t を作成する場合は、関連した People 表のオブジェクト識別子列は、 VARCHAR(16) FOR BIT DATA に基づいたものになります。
参照タイプは大文字で入力します。参照を定数と比較するには、定数をふさわしい参照タイプにキャストしてから、または参照タイプを基本タイプにキャストしてから比較を行います。特定のタイプ階層内の参照は、すべて同じ参照表示タイプになります。このようになっているので、S と T が共通のスーパータイプを持っていれば、 REF(S) と REF(T) を比較することができます。オブジェクト識別子列が固有でなければならないのは 1 つの表階層内だけなので、それぞれ異なる行を参照しているとしても、ある表階層内の REF(T) の値が、別の表階層内の REF(T) の値と等しいこともあり得ます。
CREATE TABLE の WITH OPTIONS 文節を使用すると、ある表の列と、同じ表または別の表のオブジェクトとの間に存在する関係を定義することができます。たとえば、BusinessUnit と Person 表階層では、それぞれの従業員の部門は、図 12 に示されているように、実際には BusinessUnit 表のオブジェクトへの参照になっています。特定の参照列の宛先オブジェクトを定義するには、 WITH OPTIONS 文節で SCOPE キーワードを使用します。
図 12. Dept 属性は BusinessUnit オブジェクトを参照している
![]() |
同一のタイプ付き表の中のオブジェクトへの効力範囲が指定された参照を定義することもできます。次の例のステートメントは、部品用に 1 つのタイプ付き表と製造業社用に 1 つのタイプ付き表を作成します。参照タイプ定義を示すために、このサンプルには参照タイプを作成するのに使用されるステートメントも含まれています。
CREATE TYPE Company_t AS (name VARCHAR(30), location VARCHAR(30)) MODE DB2SQL ; CREATE TYPE Part_t AS (Descript VARCHAR(20), Supplied_by REF(Company_t), Used_in REF(part_t)) MODE DB2SQL; CREATE TABLE Suppliers OF Company_t (REF IS suppno USER GENERATED); CREATE TABLE Parts OF Part_t (REF IS Partno USER GENERATED, Supplied_by WITH OPTIONS SCOPE Suppliers, Used_in WITH OPTIONS SCOPE Parts);
![]() |
効力範囲が指定された参照を使用しない場合は、外部結合または相関副照会として作成する必要のある照会を、効力範囲が指定された参照を使用して作成することができます。詳細については、参照を逆参照する照会を参照してください。
効力範囲が指定された参照は表の中のオブジェクト間の関係を定義しますが、これは参照保全関係とは異なります。効力範囲は、ターゲット表についての情報を提供するにすぎません。この情報は、そのターゲット表のオブジェクトを逆参照する時に使用されます。効力範囲が指定された参照では、他の表に値が存在していなければならないということはありません。たとえば、Employee 表の中の Dept 列は、 BusinessUnit 表の中に存在していない BusinessUnit オブジェクト識別子列を参照することができます。これらの関係においてオブジェクトを必ず存在させるようにするには、表の間に参照制約を追加する必要があります。詳細については、オブジェクト識別子列に対する制約の作成を参照してください。
CREATE VIEW ステートメントを使用して、タイプ付き視点を作成することができます。たとえば、タイプ付き BusinessUnit 表の視点を作成するには、適切な属性を持つ構造型を定義してから、その構造型を使用してタイプ付き視点を作成することができます。
CREATE TYPE VBusinessUnit_t AS (Name VARCHAR(20)) MODE DB2SQL; CREATE VIEW VBusinessUnit OF VBusinessUnit_t MODE DB2SQL (REF IS VObjectID USER GENERATED) AS SELECT VBusinessUnit_t(VARCHAR(Oid)), Name FROM BusinessUnit;
CREATE VIEW ステートメントの OF 文節は、指示された構造型の属性を視点の列の基礎とするよう DB2 に伝えます。この例では、DB2 は VBusinessUnit_t 構造型を視点の列の基礎とします。
視点の VObjectID 列のタイプは、REF(VBusinessUnit_t) です。タイプ REF(BusinessUnit_t) から REF(VBusinessUnit_t) にキャストすることはできないので、最初に表 BusinessUnit の Oid 列の値をデータ・タイプ VARCHAR にキャストしてから、データ・タイプ VARCHAR からデータ・タイプ REF(VBusinessUnit_t) にキャストする必要があります。
MODE DB2SQL 文節は、タイプ付き視点のモードを指定します。現在サポートされているモードはこのモードだけです。
REF IS... 文節は、タイプ付き CREATE TABLE ステートメントの REF IS...文節と同じです。これは、視点の最初の列である視点のオブジェクト識別子列の名前 (この例では VObjectID) を指定します。ルート・タイプ上でタイプ付き視点を作成する場合は、視点のオブジェクト識別子列を指定する必要があります。サブタイプ上でタイプ付き視点を作成する場合は、視点はオブジェクト識別子列を継承します。
USER GENERATED 文節は、ユーザーが行の挿入時にオブジェクト識別子列の初期値を提供する必要があることを指定します。初期値を挿入した後は、オブジェクト識別子列を更新することはできません。
キーワード AS の後ろにある視点の本体 は、視点の内容を決定する SELECT ステートメントです。この SELECT ステートメントが戻す列タイプは、初期オブジェクト識別子列を含むタイプ付き視点の列タイプと互換性を保っている必要があります。
タイプ付き視点階層の作成を示すために、次の例は、いくつかの機密データが省略された、また事前にタイプ付き表の作成で作成した Person 表階層のタイプ区別が除去された視点階層を定義します。
CREATE TYPE VPerson_t AS (Name VARCHAR(20)) MODE DB2SQL; CREATE TYPE VEmployee_t UNDER VPerson_t AS (Salary INT, Dept REF(VBusinessUnit_t)) MODE DB2SQL; CREATE VIEW VPerson OF VPerson_t MODE DB2SQL (REF IS VObjectID USER GENERATED) AS SELECT VPerson_t (VARCHAR(Oid)), Name FROM ONLY(Person); CREATE VIEW VEmployee OF VEmployee_t MODE DB2SQL UNDER VPerson INHERIT SELECT PRIVILEGES (Dept WITH OPTIONS SCOPE VBusinessUnit) AS SELECT VEmployee_t(VARCHAR(Oid)), Name, Salary, VBusinessUnit_t(VARCHAR(Dept)) FROM Employee;
2 つの CREATE TYPE ステートメントは、この例のオブジェクト視点階層を作成するのに必要な構造型を作成します。
上記の最初のタイプ付き CREATE VIEW ステートメントは、階層のルート視点 VPerson を作成し、 VBusinessUnit 視点定義と非常によく似たものとなっています。 Person 表の中 (副表の中ではない) の Person 表階層の中の行だけが、 VPerson 視点に組み込まれるようにするために、 ONLY(Person) が使用されている点が異なっています。これによって、VPerson の中の Oid 値は、 VEmployee の中の Oid 値と比較して一意的になります。 2 番目の CREATE VIEW ステートメントは、視点 VPerson の下に副視点 VEmployee を作成します。 CREATE TABLE...UNDER ステートメントの UNDER 文節の場合と同様に、 UNDER 文節は視点階層を設定します。スーパー視点として、同じスキーマの中に副視点を作成する必要があります。タイプ付き表と同様に、副視点はスーパー視点から列を継承します。 VEmployee 視点の中の行は、列 VObjectID と Name を VPerson から継承していて、 VEmployee_t と関連した追加の列 Salary と Dept を持っています。
CREATE VIEW ステートメントを発行する時の INHERIT SELECT PRIVILEGES 文節の効果は、タイプ付き CREATE TABLE ステートメントを発行する時と同じです。 INHERIT SELECT PRIVILEGES 文節の詳細については、 SELECT 特権は継承されたものであることの表示を参照してください。タイプ付き視点定義の中の WITH OPTIONS 文節の効果も、タイプ付き表定義の中の WITH OPTIONS 文節の効果と同じです。 WITH OPTIONS 文節を指定すると、SCOPE などの列オプションを指定することができます。 READ ONLY 文節は、スーパー視点列を強制的に読み取り専用としてマークするので、これ以降の副視点定義は、読み取り専用となっている同じ列の式を指定できます。
視点に VEmployee 視点の Dept 列のような参照列がある場合は、参照列を SQL 逆参照操作で使用するには、参照列に効力範囲を関連付ける必要があります。視点の参照列に効力範囲を指定しないで、基礎表または視点列に効力範囲を指定してある場合は、基礎列の効力範囲が視点の参照列に渡されます。 WITH OPTIONS 文節を使用して、視点の参照列に明示的に効力範囲を割り当てることができます。前述の例では、VEmployee 視点の Dept 列は、 VBusinessUnit 視点を効力範囲として受け取ります。基礎表または視点列に効力範囲が指定されていない場合で、視点定義で明示的に効力範囲が割り当てられていない場合、または ALTER VIEW ステートメントを使用して効力範囲が割り当てられている場合は、参照列には効力範囲が指定されません。
SQL 解説書 には、タイプ付き視点の照会に対する制限に関連したいくつかの重要な規則が説明されているので、タイプ付き視点を作成して使用する前に注意深く読んでください。
DROP ステートメントを使用して、ユーザー定義タイプまたはタイプ・マッピングを除去することができます。タイプ・マッピングの詳細については、データ・タイプ・マッピングの処理を参照してください。次の場合には UDT を除去することはできません。
デフォルトのタイプ・マッピングを除去することはできません。別のタイプ・マッピングを作成して上書きすることしかできないのです。
データベース・マネージャーは、この UDT に従属するすべてのユーザー定義関数 (UDF) を除去しようとします。視点、トリガー、表検査制約、または別の UDF が従属している UDF を除去することはできません。 DB2 が従属 UDF を除去できない場合は、DB2 は UDT を除去しません。 UDT を除去すると、それを使用していたパッケージまたはキャッシュされた動的 SQL すべてが無効になります。
ある UDT のための transform を作成してある場合で、その UDT を除去しようとする場合は、関連した transform を除去することを考慮してください。 transform を除去するには、DROP TRANSFORM ステートメントを発行します。 DROP TRANSFORM ステートメントの完全な構文については、 SQL 解説書 を参照してください。除去できるのはユーザー定義の変形体だけです。組み込み transform またはそれに関連したグループ定義を除去することはできません。
ALTER VIEW ステートメントは、効力範囲を追加するよう参照タイプ列を更新することによって、既存の視点を変更します。視点に対してこれ以外の変更を行うには、視点を除去してから再作成する必要があります。
視点を更新する際には、効力範囲がまだ定義されていない既存の参照タイプ列に効力範囲を追加する必要があります。さらに、その参照タイプはスーパー視点から継承されたものであってはなりません。
ALTER VIEW ステートメントの列名のデータ・タイプは、 REF (タイプ付き表名またはタイプ付き視点名のタイプ) でなければなりません。
ALTER VIEW ステートメントの詳細については、 SQL 解説書 を参照してください。
EMP_VIEW の除去方法の例は次のとおりです。
DROP VIEW EMP_VIEW;
除去された視点に従属するすべての視点は、作動不能になります。作動不能視点の詳細については、 管理の手引き の『作動不能視点の回復』セクションを参照してください。
表や索引などの他のデータベース・オブジェクトは、パッケージやキャッシュされた動的ステートメントが無効としてマークされている場合でも、影響を受けることはありません。詳細については、 管理の手引き の『ステートメントの従属関係』セクションを参照してください。
表階層の場合と同様に、次の例のように階層のルート視点を指定して、 1 つのステートメントの中の視点階層全体を除去することができます。
DROP VIEW HIERARCHY VPerson;
視点の除去と作成の詳細については、 SQL 解説書 を参照してください。
必要な SELECT 権限を持っている場合は、非タイプ付き表を照会するのと同じ方法で、タイプ付き表を照会することができます。照会は、SELECT およびそのすべての副表のターゲットの修飾行から、要求した列を戻します。たとえば、Person 表階層の中のデータに対する次の照会は、すべての人物の名前と年齢 (つまり、Person 表とその副表のすべての行) を戻します。列の 1 つが構造型列である場合の、同様の照会の作成方法の詳細については、 構造型値の検索と変更を参照してください。
SELECT Name, Age FROM Person;
この照会の結果は、次のようになります。
NAME AGE -------------------- ----------- Andrew 29 Bob 30 Cathy 25 Dennis 26 Eva 31 Franky 28 Gordon 19 Helen 20 Iris 35 Christina 10 Ken 55 Leo 35 Brian 7 Susan 39
効力範囲が指定された参照がある場合は、逆参照操作 を使用して、逆参照操作を使用しなければ外部結合または相関副照会が必要となる照会を発行できます。 BusinessUnit 表に効力範囲が指定されている、 Employee 表および Employee 表の副表の Dept 属性について考慮してみましょう。次の例は、データベース内のすべての従業員の名前、給与、および部門名、またはヌル値を使用できるところではヌル値を戻します。この照会は、Employee 表および Employee の副表の中のすべての行の値を戻すということです。相関副照会または外部結合を使用して、同様の照会を作成することができます。しかし、逆参照演算子 (->) を使用して、 Employee 表および副表の中の参照列から BusinessUnit 表へパスを渡って、 BusinessUnit 表の Name 列から結果を戻す方が簡単です。
単純な逆参照操作の形式は、次のとおりです。
効力範囲が指定された参照式 -> ターゲット・タイプ付き表の中の列
次の例は、BusinessUnit 表から Name 列を獲得するために、逆参照演算子を使用しています。
SELECT Name, Salary, Dept->Name FROM Employee
この照会の結果は、次のようになります。
NAME SALARY NAME -------------------- ----------- -------------------- Dennis 30000 Toy Eva 45000 Shoe Franky 39000 Shoe Iris 55000 Toy Christina 85000 Toy Ken 105000 Shoe Leo 92000 Shoe Brian 112000 Toy Susan 37000.48 ---
自己参照している参照を逆参照することもできます。 図 13 の部品表について考慮してみましょう。次の照会は、部品の製造業社の使用箇所を指定して、翼に直接使用されている部品をリストします。
SELECT P.Descript, P.Supplied_by ->Location FROM Parts P WHERE P.Used_in -> Descript='Wing';
DEREF 組み込み関数を使用して、構造化されたオブジェクト全体を 1 つの値として獲得するために、参照を逆参照することもできます。 DEREF の単純な形式は、次のとおりです。
DEREF (効力範囲が指定された参照式)
通常 DEREF は、TYPE_NAME などの他の組み込み関数のコンテキストの中で使用されるか、アプリケーションに結び付けるために構造化されたオブジェクト全体を獲得するために使用されます。
DEREF 関数は、TYPE_NAME、TYPE_ID、または TYPE_SCHEMA 組み込み関数の一部として、呼び出されることがよくあります。これらの関数の目的は、式の動的タイプの名前、内部 ID、およびスキーマ名を戻すことです。たとえば、次の例は、 Responsible という属性を持つ Project タイプ付き表を作成します。
CREATE TYPE Project_t AS (Projid INT, Responsible REF(Employee_t)) MODE DB2SQL; CREATE TABLE Project OF Project_t (REF IS Oid USER GENERATED, Responsible WITH OPTIONS SCOPE Employee);
Responsible 属性は、 Employee 表への参照として定義されているので、管理者、設計者、および従業員のインスタンスを参照できます。アプリケーションがすべての行の動的タイプの名前を知る必要がある場合は、次のような照会を使用します。
SELECT Projid, Responsible->Name, TYPE_NAME(DEREF(Responsible)) FROM PROJECT;
前述の例は、Employee 表の Name の値を戻すために逆参照演算子を使用し、 Employee_t というインスタンスの動的タイプを戻すために DEREF 関数を呼び出しています。
このセクションで説明した組み込み関数の詳細については、 SQL 解説書 を参照してください。
許可要件: DEREF 関数を使用するには、表階層の参照先部分にあるすべての表および副表に対する SELECT 権限がなければなりません。たとえば上記の照会では、Employee、Manager、および Architect タイプ付き表に対する SELECT 特権が必要です。
サブタイプのオブジェクトではなく特定のタイプのオブジェクトだけを戻す照会を行うには、 ONLY キーワードを使用します。たとえば、次の照会は、設計者でも管理者でもない従業員の名前だけを戻します。
SELECT Name FROM ONLY(Employee);
前述の照会は、次の結果を戻します。
NAME -------------------- Dennis Eva Franky Susan
データの機密保護を確保するために、 ONLY を使用する時には、Employee のすべての副表に対する SELECT 特権が必要です。
UPDATE または DELETE ステートメントの操作を指定された表だけで行えるようにするために、 ONLY 文節を使用することもできます。つまり、ONLY 文節は、指定された表の副表では操作が行われないようにするのです。
SQL ステートメントによって戻されるまたは影響を受ける行をより汎用的に制限する方法として、タイプ述部を使用する方法があります。タイプ述部を使用すれば、式の動的タイプを指定されたタイプ (1 つまたは複数) と比較することができます。タイプ述部の単純なバージョンは、次のとおりです。
<expression> IS OF (<type_name>[, ...])
ここで、expression は構造型のインスタンスを戻す SQL 式を表し、 type_name はインスタンスが比較される構造型 (1 つまたは複数) を表します。
たとえば、次の照会は、35 歳以上で管理者または設計者である人物を戻します。
SELECT Name FROM Employee E WHERE E.Age > 35 AND DEREF(E.Oid) IS OF (Manager_t, Architect_t);
前述の照会は、次の結果を戻します。
NAME -------------------- Ken
DB2 が構造型の行の値を戻す時には、アプリケーションは、その特定のインスタンスに入っているまたは入っている可能性のある属性を、必ずしも知っているわけではありません。たとえば、人物を戻す時には、その人物は人物の属性だけを持っているかもしれず、従業員、管理者、または人物のサブタイプという属性を持っているかもしれません。アプリケーションが、1 つの SQL 照会で可能性のあるすべての属性を獲得する必要がある場合は、表参照でキーワード OUTER を使用します。
OUTER (table-name) および OUTER (view-name) は、表または視点の列と、もしあればそれに続く表または視点の副表によって導入される追加の列で構成される仮想表を戻します。追加の列は、副表階層を順番に降下しながら表の右側に追加されていきます。共通の親を持つ副表は、それぞれのタイプが作成された順序で処理されます。行には、table-name のすべての行と、 table-name の副表のすべての追加の行が入ります。ヌル値は、行の副表の中にない列について戻されます。
たとえば、平均以上の給与を取得する傾向のある人物についての情報を見たい時に、 OUTER を使用できます。次の照会は、給与 Salary が高額であるか、平均成績点 GPA が高い Person 表階層の情報を戻します。
SELECT * FROM OUTER(Person) P WHERE P.Salary > 200000 OR P.GPA > 3.95 ;
OUTER(Person) を使用すれば、 OUTER(Person) を使用しない場合は Person 照会で参照できないサブタイプ属性を参照することができます。
OUTER を使用するには、参照先の表のすべての副表または視点に対する SELECT 特権が必要です。なぜなら、参照先の表のすべての副表または視点の情報はすべて、 OUTER を使用することによって公開されるからです。
アプリケーションが、高給の人物の属性だけでなく、その個人の最も特異なタイプが何であるかも知る必要があるとします。これは、次のようにして、オブジェクトのオブジェクト識別子を TYPE_NAME 組み込み関数に渡し、 OUTER 照会と結合することによって行うことができます。
SELECT TYPE_NAME(DEREF(P.Oid)), P.* FROM OUTER(Person) P WHERE P.Salary > 200000 OR P.GPA > 3.95 ;
Person タイプ付き表の Address 列には構造型が入っているので、追加の関数を定義し、その列からデータを戻すために追加の SQL を発行する必要があります。構造型の列からデータを戻す方法の詳細については、 構造型値の検索と変更を参照してください。これらの追加のステップを実行したとして、前述の照会は次の出力を戻します。 Additional Attributes には、GPA と Salary が入ります。
1 OID NAME Additional Attributes ------------------ ------------- -------------------- ... PERSON_T a Andrew ... PERSON_T b Bob ... PERSON_T c Cathy ... EMPLOYEE_T d Dennis ... EMPLOYEE_T e Eva ... EMPLOYEE_T f Franky ... MANAGER_T i Iris ... ARCHITECT_T l Leo ... EMPLOYEE_T s Susan ...
DB2 が固有のオブジェクト識別子を自動的に生成するようにするには、 GENERATE_UNIQUE 関数を使用します。 GENERATE_UNIQUE は CHAR (13) FOR BIT DATA 値を戻すので、そのタイプの値を CREATE TYPE ステートメントの REF USING 文節に入れることができるようにしておきます。 VARCHAR (16) FOR BIT DATA というデフォルトは、この目的にかなっています。たとえば、次のようにデフォルトの表示タイプを使用して、つまり、REF USING 文節を指定せずに、 BusinessUnit_t タイプが作成されたとします。
CREATE TYPE BusinessUnit_t AS (Name VARCHAR(20), Headcount INT) MODE DB2SQL;
タイプ付き表定義は、次のとおりです。
CREATE TABLE BusinessUnit OF BusinessUnit_t (REF IS Oid USER GENERATED);
USER GENERATED 文節は必ず指定する必要があります。
タイプ付き表に行を挿入する INSERT ステートメントは、次のようになります。
INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(GENERATE_UNIQUE( )), 'Toy' 15);
Toy 部門に所属する従業員を挿入するには、次のようなステートメントを使用します。これは、BusinessUnit 表からオブジェクト識別子列の値を検索するために副選択を発行し、その値を BusinessUnit_t タイプにキャストして、その値を Dept 列に挿入します。
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, BusinessUnit_t(SELECT Oid FROM BusinessUnit WHERE Name='Toy'));
オブジェクト識別子を外部キーの中の親表のキー列として使用する場合は、最初に、オブジェクト識別子列に対する明示的で固有な制約または基本キー制約を追加するよう、タイプ付き表を更新する必要があります。たとえば、図 14 に示されているような、従業員に対する自己参照関係を作成するとします。この自己参照関係では、それぞれの従業員の管理者は必ず従業員表の中の従業員として存在している必要があります。
![]() |
自己参照関係を作成するには、次のステップを実行します。
ステップ 1. | タイプを作成する。
CREATE TYPE Empl_t AS (Name VARCHAR(10), Mgr REF(Empl_t)) MODE DB2SQL; |
ステップ 2. | タイプ付き表を作成する。
CREATE TABLE Empl OF Empl_t (REF IS Oid USER GENERATED); |
ステップ 3. | Oid 列に対する基本制約または固有制約を追加する。
ALTER TABLE Empl ADD CONSTRAINT pk1 UNIQUE(Oid); |
ステップ 4. | 外部キー制約を追加する。
ALTER TABLE Empl ADD CONSTRAINT fk1 FOREIGN KEY(Mgr) REFERENCES Empl (Oid);
|