データベース千夜一夜第34回

データ定義命令(2)~ビューとインデックスの操作 長谷川裕行
有限会社 手國堂

ビューの作成・変更・削除

データ定義命令には、前回紹介したテーブルの他にビューやストアドプロシージャ、ユーザー定義関数などの作成・削除・変更命令もあります。ストアドプロシージャとユーザー定義関数の作成・変更・削除については、これまでの記事の中で既に紹介してきました。ビューの作成・変更・削除について紹介しましょう。


- ビューはGUIでも作れるが…… -

ビューは、EnterpriseManagerで「ビュー」を選択してコンテキストメニューから「新規ビュー」を選べば、Accessのクエリデザインと似た画面でGUIを使って作成できます。滅多に使わないビューをただ一度だけ作成するなら、この方法が便利です。

しかし業務システムなどでは、異なる業務でテーブル名や列名が異なっていても、基本部分が似たようなビューを作成することがよくあります。例えば、以下のような3つの処理ではテーブル名と列名は異なるものの、基本的なSQLは共通しています。

a)商品マスター・顧客マスター・販売記録を関連付けて販売結果を表示する

b)商品マスター・仕入先マスター・仕入記録を関連付けて仕入記録を表示する

c)社員マスター・所属マスター・勤怠記録を関連付けて勤怠記録を表示する

このような場合、GUIによるビューのデザインでは、列リストから項目をドラッグして……といった同じ作業を何度も行わなければならなくなり、非効率です。しかしSQLで記述したソースコードなら、必要な箇所を書き換えるだけで他の処理にも簡単に応用できます。

GUIで作成したビューをEnterpriseManagerで開くと、画面下部にSQL文が表示されます。これをクリップボードにコピーし、これから紹介するビューの作成命令(CREATE VIEW命令)の下に貼り付けてテーブル名や列名などを書き換えれば、簡単に新しいビューを作成できます。


- ビューを作成する~CREATE VIEW -

ビューを作成するには、CREATE VIEW命令を使います。書式は以下の通りです。

  CREATE VIEW <ビュー名> AS <SELECT文>

<SELECT文>で指定したSQLのSELECT文に<ビュー名>で示す名前が付けられ、データベースのオブジェクトとして保存されます。

例えば、テーブル「得意先_mr」から女性客だけを取り出して得意先ID・氏名・生年月日を表示するビュー「vw女性顧客」を作るなら、以下のようになります。

  CREATE VIEW vw女性顧客
  AS
  SELECT お客様ID, 氏名, 生年月日
  FROM 得意先_mr

ビューの実行結果を確かめるには、クエリアナライザでビューを選択してコンテキストメニューを開き「開く」を選びます。



- 在庫を一覧表示するビュー -

本コラムの第18回「データベース側での関連付け~ビューとダイアグラムの利用」で、GUIを使ったビューのデザインを紹介しました。このとき作った「vw在庫一覧」をCREATE VIEW命令で作るなら、SQLは以下のようになります。

  CREATE VIEW vw在庫一覧
  AS
  TOP 100 PERCENT --------★【補足】を参照
  SELECT 商品_mr.商品ID, 商品_mr.品名, 商品_mr.仕入単価,
      商品_mr.仕入先ID, 仕入先_mr.仕入先名, 在庫_mr.在庫
  FROM 商品_mr, 仕入先_mr, 在庫_mr
  WHERE 商品_mr.仕入先ID = 仕入先_mr.仕入先ID
  AND
      商品_mr.商品ID = 在庫_mr.商品ID
  ORDER BY 在庫

このビューは、商品マスターから商品名と仕入単価、仕入先マスターから仕入先名、在庫マスターから在庫数──をそれぞれ抽出し、在庫の少ない順に一覧表示します。

但し、データベースには既にビュー「vw在庫一覧」が存在するため、上記のSQLを実行するとエラーになります(そのため、サンプルのクエリファイルは用意していません)。既存のビューの内容を置き換える方法は、次項をお読みください。


【補足】

TOP命令による件数の制限

上記SQLの3行目にある“TOP 100 PERCENT”(★マークの箇所)は、結果セット(SELECT命令を処理した結果のレコード群)のレコード数を制限するための命令で、CREATE VIEW命令と次に紹介するALTER VIEW命令でORDER BY句を含むSQLを登録する場合に、この指定がないとエラーになります。

TOP命令の書式は以下の通りです。

  TOP <値> [PERCENT]

