【SQL】视图

视图解释

基于 SQL 语句的结果集的可视化的表，包含行和列，就像一个真实的表，其中的字段就是来自一个或多个真实表中的字段。

视图分类：简单视图、复杂视图

特性	简单视图	复杂视图
表的数目	一个	一个或多个
包含函数	无	有
包含数据分组	无	有
通过视图进行DML操作	是	不一定

创建语法

CREATE [OR REPLACE][FORCE|NOFORCE] VIEWview_name [(alias[,alias]...)]

AS subquery

[WITH CHECK OPTION[CONSTRAINTconstraint_name]]

[WITH READ ONLY[CONSTRAINTconstraint_name]];

子句	含义
OR REPLACE	如果视图已经存在则重新创建
FORCE	创建视图，不管基表是否存在（如果不存在，会提示“编译错误”）
NOFORCE	只在基表存在的情况下创建视图（默认值）
view_name	视图的名称
alias	为由视图查询选择的表达式指定名字（别名的个数必须与由视图选择的表达式的个数匹配）
subquery	是一个完整的SELECE语句（SELECT列表中的字段可以使用别名）
WITH CHECK OPTION	指定只有可访问的行在视图中才能被插入或修改
CONSTRAINT	CHECK OPTION约束的名字
WITH READ ONLY	确保在该视图中没有DML操作被执行

1） 创建简单视图

SQL> CREATE OR REPLACE VIEW VW_DEPT AS SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPARTMENTS;

View created.

2） 创建复杂视图

SQL> CREATE OR REPLACE VIEW VW_EMPLOYEES AS
  2  SELECT EMPLOYEE_ID,LAST_NAME,EMAIL,to_char(HIRE_DATE,'yyyy-mm-dd') as HIRE_DATE,JOB_ID
  3  FROM EMPLOYEES;

View created.

3） 非空字段不包含在视图中，则无法做INSERT操作

SQL> CREATE OR REPLACE VIEW VW_EMP AS
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,JOB_ID
FROM EMPLOYEES;

View created.
##基表EMPLOYEES表中的EMAIL字段是非空字段，但却没有包含在视图中，当做INSERT操作时会报错。这是因为当我们做视图INSERT操作时，其实是对包含在视图中的基表的列做INSERT,而不包含在视图中的列默认插入了空值或者默认值。基表中的EMAIL列是非空的，但却没有对其插入数据，这样就违反了NOT NULL约束，故报错。

SQL> INSERT INTO VW_EMP VALUES (5611,'lgr',8000,'SA_REP');
INSERT INTO VW_EMP VALUES (5611,'lgr',8000,'SA_REP')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

4） WITH CHECK OPTION

SQL> CREATE OR REPLACE VIEW VW_DEPT AS
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID<=100
WITH CHECK OPTION;  

View created.

##插入不满足WHERE条件的数据，提示违反了CHECK子句

SQL> INSERT INTO VW_DEPT VALUES(1000,'LGR');
INSERT INTO VW_DEPT VALUES(1000,'LGR')
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

##但插入满足条件的数据，可以成功插入

SQL> INSERT INTO VW_DEPT VALUES (1,'LGR');

1 row created.

5） WITH READ ONLY

##创建只图视图

SQL> CREATE OR REPLACE VIEW VW_DEPT1 AS
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID<=100
WITH READ ONLY;  

View created.

##当做DML操作时报错

SQL> INSERT INTO VW_DEPT1 VALUES (2,'lgr');
INSERT INTO VW_DEPT1 VALUES (2,'lgr')
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

6） 删除视图

SQL> DROP VIEW VW_DEPT1;

View dropped.