Oracle SQL ビュー

ビューとは論理表とも言われ、実際の表を元にして選択された列をユーザにあたかも通常の表の様に見せる仕組みのことです。元の表は論理表に対して実表と呼ばれます。以下にビューの生成の構文を示しますが、これを見ればSELECT文が返す結果をビューとしていることが分かると思います。尚、ビューを生成するには該当するユーザ(スキーマ)にビュー生成の権限が必要です。

CREATE [ OR REPLACE ] <ビュー名> [ <ビュー列名リスト> ] AS <SELECT文>

私は大抵、"OR REPLACE"を指示してビューを生成しています。<ビュー列名リスト>はビューの列名を、SELECT文の結果の列名とは別の名前を付けたい場合に指定します。


おすすめ書籍



簡単なビュー

以下にTM_商品から商品コード、商品名、売上単価のみを選択するビューを作成する例を示します。

SQL> SELECT * FROM TM_商品;

商品コード 商品名                                   商品区分              仕入単価  売上単価
---------- ---------------------------------------- -------------------- --------- ---------
         1 PC-9001                                  デスクトップパソコン     65000     98000
         2 PC-9002                                  デスクトップパソコン     95000    120000
         3 PC-9003                                  デスクトップパソコン    150000    190000
         4 NOTE-1010                                ノートパソコン          125000    188000
         5 NOTE-1020                                ノートパソコン          145000    200000
         6 NOTE-1030                                ノートパソコン          155000    220000
         7 PRT-3001                                 プリンタ                 45000     88000
         8 PRT-4001                                 プリンタ                115000    180000
         9 CRT-1001                                 ディスプレイ             45000     78000
        10 CRT-2001                                 ディスプレイ             55000     98000
        11 HUB-A001                                 ネットワーク             20000     40000
        12 HUB-B001                                 ネットワーク             40000     60000

12行が選択されました。

SQL> CREATE OR REPLACE VIEW TV_商品 AS
  2   SELECT 商品コード, 商品名, 売上単価 FROM TM_商品;

ビューが作成されました。

SQL> SELECT * FROM TV_商品;

商品コード 商品名                                    売上単価
---------- ---------------------------------------- ---------
         1 PC-9001                                      98000
         2 PC-9002                                     120000
         3 PC-9003                                     190000
         4 NOTE-1010                                   188000
         5 NOTE-1020                                   200000
         6 NOTE-1030                                   220000
         7 PRT-3001                                     88000
         8 PRT-4001                                    180000
         9 CRT-1001                                     78000
        10 CRT-2001                                     98000
        11 HUB-A001                                     40000
        12 HUB-B001                                     60000

12行が選択されました。

最初のSELECTはTM_商品を全てリストするクエリで、ビュー作成後のSELECTはビューの全てをリストするクエリです。
この様に、ビューは表の必要な列を抜き出して新しい表の様に取り扱えます。処理の用途毎に別のビューを作成することで元の表を直接参照するよりもビューを参照したほうが良い場合もあると思います。

簡単なビューに対するデータのアクセス

ビューに対してデータのINSERT,UPDATE,DELETEが行えます。この場合の例としてビューTV_商品を用いた結果を以下に示します。

SQL> INSERT INTO TV_商品 VALUES(13,'HUB-C001',80000);

1行が作成されました。

SQL> SELECT * FROM TM_商品 WHERE 商品コード > 10;

商品コード 商品名                                   商品区分               仕入単価   売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
        11 HUB-A001                                 ネットワーク              20000      40000
        12 HUB-B001                                 ネットワーク              40000      60000
        13 HUB-C001                                                                      80000

SQL> UPDATE TV_商品
  2   SET 売上単価 = 90000
  3   WHERE 商品コード = 13;

1行が更新されました。

SQL> SELECT * FROM TM_商品 WHERE 商品コード > 10;

商品コード 商品名                                   商品区分               仕入単価   売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
        11 HUB-A001                                 ネットワーク              20000      40000
        12 HUB-B001                                 ネットワーク              40000      60000
        13 HUB-C001                                                                      90000

