ビューの定義と使用

ビューの作成

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

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

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

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

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

ビューを利用することで、テーブルの制限されたサブセットを作成できます。例えば、以下のビューでは、ビューからアクセス可能な元のテーブルの行と列がどちらも制限されます。

   &sql(CREATE VIEW 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 "We have a problem: ",SQLCODE,! }

SELECT * FROM VSrStaff ORDER BY Vage

以下の例は、SalesPeople テーブルのすべての行を基にしたビューを作成し、新しい計算値列 TotalPay を作成します。

CREATE VIEW VSalesPay AS
    SELECT Name,(Salary + Commission) AS TotalPay
    FROM Sample.SalesPeople

更新可能なビュー

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

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

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

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

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

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

WITH CHECK オプション

ビューで INSERT または UPDATE の演算を実行すると、本来のビュー・テーブルの一部ではない行が基本テーブルに表れることがあります。これを防ぐために、Caché 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

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

   &sql(CREATE VIEW 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 "We have a problem: ",SQLCODE,! }

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

SELECT *,%VID AS ViewID FROM VSrStaff

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

SELECT *,%VID AS ViewID FROM 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

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

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

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

以下の例は、現在のネームスペース内のすべてのビューについて、ビュー名 (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 インタフェースの [カタログの詳細] タブを使用して、1 つのビューについて、INFORMATION.SCHEMA.VIEWSOpens in a new tab とほぼ同じ情報を表示できます。ビューの [カタログの詳細] には、ビューの各フィールドの定義 (データ型、最大長、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> としてリストされます。これにより、テーブルの特権を持たない呼び出し側は、ビューが依存しているテーブル数を判断できるようになります。ただし、その呼び出し側では、テーブルの名前は判断できません。