ビューの定義と使用

ビューの作成

以下のような方法で、ビューを定義できます。

• SQL 文の CREATE VIEW コマンドを使用します (DDL スクリプトで、または JDBC か ODBC 経由)。

• 管理ポータルの [ビュー作成] インタフェースを使用します。

ビュー名は、修飾、未修飾のどちらでもかまいません。未修飾のビュー名は、単純な識別子です (MyView)。修飾されたビュー名は、2 つの単純な識別子であるスキーマ名とビュー名で構成され、ピリオドで区切られています (MySchema.MyView)。ビュー名とテーブル名は同じ名前付け規約に従い、未修飾の名前の場合は同じスキーマ名解析を実行します。ただし、同じスキーマ内のビューおよびテーブルには、同じ名前を指定できません。

$SYSTEM.SQL.Schema.ViewExists()Opens in a new tab メソッドを使用して、ビュー名が既に存在するかどうかを確認できます。このメソッドは、ビューを投影したクラス名も返します。$SYSTEM.SQL.Schema.TableExists()Opens in a new tab メソッドを使用して、テーブル名が既に存在するかどうかを確認できます。

ビューを利用することで、テーブルの制限されたサブセットを作成できます。以下の埋め込み SQL の例は、このビューでアクセスできる元のテーブルの行 (WHERE 節を使用) と列 (Sample.Person に列が 3 つ以上含まれることを想定) の両方を制限するビューを作成します。

   &sql(CREATE VIEW Sample.VSrStaff 
        AS SELECT Name AS Vname,Age AS Vage
        FROM Sample.Person WHERE Age>75)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "Serious SQL problem: ",SQLCODE," ",%msg,! }

SELECT * FROM Sample.VSrStaff ORDER BY Vage

以下の埋め込み SQL の例は、SalesPeople テーブルに基づくビューを作成し、新しい計算値列 TotalPay を作成します。

   &sql(CREATE VIEW Sample.VSalesPay AS
    SELECT Name,(Salary + Commission) AS TotalPay
    FROM Sample.SalesPeople)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "Serious SQL problem: ",SQLCODE," ",%msg,! }

ビューの変更

管理ポータルの SQL インタフェースでは、既存のビューを選択して、そのビューの [カタログの詳細] を表示できます。[カタログの詳細] の [情報表示] オプションでは、テキスト表示 (ビューの SELECT 文) を編集するためのインタフェースを提供する [ビュー編集] リンクが表示されます。[チェックオプション] に [なし]、[読取専用]、[ローカル]、[カスケード] を選択するためのドロップダウン・リストも表示されます。

更新可能なビュー

更新可能なビューでは、INSERT、UPDATE、および DELETE 演算を実行できます。以下の条件に当てはまる場合のみ、ビューを更新できます。

• ビューのクエリの FROM 節にテーブル参照が 1 つだけ含まれる場合。このテーブル参照は、更新可能な元のテーブルか更新可能なビューと一致する必要があります。

• ビューのクエリの SELECT リスト内の値式が、すべて列参照である場合。

• ビューのクエリが、GROUP BY、HAVING、または SELECT DISTINCT を指定していない場合。

• ビューは、ビューとして投影されたクラス・クエリでない場合。

• ビューのクラスにクラス・パラメータ READONLY=1 が含まれていない場合 (ビュー定義に WITH READ ONLY 節が含まれる場合)。

WITH CHECK オプション

ビューで INSERT または UPDATE 操作を実行すると、本来のビュー・テーブルの一部ではない行が基本テーブルに表れることがあります。これを防ぐために、InterSystems SQL はビュー定義で WITH CHECK OPTION 節をサポートしています。この節は、更新可能なビューでのみ使用できます。

WITH CHECK OPTION 節は、更新可能なビューで INSERT または UPDATE 演算を実行するときに、ビュー定義の WHERE 節に対して得られた行を必ず検証するように指定し、挿入あるいは変更された行が本来のビュー・テーブルの一部になっていることを確認します。

例えば、以下の DDL 文は、GPA (grade point average : 成績の指標) が高い生徒のデータを含む、更新可能な GoodStudent ビューを定義します。

CREATE VIEW GoodStudent AS
    SELECT Name, GPA
      FROM Student
        WHERE GPA > 3.0
    WITH CHECK OPTION

ビューに WITH CHECK OPTION が含まれるので、GoodStudent ビューの中で GPA が 3.0 以下の値を持つ行を INSERT または UPDATE しようとしてもできません (それらの行は、“good student” を表すものではないからです)。

WITH CHECK OPTION には、以下の 2 種類があります。

• WITH LOCAL CHECK OPTION は、INSERT または UPDATE 文で指定されたビューの WHERE 節のみがチェックされることを示します。

