Oracle DB视图_MySQL

bitsCN.com

Oracle DB视图

什么是视图

通过创建表的视图可以显示数据的逻辑子集或组合。视图是一种基于表或其它视图的逻辑表。视图没有自己的数据，但它如同一个窗口，通过它可以查看或更改表中的数据。视图所基于的表被称为基表。视图以SELECT 语句的形式存储在数据字典中。

视图的优点

• 由于视图可以显示表中的选定列，因而可以限制对数据的访问。

• 视图可用来通过进行简单查询来检索复杂查询的结果。例如，用户在不了解如何编写联接语句时，使用视图就可以查询多个表中的信息。

• 视图为特定用户和特定应用程序提供了数据独立性。一个视图可用来检索多个表中的数据。

• 通过视图，用户组可根据各自的特定标准访问数据。

简单视图和复杂视图

视图有两种分类：简单视图和复杂视图。这两类视图的基本差别与DML（INSERT 、UPDATE 和DELETE ）操作有关。

• 简单视图有如下特点：

- 只从一个表中获得数据

- 不包含函数或数据组

- 可以通过视图执行DML 操作

• 复杂视图有如下特点：

- 从多个表中获得数据

- 包含函数或数据组

- 不一定允许通过视图执行DML 操作

创建视图

• 在CREATE VIEW 语句中嵌入一个子查询：

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias [, alias ]...)]

AS subquery

[WITH CHECK O PTION [CONSTRAINT constraint ]]

[WITH READ ON LY [CONSTRAINT constraint ]];

• 子查询可以包含复杂的SELECT 语法。

可以通过在CREATE VIEW 语句中嵌入子查询来创建视图。

在该语法中：

OR REPLACE ：如果该视图已存在则重新创建

FORCE ：不管基表是否存在都创建视图

NOFORCE ：仅当基表存在时才创建视图（默认设置）

view ：是视图的名称

alias ：指定由视图查询选定的表达式的名称（别名的数量必须与视图选择表达式数量匹配）

subquery ：是一个完整的SELECT 语句（可以在SELECT 列表中使用列的别名

WITH CHECK OPTION： 指定只插入或只更新视图中可以访问的那些行

constraint： 是为CHECK OPTION约束条件指定的名称

WITH READ ONLY ：确保不对此视图执行DML 操作

• 创建视图EMPVU80，视图包含部门80 中每位雇员的雇员编号、姓氏和薪金：

hr@TEST0924> create view empvu80

2 as select EMPLOYEE_ID,LAST_NAME,SALARY from employees where EMPLOYEE_ID=80;

View created.

• 使用SQL*Plus DESCRIBE 命令描述视图的结构：

DESCRIBE empvu80

hr@TEST0924> desc empvu80

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)

LAST_NAME NOT NULL VARCHAR2(25)

SALARY NUMBER(8,2)

• 在子查询中使用列别名创建视图，可以通过将列别名包含在子查询中来控制列名：

hr@TEST0924> create view salvu50

2 as select EMPLOYEE_ID id_number,LAST_NAME name,SALARY*12 ann_salary from employees where EMPLOYEE_ID=50;

View created.

hr@TEST0924> desc salvu50

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ID_NUMBER NOT NULL NUMBER(6)

NAME NOT NULL VARCHAR2(25)

ANN_SALARY NUMBER

示例中创建了一个视图，其中包含部门50 中每位雇员的以下信息：别名为ID_NUMBER 的雇员编号( EMPLOYEE_ID ) 、别名为NAME 的姓名( LAST_NAME) 以及别名为ANN_SALARY的年薪( SALARY ) 。

你也可以在CREATE 语句之后和SELECT 子查询之前使用别名。列出的别名数量必须与在子查询中选定的表达式数量相匹配。

hr@TEST0924> CREATE OR REPLACE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)

2 AS SELECT employee_id, last_name, salary*12 FROM employees WHERE department_id = 50;

View created.

hr@TEST0924> desc salvu50

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ID_NUMBER NOT NULL NUMBER(6)

NAME NOT NULL VARCHAR2(25)

ANN_SALARY NUMBER

准则

• 定义视图的子查询可以包含复杂的SELECT 语法，其中包括联接、分组和子查询。

• 如果你没有为使用WITH CHECK OPTION创建的视图指定约束条件名称，系统则会以SYS_Cn 格式指定默认名称。

• 可以使用OR REPLACE选项更改视图的定义，而不必在删除该视图后再重新创建，也不必对其重新授予以前授予过的对象权限。

从视图中检索数据

你可以从视图中检索数据，就像从任何表中检索数据一样。可以显示整个视图的内容，也可以只显示特定的行或列。

hr@TEST0924> select * from salvu50;

ID_NUMBER NAME ANN_SALARY

---------- ------------------------- ----------

198 OConnell 31200

199 Grant 31200

...

45 rows selected.

修改视图

• 可以使用CREATE OR REPLACE VIEW子句修改视图EMPVU80。为每个列名添加一个别名：

hr@TEST0924> CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)

2 AS SELECT employee_id, first_name || ' '|| last_name, salary, department_id FROM employees WHERE department_id = 80;

View created.

CREATE OR REPLACE VIEW子句中列出的列别名与子查询中的列具有相同的顺序。

