データ定義命令には、前回紹介したテーブルの他にビューやストアドプロシージャ、ユーザー定義関数などの作成・削除・変更命令もあります。ストアドプロシージャとユーザー定義関数の作成・変更・削除については、これまでの記事の中で既に紹介してきました。ビューの作成・変更・削除について紹介しましょう。
- ビューはGUIでも作れるが…… -
ビューは、EnterpriseManagerで「ビュー」を選択してコンテキストメニューから「新規ビュー」を選べば、Accessのクエリデザインと似た画面でGUIを使って作成できます。滅多に使わないビューをただ一度だけ作成するなら、この方法が便利です。
しかし業務システムなどでは、異なる業務でテーブル名や列名が異なっていても、基本部分が似たようなビューを作成することがよくあります。例えば、以下のような３つの処理ではテーブル名と列名は異なるものの、基本的な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の３行目にある“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 在庫
 |
|
|
