SQL 解説書

CREATE VIEW

CREATE VIEW ステートメントは、1 つまたは複数の表、 視点、またはニックネームに基づく視点を作成します。

呼び出し

このステートメントは、アプリケーション・プログラムに組み込むか、 あるいは動的 SQL ステートメントの使用によって発行することができます。 このステートメントは、動的に準備可能な実行可能ステートメントです。 しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、 ステートメントを動的に準備することはできません (SQLSTATE 42509)。

許可

このステートメントの許可 ID には、以下の特権が少なくとも 1 つ含まれている必要があります。

グループ特権は、CREATE VIEW ステートメントで指定された表や視点に対しては考慮されません。

特権は、連合データベースのニックネームに視点を定義するときには考慮されません。 このニックネームで示されている表または視点のデータ・ソースの許可要件は、照会の処理時に適用されます。 ステートメントの許可 ID は、別のリモート許可 ID へマップできます。

視点の定義者が SYSADM 権限を持つために、 視点の作成しかできない場合、視点作成のため、 その定義者には明示的な DBADM 権限が付与されます。

構文

>>-CREATE--+-----------+---VIEW--view-name---------------------->
           '-FEDERATED-'
 
>-----+---------------------------------------------+--AS------->
      |    .-,--------------.                       |
      |    V                |                       |
      +-(-----column-name---+---)-------------------+
      '-OF--type-name--+-| root-view-definition |-+-'
                       '-| subview-definition |---'
 
>----+---------------------------------------+--fullselect------>
     |       .-,--------------------------.  |
     |       V                            |  |
     '-WITH-----common-table-expression---+--'
 
>-----+--------------------------------------+-----------------><
      |       .-CASCADED--.                  |
      '-WITH--+-----------+---CHECK OPTION---'
              '-LOCAL-----'
 
root-view-definition
 
|---MODE DB2SQL------------------------------------------------->
 
>----(--| oid-column |--+----------------------+---)------------|
                        '-,--| with-options |--'
 
subview-definition
 
|---MODE DB2SQL--|  under-clause |------------------------------>
 
>-----+--------------------------+---+---------+----------------|
      '-(--| with-options |--)---'   '-EXTEND--'
 
oid-column
 
|---REF IS--oid-column-name--USER GENERATED----+------------+---|
                                               '-UNCHECKED--'
 
with-options
 
       .-,----------------------------------------------------------------.
       |                            .-,--------------------------------.  |
       V                            V                                  |  |
|--------column-name--WITH OPTIONS----+-SCOPE--+-typed-table-name-+-+--+--+--->
                                      |        '-typed-view-name--' |
                                      '-READ ONLY-------------------'
 
>---------------------------------------------------------------|
 
 under-clause
 
|---UNDER--superview-name--INHERIT SELECT PRIVILEGES------------|
 
注:common-table-expression (共通表式) と fullselect (全選択) の構文については、 照会 を参照してください。

説明

FEDERATED
作成されている視点がニックネームまたは OLEDB 表関数を参照することを示します。 この FEDERATED キーワードが指定されていない状態で、 OLEDB 表関数やニックネームを全選択して直接または間接的に参照すると、 CREATE VIEW ステートメントを送信する際に警告 (SQLSTATE 01639) が出されます。 ただし、警告が出されても視点は作成されます。

逆に、この FEDERATED キーワードが指定されているのに、 OLEDB 表関数やニックネームを全選択して直接または間接的に参照しないと、 CREATE VIEW ステートメントを送信する際にエラー (SQLSTATE 429BA) が発生します。 この場合、視点は作成されません。

view-name
視点の名前を指定します。 暗黙または明示の修飾子を含む名前は、 カタログに記述されている表、視点、ニックネーム、 または別名を指定するものであってはなりません。 修飾子は、SYSIBM、SYSCAT、SYSFUN、または SYSSTAT であってはなりません (SQLSTATE 42939)。

この名前は、作動不能な視点の名前と同じであっても構いません (作動不能視点を参照)。 このような場合、作動不能な視点は、 CREATE VIEW ステートメントに指定した新しい視点によって置き換えられます。 作動不能な視点が置き換えられると、ユーザーに警告 (SQLSTATE 01595) が出されます。 バインド・オプション SQLWARN を NO に設定してアプリケーションがバインドされた場合は、 警告は戻されません。

column-name
視点の列の名前を指定します。 列名のリストを指定する場合、リスト中の列の名前の数は、 全選択の結果表の列の数と同じ数でなければなりません。 各 column-name (列名) は、固有で、しかも非修飾でなければなりません。 列名のリストの指定がない場合、視点の列は、全選択の結果表の列名を継承します。