使用OR REPLACE选项，可以创建一个视图，甚至可以创建一个与已存在的视图同名的视图，以便替换旧版本的视图。这意味着可以更改视图，而不必经过删除、重新创建对象和重新授予对象权限的过程。

注：在CREATE OR REPLACE VIEW子句中指定列别名时，请注意别名的列出顺序应与子查询中列的顺序相同。

创建复杂视图

创建包含组函数的复杂视图以显示两个表中的值：

hr@TEST0924> create or replace view dept_sum_vu(name,minsal,maxsal,avgsal)

2 as select d.department_name,min(e.salary),max(e.salary),avg(e.salary) from employees e join departments d

3 on (e.department_id=d.department_id) group by d.department_name

4 /

View created.

示例中创建了一个复杂视图，其中包含按部门列出的部门名称、最低薪金、最高薪金和平均薪金。请注意，已为该视图指定了替代名称。如果视图中有任何列来自于函数或表达式，则需要使用替代名称。

可以使用DESCRIBE 命令查看视图的结构。通过发出SELECT 语句可显示视图的内容。

hr@TEST0924> select * from dept_sum_vu;

NAME MINSAL MAXSAL AVGSAL

------------------------------ ---------- ---------- ----------

Administration 4400 4400 4400

Accounting 8300 12008 10154

...

11 rows selected.

对视图执行DML 操作的规则

• 可以对整个视图中的数据执行DML 操作，但这些操作必须符合特定的规则,通常可以对简单视图执行DML 操作。

• 如果视图不包含以下任何内容，则可以从视图中删除行：

- 组函数

- GROUP BY 子句

- DISTINCT 关键字

- 伪列ROWNUM 关键字

对视图执行DML 操作的规则修改数据

如果视图包含以下内容，则不能修改视图中的数据：

• 组函数

• GROUP BY 子句

• DISTINCT 关键字

• 伪列ROWNUM 关键字

• 由表达式定义的列

可以修改视图中的数据，除非该视图包含上规则中提到的任何条件或由表达式定义的列（例如，SALARY * 12 ）。

对视图执行DML 操作的规则插入数据

如果视图包括以下内容，则不能向视图添加数据：

• 组函数

• GROUP BY 子句

• DISTINCT 关键字

• 伪列ROWNUM 关键字

• 由表达式定义的列

• 基表中未被视图选中的NOT NULL 列

可以向视图添加数据，除非视图包含此上列中列出的任何项。如果视图包含的NOT NULL列在基表中没有指定默认值，则不能向视图添加数据。在视图中必须显示所有需要

的值。请记住，您要通过视图将值直接添加到基表中。

使用WITH CHECK OPTION子句

• 使用WITH CHECK OPTION子句可确保对视图执行的DML 操作只在视图范围内起作用：

hr@TEST0924> create or replace view empvu20

2 as select * from employees where department_id=20

3 with check option constraint empvu20_ck

4 /

View created.

• 如果尝试使用INSERT语句插入department_id不为20 的一行，或者使用UPDATE语句更新视图中任何行的部门编号，则操作会失败，因为这违反WITH CHECK OPTION约束条件。

可以在视图中执行引用完整性检查。还可以在数据库级别强制实现约束条件。视图可以用来保护数据完整性，但此用途很有限。

WITH CHECK OPTION子句指定通过视图执行的INSERT和UPDATE不能创建该视图无法选择的行。因此，能够对要插入或更新的数据强制执行完整性约束条件和数据验证检查。如果尝试对视图未选中的行执行DML 操作，则会显示一条错误，还会显示约束条件名称（如果已指定）。

hr@TEST0924> update empvu20 set department_id=10 where employee_id=201;

update empvu20 set department_id=10 where employee_id=201

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

注：如果部门编号已更改为10，则该视图将无法看到此雇员，因此不会更新任何行。

所以，使用WITH CHECK OPTION子句时，该视图只能看到部门20 中的雇员，并且不允许通过该视图更改这些雇员的部门编号。

拒绝DML 操作

• 通过在视图定义中添加WITH READ ONLY选项可以确保不会执行DML 操作。

• 尝试对视图中的任何行执行DML 操作都会导致产生Oracle Server 错误。

hr@TEST0924> create or replace view empvu10(employee_number,employee_name,job_title)

2 as select employee_id,last_name,job_id from employees where department_id=10

3 with read only

4 /

View created.

尝试从具有只读约束条件的视图中删除任何行会导致产生错误：

hr@TEST0924> delete from empvu10 where employee_number=200;

delete from empvu10 where employee_number=200

*

ERROR at line 1:

ORA-42399: cannot perform a DML operation on a read-only view

与此类似，尝试使用具有只读约束条件的视图插入行或修改行会导致同样的错误。

删除视图

因为视图是基于数据库中的基表建立的，所以删除视图不会导致丢失数据。

hr@TEST0924> drop view empvu80;

View dropped.

使用DROP VIEW语句可删除视图。该语句会从数据库中删除视图定义。但是，删除视图不会影响视图的基表。此外，基于已删除视图的视图或其它应用程序会变得无效。只有创建者或具有DROP ANY VIEW权限的用户才能删除视图。

bitsCN.com