已索引

什么是视图

A view is a logical table based on a table or another view. A view contains no data of its own, but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary.

视图的好处

• To make complex queries easy（比如数据字典和内联视图）
• To present different views of the same data（比如数据字典）
• To restrict data access（it displays selected columns from the table，用户只能看到基表的部分信息。方法：赋予用户访问视图对象的权限，而不是表的对象权限。）
• To provide data independence：Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.

视图可以提供数据的独立性：
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立：
1.如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动；当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。
2.如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序可以不动；当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。

简单视图和复杂视图

上表来源于官方PPT，但是，并非总是可以在简单视图上使用DML语句：
create view v_region as select region_name from regions;
insert into v_region values('Big China'); 报错
看能否对视图DML操作就是看反射映射的DML操作能否成功。

语法

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

FORCE的作用：强制创建视图

FORCE ：Creates the view regardless of whether or not the base tables exist
NOFORCE ：Creates the view only if the base tables exist (This is the default.)
FORCE作用：可以先建视图，后建基表，会有警告：创建的视图带有编译错误。但是视图已经创建好了。基表一旦创建好，视图就可以用了。

创建一个简单的视图

示例：
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

DESC empvu80

创建一个复杂的视图

示例（这个视图的子查询涉及到多张表并使用了组函数）：
CREATE OR REPLACE 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 JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;

修改视图

OR REPLACE ：Re-creates the view if it already exists. This means that the view can be altered without dropping, re-creating, and regranting object privileges.

定义视图列

alias ：Specifies names for the expressions selected by the view's query (The number of aliases must match the number of expressions selected by the view.)
示例：
CREATE OR REPLACE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
FROM employees
WHERE department_id = 50;

也可以在子查询中给出列别名：
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

WITH CHECK OPTION

Specifies that only those rows that are accessible to the view can be inserted or updated. 对视图where子句进行约束。不允许对限定关键字修改。

CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

如此，则不能向视图中插入department_id不是20的新记录，也不能修改现有视图行的department_id，例如：

UPDATE empvu20
SET department_id = 10
WHERE employee_id = 201;
员工201原来的部门编号是20，包含在视图中，尝试修改其部门编号时将报错。

也就是说，不能去动视图的要害，不能造成视图的飘忽不定，看住where条件。

只读视图

WITH READ ONLY：禁止对视图执行DML操作
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 ;

删除视图

DROP VIEW view;

视图的DML规则

You cannot remove a row if the view contains the following:
– Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword

You cannot modify data in a view if it contains:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions

You cannot add data through a view if the view includes:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions
• NOT NULL columns in the base tables that are not selected by the view

查看视图定义

如果想查看视图的定义，可以访问dba_views中的text字段(long 型)：

SQL> set long 99999;
SQL> set pages 900;
SQL> select text from dba_views;

或者：
declare
v_text dba_views.text%type;
v_name dba_views.view_name%type;
begin
select text, view_name into v_text,v_name FROM dba_views WHERE view_name='V1';
dbms_output.put_line(v_name||' define is :'||v_text);
end;
/

案例记录---备库访问视图时报报 ORA-04045 和 ORA-16000 错误