Oracle12C--视图（十九）

知识点的梳理：

• 使用视图进行单表更新时，会影响视图中指定的数据。而删除操作也会影响原始表的数据；
  • 视图在进行多表更新操作时，因涉及到多表，所以都会出现异常，只有删除操作可以正常执行。同样会影响原始表的数据；
  • 可以通过触发器，对视图进行多表的更新操作：
• 删除视图语法：drop view视图名称;
• 视图本身不属于真实数据，所以在创建视图时，利用with read only 设置为只读视图；

    

• 为什么需要视图
  • 视图可以实现对复杂sql语句的封装；
  • 限制数据访问；
• oracle实体 -- 视图
  • 视图是Oracle数据库对象 , 可以把视图当做一张数据库 虚拟表对待 , 数据库只存放视图的定义，而不存放视图对应的数据。这些数据仍然在原来的实体表中。所以实体表中的数据发生变化，视图中查询出的数据也随之改变；
    • 
• 创建视图
  • 语法：

create [force | noforce] [or replace] view视图名称 [(别名1，别名2,.....)] as 子查询;

语法参数解析： force：表示要创建视图的表，不存在也可以创建视图； noforce：(默认)表示要创建视图的表必须存在，否则无法创建； or replace：表示视图的替换。如果创建的视图不存在，则创建新的视图；如果视图已经存在，则将其替换；

示例：如果出现权限不足的问题看这里：创建视图时出现权限不足 CREATE VIEW v_myview AS SELECT * FROM emp WHERE sal>2000 ;

示例：查看视图是否创建成功 select * from tab where tabtype = 'VIEW'; --VIEW必须是大写 示例2：第2种查看视图的方式，通过user_view数据字典 SELECT view_name,text_length,text FROM user_views ;

示例：通过视图来执行查询语句 select * from v_myview;

• OR REPLACE参数
  • 使用此参数后，如果视图不存在则直接创建，如果视图存在则使用新的子查询替换已有的视图；
  • 示例：通过or replace，替换v_myview视图

CREATEOR REPLACE VIEW v_myview AS SELECT d.deptno,d.dname,d.loc, COUNT(e.empno) count,NVL(ROUND(AVG(sal),2),0) avg,NVL(SUM(sal),0) sum, NVL(MAX(sal),0) max,NVL(MIN(sal),0) min FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ;

视图创建完毕之后，视图中的列名称是通过默认查询返回，如有需要也可以为每一个列起一个别名

示例：为视图中查询的列起别名 CREATE OR REPLACE VIEW v_myview (部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资) AS SELECT d.deptno,d.dname,d.loc, COUNT(e.empno) count,NVL(ROUND(AVG(sal),2),0) avg,NVL(SUM(sal),0) sum, NVL(MAX(sal),0) max,NVL(MIN(sal),0) min FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ;

• 在视图上执行DML操作
  • 单表映射数据
    • 先创建视图，并向该视图插入数据

示例：首先创建一个视图 CREATE OR REPLACE VIEW v_emp20 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=20 ;

步骤1：向v_emp20视图中增加一条新数据 INSERT INTO v_emp20(empno,ename,job,sal,deptno) VALUES (6688,'smith','CLERK',1900,20) ; COMMIT ;

效果：视图只包含了emp表的部分字段信息，所以在执行增加操作后，emp表只保存了部分的数据信息 查询v_emp20视图当前数据 SELECT * FROM v_emp20 ; 查询emp表中所有20部门雇员的详细信息 SELECT * FROM emp WHERE deptno=20 ;

• 对视图执行修改操作

示例：对视图执行修改操作，此操作和插入类似，都只会更新部分操作 UPDATE v_emp20 SET ename='MLDNJAVA',job='MANAGER',sal=2300 WHERE empno=6688 ; COMMIT ;

• 对视图执行删除操作，会影响原始数据表的数据

示例：删除v_emp20视图中的数据 DELETE FROM v_emp20 WHERE empno=6688 ; COMMIT ;

• 多表映射数据
  • 创建一个视图，要求显示所有20部门中的雇员编号、姓名、职位、基本工资、部门编号、部门名称、位置

CREATE OR REPLACE VIEW v_myview AS SELECT e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND d.deptno=20 ;

• 向v_myview增加一条数据

INSERT INTO v_myview (empno,ename,job,sal,deptno,dname,loc) VALUES (6688,'csdn','CLERK',2000,50,'教学','北京') ;

效果：会出现报错信息

• 修改v_myview视图中的数据

UPDATE v_myview SET ename='史密斯',sal=5000,dname='教学' WHERE empno=7369 ;

效果：报错信息提示

• 删除v_myview视图中的数据，同样也会影响原始表的数据

DELETE FROM v_myview WHERE empno=7369 ; --删除错误是可以正常执行的

• with check option子句
  • 如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。
  • 语法：使用该子句时，可以设置一个约束名称，如果不设置，系统将自动设置一个约束的名称；

create [force | noforce] [or replace] view 视图名称 [(别名1,别名2,....)]

• 示例：

create or replace view v_t3 as select id, name,salary from t3 where salary>=7000 with check option constraint v_t3_ck;

视图中的条件是salary>=7000，因此，我插入一个salary小于7000的行，将会报出错误：

insert into v_t3 values(5,'gyj5',1000); insert into v_t3 values(5,'gyj5',1000) * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

而插入一个大于7000的行，可以成功插入： insert into v_t3 values(5,'gyj5',7500);

• with read only子句
  • 视图添加该子句后，用户将无法使用该视图，对表进行delete,update,insert操作；
  • 语法

create [force | noforce] [or replace] view 视图名称 [(别名1,别名2,....)] as 子查询 [with check option [constraint 约束名称]] [with read only];

• 示例：

CREATE OR REPLACE VIEW v_emp20 AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY ;

如果此时使用该视图，进行更新操作

UPDATE v_emp20 SET ename='SMITH',comm=null WHERE empno=7369 ;

会提示如下错误：