--================================
-- SQL 基础 --> 视图 (CREATE VIEW)
--================================
视图：
从表中抽出来的逻辑上相关的数据集合
视图其实就是一条查询 SQL 语句，用于显示一个或多个表或其它视图中相关数据。
视图将查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表
视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束，并同样会触发定义
在基表上的触发器。 ( Oracle 支持在视图上显式的定义触发器和定义一些逻辑约束）
使用视图的好处：
可把复杂的 SQL 语句简单化
可保证数据的安全性，限制对数据的访问，因为它对表中的一些字段是隐藏的
可使相同的数据以不同形式出现在不同的视图中
视图分类：
简单视图
只从一个表中获取数据
不包含函数
不包含分组数据
可通过该视图进行 DML 操作
复杂视图
从多个表中获取数据
包含函数
包含分组数据
不一定能通过视图进行 DML 操作
创建视图：
CREATE [OR REPLACE] ［ FORCE ｜ NOFORCE ］ VIEW view_name [alias [,alias] ... ]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY]
OR REPLACE 如果视图存在，重建、修改这个视图
FORCE 不管引用的表是否存在，都创建这个视图
NOFORCE 只有当表存在的时候，才能创建这个视图（默认方式）
WITH CHECK OPTION 只有子查询能够检索出的行才能够被插入 , 修改 , 或删除。默认情况下对此不作检查
没有指定约束名，系统会自动为约束命名，形式为 SYS_Cn 。
WITH READ ONLY 只读 , 不可对视图做 DML 操作
需要注意的是，在子查询中不能包含 ORDER BY , 子查询可以是复杂的 SELECT 语句
修改视图
使用 CREATE OR REPLACE VIEW 子句修改视图
删除视图：
DROP VIEW view_name
视图中使用 DML 的规定：
当视图定义中含有以下元素之一不能使用 INSERT
组函数
GROUP BY 、 ORDER BY
DISTINCT
ROWNUM
列的定义为表达式
表中非空的列，在视图定义中未包括
视图定义含有以下元素不能使用 UPDATE
组函数
GROUP BY 、 ORDER BY
DISTINCT
ROWNUM
列的定义为表达式
视图包含以下元素不能 DELETE
组函数
GROUP BY 、 ORDER BY
DISTINCT
ROWNUM
与视图有关的数据字典：
DBA ＿ VIEWS
USER_VIEWS
-- 演示创建视图
SQL > create or replace view vw_emp as
2 select empno , ename , sal from emp where sal > 2500 ;
View created .
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
SQL > update vw_emp set sal = 3500 where ename = 'FORD' ; -- 可以更新并且直接修改了基表
1 row updated .
SQL > select * from emp where ename = 'FORD' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03 - DEC - 81 3500 20
SQL > insert into vw_emp select 9999 , 'Robinson' , 6000 from dual ; -- 可以插入并且直接修改了基表
1 row created .
SQL > select * from emp where empno = 9999 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9999 Robinson 6000
1 rows selected .
SQL > delete from emp where ename = 'Robinson' ; -- 可以删除并且直接修改了基表
1 row deleted .
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3500
SQL > rollback ;
Rollback complete .
-- 使用 with check option
-- 使用 WITH CHECK OPTION 子句确保 DML 只能在特定的范围内执行，任何违反
--WITH CHECK OPTION 约束的请求都会失败
SQL > create or replace view vw_emp
2 as
3 select empno , ename , sal from emp where sal > 2500
4 with check option ;
View created .
SQL > insert into vw_emp select 9999 , 'Robinson' , 2000 from dual ; -- 不满足条件 sal > 2500 不可插入
insert into vw_emp select 9999 , 'Robinson' , 2000 from dual
*
ERROR at line 1 :
ORA - 01402 : view WITH CHECK OPTION where - clause violation
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
SQL > delete from vw_emp where empno = 7902 ; -- 满足条件 sal > 2500 可以删除 , 并且修改了基表
1 row deleted .
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
SQL > select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17 - DEC - 80 800 20
7499 ALLEN SALESMAN 7698 20 - FEB - 81 1600 300 30
7521 WARD SALESMAN 7698 22 - FEB - 81 1250 500 30
7566 JONES MANAGER 7839 02 - APR - 81 2975 20
7654 MARTIN SALESMAN 7698 28 - SEP - 81 1250 1400 30
7698 BLAKE MANAGER 7839 01 - MAY - 81 2850 30
7782 CLARK MANAGER 7839 09 - JUN - 81 2450 10
7788 SCOTT ANALYST 7566 19 - APR - 87 3000 20
7839 KING PRESIDENT 17 - NOV - 81 5000 10
7844 TURNER SALESMAN 7698 08 - SEP - 81 1500 0 30
7876 ADAMS CLERK 7788 23 - MAY - 87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03 - DEC - 81 950 30
7934 MILLER CLERK 7782 23 - JAN - 82 1300 10
13 rows selected .
SQL > rollback ;
Rollback complete .
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
SQL > update vw_emp set sal = 2000 where empno = 7499 ; -- 不满足条件 , 不能更新
0 rows updated .
--WITH READ ONLY 屏蔽 DML 操作
-- 可以使用 WITH READ ONLY 选项屏蔽对视图的 DML 操作
-- 任何 DML 操作都会返回一个 Oracle server 错误
SQL > create or replace view vw_emp
2 as
3 select empno , ename , sal from emp where sal > 2500
4 with read only ;
View created .
SQL > insert into vw_emp select 9901 , 'Robinson' , 3000 from dual ;
insert into vw_emp select 9901 , 'Robinson' , 3000 from dual
*
ERROR at line 1 :
ORA - 01733 : virtual column not allowed here
SQL > select * from vw_emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
SQL > update vw_emp set sal = 8000 where empno = 7902 ;
update vw_emp set sal = 8000 where empno = 7902
*
ERROR at line 1 :
ORA - 01733 : virtual column not allowed here
SQL > delete from vw_emp where empno = 7566 ;
delete from vw_emp where empno = 7566
*
ERROR at line 1 :
ORA - 01752 : cannot delete from view without exactly one key - preserved table
-- 创建复杂视图
SQL > create view vw_sum_emp ( name , minsal , maxsal , avgsal )
2 as
3 select dname , min ( e . sal ), max ( e . sal ), avg ( e . sal )
4 from emp e
5 join dept d
6 on e . deptno = d . deptno
7 group by dname ;
View created .
SQL > select * from vw_sum_emp ;
NAME MINSAL MAXSAL AVGSAL
-------------- ---------- ---------- ----------
ACCOUNTING 1300 5000 2916.66667
RESEARCH 800 3000 2175
SALES 950 2850 1566.66667
-- 查询与视图有关的数据字典
SQL > select view_name , text from user_views ;
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
VW_SUM_EMP select dname , min ( e . sal ), max ( e . sal ), avg ( e . sal )
from emp e
join dept d
on e . deptno = d . deptno
group by dname
VW_EMP select empno , ename , sal from emp where sal > 2500
with read only
更详细的创建视图的语法：
更多参考：
使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 实例
Oracle 实例和 Oracle 数据库 (Oracle 体系结构 )