全選択の結果表の列名が重複している場合、または無名の列がある場合には、 列名のリストを指定する必要があります (SQLSTATE 42908)。 無名列とは、定数、関数、式、またはセット演算から派生した列で、 選択リストの AS 文節によって名前が指定されていない列を指します。

OF type-name
視点の列が type-name で指定される構造タイプの属性に基づいていることを指定します。 type-name の指定にスキーマ名が含まれていない場合、 そのタイプ名は SQL パスのスキーマから探索することによって決まります (このパスは、 静的 SQL の場合は FUNCPATH 前処理オプションによって、 動的 SQL の場合は CURRENT PATH レジスターによって定義されます)。 ここに指定するタイプ名は、既存のユーザー定義タイプ名で (SQLSTATE 42704)、 かつインスタンス化の可能な構造タイプでなければなりません (SQLSTATE 428DP)。

MODE DB2SQL
この文節は、タイプ付き視点のモードを指定するために使用されます。 これは、現在サポートされている唯一有効なモードです。

UNDER superview-name
この視点が superview-name の副視点であることを指定します。 スーパー視点は既存の視点でなければならず (SQLSTATE 42704)、 この視点は type-name のすぐ上位にある上位タイプである 構造タイプで定義する必要があります (SQLSTATE 428DB)。 view-name および superview-name のスキーマ名は、 同一のものでなければなりません (SQLSTATE 428DQ)。 superview-name で指定される視点には、 type-name で既に定義された既存の副視点を含めることはできません (SQLSTATE 42742)。

表の列には、スーパー視点のオブジェクト ID 列が含まれています。 オブジェクト識別列のタイプは REF(type-name) に変更されており、 type-name の属性に基づく列が続きます (ここでいうタイプには、 上位タイプの属性も含まれていることを念頭に置いてください)。

INHERIT SELECT PRIVILEGES
スーパー視点に対して SELECT 特権を持つユーザーやグループはすべて、 新しく作成した副視点に対しても同様の特権を付与されます。 この特権は、副視点定義者によって付与されたものとみなされます。

OID-column
タイプ付き視点のオブジェクト ID 列を定義します。

REF IS OID-column-name USER GENERATED
オブジェクト ID 列 (OID) を視点の最初の列として定義することを指定します。 視点階層のルート視点には、OID が必須です (SQLSTATE 428DX)。 この視点は副視点以外のタイプ付き視点 (OF 文節が必須) でなければなりません (SQLSTATE 42613)。 この列の名前は OID-column-name という形式で定義されますが、 構造タイプ type-name のいかなる属性の名前と同一であることはできません (SQLSTATE 42711)。 全選択で指定した最初の列は、 REF(type-name) というタイプでなければなりません (キャストして適切なタイプにする必要があるかもしれません)。 UNCHECKED を指定しない場合、索引 (基本キー、固有制約、固有索引、 または OID 列) を使って固有なものにできる列 (ヌル可能ではない) に基づいている必要があります。 この列をオブジェクト ID 列 または OID 列 といいます。 USER GENERATED というキーワードは、 行を挿入する際にユーザーが OID 列の初期値を提供しなければならないことを指しています。 行を挿入した後は、OID 列を更新することはできません (SQLSTATE 42808)。

UNCHECKED
固有であることをシステムが証明できない場合でも、 タイプ付き視点定義のオブジェクト ID の列を固有であるとみなすように定義します。 この属性は、 次のようなタイプ付き視点階層に定義されている表または視点での使用を想定しています。 すなわち、そのデータが固有性規則に準拠しているものの、 システムが固有性を証明できる規則には準拠していないことをユーザーが認識しているという場合です。 UNCHECKED オプションは、 複数の階層や従来型の表または視点にまでわたっている視点の階層に必須のオプションです。 UNCHECKED を指定する場合、ユーザーの責任で視点の各行に固有の OID が確実にあるようにします。 ユーザーがこの特性を保証しなかったために、視点に重複した OID 値が入ってしまうと、 固有でない OID 値のどれかを含むパスの式または DEREF 演算子はエラーになります (SQLSTATE 21000)。

with-options
タイプ付き視点の列に適用される追加オプションを定義します。

column-name WITH OPTIONS
追加オプションを指定する列の名前を指定します。 column-name は、 視点の type-name に定義されている (継承されてはいない) 属性名に対応していなければなりません。 この列は参照タイプである必要があります (SQLSTATE 42842)。 また、すでにスーパー視点に存在する列に対応することはできません (SQLSTATE 428DJ)。 列名は、ステートメント内の 1 つの WITH OPTIONS SCOPE 文節に 1 回しか指定できません (SQLSTATE 42613)。