• WITH CASCADED CHECK OPTION (および WITH CASCADE CHECK OPTION) は、INSERT または UPDATE 文で指定されたビューの WHERE 節とそのビューが元にしている ALL ビューがチェックされることを示します。これは、外観やそれらの定義内の WITH LOCAL CHECK OPTION 節の存在の有無には関係ありません。

WITH CHECK OPTION のみが指定されている場合は、既定は CASCADED です。

UPDATE または INSERT の実行時に、すべての既定値とトリガされた計算フィールドが、下位のテーブルのフィールドのために計算された後で、通常のテーブルの検証 (必須フィールド、データ型の検証、制約など) の前に、WITH CHECK OPTION 条件がチェックされます。

WITH CHECK OPTION の検証が済むと、INSERT または UPDATE が基本テーブル自体で実行されているかのように、INSERT または UPDATE 演算を続けます。すべての制約が確認され、トリガを発生します。

INSERT または UPDATE 文で %NOCHECK オプションが指定されている場合、WITH CHECK OPTION 検証は確認されません。

以下は、WITH CHECK OPTION 検証に関連する 2 つの SQLCODE 値です (INSERT または UPDATE は生成されたビュー・テーブルには存在しない行を生成することがあります)。

• SQLCODE -136 — ビューの WITH CHECK OPTION 検証が INSERT に失敗しました。

• SQLCODE -137 — ビューの WITH CHECK OPTION 検証が UPDATE に失敗しました。

読み取り専用ビュー

読み取り専用ビューでは、INSERT、UPDATE、および DELETE 演算を実行できません。更新可能なビューの基準を満たさないビューは、読み取り専用です。

ビュー定義で WITH READ ONLY 節を指定すると、そのビューは読み取り専用になります。

読み取り専用ビューに INSERT、UPDATE、DELETE 文をコンパイル、作成しようとすると、エラー・コード -35 が生成されます。

ビュー ID ： %VID

InterSystems IRIS は、ビューまたは FROM 節のサブクエリで返される各行に整数のビュー ID (%VID) を割り当てます。テーブルの行 ID 番号と同様、これらのビューの行 ID 番号は、システムによって割り当てられる一意の非 NULL、非ゼロ値で、変更することはできません。この %VID は、通常、ユーザには表示されず、明示的に指定した場合にのみ返されます。これはデータ型 INTEGER として返されます。%VID の値は連続した整数であるため、ビューが順序付きのデータを返す場合はとても有意義なものになります。TOP 節と組み合わせるときには、ビューでは ORDER BY 節以外は使用できません。以下の埋め込み SQL の例では、VSrStaff というビューを作成します。

   &sql(CREATE VIEW Sample.VSrStaff 
        AS SELECT TOP ALL Name AS Vname,Age AS Vage
        FROM Sample.Person WHERE Age>75
        ORDER BY Name)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "Serious SQL problem: ",SQLCODE," ",%msg,! }

以下の例は、VSrStaff ビューで定義されているすべてのデータを (SELECT * を使用して) 返します。また、各行のビュー ID も返すように指定しています。テーブルの行 ID とは異なり、ビューの行 ID はアスタリスク構文の使用時には表示されません。表示されるのは、SELECT で明示的に指定された場合のみです。

SELECT *,%VID AS ViewID FROM Sample.VSrStaff

%VID を使用すると、以下の例のように、ビューから SELECT で返される行の数をさらに制限することができます。

SELECT *,%VID AS ViewID FROM Sample.VSrStaff WHERE %VID BETWEEN 5 AND 10

このように、TOP の代わりに (または TOP に加えて) %VIDを使用すると、クエリで返される行の数を制限できるようになります。一般に、TOP 節は、データ・レコードの小さなサブセットを返すために使用されます。%VID は、小さなサブセットでレコードを返すことで、ほとんどのデータ・レコードまたはすべてのデータ・レコードを返すために使用されます。この機能は、特に Oracle のクエリを移植する場合に便利です (%VID は Oracle の ROWNUM に簡単に対応します)。ただし、以下に示すように TOP と比べると、%VID の使用には、いくつかのパフォーマンス上の制限があることを認識しておく必要があります。

• %VID は、最初の行を返す時間を最適化しません。TOP は、データの最初の行をできる限り短時間で返すように最適化します。%VID は、すべてのデータ・セットを可能な限り短時間で返すように最適化します。

• %VID は、ソートされた結果をクエリで指定した場合、制限付きソート (TOP で実行される特別な最適化) を実行しません。クエリは、まずデータ・セット全体をソートしてから、%VID を使用して、返されるデータ・セットを制限します。TOP はソートの前に適用されるため、SELECT は、制限された行のサブセットだけを対象とした制限付きソートを実行します。

最初の行を返す時間の最適化と制限付きソートの最適化を保持するには、TOP と %VID を組み合わせた FROM 節のサブクエリを使用します。FROM 節のサブクエリで、TOP ALL を使用するのではなく、TOP の値として上限 (ここでは 10) を指定します。WHERE 節で、%VID を使用して下限 (ここでは >4) を指定します。以下の例は、この方法を使用して前述のビューのクエリと同じ結果を返します。

