ビューのメリット/ 作成 / 変更 / 削除


ビューとは、CREATE TABLE で定義された実テーブルから作成される仮想的なテーブルのことです。

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

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

テーブルは基本的な結合 / 自己結合で使用したテーブルを使います。

ビューのメリット

ビューの実体は SELECT 文によるクエリを定義したものです。

ビューにアクセスすると、定義されたクエリが実行され、実テーブルから必要なデータが抽出されます。

また、ビューからビューを定義することも可能です。
従って、あらかじめ毎回入力するのが面倒な複雑なクエリを一度ビューとして定義しておけば、ユーザやプログラムは単純なクエリを実行するだけですむことになります。

このような開発労力の軽減という観点以外に、セキュリティについてもメリットもあります。

たとえば、実テーブルのデータの中に一般ユーザには見せたくないものがある場合、実テーブルへのアクセス権限を一般ユーザには与えることはできません。
しかし、見せたくないデータを除いたビューを定義することにより、一般ユーザでも実テーブルのデータを利用できるようになります。

ビューの作成

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

ビュー名の後には、抽出するデータの列名を記述します。

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

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

/* ビューの作成 */

 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.商品コード ;
/* 確認の実行結果 */

受注番号 顧客名       商品名 受注個数 納品日
──── ────── ─── ──── ────
10001    KUROKIYA     JUICE  300      20010401
10002    ONSIDE       BEER   200      20010401
10003    KUSHINANA    BEER   150      20010401
10004    KUSHINANA    JUICE  80       20010402
10005    ONSIDE       WINE   110      20010402
10006    KUROKIYA     TEA    250      20010402
10007    FIRST HOUSE  JUICE  45       20010402
10008    KUSHINANA    BEER   135      20010403
10009    FIRST HOUSE  TEA    65       20010403
10010    KUROKIYA     BEER   290      20010403
10011    FIRST HOUSE  TEA    60       20010404
10012    KUSHINANA    BEER   175      20010404

 

/* ビューの確認 */

 SELECT * FROM v_受注 ;
/* ビュー確認の実行結果 */

受注番号 顧客名       商品名 受注個数 納品日
──── ────── ─── ──── ────
10001    KUROKIYA     JUICE  300      20010401
10002    ONSIDE       BEER   200      20010401
10003    KUSHINANA    BEER   150      20010401
10004    KUSHINANA    JUICE  80       20010402
10005    ONSIDE       WINE   110      20010402
10006    KUROKIYA     TEA    250      20010402
10007    FIRST HOUSE  JUICE  45       20010402
10008    KUSHINANA    BEER   135      20010403
10009    FIRST HOUSE  TEA    65       20010403
10010    KUROKIYA     BEER   290      20010403
10011    FIRST HOUSE  TEA    60       20010404
10012    KUSHINANA    BEER   175      20010404

 

/* ビューからの抽出 */

 SELECT 顧客名,商品名,受注個数 FROM v_受注
        WHERE 受注個数 >= 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

ビューの変更

定義したビューに対してデータを変更することもできます。

ビューはデータの入っていない仮想的なテーブルなので、実際にはビューの元になる実テーブルのデータが変更されることになります。

また、ビューのデータの変更は実テーブルに定義されている制約を受けます。

/* v_101の定義 */

 CREATE VIEW v_101 AS SELECT * FROM 受注表
   WHERE 商品コード = '101' ;
/* v_101の確認 */

 SELECT * FROM v_101 ;

受注番号 顧客コード 商品コード 受注個数 納品日
──── ───── ───── ──── ────
10002    002        101        200      20010401
10003    004        101        150      20010401
10008    004        101        135      20010403
10010    001        101        290      20010403
10012    004        101        175      20010404
/* INSERT */

 INSERT INTO v_101
   VALUES('10013', '002', '101', 120, '20010406') ;
/* INSERT 後の確認 */

 SELECT * FROM v_101 ;

受注番号 顧客コード 商品コード 受注個数 納品日
──── ───── ───── ──── ────
10002    002        101        200      20010401
10003    004        101        150      20010401
10008    004        101        135      20010403
10010    001        101        290      20010403
10012    004        101        175      20010404
10013    002        101        120      20010406

 

/* 受注表 */

 SELECT * FROM 受注表 ;
/* 出力結果 */

受注番号 顧客コード 商品コード 受注個数 納品日
──── ───── ───── ──── ────
10001    001        102        300      20010401
10002    002        101        200      20010401
10003    004        101        150      20010401
10004    004        102        80       20010402
10005    002        104        110      20010402
10006    001        103        250      20010402
10007    003        102        45       20010402
10008    004        101        135      20010403
10009    003        103        65       20010403
10010    001        101        290      20010403
10011    003        103        60       20010404
10012    004        101        175      20010404
10013    002        101        120      20010406

ビュー v_101 に商品コード ’102′ のデータを挿入した場合はどうなるのでしょうか。