SCOPE
参照タイプ列の効力範囲を指定します。 参照解除演算子の左オペランド、または DEREF 関数の引き数として使用する列には、 すべて効力範囲を指定しなければなりません。

ターゲット表またはターゲット視点が定義されるように、 後続する ALTER VIEW ステートメント (効力範囲が継承されていない場合) まで、 参照タイプ列の指定を遅らせることができます (通常は、 相互参照表および相互参照視点の場合に適用する)。 視点の参照タイプ列で効力範囲が指定されていないのに、 基礎表または視点列の効力範囲が指定された場合、 基礎列の効力範囲は参照タイプ列によって継承されます。 基礎表または視点の列に効力範囲がない場合には、この列に効力範囲は指定されません。 読み取り専用カーソルと更新可能カーソルの詳細については、 を参照してください。

typed-table-name
タイプ付き表の名前。 この表は既に存在しているものか、 作成する表と同じ名前のものでなければなりません (SQLSTATE 42704)。 column-name のデータ・タイプは REF(S) でなければなりません。 Styped-table-name のタイプを表します (SQLSTATE 428DM)。 値が typed-table-name の既存行を実際に参照していることを確認するための、 column-name の既存値の検査は行われません。

typed-view-name
タイプ付き視点の名前。 この視点は既に存在しているものか、 作成する視点と同じ名前のものでなければなりません (SQLSTATE 42704)。 column-name のデータ・タイプは REF(S ) でなければなりません。 Styped-view-name のタイプを表します (SQLSTATE 428DM)。 値が typed-view-name の既存行を実際に参照していることを確認するための、 column-name の既存値の検査は行われません。

READ ONLY
列を読み取り専用列として指定します。 このオプションは、列を読み取り専用に指定し、副視点定義により、 暗黙的に読み取り専用になっている同じ列の式を指定するために使用します。

AS
視点定義を指定します。

WITH common-table-expression
後に続く全選択で使用する共通表式 (common-table-expression) を定義します。 タイプ付き視点を定義するときに、共通表式は指定できません。 共通表式 を参照してください。

fullselect
視点を定義します。 どのような場合でも、視点は、SELECT が実行されたとしたらその結果となるような行で構成されます。 fullselect でホスト変数、パラメーター・マーカー、 または宣言された一時表を参照することはできません。 ただし、パラメーター化された視点を SQL 表関数として作成することは可能です。 CREATE FUNCTION (SQL スカラー、表、または行)を参照してください。

タイプ付き視点および副視点の場合 : fullselect は、以下の規則に準拠していなければなりません。 そうでない場合、エラーが戻されます (SQLSTATE 428EA 何も指定されていない場合)。

視点および副視点の階層の場合 : BR1 および BR2 が、 階層内の視点定義に現れる分岐になるようにします。 T1 を BR1 の基礎表または視点に、T2 を BR2 の基礎表または視点にします。 この場合は以下のようになります。

EXTEND AS を使って定義されたタイプ付きの視点の場合 : 副視点の本体内の各分岐について :

AS (EXTEND なし) を使って定義されたタイプ付き副視点の場合:

WITH CHECK OPTION
視点によって挿入または更新される行すべてが、 視点の定義に従っていなければならないという制約を指定します。 視点の定義に従わない行とは、視点の探索条件を満たしていない行です。

WITH CHECK OPTION は、視点が読み取り専用の場合には指定できません (SQLSTATE 42813)。 挿入が許されていない更新可能な視点に対して WITH CHECK OPTION を指定すると、 制約は更新にのみ適用されます。

視点が、NODENUMBER または PARTITION 関数、非 deterministic 関数、 または外部アクションを伴う関数を参照する場合、 WITH CHECK OPTION を指定することはできません (SQLSTATE 42997)。

WITH CHECK OPTION は、 視点がタイプ付き視点の場合には指定できません (SQLSTATE 42997)。

WITH CHECK OPTION は、ニックネームが視点の更新目標である場合には指定できません。

WITH CHECK OPTION を省略すると、 視点を使用するどのような挿入操作または更新操作の検査においても、 視点の定義は使用されません。 ただし、視点が WITH CHECK OPTION を含む他の視点に直接または間接的に従属する場合には、 挿入操作または更新操作の過程で、何らかの検査が行われる場合があります。 この場合、視点の定義が使用されるわけではないため、 視点の定義に従っていない視点を介して、 行が挿入または更新される可能性があります。

