34章 ビュー
それでは今回はビューオブジェクトについて学んでいきましょう。
ビューとは表と同じように使われており、ビューに対してSQL文を実行することができます。
表は実データを格納しますが、ビューに関してはデータはありません。
ビューを通して表にアクセスするようなイメージです。
こんなものなぜ必要なのかと思われるかもしれません。
以下の様な場合はメリットがあります。
・対象カラムだけを表示させたい
・結合などの複雑なSELECT文を簡略化したい
1つ目の対象のカラムだけ表示させたいというのは、セキュリティの観点で有効です。
例えば従業員情報などを格納する表には給料や住所など個人情報も多く含まれます。
このような場合、個人情報は伏せてその他の情報は
公開させたい場合はビューを使うメリットがあります。
また結合など複雑なSQL文も一旦ビューを作成してしまえば、
SELECT * FROM ビュー名;
で結合した表の情報を取得することが可能です。
それではビューを作成する構文から見ていきましょう。
[構文] ビューの作成
CREATE VIEW ビュー名
AS SQL文;
ビューを作成するためには上記の構文を使用し、AS以下にSQL文を記載します。
このSQLには表示させたい列を指定することで、見せたい列だけを表示することができます。
ビューに問い合わせを行う場合は、表と同様です。
SELECT * FROM ビュー名;
上記のコマンドでCREATE VIEW構文で記載したSQL文の結果を表示することができます。
では実行例を確認していきましょう。
SQL> CREATE VIEW emp_view as
2 select emp_id,last_name from employees;
View created.
SQL> desc emp_view
Name Null? Type
------------------ -------- ----------------------------
EMP_ID NOT NULL NUMBER(10)
LAST_NAME VARCHAR2(30)
SQL> select * from emp_view;
EMP_ID LAST_NAME
---------- ------------------------------
1 Hiroshi
2 Takashi
3 Keiko
4 yoko
5 Sawa
6 Tomonori
7 Osamu
…
20 rows selected.
実行例では、emp_viewという名前でビューを作成しています。
そのSELECT文はemp_id,last_name列を指定しています。
ビュー作成後、FROM句をビュー名にしSELECT文を実行しています。
SELECT句は*(アスタリスク)を指定しているので、
ビュー作成時に指定した列全てが表示されます。
SELECT句で指定できる列は、CREATE VIEWコマンド実行時に
指定したSELECT句の列のみとなります。
そのため、以下の実行例はエラーとなります。
SQL> select emp_id,salary from emp_view;
select emp_id,salary from emp_view
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
上記例ではemp_id,salary列を指定してビューをSELECTしていますが、
salary列はビュー作成時に指定していない為、
「salary列は存在しない」と怒られてしまいます。
このように見せたい列だけを指定しビューを作成して、
一般ユーザにはビューを参照させることにより、
セキュリティを高めることができるのです。
また複雑な結合処理も一旦ビューを作成してしまえば簡単にアクセスができます。
SQL> create view emp_dept_view
2 as
3 select e.emp_id,e.first_name,d.dept_name
4 from employees e join departments d
5 on e.dept_id = d.dept_id;
View created.
SQL> select * from emp_dept_view;
EMP_ID FIRST_NAME DEPT_NAME
---------- --------------------- ----------------------
2 Sato Consulting
3 Yamamoto Human Resources
4 Watanabe Human Resources
5 Watanabe Human Resources
6 Kataoka Engineering
7 Harada Engineering
…
18 rows selected.
これならSQL文が得な人でなくても簡単に結合した表を確認することができますね。
また計算式を含めることも可能です。
SQL> create view emp_sal_view as
2 select first_name,salary/12 from employees;
select first_name,salary/12 from employees
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
エラーになりました。
原因は計算式に列別名を使用していない為です。
SQL> create view emp_sal_view as
2 select first_name,salary/12 month_sal from employees;
View created.
別名を指定することでエラーなく作成できました。
SQL> select * from emp_sal_view;
FIRST_NAME MONTH_SAL
------------------------------ ----------
Suzuki 833333.333
Sato 666666.667
Yamamoto 416666.667
Watanabe 500000
Watanabe 458333.333
Kataoka 541666.667
Harada 516666.667
…
20 rows selected.
上記のように、ビューは表と同じようにSQL文を使用できます。
またSELECT * でビューからデータを取得していますが、
先ほど、ビューを作成するときに指定したSELECT文が実行されているため、
FIRST_NAME列とSALARY/12の結果が表示されていることがわかります。
ビューは実体がないのに対し、テーブルのコピーは実体があります。
ビューはCREATE VIEWコマンドのFROMに記載された表に対しアクセスしに行きます。
その表を実表と言います。
ビューというオブジェクトの中にSQL文を格納し、そのビューをSELECTすると、
そのSQLを実行するイメージです。表と使い方は似ていますが、全然違いますね。
ビューに対するDML文の制約について
またビューに対してDML文も実行可能です。ただ、ビューにDML文を実行するわけではなく、
ビューを通して実表に対してDMLを実行します。
ただし、以下の制限事項があります。
ビューに対するDMLの制限事項
・DISTINCT,GROUP BYまたはグループ関数が使用されている場合、
ビューからの挿入、更新、削除は実行できない
・ビューに計算式を使用している場合は行の挿入、更新は実行できない
・DEFAULT値を持たないNOT NULL列がビューの列で
省略されている場合は挿入ができない
・WITH CHECK OPTIONがビューに定義されており、
そのビューから参照できない値は挿入、更新ができない
INSERT時に指定しなかった列はNULLが挿入されることを覚えているでしょうか。
ビューも同様でビューにINSERTを実行すると、
ビューで指定されていない列にはNULL値が挿入されます。
その為、NULLが挿入される列に対して、NOT NULL制約が定義されているとエラーにります。
次に「WITH CHECK OPTION」は制約の一種です。
このオプションはビューを作成するときに使用できる制約の一種となります。
例えば以下の様なビューを作成するとします。
SQL> CREATE VIEW emp_view AS
2 SELECT emp_id, dept_id, first_name
3 FROM employees
4 WHERE dept_id = 10;
View created.
このビューはdept_idが10のみを表示するビューとなります。
もし、dept_idが10以外に変更されてしまうと、
このビューは参照できるレコードが存在しなくなってしまいます。
この時にオプションを付けると、dept_idを10以外に変更するような更新があった場合は、
SQLをエラーにすることができるのです。
SQL> CREATE VIEW emp_view AS
2 SELECT emp_id, dept_id,first_name
3 FROM employees
4 WHERE dept_id = 10
5 WITH CHECK OPTION;
View created.
SQL> select * from emp_view;
EMP_ID DEPT_ID FIRST_NAME
---------- ---------- ------------------------------
3 10 Yamamoto
4 10 Watanabe
SQL> update emp_view set dept_id = 20 where emp_id in (3,4);
update emp_view set dept_id = 20 where emp_id in (3,4)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
ただし、実表に対しての直接UPDATE文は実行できます。
SQL> update employees set dept_id = 10 where emp_id in (3,4);
2 rows updated.
WITH CHECK OPTIONはビューに対し、定義できる制約となります。
またもう一つWITCH READ ONLYというオプションがあります。
こちらはビューに対してDML操作を禁止したい場合に使用します。
SQL> CREATE VIEW emp_view AS
2 SELECT emp_id, dept_id,first_name
3 FROM employees
4 WHERE dept_id = 10
5 WITH READ ONLY;
View created.
SQL> update emp_view set dept_id = 20 where emp_id in (3,4);
update emp_view set dept_id = 20 where emp_id in (3,4)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
以上がビューに対するDML操作についてのご紹介となります。
最後にビューを削除する方法です。
[構文] ビューの削除
DROP VIEW ビュー名;
AS SQL文;
ビューを削除しても、実表には影響はありません。
以上がビューのご紹介となります。いかがでしたでしょうか。
ビューとは実体のないものなので理解が難しいところではありますが、
実行例を見ることである程度は理解できたのではないでしょうか。