SQL> DELETE FROM TV_商品 WHERE 商品コード = 13;

1行が削除されました。

SQL> SELECT * FROM TM_商品 WHERE 商品コード > 10;

商品コード 商品名                                   商品区分               仕入単価   売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
        11 HUB-A001                                 ネットワーク              20000      40000
        12 HUB-B001                                 ネットワーク              40000      60000

INSERT文の結果、実際の表であるTM_商品をSELECTすると商品区分、仕入単価の列には何も設定されていない(NULLの値)様子がわかると思います。ビューに対してINSERTを行うと、選択されていない元の表の列にはデフォルト値が設定されることになります。また、UPDATE文、DELETE文も実表に対する場合と同様に処理可能です。


行選択のあるビュー

ビュー作成のSELECT文にWHERE句を追加して行の選択を行うビューを作成してみます。さらに、そのビューに対してデータアクセスを行ってみます。

SQL> CREATE OR REPLACE VIEW TV_商品2 AS 
  2   SELECT 商品コード, 商品名, 売上単価 FROM TM_商品
  3   WHERE 売上単価 < 100000;

ビューが作成されました。

SQL> SELECT * FROM TV_商品2;

商品コード 商品名                                    売上単価
---------- ---------------------------------------- ---------
         1 PC-9001                                      98000
         7 PRT-3001                                     88000
         9 CRT-1001                                     78000
        10 CRT-2001                                     98000
        11 HUB-A001                                     40000
        12 HUB-B001                                     60000

6行が選択されました。

売上単価が100,000未満の商品を選択するビューをTV_商品2として作成しています。
TV_商品2をSELECTすると、確かに売上単価が100,000未満の商品のみを表示しています。
このビューに対して、売上単価が100,000以上の商品をINSERTした結果を以下に示します。

SQL> INSERT INTO TV_商品2 VALUES(13,'HUB-C001',100000);

1行が作成されました。

SQL> SELECT * FROM TV_商品2;

商品コード 商品名                                    売上単価
---------- ---------------------------------------- ---------
         1 PC-9001                                      98000
         7 PRT-3001                                     88000
         9 CRT-1001                                     78000
        10 CRT-2001                                     98000
        11 HUB-A001                                     40000
        12 HUB-B001                                     60000

6行が選択されました。

SQL> SELECT * FROM TM_商品 WHERE 商品コード = 13;

商品コード 商品名                                   商品区分              仕入単価  売上単価
---------- ---------------------------------------- -------------------- --------- ---------
        13 HUB-C001                                                                   100000

INSERT後、TV_商品2をSELECTしても追加されたデータは表示されません。実表のTM_商品をSELECTすると確かに追加されていることが分かります。ビューの選択条件に合致しないデータを追加することがあまり意味があるとは思えませんが、こういったことも可能です。もし、このことが起きない様にするにはビュー作成でWITH CHECK OPTIONを指定します。

SQL> CREATE OR REPLACE VIEW TV_商品2 AS 
  2   SELECT 商品コード, 商品名, 売上単価 FROM TM_商品
  3   WHERE 売上単価 < 100000
  4   WITH CHECK OPTION;

ビューが作成されました。

SQL> INSERT INTO TV_商品2 VALUES(14,'HUB-D001',200000);
INSERT INTO TV_商品2 VALUES(14,'HUB-D001',200000)
            *
エラー行: 1: エラーが発生しました。
ORA-01402: ビューのWITH CHECK OPTION WHERE句でエラーが発生しました。

ビューTV_商品2を作成後、売上単価が100000以上であるデータをINSERTします。結果としてWITH CHECK OPTIONのエラーが表示されてデータの追加が失敗します。

転職を本気で考えている方向けのプログラミングスクール!【WebCampPRO】

複雑なビュー

表の結合での外部結合の説明で用いた、売上明細と商品マスタを結合するSELECT文でビューを作成してみます。

SQL> CREATE OR REPLACE VIEW TV_売上明細 AS
  2      SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  3             TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  4      FROM TT_売上明細 TU , TM_商品 TS
  5      WHERE TU.商品コード = TS.商品コード(+);