ビュー v_101 の定義では商品コード ’101′ のものを抽出するようになっています。

結果は、エラーにはならず挿入できてしまいます。
挿入は可能ですが、ビューv_101 の出力結果には、挿入した行は出力されません。

INSERT 文により行の挿入は完了し、実テーブルである受注表にデータが書き込まれますが、ビュー v_101 では商品コードが ’101′ のもののみを抽出するようになっているので、挿入した行は出力されないということになります。

本来ビュー v_101 に商品コードが ’101′ 以外のデータを挿入することに意味はありません。

INSERT 文に商品コードが ’101′ のデータ以外を挿入させないようにするには、ビュー v_101 の定義のところで WITH CHECK OPTION 句を指定し、明示的に条件 (WHERE 句) に反する行が挿入される場合はエラーメッセージが返されるようにします。

/* v_101の定義 WITH CHECK OPTION */

 CREATE VIEW v_101 AS SELECT * FROM 受注表
   WHERE 商品コード = '101'
   WITH CHECK OPTION ;

次に、 v_102 というビューを定義したときに、受注番号 ’10014′、顧客コード ’002′、受注個数 ’125′ という行を挿入することを考えます。

/* v_102の定義 WITH CHECK OPTION */

 CREATE VIEW v_102 AS
   SELECT 受注番号,顧客コード,受注個数 FROM 受注表
     WHERE 商品コード = '102'
     WITH CHECK OPTION ;
/* 行の挿入 */

 INSERT INTO v_102
   VALUES('10014', '002', 125);
/* 確認 */

 SELECT * FROM v_102 ;

受注番号 顧客コード 受注個数
──── ───── ────
10001    001        300
10004    004        80
10007    003        45
10014    002        125

INSERT 文を実行した後の、実テーブルにはどのようなデータが入るのでしょうか。

ビュー v_102 は受注番号、顧客コード、受注個数しかないため、ビューに行が追加されたとき、顧客コード、納品日には、デフォルト値が与えられていない場合は、NULL が自動的に挿入されることになります。

実テーブルでデフォルト値が設定されていれば、デフォルト値が挿入されます。
もし、顧客コード、納品日の列制約として NOT NULL が定義されていた場合は、エラーとなり v_102 に行の挿入はできなくなります。
すなわち、実テーブルで定義した制約がここにも反映されることになります。

この後の、データの更新の際も同様のことが言えます。

ちなみに、実テーブルの確認を次のSQL文で行っています。
出力結果のように、受注番号が ’10014′ の行の商品コードと納品日は NULL となっています。

このように、商品コードも NULL となってしまうため、データを挿入する可能性のある場合は、ビューの定義で商品コードも出力列として定義しておいたほうがよいと思います。

/* 実テーブルの確認 */

 SELECT * FROM 受注表
   WHERE 商品コード = '102' ;
/* 出力結果 */

受注番号 顧客コード 商品コード 受注個数 納品日
──── ───── ───── ──── ───
10001    001        102        300      20010401
10004    004        102        80       20010402
10007    003        102        45       20010402
10014    002        NULL       125      NULL

データの更新は UPDATE 文を用います。

次のSQL文はビュー v_101 の受注番号が ’10012′ の受注個数を 175 から 150 に変更するものです

/* UPDATE */

 UPDATE v_101 SET 受注個数 = 150
   WHERE 受注番号 = '10012' ;

また、行を削除するには DELETE 文を使用します。
受注番号 = ’10013′ の行を削除するには次のように書きます。

/* DELETE */

 DELETE FROM v_101
   WHERE 受注番号 = '10013' ;

このように、ビューに対してデータの追加、更新、削除を行う方法は実テーブルに対して行うときと同じように行えます。
ただし、ビューは実テーブルから仮想的な表として定義されているものなので、実テーブルに対してデータの追加、更新、削除を行うように自由にはできません。

ビューに対するデータの変更には次のような制限があります。

  •   1つの実テーブルから定義されているビューに対してのみデータの変更が可能。
  • 複数の実テーブルから定義されているビューに対してはデータの変更はできない 。
  • GROUP BY 句及び HAVING 句を使用して定義されたビューに対してはデータの変更ができまない。GROUP BY 句及び HAVING 句を用いているクエリは出力するデータの行を特定ができないため。
  • DISTINCT 句を使用して定義されたビューに対してはデータの変更ができない。これも GROUP BY 句及び HAVING 句用いているクエリと同様に、出力されるデータの行が特定できないから。

ビューの削除

DROP VIEW ビュー名

このとき、ビューは削除されるが、ビューの元になる実テーブルのデータは削除されません。
これに対して、ビューを元に定義されたビューは元になるビューが削除されると、そのビューは無効になります。

/* ビューの削除 */

 DROP VIEW v_受注 ;

 

コメントを残す

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


7 + = 9

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>