ORACLE_OCP之视图VIEW

ORACLE_OCP之视图VIEW

• 视图不光是ORACLE的概念，在所有的数据库中基本都包含了视图这个数据对象。

一、什么是视图？

• 视图是在逻辑上代表来自一个和多个表的数据子集[结果集]。他和同义词一样都是一种映射关系。但是视图的功能和在数据库中的地位要远远的高于同义词，是ORACLE和其他数据库中一个重要的数据对象概念。

二、为什么我们要使用视图？

• 1 限制数据访问
• 2 使复杂的查询变得更容易 将复杂查询的结果变成视图 或者 将复杂查询要查询的条件提前视图化。
• 3 提供数据的对立性 我不用什么都去访问表 可以先访问视图 确定以后再去访问表 减少对表的不必要访问
• 4 提供相同数据但是不同的视图[范围]

三、视图的种类

备注	简单视图	复杂视图
表的数量	一个	一个或者多个
包含函数	不	包含
是否分组	不	包含
是否支持DML	是	不一定

四、视图操作

• 建立一个简单的视图：

CREATE VIEW EMPVW1 AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;
CREATE VIEW EMPVW2 AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 ANNSAL FROM EMP1;
DESC EMPVW1;
DESC EMPVW2;
SELECT * FROM EMPVW1;
SELECT * FROM EMPVW2;

• 创建视图还有一个语句

CREATE OR REPLACE VIEW EMPVW1
(ID,NAME,SAL)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;

• 使用视图访问数据：当我们使用视图访问数据时，ORACLE将做以下操作：

  • 1 他从USER_VIEWS 数据字典中检索视图的定义

  SELECT * FROM USER_VIEWS;
  

  • 2 他检查视图基表的访问权限。对于EMPVW1和EMPVW2来说EMP1就是视图的基表，也就是说创建视图所用到的表，我们称之为视图的基表。
  • 3 他将视图查询装换成对基表的等同操作。也就是说数据依然从基表中检索，或对基本进行相关的DML操作。

• 创建以后通过查询我们可以看到新增了别名，那我们别名有两种修改方式

  • 1 SELECT语句后面加入相关的别名
  • 2 通过CREATE OR RELPLACE方式修改
    • 栗子：

    CREATE OR REPLACE VIEW EMPVW1
    (ID,NAME,SAL)
    AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;

• 创建一个复杂的视图：

CREATE OR REPLACE VIEW EMPVWF1
(NAME ,MINSAL,MAXSAL,AVGSAL)
AS SELECT D.DEPARTMENT_NAME,MIN(E.SALARY),MAX(E.SALARY),AVG(E.SALARY)
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;

• 视图上执行DML操作规则

  • 1 可以在简单视图上执行DML操作

  • 2 如果视图包含了以下内容，则不能发生相关DML操作

    • 分组函数
    • GROUP BY
    • DISTINCT
    • 伪劣ROWNUM等
    • 有表达式定义的列
    • 基表中未被视图选中的NOT NULL的列

  • 例子

  select * from empvw1;
  select * from emp1 where employee_id = 100;
  update empvw1 set name=‘MONKEY KING’ where id = 100;
  select * from empvw1;
  rollback;

• 对视图加入两种相关的约束：

  • 未加约束的

  CREATE OR REPLACE VIEW EMPVW3
  (ID,NAME,SAL,DEPTNO)
  AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20;
  select * from emp1;

  • 加了约束的

  CREATE OR REPLACE VIEW EMPVW4
  (ID,NAME,SAL,DEPTNO)
  AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20
  WITH CHECK OPTION CONSTRAINT EMPVW4_CK20_1;
  //加了约束之后，不能修改
  update empvw4 set NAME=‘FAY11’ WHERE ID=202; —修改报错
  SELECT * FROM EMPVW4;

  • 我们可以发现，加了约束条件之后，我们就没有办法去修改DEPTNO,但是其他可以照常修改，也就是说我们加了WITH CHECK OPTION CONSTRAINT以后，就没有办法修改创建视图时where子句之后的条件字段了。

• 彻底不让你修改：

  • WITH READ ONLY 将视图变成只读视图 拒绝所有修改！

  CREATE OR REPLACE VIEW EMPVW5
  (ID,NAME,SAL,DEPTNO)
  AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20
  WITH READ ONLY;

• 删除视图

DROP VIEW 视图名;
DROP VIEW EMPVW5;

• 内联视图

  • 1 内联视图是带有可以在SQL语句中使用的别名[相关名称]的子查询
  • 2 主查询FROM 子句中的命名子查询就是一个内联视图实例
  • 3 内联视图不是数据对象
  • 内联视图小DEMO

  SELECT A.LAST_NAME ,A.SALARY,A.DEPARTMENT_ID ,B.MAXSAL
  FROM EMPLOYEES A,
  (SELECT DEPARTMENT_ID,MAX(SALARY) MAXSAL FROM EMPLOYEES GROUP BY DEPARTMENT_ID) B
  WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
  AND A.SALARY < B.MAXSAL;

• 排序TOP-N分析

  • 显示EMPLOYEES表中收入最高的三个人的工资的信息

  SELECT ROWNUM AS RANK,LAST_NAME,SALARY
  FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC)
  WHERE ROWNUM <= 3;