ビューが作成されました。

SQL> SELECT * FROM TV_売上明細;

  売上番号   明細番号 商品コード 商品名                                     売上単価   売上数量       金額
---------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
         1          1          1 PC-9001                                       98000          2     196000
         1          2          4 NOTE-1010                                    188000          1     188000
         2          1          2 PC-9002                                      120000          1     120000
         2          2          5 NOTE-1020                                    200000          2     400000
         2          3         11 HUB-A001                                      40000          1      40000
         3          1          3 PC-9003                                      190000          3     570000
         4          1          6 NOTE-1030                                    220000          1     220000
         4          2          7 PRT-3001                                      88000          2     176000
         5          1          8 PRT-4001                                     180000          3     540000
         5          2          9 CRT-1001                                      78000          2     156000
         6          1          8 PRT-4001                                     180000          1     180000
         6          2          9 CRT-1001                                      78000          1      78000
         6          3         10 CRT-2001                                      98000          2     196000

13行が選択されました。

表題は複雑なビューとしていますが、この様に2個の表のみを結合したSELECT文では複雑ではありませんが、よく使用されるSELECT文であればビューにしておくと便利かもしれません。

クロス集計のあるビュー

以前表の結合で用いた売上データから商品毎の10月から12月までの売上金額の集計を行うクエリーのビューを作成します。このSELECT文の注意点はDECODE関数によりデータを横方向に集計できる点です。

SQL> CREATE OR REPLACE VIEW TV_商品売上集計 AS
  2    SELECT TD.商品コード,TM.商品名,
  3           SUM(DECODE(TO_CHAR(TU.売上日,'MM'),'10',TD.売上数量 * TM.売上単価,0)) AS 売上10月,
  4           SUM(DECODE(TO_CHAR(TU.売上日,'MM'),'11',TD.売上数量 * TM.売上単価,0)) AS 売上11月,
  5           SUM(DECODE(TO_CHAR(TU.売上日,'MM'),'12',TD.売上数量 * TM.売上単価,0)) AS 売上12月
  6    FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
  7    WHERE TU.売上番号   = TD.売上番号 
  8    AND   TD.商品コード = TM.商品コード
  9    GROUP BY TD.商品コード,TM.商品名;

ビューが作成されました。

SQL> SELECT * FROM TV_商品売上集計;

商品コード 商品名                                     売上10月   売上11月   売上12月
---------- ---------------------------------------- ---------- ---------- ----------
         1 PC-9001                                           0     196000          0
         2 PC-9002                                           0     120000          0
         3 PC-9003                                           0     570000          0
         4 NOTE-1010                                         0     188000          0
         5 NOTE-1020                                         0     400000          0
         6 NOTE-1030                                         0     220000          0
         7 PRT-3001                                     176000     176000          0
         8 PRT-4001                                          0     720000     540000
         9 CRT-1001                                      78000     234000      78000
        10 CRT-2001                                          0     196000          0
        11 HUB-A001                                          0      40000          0

11行が選択されました。

ビューのデータ更新についての注意点

ビューは全てのものがデータの更新が可能ではなく、集合演算子、DISTINCT演算子、集計グループ関数、GROUP BY,ORDER BYなどを指定すると更新不可能になります。また、表結合を含んでいたり副問い合わせがある場合にも更新不可能です。
ここでどういったビューが更新可能かそうでないかを調べる方法を以下に示します。

SQL> INSERT INTO TV_売上明細
  2   SELECT * FROM TV_売上明細
  3   WHERE 1=2;
INSERT INTO TV_売上明細
*
1行でエラーが発生しました。
ORA-01776: 結合ビューを介して複数の実表を変更できません


SQL> INSERT INTO TV_商品
  2   SELECT * FROM TV_商品
  3   WHERE 1=2;

0行が作成されました。

このINSERT文は、自分自身のビューに対して全てのデータを追加する様に見えますが、WHERE 1=2の条件により実際の処理は行われません。しかし、ビューが更新可能でなければエラーが返されます。





ページのトップへ戻る