SQL基础--视图
视图其实就是一条查询SQL语句，用于显示一个或多个表或其它视图中相关数据。
创建视图：
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 v_test as select empno,ename,hiredate,sal from emp where sal<1000;
如果提示权限不足需要赋权：grant create view to scott;
--查询刚创建的视图
SQL> select * from v_test; EMPNO ENAME HIREDATE SAL ---------- ---------- -------------- ---------- 7369 SMITH 17-12月-80 800 7900 JAMES 03-12月-81 950
--在基于单表的视图上执行DML操作
SQL> insert into v_test(empno,ename,hiredate,sal) values(6688,'Keven',to_date('20170101','YYYY-MM-DD'),850); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from v_test; EMPNO ENAME HIREDATE SAL ---------- ---------- -------------- ---------- 7369 SMITH 17-12月-80 800 7900 JAMES 03-12月-81 950 6688 Keven 01-1月 -17 850 SQL> select * from emp where empno=6688; ###可以看出基表被直接修改 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 6688 Keven 01-1月 -17 850 SQL> delete from v_test where empno=6688; 已删除 1 行。 SQL> commit; 提交完成。 SQL> select * from v_test; EMPNO ENAME HIREDATE SAL ---------- ---------- -------------- ---------- 7369 SMITH 17-12月-80 800 7900 JAMES 03-12月-81 950 SQL> select * from emp where empno=6688; 未选定行
--基于多表的复杂视图的DML操作
SQL> create or replace view v_test2 as select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and d.deptno=20; 视图已创建。 SQL> select * from v_test2; ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH SCOTT 20 RESEARCH ADAMS 20 RESEARCH FORD 20 RESEARCH SQL> insert into v_test2(ename,deptno,dname) values('Stone',20,'RESEARCH'); ##不能进行更新和插入的操作 insert into v_test2(ename,deptno,dname) values('Stone',20,'RESEARCH') * 第 1 行出现错误: ORA-01776: 无法通过联接视图修改多个基表 SQL> delete from v_test2 where deptno=20; 已删除5行。 SQL> select * from emp where deptno=20; ###emp中对应的数据被删除了 未选定行 SQL> select * from dept; ##dept中的数据并没有被删除 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
总结：基于多表的复杂视图在修改时无法正常完成，如果希望同时更新多张表的数据记录，可以采用替代触发器来完成；删除操作可以正常执行，但是会修改基表的数据。
--使用with check option
--使用WITH CHECK OPTION 子句确保DML只能在特定的范围内执行，任何违反
--WITH CHECK OPTION 约束的请求都会失败
SQL> create or replace view v_test as select empno,ename,sal from emp where sal<1000 with check option; 视图已创建。 SQL> insert into v_test(empno,ename,sal) values(6688,'Keven',1650); insert into v_test(empno,ename,sal) values(6688,'Keven',1650) * 第 1 行出现错误: ORA-01402: 视图 WITH CHECK OPTION where 子句违规 ##--不满足条件sal < 1000 不可插入
--WITH READ ONLY 屏蔽DML 操作
--可以使用WITH READ ONLY 选项屏蔽对视图的DML操作
--任何DML 操作都会返回一个Oracle server 错误
SQL> create or replace view v_test as select empno,ename,sal from emp where sal<1000 with read only; 视图已创建。 SQL> insert into v_test(empno,ename,sal) values(6688,'Keven',1650); insert into v_test(empno,ename,sal) values(6688,'Keven',1650) * 第 1 行出现错误: ORA-42399: 无法对只读视图执行 DML 操作
--查询与视图有关的数据字典
user_views
SQL> select view_name,text from user_views; VIEW_NAME TEXT ------------------------------ -------------------------------------------------------------------------------- V_TEST select empno,ename,sal from emp where sal<1000 with read only V_TEST2 select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno
tab
SQL> select * from tab where tabtype='VIEW'; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- V_TEST2 VIEW V_TEST VIEW