SELECT *,%VID AS SubQueryID
   FROM (SELECT TOP 10 Name,Age 
         FROM Sample.Person
         WHERE Age > 75
         ORDER BY Name)
   WHERE %VID > 4

%PARALLEL キーワードを明示的に指定していても、%VID を指定するクエリに対して並列実行を行うことはできません。

ビューのプロパティのリスト

INFORMATION.SCHEMA.VIEWSOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのビューに関する情報を表示します。ビュー定義、ビューの所有者、ビューが作成されたときと最後に変更されたときのタイムスタンプなど、さまざまなプロパティが表示されます。これらのプロパティには、ビューが更新可能かどうか、更新可能な場合は、チェック・オプションを指定して定義されているかどうかも含まれます。

埋め込み SQL で指定する場合、INFORMATION.SCHEMA.VIEWSOpens in a new tab には、#include %occInclude マクロ・プリプロセッサ指示文が必要です。この指示文は、ダイナミック SQL の場合は不要です。

VIEWDEFINITIONOpens in a new tab プロパティ (SqlFieldName = VIEW_DEFINITION) は、現在のネームスペース内のすべてのビューのビュー・フィールド名およびビューのクエリ式を文字列として返します。以下に例を示します。

SELECT View_Definition FROM INFORMATION_SCHEMA.VIEWS

"(vName,vAge) SELECT Name,Age FROM Sample.Person WHERE Age > 21" のような文字列を返します。管理ポータルの SQL インタフェース [クエリ実行] から発行した場合、ビュー・フィールド・リストの間にスペースが 1 つ入れられ、空白と改行が削除されて、(必要に応じて) 内容が切り捨てられたことを示す省略記号 (...) が付加された文字列を返します。それ以外の場合、このクエリを発行すると、ビュー・フィールド・リストとクエリ・テキストの間に改行が入れられ、ビューのクエリ式に指定された空白が保持されて、ビューごとに最大 1048576 文字の文字列が返され、(必要に応じて) 内容が切り捨てられたことを示す省略記号 (...) が付加されます。

以下の例では、現在のネームスペース内のすべてのビューのビュー名 (Table_Name フィールド) および所有者名が返されます。

SELECT Table_Name,Owner FROM INFORMATION_SCHEMA.VIEWS

以下の例では、現在のネームスペース内のすべての非システム・ビューに関するすべての情報が返されます。

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE Owner != '_SYSTEM'

INFORMATION.SCHEMA.VIEWCOLUMNUSAGEOpens in a new tab 永続クラスは、現在のネームスペース内の各ビューの、ソース・テーブルのフィールド名を表示します。

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE View_Name='MyView'

管理ポータルの SQL インタフェースの [カタログの詳細] タブを使用して、単一ビューに対して INFORMATION.SCHEMA.VIEWSOpens in a new tab を指定した場合とほぼ同じ情報を表示できます。ビューの [カタログの詳細] には、各ビュー・フィールドの定義 (data type (データ型)、max length (最大長)、minval/maxval (最小/最大有効値) など) が表示され、INFORMATION.SCHEMA ビュー・クラスでは提供されない詳細も含まれています。[カタログの詳細] の [ビュー情報] の表示では、ビュー定義を編集するオプションも用意されています。

ビューの依存関係のリスト

INFORMATION.SCHEMA.VIEWTABLEUSAGEOpens in a new tab 永続クラスは、現在のネームスペースのビューのすべておよび依存テーブルを表示します。これを以下の例で示しています。

SELECT View_Schema,View_Name,Table_Schema,Table_Name FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

%Library.SQLCatalog.SQLViewDependsOn クラス・クエリを呼び出すことで、指定したビューが依存しているテーブルをリストできます。このクラス・クエリに、schema.viewname を指定します。viewname のみを指定すると、システム全体の既定のスキーマ名が使用されます。このクラス・クエリを実行するには、指定したビューに対する特権を呼び出し側が持っている必要があります。これを以下の例で示しています。

  SET statemt=##class(%SQL.Statement).%New()
  SET cqStatus=statemt.%PrepareClassQuery("%Library.SQLCatalog","SQLViewDependsOn")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rset=statemt.%Execute("vschema.vname")
  DO rset.%Display()

この SQLViewDependsOn クエリは、ビューが依存しているテーブルをリストします。テーブル・スキーマに続けてテーブル名がリストされます。呼び出し側に、ビューが依存するテーブルに応じた特権がない場合、そのテーブルとテーブルのスキーマは <NOT PRIVILEGED> としてリストされます。これにより、テーブルの特権を持たない呼び出し側は、ビューが依存しているテーブル数を判断できるようになります。ただし、その呼び出し側では、テーブルの名前は判断できません。