今回は、ビューの作成にチャレンジします。ビューは、いままでに作成してきたSELECT文に名前を付けてデータベース上に保存しておける仕組みです。実際に作成しながら、ビューがどのような場面で必要になるのかを確認していきましょう。
今回はまず、Productsテーブルから「欠品のリスト」を作成してみましょう。
【例1】
SELECT * FROM Products WHERE UnitsInStock = 0 and Discontinued = 0
事例解説に使用する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形式やタブ区切り形式でテキストファイルに保存することも可能です。かなり便利になりましたね。
本題に戻りましょう。例1のSQLは、Productsテーブルから、在庫「0（UnitsInStock = 0）」で、かつ現在販売中の製品（Discontinued = 0）の製品を選択するSELECT文です。結果としては、欠品している製品のリストを得ることができます。
ビューの定義
例1の欠品リストは、製品の在庫管理者がよく利用するため、ビューとして定義することにします。ビューとして定義すれば、毎回条件を指定したSELECT文を実行する必要がなくなり、利便性の向上を図ることができます。実際に作成してみましょう。
【例2】
CREATE VIEW KeppinList AS SELECT * FROM Products WHERE UnitsInStock = 0 and Discontinued = 0
ビューが間違いなく作成されたかは、次のSELECT文を実行し、例1と同じ結果が表示されることを確認すればOKです。
【例3】
SELECT * FROM KeppinList
では、例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、製品名、定価、在庫数のみを表示するビューを定義してみましょう。
【例4】
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では、現在欠品中の製品を受注したオーダーリストを表示します。
【例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の解説をしていきましょう。この例では、SELECT文のFROM句の中に「KeppinList」という先ほど作成したビューを使用しています。このようにビューは、FROM句でテーブルと同様に使用できます。また、ほかのビューを作成するときに指定するSELECT文中に指定することも可能であることが分かりますね。ビューの定義は入れ子が可能であるということです。
例5ではそのほか、旧形式でOrdersテーブル、Order Detailsテーブル、KeppinListをジョインしています。もちろん、INNER JOIN句を指定したジョインも可能です。
集計関数を使用したビューの定義
もう1つビューを作成してみましょう。今度は、在庫担当者用に、現在欠品している製品のうち、オーダーが入っている製品のリストを表示します。
【例6】
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では、SELECT文に集計関数を使用しています。このように、ビューの定義に集計関数やGROUP BY句を使用することも可能です。この方法を使うことにより、各データの詳細はユーザーに見せず、集計した結果のみをユーザーに提供することが可能になります。
ビュー定義の制限
これまでの例で紹介したとおり、ビュー定義時に使用するSELECT文には、いままでの連載で紹介してきたほとんどのSELECT文を指定することが可能ですが、すべてのSELECT文が指定できるわけではありません。ビューに指定できないSELECT文の代表的なものは、以下のとおりです。
- ORDER BYを含むSELECT文
- COMPUTE句、COMPUTE BY句を含むSELECT文
- INTO句を含むSELECT文
特に、ORDER BY句が指定できない点には注意しましょう。ビューの参照結果を並べ替えたい場合は、ビューの定義中にORDER BYを指定するのではなく、ビューのSELECT時にORDER BYを指定します。
【例7】
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ビューは更新可能です。
【例8】
UPDATE KeppinList Set UnitsInStock = 100 WHERE ProductID = 31
しかし、KeppinListProductsビューはSUM関数を使用しているため更新不可能です。
【例9】
UPDATE KeppinOrderProducts Set ProductName = 'Gorgonzola Telino2' WHERE ProductID = 31
このように、ビューのカラムが集計や計算をした結果であるため、テーブル上の特定のカラムに結び付かない場合は更新ができません。また更新処理が1つ以上のテーブルに影響がある場合は、更新ができません。例10では、KeppinOrderのベーステーブルであるCustomersテーブルのCustomerNameカラムとProductsテーブルのProductNameカラムに更新をかけようとしているため、エラーとなります。
【例10】
UPDATE KeppinOrder Set CustomerName = 'Hanari Carnes2', ProductName = 'Gorgonzola Telino3' WHERE CustomerID = 'HANAR'
ビューを更新する際は、ベーステーブルへの影響範囲を十分に考えて慎重に実行する必要がある点を注意しましょう。
次回の予定
今回は、ビューの作成について解説しました。次回は、Enterprise Managerを使用したビューの定義について紹介する予定です
- SQL Serverで「デッドロック」を回避する
- トランザクションの一貫性を保証するロック
- トランザクションを用いて注文登録をする
- トランザクションでデータの不整合を防ぐ
- テーブルで複数の処理を実行させるトリガー
- ユーザー定義関数を作成するストアドファンクション
- ストアドプロシージャによる繰り返し処理
- 条件分岐のあるストアドプロシージャ
- ストアドプロシージャの作成
- システム・ストアドプロシージャを用いたロールの詳細設定
- ロールを利用したグループ単位での権限設定
- SQL Serverのオブジェクトに権限を設定する
- Enterprise Managerによるビューの作成
- 作成したSELECT文をDBに登録する「ビュー」
- データの更新と主キーの重要性
- テーブル中のデータ識別に必要な主キーを定義する
- データの登録を行うINSERT文
- CREATE文をさらに使いこなそう
- CREATE文でテーブルを作成する
- SELECT文を統合する「UNION」
- サブクエリーの応用「相関サブクエリー」
- SELECT文の結果を抽出条件に使う
- テーブル結合のバリエーションを増やす
- テーブル結合の仕組みを理解する
- 異なるテーブル同士を結合する「JOIN」句
- 集計を行う「GROUP BY」句
- SELECT文で並べ替えを行うには？
- SQLの基礎 「SELECT」文を覚えよう
Copyright © ITmedia, Inc. All Rights Reserved.