<値>のみで“PERCENT”を指定しないと<値>は件数を示し、“PERCENT”を指定すると<値>は結果セット全体の割合(100分率)となります。通常は“100 PERCENT”としてすべてのレコードを返すようにして構いませんが、大量のレコードが返ってくると予想される場合、この命令によって件数を絞り込み、サーバーとネットワークの負荷を軽減できます。



- ビューの内容を変更する~ALTER VIEW -

既に作成しデータベースに登録されたビューの内容を変更するには、ALTER VIEW命令を使います。書式は以下の通りです。

  ALTER VIEW <ビュー名> AS <SELECT文>

<ビュー名>で指定した既存のビューの中身を、<SELECT文>で示すSELECT文に置き換えます。

先に、EnterpriseManagerで既存のビューを開くと、画面下部にSQL文が表示される──と説明しました。このとき表示されるSELECT文の上にはALTER命令が記述されています。

画面2は、ビュー「vw在庫一覧」の編集画面です。このとき、AS以降のSELECT命令を書き換えて保存すれば、ALTER VIEW命令が実行されてビューの内容が書き換えられます。

ビューの編集画面は単純なテキストエディタなので、SELECT文以外の箇所も書き換えることができます。しかし、“ALTER VIEW”の箇所やビュー名(例では“dbo.vw在庫一覧”)を書き換えると、目的のビューが正しく書き換えられずにエラーとなります。注意しましょう。

なお、編集モードで開いたビューのウィンドウを閉じようとすると、どこも書き換えていない場合でも「変更を保存しますか?」という確認のメッセージが表示されます(既存ビューを変更するための≪新たなSQL文を作成した≫と見なされるためです)。内容を確認するだけの場合は、[いいえ]をクリックしておきましょう。



- 既存ビューの処理を書き換える -

先に紹介した既存のビュー「vw在庫一覧」は「在庫数の少ない順」に並べ替えられましたが、これを「商品IDの昇順」に並べ替える処理に置き換えるなら、以下のようなSQLを記述します。

  ALTER VIEW vw在庫一覧
  AS
  TOP 100 PERCENT
  SELECT 商品_mr.商品ID, 商品_mr.品名, 商品_mr.仕入単価,
      商品_mr.仕入先ID, 仕入先_mr.仕入先名, 在庫_mr.在庫
  FROM 商品_mr, 仕入先_mr, 在庫_mr
  WHERE 商品_mr.仕入先ID = 仕入先_mr.仕入先ID
  AND
      商品_mr.商品ID = 在庫_mr.商品ID
  ORDER BY 商品ID

ここでも、SELECTに続けて“TOP 100 PERCENT”命令を記述している点に注意してください。置き換えたビュー「vw在庫一覧」を開くと、レコードの並び順が「商品ID」の昇順になっています。



- ビューを削除する~DROP VIEW -

既存のビューをデータベースから削除するには、DROP VIEW命令を使います。書式は以下のようになります。

  DROP VIEW <ビュー名>

パラメータはなく、<ビュー名>で指定したビューを単に削除するだけです。


- ビューの扱い方 -

ビューは「仮想テーブル」とも呼ばれ、アプリケーションのレベルではテーブルと同じように扱えます。データベースの中ではテーブルとビューは異なるオブジェクトですが、アプリケーションがSQLを発行してその結果を取得するとき、どちらも行の集合である「結果セット(行セット/レコードセット)」を返します。

従って、SELECT文のFROM句でテーブルの代わりにビューを指定することもできます。例えば、前掲の「vw在庫一覧」から「品名」と「在庫」フィールドだけを取り出し、フィールド名を「品名→商品名」、「在庫→在庫数」と置き換え、さらに「在庫」の昇順で並べ替えるなら、以下のようなSQLを記述します。

  SELECT 品名 AS 商品名, 在庫 AS 在庫数
  FROM vw在庫一覧
  ORDER BY 在庫




トップページ
ビューの作成・変更・削除
ビューはGUIでも作れるが……
ビューを作成する~CREATE VIEW
在庫を一覧表示するビュー
ビューの内容を変更する~ALTER VIEW
既存ビューの処理を書き換える
ビューを削除する~DROP VIEW
ビューの扱い方
インデックスの操作
ストアドプロシージャとデータ定義命令のまとめ
あとがき
Copyright © GrapeCity inc. All rights reserved.