■创建视图
你可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改
变表中的数据。视图基于其上的表称为基表。
视图在数据字典中作为一个SELECT语句存储。
视图的优越性
:: 视图限制数据的访问，因为视图能够选择性的显示表中的列。
:: 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查询信息，而用户不必知道怎样写连接语句。
:: 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。
:: 视图提供用户组按照他们的特殊标准访问数据。
■简单视图和复杂视图
特性 简单视图 复杂视图
表的数目 一个 一个或多个
包含函数 无 有
包含数据分组 无 有
通过视图进行DML 操作 是 不允许
■创建视图
::在CREATE VIEW 语句中嵌入一个子查询
::子查询可以包含复杂的SELECT语法.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
在语法中：
OR REPLACE 如果视图已经存在重新创建它
FORCE 创建视图，而不管基表是否存在
NOFORCE 只在基表存在的情况下创建视图(这是默认值)
view 视图的名字
alias 为由视图查询选择的表达式指定名字
(别名的个数必须与由视图选择的表达式的个数匹配)
subquery 是一个完整的SELECT语句(对于在SELECT列表中的字段你可以用别名)
WITH CHECK OPTION 指定只有可访问的行在视图中才能被插入或修改
constraint 为CHECK OPTION约束指定的名字
WITH READ ONLY 确保在该视图中没有DML操作被执行
eg:
创建一个视图，EMPVU80，其中包含了在部门80 中雇员的详细信息
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
View created.View created.
:: 用iSQL*Plus DESCRIBE命令查看视图的结构
DESCRIBE empvu80
･创建视图的原则：
:: 定义一个视图的子查询可以包含复杂的SELECT语法，包括连分组和多个子查询。
:: 定义视图的子查询不能包含ORDER BY子句，当你从视图取回数据时可以指定ORDER BY子句。
:: 如果你没有为用WITH CHECK OPTION选项创建的视图指定一个约束名字，系统将以SYS_Cn格式指定一个默认的名字。
:: 你可以用OR REPLACE选项改变视图的定义而无须删除和重新创建它，或重新授予以前已经授予它的对象权限。
:: 用子查询中的列别名创建视图
eg:
:: 从该视图中选择列，视图中的列使用别名命名
可以在子查询中包括列别名来控制列名
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY //必须要有别名
FROM employees
WHERE department_id = 50;
View created.
:: 作为一个选择，你可以在CREATE语句后面在SELECT子查询前面用一个别名，被列出的别名的个数必须与在子查询中被选择的表达式相匹配。
CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
FROM employees
WHERE department_id = 50;
视图创建。
■从视图中取回数据
你可以像从任何表中取回数据一样从视图取回数据，你既可以显示整个视图的内容，也可以仅显示指定的行和列。
select * from salvu50;
■数据字典中的视图
一旦视图被创建，你就可以查询数据字典视图USER_VIEWS来看视图的名字和视图定义。构成视图的SELECT语句的文本被存储在一个LONG列中。
■■从视图中取回数据过程
当你用视图存取数据时，Oracle 服务器执行下面的操作：
1. 从数据字典表USER_VIEWS中取回视图定义。
2. 检查对视图的基表的数据存取权限。
3. 转换视图查询为一个在基表或表上的等价操作，换句话说，数据从基表得到，或更新基表
USER_VIEWS
--empvu80
select * -----> select employee_id, last_name,salary from employees
from empvu80; where department_id=80;
employee_id last_name salary |
149 Zlotkey 100 |
174 Abel 1100 <--- employees
■修改视图
:: 用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子句中的字段别名列表，按照与子查询中的字段相同的顺序排列
修改视图
用OR REPLACE选项，允许创建视图时同名的视图已经存在，这样旧版本的视图会被替换，这意味着视图可以在不被删除、重新创建和再次授予对象权限的情况下修改。
注：当在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 操作
你可以通过视图对数据进行执行DML操作，但那些操作必须符合下面的规则。
如果视图中不包含下面的部分，你就可以从视图中删除数据：
:: 组函数
:: GROUP BY子句
:: DISTINCT关键字
:: 伪列ROWNUM关键字
:: 基表中的NOT NULL列不在视图中
::如果视图中不包含上面提到的情况，或者不包含由表达式定义的列，例如，SALARY * 12，你就可以通过视图修改数据。
■WITH CHECK OPTION 子句
:: 你可以确保DML操作在视图上被执行，用WITH CHECK OPTION子句检查视图中的域
eg:
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.
:: 任何改变视图的任意行中部门号的企图都会失败，因为它违反了WITH CHECK OPTION约束
使用WITH CHECK OPTION子句
可以通过视图执行引用完整性检查，你也可以在数据库级别强制约束。视图能用于保护数据的完整性，但用途非常有限。
WITH CHECK OPTION子句指出通过视图执行的INSERTs和UPDATEs不能创建视图不能选择的行，因此，该子句在数据开始插入或更新时允许完整性约束和数据验证检查。
如果在没有选择的行上有一个执行DML操作的尝试，将会显示错误，如果指定了约束名，会带在错误提示中。
eg:
UPDATE empvu20
SET department_id = 10
WHERE employee_id = 201;
UPDATE empvu20
*
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 服务器错误
如果用户没有提供约束名，系统将以SYS_Cn格式指定一个名字，这里n是一个整数，在系统中约束名是唯一的。
eg:
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;
DELETE FROM empvu10
WHERE employee_number = 200;
DELETE FROM empvu10
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-
preserved table
任何对带只读约束的视图进行的插入或修改行的尝试，在Oracle服务器中都显示下面的错误结果：
01733: virtual column not allowed here.
■删除视图
DROP VIEW viewName;
删除视图不会丢失数据，因为视图是基于数据库中的基本表的
DROP VIEW empvu80;
View dropped.
你可以用DROP VIEW语句来删除视图。该语句从数据字典中删除视图定义。删除视图不影响用于建立视图的基表。基于已删除视图上的其它视图或应用程序将无效。只有创建者或具有DROP ANY VIEW权限的用户才能删除视图。
■内建视图
:: 内建视图是一个带有别名(或相关名) 的可以在SQL 语句中使用的子查询
:: 一个主查询的在FROM 子句中指定的子查询就是一个内建视图的离子
:: 内建子查询不是方案对象
内建视图由位于FROM子句中命名了别名的字查询创建。该字查询定义一个可以在主查询中引用数据源。
eg:
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;
在上面的例子中，内建视图b从EMPLOYEES表中返回每一个部门的部门号和最高薪水。主查询显示雇员的名字、薪水、部门号和该部门的最高薪水，WHERE a.department_id =
b.department_id AND a.salary < b.maxsal 子句取回所有收入少于该部门最高薪水的雇员。
■■Top-N 分析
:: Top-N 查询寻找一列的n个最大或最小值，例如：
–销售最好的前10 位产品是什么?
–销售最差的前10 位产品是什么?
:: 最大值和最小值在Top-N 查询中设置
“Top-N”分析
Top-N查询在需要基于一个条件，从表中显示最前面的n条记录或最后面的n条记录时是有用的。该结果可以用于进一步分析，例如，用Top-N分析你可以执行下面的查询类型：
:: 公司中挣钱最多的三个人
:: 公司中最新的四个成员
:: 销售产品最多的两个销售代表
:: 过去6个月中销售最好的3种产品
在子查询中包含ORDER BY子句也可以进行Top-N分析。
■执行Top-N 分析
Top-N 分析查询的高级结构是：
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column [asc|desc])
WHERE ROWNUM <= N;
执行“Top-N”分析
Top-N查询使用一个带有下面描述的元素的一致的嵌套查询结构：
:: 子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含ORDER BY子句来确保排序以想要的顺序排列。为了取回最大值，需要用DESC参数。
:: 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：
- ROWNUM伪列，它为从子查询返回的每一行指定一个从1开始的连续的值
- 一个WHERE子句，它指定被返回的n行，外WHERE子句必须用一个<或者<=操作。
･Top-N 分析的例子
eg:
①为了从EMPLOYEES表中显示挣钱最多的3 个人的名字及其薪水:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
SQL> SELECT ROWNUM as RANK, last_name, salary
2 FROM (SELECT last_name,salary
3 FROM employees
4 ORDER BY salary DESC)
5 WHERE ROWNUM <= 3;
RANK LAST_NAME SALARY
---------- ------------------------- ----------
1 King 24000
2 Kochhar 17000
3 De Haan 17000
②下面是用内建视图进行Top-N分析的另一个例子，该例子用内建视图E显示公司中4个资格最老的雇员。//使用了表别名.
SQL> SELECT ROWNUM as SENIOR,E.last_name, E.hire_date
2 FROM (SELECT last_name,hire_date
3 FROM employees
4 ORDER BY hire_date) E
5 WHERE rownum <= 4;
SENIOR LAST_NAME HIRE_DATE
---------- ------------------------- ----------
1 King 17-6月 -87
2 Whalen 17-9月 -87
3 Kochhar 21-9月 -89
4 Hunold 03-1月 -90
:: 内建视图是一个带有别名的子查询
:: 用子查询和外查询能够进行Top-N 分析
