Oracle SQL ビュー
- クエリーの結果に集計関数を使用
ビューとは論理表とも言われ、実際の表を元にして選択された列をユーザにあたかも通常の表の様に見せる仕組みのことです。元の表は論理表に対して実表と呼ばれます。以下にビューの生成の構文を示しますが、これを見ればSELECT文が返す結果をビューとしていることが分かると思います。尚、ビューを生成するには該当するユーザ(スキーマ)にビュー生成の権限が必要です。
CREATE [ OR REPLACE ] <ビュー名> [ <ビュー列名リスト> ] AS <SELECT文>
私は大抵、"OR REPLACE"を指示してビューを生成しています。<ビュー列名リスト>はビューの列名を、SELECT文の結果の列名とは別の名前を付けたい場合に指定します。
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
簡単なビュー
以下に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行が選択されました。
表題は複雑なビューとしていますが、この様に２個の表のみを結合した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の条件により実際の処理は行われません。しかし、ビューが更新可能でなければエラーが返されます。
