SQL ビュー(View) 紹介

ビューとは、CREATE TABLE で定義された実テーブルから作成される仮想的なテーブルのことです。ビューはあくまでも仮想テーブルなので、その中にデータは存在しません。ビューは実テーブルをどのように見るのかを定義したものと言えます。

もうすでに使っていますが、CREATE TABLE で定義された、データの入ったテーブルのことを実テーブル、この実テーブルから作られる仮想的なテーブルをビューと呼ぶことにします。

ビューのメリット

ビューの実体は SELECT 文によるクエリを定義したものです。ビューにアクセスすると、定義されたクエリが実行され、実テーブルから必要なデータが抽出されます。また、ビューからビューを定義することも可能です。ですから、あらかじめ毎回入力するのが面倒な複雑なクエリを一度ビューとして定義しておけば、ユーザやプログラムは単純なクエリを実行するだけですみます。

このような開発労力の軽減という観点以外に、セキュリティについてもメリットもあります。たとえば、実テーブルのデータの中に一般ユーザには見せたくないものがある場合、実テーブルへのアクセス権限を一般ユーザには与えることはできません。しかし、見せたくないデータを除いたビューを定義することにより、一般ユーザでも実テーブルのデータを利用できるようになります。

ビューの作成

ビューを作成するには CREATE VIEW 文を用います。基本構文は次のとおりです。

CREATE VIEW ビュー名 [(列名, 列名, .....)] AS
SELECT文
[WITH CHECK OPTION];

ビュー名の後には、抽出するデータの列名を記述します。列名を省略した場合、SELECT 文で指定した列の名前がそのまま使用されます。SELECT 文で演算子を使用した場合や、改めて列名を指定したい場合に明示的に記述するものです。

ビュー名と列名の後には、AS に続けて SELECT 文によるクエリを記述します。このクエリの結果がこのビューのデータということになります。[WITH CHECK OPTION] はビューの更新に関連するオプションです。ビューの更新については後の節で説明します。

この章の SQL のサンプルはこれまでの章でも使用してきた受注表・顧客表・商品表を実テーブルとして使用します。sample.21-1 では v_受注という名前のビューを定義し、最後にビューの内容を確認しています。

/* ビューの作成 */
CREATE VIEW v_受注 AS
SELECT JJ.受注番号, KK.顧客名, SS.商品名,
JJ.受注個数, JJ.納品日
FROM 受注表 JJ, 顧客表 KK, 商品表 SS
WHERE JJ.顧客コード = KK.顧客コード
AND JJ.商品コード = SS.商品コード;
 
/* 確認 */
SELECT JJ.受注番号, KK.顧客名, SS.商品名,
JJ.受注個数, JJ.納品日
FROM 受注表 JJ, 顧客表 KK, 商品表 SS
WHERE JJ.顧客コード = KK.顧客コード
AND JJ.商品コード = SS.商品コード;

定義したビューから特定の行や列を出力することも出来ます。sample.21-2 は定義したv_受注から、受注個数が 100以上である行を選択し、その顧客名、商品名及び受注個数を出力するものです。

実行結果
顧客名 商品名 受注個数
──────────── ────── ───────
KUROKIYA JUICE 300
ONSIDE BEER 200
KUSHINANA BEER 150
ONSIDE WINE 110
KUROKIYA TEA 250
KUSHINANA BEER 135
KUROKIYA BEER 290
KUSHINANA BEER 175

標準規格として

標準SQL規格としては、SQL89から使用可能です。SQL92ではCHECK OPTION、LOCAL、CASCADEの機能拡張が行なわれています。SQL99では更に機能強化が計られ、一定条件下でビューから実表のデータの更新が可能になった。なお、SQL89においては、ビューを作成できるものの、DROPステートメントが無いため削除できません。

ビューの削除

ビュー削除の際に、以下のようにSQL文を記述します。(DROPステートメントの詳細)

DROP VIEW ビュー名;

実習課題

  1. 次のテーブルを以下の条件に合わせて表示するクエリを作ってみましょう。
  • 名前を一つのコラムに入れてfullnameと呼ぶ
  • Birthdateが2001-09-01より前の人だけを表示する
id first_name last_name email birthdate added
1 Bonnie Medhurst [email protected] 1999-07-26 1982-05-01 21:32:19
2 Vincenza Streich [email protected] 2006-01-30 2000-07-15 18:19:18
3 Carter Kilback do’[email protected] 1998-12-07 1998-08-14 06:13:52
4 Mariano Stroman [email protected] 1996-06-18 2008-04-06 14:43:43
5 Deondre Kerluke [email protected] 1994-04-10 2008-06-26 22:02:19
6 Gia Towne [email protected] 1988-07-13 1982-03-22 03:16:39
7 Margarett Beahan [email protected] 2008-01-22 2010-09-12 08:59:33
8 Gilda Brekke [email protected] 1981-09-18 2016-04-14 01:17:38
9 Zackary Cummerata [email protected] 2001-05-27 2003-05-23 21:18:54
10 Kory Wiegand [email protected] 2010-03-16 1992-03-22 04:14:31
11 Vinnie Zboncak [email protected] 2016-06-23 2014-12-29 20:55:25
12 Carmella Bogan [email protected] 1978-06-10 1993-01-17 08:43:08
13 Vance Rice [email protected] 1996-10-09 1999-09-14 15:27:08
14 Coleman Maggio [email protected] 1999-11-22 2001-05-01 05:42:36
15 Patricia Zieme [email protected] 1978-11-18 1987-07-21 18:44:25
16 Jada Beer [email protected] 1991-07-17 1997-10-08 09:41:57
17 Kianna Torphy [email protected] 1994-03-17 2011-11-20 20:15:40
18 Maegan Harvey [email protected] 1979-07-18 2010-10-06 20:52:03
19 Winona Welch [email protected] 1974-10-28 2010-09-02 22:05:57
20 Queen Mosciski [email protected] 1983-05-23 1985-10-09 08:51:49

CREATE VIEW scit_view AS
SELECT (first_name || last_name) AS fullname, email
FROM scit_user
WHERE birthdate < to_date('2001-09-01','YYYY-MM-DD');

SELECT fullname, email FROM scit_view;
  1. WHEREで決められた条件に合うアイテムが元のテーブルで変わると、ビューは変わりますか。
  2. Oracleを含めて、最近のデータベースシステムではビューのINSERTやUPDATE、DELETEなどもできますが、こういったDML操作ができない場合を考えてみましょう。

 

これは、Techscoreからの原文を加工して、発表のためにまとめた資料です。

 

SQL ビュー(View) 紹介” への1件のフィードバック

햏자 へ返信する コメントをキャンセル

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です