作成したSELECT文をDBに登録する「ビュー」：SQL実践講座（15）

　今回は、ビューの作成にチャレンジします。ビューは、いままでに作成してきたSELECT文に名前を付けてデータベース上に保存しておける仕組みです。実際に作成しながら、ビューがどのような場面で必要になるのかを確認していきましょう。

　今回はまず、Productsテーブルから「欠品のリスト」を作成してみましょう。

SELECT *
FROM Products
WHERE UnitsInStock = 0 and Discontinued = 0

画面1　欠品リストを作成するSELECT文を実行したところ。また、今回の解説よりSQL Server 2000を使用している（画面をクリックすると拡大表示します）

事例解説に使用するSQL Serverのバージョンについて

　今回からは、SQL Server 2000を使用して事例の解説を行っていきます。SQL Server 2000では、SQLクエリアナライザなどのツール類が大きく機能アップしていますが、基本的な機能はこれまでの連載で使用してきたSQL Server 7と変わりません。よって、現在SQL Server 7を使用している方でも、少なくとも例題のSQL文は問題なく実行できると思います。また、SQL Server 2000で拡張された機能に関しては、随時解説を挿入して紹介していきたいと思います。

　画面1は、SQL Server 2000のSQLクエリアナライザでの実行結果です。標準の起動状態では、左端に「オブジェクト・ブラウザ」が表示されます。オブジェクト・ブラウザでは、データベースに定義されているテーブルなどのオブジェクトがすべて参照できます。このため、これまでEnterprise Managerなどを使用してテーブル名やカラム名の確認をしていた作業を、すべてSQLクエリアナライザで実行できるようになりました。普段使用しないときは、ツールメニューから表示・非表示を制御できますので、非表示にしておきましょう。

　さて、SELECT文の実行結果ですが、これまでテキストで表示されていたデータが、グリッドで表示されるようになりました。このグリッドは、選択した個所をコピーしてExcelに貼り付けることもできますし、CSV形式やタブ区切り形式でテキストファイルに保存することも可能です。かなり便利になりましたね。

画面2　SELECT文の実行結果は、テキスト形式ではなく、グリッド形式での表示になり、コピー＆ペーストやCSV形式での保存も容易になった（画面をクリックすると全体を表示します）

　本題に戻りましょう。例1のSQLは、Productsテーブルから、在庫「0（UnitsInStock = 0）」で、かつ現在販売中の製品（Discontinued = 0）の製品を選択するSELECT文です。結果としては、欠品している製品のリストを得ることができます。

ビューの定義

　例1の欠品リストは、製品の在庫管理者がよく利用するため、ビューとして定義することにします。ビューとして定義すれば、毎回条件を指定したSELECT文を実行する必要がなくなり、利便性の向上を図ることができます。実際に作成してみましょう。

CREATE VIEW KeppinList
AS
SELECT *
FROM Products
WHERE UnitsInStock = 0 and Discontinued = 0

　ビューが間違いなく作成されたかは、次のSELECT文を実行し、例1と同じ結果が表示されることを確認すればOKです。

SELECT *
FROM KeppinList

画面3　例2で定義したビューを呼び出すと、例1の結果と同じであることが確認できる（画面をクリックすると拡大表示します）

　では、例2の解説をしましょう。ビューの作成には「CREATE」文を使用します。CREATE文は以前、テーブルの作成の回で紹介しました。テーブルを作成するときには、「CREATE TABLE テーブル名テーブル名」としたのを覚えていますか？ ビューを作成する場合には、同様に、「CREATE VIEW ビュー名ビュー名」とします。CREATE句に続き、「AS SELECT文SELECT文」とSELECT文の指定をします。例2のビュー作成に使用したSELECT文は、例1で使用したSELECT文とまったく同じですね。

　作成したビューは、ほかのテーブルと同様に扱うことができます。例えば、例3で紹介したSELECT文のように、通常、テーブル名を指定する個所に、ビュー名を指定することが可能です。

　このように、ビューは特定のSELECT文に名前を付けてデータベースに保存しておく機能です。例2のように、条件付きのSELECT文をビューとして定義しておくことで、毎回複雑なSELECT文を指定することなく、欲しいデータの取得が可能になります。

　データベースに保存するデータは、さまざまな業務で利用することになります。例えば、例2で使用した製品マスターである「Products」テーブルは、製品担当者が在庫管理で使用するでしょうし、販売担当者が製品を参照して発注を登録するときにも使用するでしょう。しかし、それぞれの担当者が欲しいデータは違うはずです。製品担当者には、Productsテーブルに保存されているすべてのデータが必要かもしれませんが、販売担当者には型番と定価と在庫数があれば十分かもしれません。ビューを使用すれば、担当者ごとに最適な形でデータを提供することが可能になります。これがビューを作成する1つ目の目的です。

　ビューを作成するもう1つの目的は、特定のユーザーに必要なデータのみを提供することです。これは、利便性の向上ではなく、データに対するアクセス制限を設けることが目的です。アクセス制限に関しては次回以降で詳しく解説します。

カラム名指定のビューの作成

　では、引き続き、いくつかのビューを作成してみましょう。まずは、販売担当者用に製品ID、製品名、定価、在庫数のみを表示するビューを定義してみましょう。

CREATE VIEW SyohinList (ID, Name, UnitPrice, UnitsInStock)
AS
SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM Products
WHERE Discontinued = 0

画面4　製品テーブルから「製品ID」「製品名」「定価」「在庫数」のみを表示するビューを作成し、結果を表示したところ。テーブル定義とは別のカラム名を表示していることに注目（画面をクリックすると拡大表示します）