CASCADED
視点 V に対する WITH CASCADED CHECK OPTION 制約は、 V が従属するいずれかの更新可能視点から、 制約としての探索条件を V が継承することを意味します。 さらに、V に従属するすべての更新可能視点も、 このような制約の対象になります。 したがって、V の探索条件と、 V が従属している各視点の探索条件との AND を取ったものが、 V あるいは V に従属するいずれかの視点の挿入または更新に対して 適用される制約となります。

LOCAL
視点 V に対する WITH LOCAL CHECK OPTION 制約は、 V の探索条件が、 V または V に従属するいずれかの視点の挿入あるいは更新に対する 制約として適用されることを意味しています。

次の例は、CASCADED と LOCAL の差異を示しています。 次のような更新可能な視点を想定します (Y は、一覧表の見出しに示しているように、 LOCAL または CASCADED に置き換えます)。

  V1 : 表 T に基づいて定義される視点
  V2 : V1 に基づいて WITH Y CHECK OPTION として定義される視点
  V3 : V2 に基づいて定義される視点
  V4 : V3 に基づいて WITH Y CHECK OPTION として定義される視点
  V5 : V4 に基づいて定義される視点

次の表は、挿入または更新された行を検査するのに使われる探索条件を示しています。



Y が LOCAL の場合 Y が CASCADED の場合
V1 での検査条件: 対象となる視点なし 対象となる視点なし
V2 での検査条件: V2 V2、V1
V3 での検査条件: V2 V2、V1
V4 での検査条件: V2、V4 V4、V3、V2、V1
V5 での検査条件: V2、V4 V4、V3、V2、V1

また、次のような更新可能視点についても考えてみます。 これは、デフォルトの CASCADED オプションを使用した場合の WITH CHECK OPTION の効果を示しています。

   CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > 10
   CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH CHECK OPTION
   CREATE VIEW V3 AS SELECT COL1 FROM V2 WHERE COL1 < 100

次の INSERT ステートメントは V1 を使用するものですが、 V1 に WITH CHECK OPTION が指定されておらず、 また V1 が、WITH CHECK OPTION の指定された他のどの視点にも従属していないため、 このステートメントは成功します。

   INSERT INTO V1 VALUES(5)

次の INSERT ステートメントは V2 を使用するものですが、 V2 に WITH CHECK OPTION が指定されており、 挿入 (INSERT) によって V2 の定義に従っていない行が作成されるため、 このステートメントはエラーになります。

   INSERT INTO  V2 VALUES(5)

次の INSERT ステートメントでは V3 を使用しています。 V3 に WITH CHECK OPTION は指定されていませんが、 これは、WITH CHECK OPTION の指定された V2 の従属であるため、 エラーになります (SQLSTATE 44000)。

   INSERT INTO V3 VALUES(5)

次の INSERT ステートメントも、V3 を使用しています。 これは V3 の定義に準拠していませんが、 成功します (V3 には WITH CHECK OPTION が指定されていません)。 これは、WITH CHECK OPTION の指定された V2 の定義に従ったものになっています。

   INSERT INTO V3 VALUES(200)

例 1: PROJECT 表に基づく視点 MA_PROJ を作成します。この視点には、 文字 'MA' で始まるプロジェクト番号 (PROJNO) を持つ行だけを入れます。

  CREATE VIEW MA_PROJ  AS SELECT * 
     FROM PROJECT
       WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

例 2: 例 1 と同様に視点を作成します。ただし、プロジェクト番号 (PROJNO)、 プロジェクト名 (PROJNAME)、およびプロジェクトの責任者 (RESPEMP) の列だけを選択します。

  CREATE VIEW MA_PROJ
   AS SELECTPROJNO, PROJNAME, RESPEMP
   FROM PROJECT
   WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

例 3: 例 2 と同様の視点を作成します。 ただし、視点の中でプロジェクトの責任者の列を IN_CHARGE と呼びます。

  CREATE VIEW MA_PROJ 
  (PROJNO, PROJNAME, IN_CHARGE) 
  AS SELECTPROJNO, PROJNAME, RESPEMP 
   FROM PROJECT
   WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

注: 列名のいずれか 1 つだけを変更する場合でも、 視点の 3 つの列すべての名前を MA_PROJ の後の括弧内に指定する必要があります。

例 4: PRJ_LEADER という名前の視点を作成します。この視点には、 PROJECT 表の最初の 4 つの列 (PROJNO、 PROJNAME、 DEPTNO、 RESPEMP) と、 プロジェクトの責任者 (RESPEMP) のラストネーム (LASTNAME) を入れます。 ラストネームは、EMPLOYEE 表の EMPNO を PROJECT 表の RESPEMP と突き合わせることによって、 EMPLOYEE 表から入手します。

  CREATE VIEW PRJ_LEADER
   AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME
   FROM PROJECT, EMPLOYEE
   WHERE RESPEMP = EMPNO

