[SQL] 9. ビュー 2

SQL 9章　ビュー

9.3. ビューの変更

定義したビューに対してデータを変更することもできます。ビューはデータの入っていない仮想的なテーブルなので、実際にはビューの元になる実テーブルのデータが変更されることになります。また、ビューのデータの変更は実テーブルに定義されている制約を受けます。

データの追加、更新及び削除はテーブルの時と同じように、INSERT 文、UPDATE 文、DELETE 文を用いて実行します。ここでは受注表から商品コードが '101' のものだけを抽出する v_101 を定義します。そして v_101 に対して、受注番号 = '10013'、顧客コード = '002'、受注個数 = 120、納品日 = '20010406' の行を追加します。

-- sample.21-3 --

/* 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

データが追加された後の受注表は sample.21-4 のようになります。

-- sample.21-4 --

/* 受注表 */

  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' のものを抽出するようになっています。結果は、エラーにはならず挿入できてしまいます。挿入は可能ですが、sample.21-3 の出力結果には、挿入した行は出力されません。これは、INSERT 文により行の挿入は完了し、実テーブルである受注表にデータが書き込まれます。しかし、sample.21-3 で定義したビュー v_101 では商品コードが '101' のもののみを抽出するようになっていますので、挿入した行は出力されないということになります。

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

-- sample.21-5 --

/* v_101の定義 WITH CHECK OPTION */

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

次に、sample.21-6 のように v_102 というビューを定義したときに、受注番号 '10014'、顧客コード '002'、受注個数 '125' という行を挿入することを考えます。

-- sample.21-6 --

/* v_102の定義 WITH CHECK OPTION */

  CREATE 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

sample.21-6 の INSERT 文を実行した後の、実テーブルにはどのようなデータが入るでしょうか? ビュー v_102 は受注番号、顧客コード、受注個数しかないため、ビューに行が追加されたとき、顧客コード、納品日には、デフォルト値が与えられていない場合は、NULL が自動的に挿入されることになります。実テーブルでデフォルト値が設定されていれば、デフォルト値が挿入されます。もし、顧客コード、納品日の列制約として NOT NULL が定義されていた場合は、エラーとなり v_102 に行の挿入はできなくなります。すなわち、実テーブルで定義した制約がここにも反映されることになります。この後の、データの更新の際も同様のことが言えます。

ちなみに、実テーブルの確認を sample.21-7 で行っています。出力結果のように、受注番号が '10014' の行の商品コードと納品日は NULL となります。このように、商品コードも NULL となってしまうため、データを挿入する可能性のある場合は、ビューの定義で商品コードも出力列として定義しておいたほうがよいと思われます。

-- sample.21-7 --

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

  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 文を用います。sample.21-8 はビュー v_101 の受注番号が '10012' の受注個数を 175 から 150 に変更するものです。

-- sample.21-8 --

/* UPDATE */

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

また、行を削除するには DELETE 文を使用します。sample.21-4 で挿入された受注番号 = '10013' の行を削除するには sample.21-9 のように書きます。

-- sample.21-9 --

/* DELETE */

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

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

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

9.4. ビューの削除

  DROP VIEW ビュー名

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

sample.21-10 では 9.1. 節で定義した v_受注ビューを削除します。

-- sample.21-10 --

/* ビューの削除 */

  DROP VIEW v_受注 ;

↑このページの先頭へ