　例4では、Productsテーブルの特定のカラムを指定したSELECT文を使用してビューを作成しました。WHERE句で「Discontinued = 0」を指定しているので、現在販売可能な製品のみのリストとなっています。この例のCREATE文では、「CREATE VIEW ビュー名ビュー名」の後に、ビューのカラム名を「（ ）」内に指定しています。こうすることにより、元のテーブル（ベーステーブルと呼びます）で定義されているカラム名とは別のカラム名でビューを作成することが可能になります。ここでは、ProductIDを「ID」、ProductNameを「Name」と短縮して表示させるようにしています。

入れ子のビュー定義

　次の例にいきましょう。例5では、現在欠品中の製品を受注したオーダーリストを表示します。

CREATE VIEW KeppinOrder 
        (CustomerID, CustomerName, ProductID, ProductName, Quantity)
AS
SELECT cust.CustomerID, cust.CompanyName, kep.ProductID, 
kep.ProductName, ordd.Quantity
FROM Customers cust, Orders ord, "Order Details" ordd, KeppinList kep
WHERE ordd.ProductID = kep.ProductID
        AND ord.CustomerID = cust.CustomerID
        AND ord.OrderID = ordd.OrderID

画面5　例5で作成したビューを表示したところ。例5のビューでは、例2で作成したビューをFROM句で使用している（画面をクリックすると拡大表示します）

　例5の解説をしていきましょう。この例では、SELECT文のFROM句の中に「KeppinList」という先ほど作成したビューを使用しています。このようにビューは、FROM句でテーブルと同様に使用できます。また、ほかのビューを作成するときに指定するSELECT文中に指定することも可能であることが分かりますね。ビューの定義は入れ子が可能であるということです。

　例5ではそのほか、旧形式でOrdersテーブル、Order Detailsテーブル、KeppinListをジョインしています。もちろん、INNER JOIN句を指定したジョインも可能です。

集計関数を使用したビューの定義

　もう1つビューを作成してみましょう。今度は、在庫担当者用に、現在欠品している製品のうち、オーダーが入っている製品のリストを表示します。

CREATE VIEW KeppinOrderProducts 
        (ProductID, ProductName, QuantitySum)
AS
SELECT kep.ProductID, kep.ProductName, Sum(ordd.Quantity)
FROM "Order Details" ordd, KeppinList kep
WHERE ordd.ProductID = kep.ProductID
GROUP BY kep.ProductID, kep.ProductName

画面6　例6で作成したビューを表示したところ。集計関数「SUM」の結果が表示される（画面をクリックすると拡大表示します）

　例6では、SELECT文に集計関数を使用しています。このように、ビューの定義に集計関数やGROUP BY句を使用することも可能です。この方法を使うことにより、各データの詳細はユーザーに見せず、集計した結果のみをユーザーに提供することが可能になります。

ビュー定義の制限

　これまでの例で紹介したとおり、ビュー定義時に使用するSELECT文には、いままでの連載で紹介してきたほとんどのSELECT文を指定することが可能ですが、すべてのSELECT文が指定できるわけではありません。ビューに指定できないSELECT文の代表的なものは、以下のとおりです。

• ORDER BYを含むSELECT文
• COMPUTE句、COMPUTE BY句を含むSELECT文
• INTO句を含むSELECT文

　特に、ORDER BY句が指定できない点には注意しましょう。ビューの参照結果を並べ替えたい場合は、ビューの定義中にORDER BYを指定するのではなく、ビューのSELECT時にORDER BYを指定します。

SELECT *
FROM KeppinList
ORDER BY ProductID

　COMPUTE句、COMPUTE BY、INTO句については、この連載ではまだ紹介していませんので、必要に応じてSQL Server Books Onlineなどを参照してください。

ビュー利用上の制限

　次に、ビュー利用上の制限をまとめておきましょう。ビューはテーブルと同様に使用することができる、と説明しましたが、それはSELECT文で参照するときのみで、INSERT文、UPDATE文、DELETE文で更新するときには大きな制限が加わります。

　更新不可能なビューの代表的なものは、以下のとおりです。

• 集計関数とGROUP BY句を使用しているSELECT文を指定したビュー
• DISTINCT、UNION、TOPを使用しているSELECT文を指定したビュー
• 派生列（「SELECT Quantity+1 FROM…」など）を使用しているSELECT文を指定したビュー

　例えば、KeppinListビューは更新可能です。

UPDATE KeppinList 
Set UnitsInStock = 100
WHERE ProductID = 31

　しかし、KeppinListProductsビューはSUM関数を使用しているため更新不可能です。

UPDATE KeppinOrderProducts
Set ProductName = 'Gorgonzola Telino2'
WHERE ProductID = 31

画面7　ビューを対象にした更新ではいくつか制限がある。上記の例では、集計関数を含むビューにUPDATEをかけようとしたところ、エラーが発生したことが分かる（画面をクリックすると拡大表示します）

　このように、ビューのカラムが集計や計算をした結果であるため、テーブル上の特定のカラムに結び付かない場合は更新ができません。また更新処理が1つ以上のテーブルに影響がある場合は、更新ができません。例10では、KeppinOrderのベーステーブルであるCustomersテーブルのCustomerNameカラムとProductsテーブルのProductNameカラムに更新をかけようとしているため、エラーとなります。

UPDATE KeppinOrder
Set CustomerName = 'Hanari Carnes2', ProductName = 'Gorgonzola Telino3'
WHERE CustomerID = 'HANAR'

画面8　複数のテーブルに影響のあるビューに対しての更新はできないようになっている（画面をクリックすると拡大表示します）

　ビューを更新する際は、ベーステーブルへの影響範囲を十分に考えて慎重に実行する必要がある点を注意しましょう。