例 5: 例 4 と同様の視点を作成します。ただし、列 PROJNO、 PROJNAME、 DEPTNO、 RESPEMP、 および LASTNAME に加えて、担当者の給与総額 (SALARY + BONUS + COMM) を入れます。 また、平均スタッフ数 (PRSTAFF) が 1 より大きいプロジェクトだけを選択します。

CREATE VIEW PRJ_LEADER
  (PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, TOTAL_PAY )
  AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, SALARY+BONUS+COMM
          FROM PROJECT, EMPLOYEE
          WHERE RESPEMP = EMPNO 
          AND PRSTAFF > 1

全選択に、TOTAL_PAY として式 SALARY+BONUS+COMM を指定することによって、 次のように、列名リストの指定を省略することができます。

   CREATE VIEW PRJ_LEADER
     AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP,
                          LASTNAME, SALARY+BONUS+COMM AS TOTAL_PAY
          FROM PROJECT, EMPLOYEE
          WHERE RESPEMP = EMPNO AND PRSTAFF > 1

例 6: 次の図に示す表と視点があると想定します。

図 12. 例 6 の表と視点

CREATE VIEW、表と視点の例

ユーザー ZORPIE (DBADM または SYSADM のいずれの権限も持たない) は、 各オブジェクトの下の括弧内に示している権限を与えられています。

  1. 次のステートメントによって作成する視点に対して、ZORPIE は CONTROL 特権を獲得します。
      CREATE VIEW VA AS SELECT * FROM S1.V1
    

    これは、ZORPIE が S1.V1 に対して CONTROL 権限を持っているからで

    す。 86 基礎となる基礎表に対して、どのような特権が与えられているかは関係ありません。

  2. ZORPIE は、次のような視点の作成は許されません。
      CREATE VIEW VB AS SELECT * FROM S1.V2
    

    これは、ZORPIE には、S1.V2 に対する CONTROL も SELECT も与えられていないからです。 基礎となる基礎表 (S1.T2) に対して CONTROL を与えられているかどうかは、関係ありません。

  3. 次のステートメントによって作成する視点に対して、ZORPIE は CONTROL 特権を獲得します。
      CREATE VIEW VC (COLA, COLB, COLC, COLD)
        AS SELECT * FROM S1.V1, S1.T2
        WHERE COLA = COLC
    

    これは、ZORPIE.VC の全選択では、視点 S1.V1 と S1.T2 を参照しており、 ZORPIE はその両方に対する CONTROL を持っているからです。 視点 VC は読み取り専用で、INSERT、UPDATE、 または DELETE のいずれの権限も ZORPIE には与えられないことに注意してください。

  4. 次のステートメントによって作成する視点に対して、ZORPIE は SELECT 特権を入手します。
      CREATE VIEW VD (COLA,COLB, COLE, COLF)
        AS SELECT * FROM S1.V1, S1.V3
        WHERE COLA = COLE
    

    これは、ZORPIE.VD の全選択で 2 つの視点 S1.V1 および S1.V3 を参照しており、 ZORPIE はその 1 つに対しては SELECT 特権を、 もう 1 つに対しては CONTROL 特権を与えられているからです。 ZORPIE.VD に対する特権として、 2 つの特権のうち低い方の特権である SELECT が ZORPIE に与えられます。

  5. 以下の視点定義では、ZORPIE は視点 VE に対して WITH GRANT OPTION を伴う INSERT、 UPDATE および DELETE の各特権と、SELECT 特権を与えられます。
      CREATE VIEW VE 
      AS SELECT * FROM S1.V1
      WHERE COLA > ANY 
             (SELECT COLE FROM S1.V3)
    

    ZORPIE の VE に対する特権は、主として S1.V1 に対する特権によって決定されます。 S1.V3 は副照会で参照されているだけなので、 視点 VE の作成には S1.V3 に対する SELECT 特権だけが必要です。 視点の定義者は、視点の定義で参照されているすべてのオブジェクトに対して CONTROL を持っている場合のみ、 その視点に対して CONTROL を入手します。 ZORPIE は S1.V3 に対する CONTROL を持っていないので、 VE に対する CONTROL は与えられません。


脚注:

86
DBADM または SYSADM のいずれかの権限を持つユーザーが、 S1.V1 に対する CONTROL 権限を ZORPIE に与えている必要があります。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]