视图简介
视图是表的另一种表示形式,它通过使用select语句定义一个视图所需显示数据的虚表,这个虚表只有对视图的定义,并不包含实际的数据。可以说视图是在表的基础上用来展现数据的一种方式。它通过select语句使用来自一个或多个表中的数据创建逻辑表,只是一个“存储的查询”。在创建试图时,只是将视图的定义信息保存到数据字典中,并不将实际的数据复制到任何地方
--建立view_dept_e视图
CREATE OR REPLACE VIEW VIEW_DEPT_E
AS
SELECT
SCOTT.EMP.EMPNO,
SCOTT.EMP.ENAME,
-- HR.EMP.JOB,
--HR.EMP.MGR,
SCOTT.DEPT.DNAME,
SCOTT.DEPT.LOC
FROM SCOTT.EMP, SCOTT.DEPT
WHERE SCOTT.EMP.DEPTNO = SCOTT.DEPT.DEPTNO;
查询视图
select * from view_dept_e;
创建视图
视图按照其是否设计DML操作,又可分为两类
- 简单视图:视图的数据仅来自一个表,在视图的select语句中不包含函数或数据分组,总是可以通过视图来执行DML操作
- 复杂视图:视图的数据来自多表,可以包含函数或数据分组,并不总是可以通过视图进行DML操作
简单视图
简单视图是指基于单个表建立的,不包括任何函数、表达式和分组数据视图。例如可以根据EMP表中部门编号为20的员工创建一个视图
CREATE OR REPLACE VIEW v_deptemp AS
SELECT empno,ename,job,mgr,hiredate,comm
FROM scott.emp WHERE deptno=20;
查询视图
select * from v_deptemp;
如果需要限制向视图中插入数据,可以使用WITH CHECK OPTION选项来定义CHECK约束。如果在视图上执行INSERT、UPDATE和DELETE 语句,就要求所操作的数据必须是SELECT查询所能选择出来的数据
CREATE OR REPLACE VIEW v_deptemp_check AS
SELECT empnp,ename,job,mgr,hiredate,sal,comm,deptno
FROM scott.emp WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT v_empdept_chk;
如果想禁止在视图上执行INSERT、UPDATE或DELETE操作,可以使用WITH READ ONLY选项。
CREATE OR REPLACE VIEW v_deptemp_readonly AS
SELECT empno,ename,job,mgr,hiredate,sal,comm
FROM SCOTT.EMP WHERE deptno = 20
WITH READ ONLY;
可以通过为视图指定别名来提供更加友好的视图名称,
CREATE OR REPLACE VIEW v_deptemp_alias
( 员工编号,
员工名称,
职位,
经理,
雇佣日期,
薪水,
备注 )
AS
SELECT empno,ename,job,mgr,hiredate,sal,comm
FROM scott.emp WHERE deptno = 20;
复杂视图
复杂视图中可以包含来自多个表的数据,可以包含函数或分组等。
CREATE OR REPLACE VIEW v_sumdept(部门名称, 部门薪资)
AS SELECT dept.dname, SUM(emp.sal) sumsal
FROM emp,dept WHERE emp.deptno=dept.deptno(+)
GROUP BY dept.danme;
这个复杂视图中既包含了表间的连接,也包含了聚合函数来进行聚合操作。
修改视图
修改视图并不会对基础的表进行修改,所做的只是改变数据字典中对该视图的定义信息,视图的基础对象不会受到任何影响
- 由于视图只是虚表,因此对视图的更改不会影响底层的基础表
- 如果是途中具有WITH CHECK OPTION 选项,但是定义时没有使用WITH CHECK OPTION选项,则以前的此选项将自动删除
- 更改试图后,依赖于该视图的所有视图和PL/SQL程序都将变成INVALID状态
- 更新基础表后,视图会失效,可以对视图进行重编译使视图有效
实际上当在CREATE语句中使用了OR REPLACE关键字后,就可以随时对视图进行更改,这种方法先删除原来的视图,然后创建了一个新的视图取代原有的视图,同时会保留在该视图上授予的各种权限,但是与该视图相关的存储过程和视图会失效
CREATE OR REPLACE VIEW v_deptemp_check
AS SELECT empno,ename,job,mgr,hiredate,dal,comm,deptno
FROM emp WHERE deptno = 30;
因为去掉了WITH CHECK OPTION 约束,所以可以向v_deptemp_check视图中插入不在SELECT列表中的信息。
当视图的基础表发生改变后,视图会变成失效状态,Oracle会在视图被访问时自动重新编译这些视图,也可以通过ALTER语句显式的重新编译视图。当视图重新编译后,依赖该视图的对象会失效
注:ALTER VIEW 语句仅能重新编译视图,要修改视图的定义,需要使用CREATE OR REPLACE VIEW语句
删除视图
当视图不再需要时,可以使用DROP VIEW 语句对视图进行删除,如果要删除的视图在其他的方案中,则需要具备DROP ANY VIEW系统权限
当视图被删除后,视图的定义会从数据字典中删除,在视图上授予的权限也被删除,同时其他引用该视图的视图及存储过程都会失效