18-Oracle入门之视图

视图是从表中抽出的逻辑上相关的数据集合。

基本概念

• 视图是一种虚表.
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
• 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
• 视图向用户提供基表数据的另一种表现形式

视图的优点

视图的优点 简化查询

1. 简化复杂查询 (封装 复杂sql语言)
2. 限制数据访问 银行系统视图呈现,而且只读视图, 通过存储过程和存储函数来做
3. 通过视图修改表中的数据,不建议,有很多限制
4. 提供数据的相互独立
5. 同样的数据，可以有不同的显示方式
6. 但视图不能提高性能

简单视图和复杂视图

注意：不建议通过视图对表进行修改

创建视图

• 是一个逻辑概念,本身没有数据,还是来源于表

• 使用下面的语法格式创建视图

  • FORCE： 子查询不一定存在
  • NOFORCE： 子查询存在（默认）
  • WITH READ ONLY:只能做查询操作

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view  [(alias[, alias]...)] AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]];

• 子查询可以是复杂的 SELECT 语句

• 应用：

CREATE VIEW     empvu80 AS SELECT  employee_id, last_name, salary    FROM    employees    WHERE   department_id = 80;View created.

create view empincomeview    as    select e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0) income, d.dname    from emp e, dept d    where e.deptno = d.deptno;

• 用户管理中/权限管理 需要管理员登录进行授权

sqlplus /as sysdbaSQL> grant create view to scott; --创建视图的权限给scott用户

描述视图结构

DESCRIBE empvu80

• 创建视图时在子查询中给列定义别名

CREATE VIEW     salvu50 AS SELECT  employee_id ID_NUMBER, last_name NAME,            salary*12 ANN_SALARY    FROM    employees    WHERE   department_id = 50;View created.

在选择视图中的列时应使用别名

查询视图

SELECT *FROM    salvu50;

修改视图

使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80  (id_number, name, sal, department_id)AS SELECT  employee_id, first_name || ' ' || last_name,            salary, department_id   FROM    employees   WHERE   department_id = 80;View created.

CREATE VIEW 子句中各列的别名应和子查询中各列相对应

创建复杂视图

复杂视图举例：查询各个部门的最低工资，最高工资，平均工资

CREATE VIEW dept_sum_vu  (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;View created.

视图中使用DML的规定

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

• 当视图定义中包含以下元素之一时不能使用delete:

  • 组函数
  • GROUP BY 子句
  • DISTINCT 关键字
  • ROWNUM 伪列

• 当视图定义中包含以下元素之一时不能使用update :
  *组函数

  • GROUP BY子句
  • DISTINCT 关键字
  • ROWNUM 伪列
  • 列的定义为表达式

• 当视图定义中包含以下元素之一时不能使用insert :

  • 组函数
  • GROUP BY 子句
  • DISTINCT 关键字
  • ROWNUM 伪列
  • 列的定义为表达式
  • 表中非空的列在视图定义中未包括

• 屏蔽 DML 操作

• 可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作

• 任何 DML 操作都会返回一个Oracle server 错误

CREATE OR REPLACE VIEW empvu10    (employee_number, employee_name, job_title)AS SELECT   employee_id, last_name, job_id   FROM     employees   WHERE    department_id = 10   WITH READ ONLY;View created.

删除视图

删除视图只是删除视图的定义，并不会删除基表
的数据.
基本套路是：DROP VIEW view;

DROP VIEW empvu80;View dropped.

视图注意点

• 视图 with check option

create view view1asselect * from emp where deptno=10with check option;insert into view1 values(***,***,...., 10);insert into view1 values(***,***,...., 20);

通过视图只能看到10号部门的员工信息,不能插入20号部门员工

• 视图with read only

create or replace view empincomeview2asselect e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0) income, d.dnamefrom emp e, dept dwhere e.deptno = d.deptnowith read only;

修改视图 视图只能替换,不能修改

• 结论:一般不通过视图做insert/update/delete操作,视图的目的:就是简化查询!