建立可更新的视图

可更新视图是一个微妙的问题。这里了一些践练习形式的帮助，你可以使用Oracle8i的任何译本完成这些练习。
这篇文章改编自Animated Learning，Inc. /The Database Domai n 设立的培训课程，这是一个为Oracle DBA（数据库管理员）和开发者提供基于Web和CD培训的公司。要获得更多信息，请查看站点
视图是Oracle数据库中一个基本的模式对象，它是访问一个或多个表中数据的途径。视图通常用于为表数据建立额外的安全级别，从数据库表格中的数据中得到信息并简化应用程序开发。建立可更新的视图--你可以用来插入、更新及删除数据库表格行的视图--有时可能是一个很微妙的问题。这篇文章详细介绍了如何为支持各种应用程序而在一个数据库模式方案中创建各种类型的可更新视图以支持应用的细节。


先决条件
这篇文章假设你对关系型数据库结构有一些基本的了解，包括表格和视图，以及基本的SQL命令：INSERT, UPDATE, DELETE和SELECT 。你也应该知道如何使用SQL命令CREA TE VIEW建立只读视图，以及如何使用PL/SQL建立数据库触发器。
你必须完成如下步骤，来建立必要的模式和配置一个SQL*Plus对话，以支持本文中的动手练习。


在你的计算机上建立一临时目录，例如 c:\temp\courseware。
下载一个支持脚本到你的临时目录。你可以从
开始SQL*Plus
在SQL*Plus命令提示符下，用如下命令运行这个支持脚本：SQL>@c:\t emp\courseware\m048.sql(适用于Windows用户) 或SQL>@/tmp/courseware/m048.sql(适用于 UNIX和Linux用户)
一旦脚本开始运行，就遵循它的用法说明和指令。
脚本成功地执行后，不要断开或退出SQL*Plus--让它保持开放。
继续读这篇文章。当你遇到一个练习时，按着提示用你现有的SQL*Plu s对话完成这一练习。
由于为了实践练习能够正确进行，数据和对话设置必须处于特殊的状态，请按出现的顺序完成所有的练习，不要退出SQL*Plus。

固有可更新视图
如果你没明确地建立一个只读视图，Oracle将建立一个你可以用来插入、更新、删除数据表格行的视图--即可更新视图。然而，视图并不是自动地或固有地可更新，除非O racle能够通过这一视图正确地将插入、更新、删除操作映射到视图的底层表格数据中。
下面的列表详述了考虑建立一个视图定义查询时的常规限制--你想让视图成为固有可更新的而不需额外的操作。一个固有可更新视图的定义查询不能包括：


集操作符（UNION，UNION ALL，INTERSECT，或MINUS）
DISTINCT 操作符
组（集合）函数，例如AVG，MCOUNT，MAX，MIN等
GROUP BY，ORDER BY，CONNECT BY或 START WITH 子句
对SELECT列表中集合表达式的引用
SELECT列表中的子查询
JOIN查询
此外，当视图定义查询中的SELECT列表包含虚拟列（表达式或伪列引用）时，如果UPDATE语句没有引用视图的任何虚拟列你只能使用视图更新数据库表格行。

1
练习


建立一个固有可更新视图
使用你现有的SQL*Plus对话，输入以下命令建立CUSTOMERS表的可更新视图。


CREATE VIEW mail_labels AS
SELECT firstname||' '||lastname AS name,
companyname,
address,
city||', '||state||' '||zipcode AS place
FROM customers;

预期结果是这一简单信息：


View created.

快速测试:为什么由下列语句创建的ORDER_TOTALS视图不是可更新视图？


CREATE VIEW order_totals
(order_id, total)
AS
SELECT i.ord_ord_id,
SUM(i.quantity * p.unitprice)
FROM items i, parts p
WHERE i.part_part_id = p.part_id
GROUP BY i.ord_ord_id;

答案：ORDER_TOTALS 视图不是可更新视图，因为这一视图的定义查询包含了一个GROUP BY子句。

2
练习

显示可更新视图的信息
要显示一个可更新视图的哪些列是可插入、可更新和可删除方面的信息，可以查询US ER_UPDATABLE_COLUMNS 数据字典视图。然而，如果一个视图不是可更新的，视图中关于可更新列的信息是不相关的。
使用SQL*Plus，输入如下查询，显示新MAIL_LABELS视图中可更新列的信息。注意：Oracle8i不支持MAIL_LABELS视图中所有列的插入、更新和删除操作。


SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'MAIL_LABELS';

你的结果应与下面匹配：


COLUMN_NAME UPD INS DEL
_______________ __ __ __
NAME NO NO NO
COMPANYNAME YES YES YES
ADDRESS YES YES YES
PLACE NO NO NO

3
练习

使用可更新视图
使用SQL*Plus，输入列表1中显示的命令来测试新MAIL_LABELS视图的功能性。注意某些语句如何执行，及其它语句由于视图定义的限制如何返回错误信息。
约束可更新视图

建立一个可更新视图时，你可以添加 WITH CHECK OPTION 将视图创建为局限视图，禁止对该视图的特定插入和更新操作。例如，考虑下列约束视图 ：


CREATE VIEW contacts_ca AS
SELECT *
FROM customers
WHERE state = 'CA'
WITH CHECK OPTION;

一个带有约束的视图只允许那些创建视图可以依次选取的行的INSERT和UPDATE 语句 。

4
练习

建立约束可更新视图
使用SQL*Plus，输入如下命令建立CUSTOMERS表的CONTACTS_CA约束视图。


CREATE VIEW contacts_ca AS
SELECT *
FROM customers
WHERE state = 'CA'
WITH CHECK OPTION;

预期结果是这一简单信息：


View created.

5
练习

使用约束可更新视图
使用SQL*Plus，输入如下命令观察约束可更新视图CONTACTS_CA如何允许和禁止某些U PDATE语句。


UPDATE contacts_ca
SET address = '100 Skyview Place',
city = 'Brentwood',
zipcode = '92011'
WHERE cust_id = 4;

UPDATE contacts_ca
SET state = 'NY'
WHERE cust_id = 4;

ROLLBACK;

预期结果如下：


SQL> UPDATE contacts_ca
2 SET address = '100 Skyview Place',
3 city = 'Brentwood',
4 zipcode = '92011'
5 WHERE cust_id = 4;

1 row updated.

SQL> UPDATE contacts_ca
2 SET state = 'NY'
3 WHERE cust_id = 4;
UPDATE contacts_ca
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> ROLLBACK;

Rollback complete.

可更新联合视图

联合视图是可更新的，但它受到一些额外的约束。根据定义，联合视图有一个连接多个表格中信息的定义查询。如果数据库表格中每一个主键标或者唯一键标的值在联合视图中的结果集中也唯一--换句话说，如果联合视图保留了数据库表格的实体完整性，视图数据库表格被认为是键标保留表格。为了更好地理解联合视图中键标保留表格的概念，考虑一下 图１。.


图1：首先，检查数据库表格及们的主键标。表ORDERS的主键标加强了表的实体完整性，主键标由ORD_ID列组成。表ITEMS的主键标加强了表的实体完整性，主键标由 ORD_ORD_ID列和ITEM_ID列组成。现在检查联合视图ORDER_ITEMS的定义查询。注意表ITEMS的实体完整性通过联合视图得以保留，但是表ORDERS 的实体完整性丧失了。因此，表ITEMS 被认为是关于ORDERS_ITEMS视图的键标保留表格，而表ORDERS却不是。

建立固有可更新联合视图
除了自动或固有可更新视图的一般规则，联合视图通过下列的INSERT、< b>UPDATE和DELETE语句成为固有可更新的。


如果INSERT语句只把视图的单一键标保留表格的列作为目标，你可以在非约束联合视图中（创建的联合视图不含 WITH CHECK OPTION）插入行。
如果UPDATE语句只把来自于视图的单一键标保留表格的列作为目标，你可以在联合视图中更新行；此外，如果联合视图是约束视图，UPDATE语句不能更新联合条件下使用的列或者视图中引用不只一次的列。
如果视图基于唯一的键标保留表格，你可以从联合视图中删除行。
若没有下面练习提供例子加以辅助，上述键标保留表格的概念和规则很难理解。

6
练习

建立固有可更新联合视图
使用SQL*Plus，输入如下命令建立固有可更新联合视图，然后显示视图中可更新的列。


CREATE VIEW order_items AS
SELECT o.ord_id,
o.orderdate,
i.item_id,
i.part_part_id AS part_id,
i.quantity
FROM orders o, items i
WHERE o.ord_id = i.ord_ord_id;

SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'ORDER_ITEMS';
预期结果如下：
SQL> CREATE VIEW order_items AS
2 SELECT o.ord_id,
3 o.orderdate,
4 i.item_id,
5 i.part_part_id AS part_id,
6 i.quantity
7 FROM orders o, items i
8 WHERE o.ord_id = i.ord_ord_id;

View created.

SQL> SELECT column_name, updatable, insertable, deletable
2 FROM user_updatable_columns
3 WHERE table_name = 'ORDER_ITEMS';

COLUMN_NAME UPD INS DEL
___________ ___ ___ ___
ORDERDATE NO NO NO
ITEM_ID YES YES YES
PART_ID YES YES YES
QUANTITY YES YES YES
ORD_ID NO NO NO

介绍INSTEAD OF 触发器

这篇文章前面的所有部分详细解释了内在可更新试图。现在我们学习如何使用IN STEAD OF 触发器使任何一个视图--无论它有多么复杂--成为可更新的。

INSTEAD OF触发器是一种特殊类型的触发器，它告诉Oracle如何处理视图中执行的DML操作（INSERT、UPDATE或DELETE）。例如，假设你有一个组合了两个不同表格中信息的视图，如果你针对包含两个表格同时使用的字段的视图执行 INSERT语句，Oracle8i将不清楚如何将新行映射到底层中。通过为视图建立INSTEAD OF触发器，你可以明确地告诉Oracle8i怎样处理此类INSERT 语句。要为视图创建INSTEAD OF 触发器，使用如下SQL命令的简短语法CREAT TRIGGER。


CREATE [OR REPLACE] TRIGGER
[schema.]trigger
INSTEAD OF
{DELETE|INSERT|UPDATE [OF column [,column]
.. ]}
[OR {DELETE|INSERT|UPDATE [OF column
[,column] ... ]} ] ...
ON [schema.]view
... trigger body ...

警告：如果你创建了一个带有定义查询的视图，而引用了另一带有IN STEAD OF触发器的视图，即使新视图是内在可更新的，你也必须为新视图定义INSTEA D OF触发器。

7
练习

为可更新视图创建INSTEAD OF触发器
使用SQL*Plus，输入列表2 中显示的命令，创建一个组合了从表CUSTOMERS和ORDERS选取的字段的视图，并为视图创建一个INSTEAD OF触发器来处理以该视图为目标的INSERT语句。注意，触发器对视图的两个表格的适当域执行两个截然不同的INSERT语句。

8
练习

显示ORDER_INFO视图的信息
在测试新的ORDER_INFO视图和相关的INSTEAD OF触发器之前，使用SQL*Plus输入如下查询，显示视图中可更新列的信息。

SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'ORDER_INFO';
预期结果如下：

COLUMN_NAME UPD INS DEL
___________ ___ ___ ___
CUST_ID NO YES NO
COMPANYNAME NO YES NO
LASTNAME NO YES NO
FIRSTNAME NO YES NO
ORD_ID YES YES YES
ORDERDATE YES YES YES
SHIPDATE YES YES YES
PAIDDATE YES YES YES

9
练习


测试INSTEAD OF触发器
使用SQL*Plus，输入如下INSERT语句和后续的查询，验证与视图ORDER_INFO相关联的INSTEAD OF触发器的功能。
关于随Oracle8引入的INSTEAD OF触发器的更多信息，请查看Oracle技术网站 ().

INSERT INTO order_info
( cust_id,
companyname,
lastname,
firstname,
ord_id,
orderdate )
VALUES
( 10,
'Acme',
'Schwartz',
'Elsie',
6,
'13-DEC-97' );

SELECT cust_id, lastname
FROM customers
WHERE cust_id = 10;

SELECT ord_id, orderdate
FROM orders
WHERE ord_id = 6;

预期结果如下：


SQL> INSERT INTO order_info
2 ( cust_id,
3 companyname,
4 lastname,
5 firstname,
6 ord_id,
7 orderdate )
8 VALUES
9 ( 10,
10 'Acme',
11 'Schwartz',
12 'Elsie',
13 6,
14 '13-DEC-97' );

1 row created.

SQL> SELECT cust_id, lastname
2 FROM customers
3 WHERE cust_id = 10;

CUST_ID LASTNAME
_____ _______
10 Schwartz

SQL> SELECT ord_id, orderdate
2 FROM orders
3 WHERE ord_id = 6;

ORD_ID ORDERDATE
_____ ________
6 13-DEC-97


小结
Steve Bobrowski是 Oracle 出版社的Oracle8 Architecture, Oracle8i f or Windows NT Starter Kit, 和 Oracle8i for Linux Starter Kit等书的作者，这几本书可通过 在线获得 。
记住下列要点：
视图并不是固有可更新的，除非Oracle8i能够通过视图的定义查询将INSE RT、UPDATE或DELETE操作正确映射到视图的底层表格数据中。
带有约束（WITH CHECK OPTION）的视图只允许那些创建视图能够依次选取的行的INSERT和 UPDATE语句。
除了固有可更新视图的一般规则，联合视图通过INSERT、 UPDATE 和DELETE语句成为固有可更新的，但受到额外地限制。
你可以使用INSTEAD OF触发器使任意视图成为可更新的，而不管视图多么复杂。
Steve Bobrowski 是Animated Learning() 的CEO。

只能在线进行的快速测试
练习2：:
快速测试:解释为什么在练习1中创建的视图MAIL_LABELS的NAM E列和 PLACE列不是可插入、可更新和可删除的。

答案: NAME列和 PLACE列是不可更新的，因为它们是虚拟列--也就是说，视图是使用定义查询中SELECT列表的表达式创建这些列的。

练习 4:
快速测试: 为什么当你试图执行以下语句时，Oracle返回错误信息？


CREATE VIEW contacts_ca AS
SELECT *
FROM customers
WHERE state = 'CA'
WITH READ ONLY
WITH CHECK OPTION;

答案: 你不能建立具有约束的只读视图。视图约束只适用于可更新视图。

练习 5:
快速测试: 为什么这个练习中Oracle允许第一个UPDATE语句而不允许第二个UPDATE语句？

答案: 执行完第一个UPDATE语句后，由于已更新的客户记录中的STATE列值被作为CA保留，因此视图仍旧能够从底层表格CUSTOMERS 检索到该行。然而，Oracle不允许第二个UPDATE语句执行是因为已更新客户记录中的STATE值是NY，视图不再能够检索到该行。

练习 6:
快速测试:这个练习中查询的结果集显示所有来自于表格ITEMS 的视图列都是可更新的，然而所有来自表格ORDERS的视图列是不可更新的，为什么？

答案:视图保留了表格ITEMS的实体完整性，但没有保留表格ORDERS 的实体完整性。

练习 8:
快速测试: 为什么即使视图ORDER_INFO有INSTEAD OF触发器，它所有的列也不是可更新和可删除的？

答案: 视图的INSTEAD OF触发器不处理UPDATE和 DELE TE语句，只处理INSERT语句。

练习 9:
快速测试: 你能解释当你在此练习中给出INSERT语句时发生的情况么？

答案: INSERT语句指向视图ORDER_INFO，它有一相应的IN STEAD OF触发器ORDER_INFO _INSERT来处理INSERT 语句。ORDER_INFO_INSERT触发器的主体获取了在起触发作用的INSERT语句中提供的值，并把它们重新指向带有两个单独的INS ERT语句的视图的CUSTOMERS和 ORDERS表格。

快速测试:如果你在给出前述例子INSERT语句之前没为视图 ORDER_INFO建立ORDER_INFO INNSERT触发器，将会发生什么事情？

答案: Oracle将返回错误信息，因为它不能将INSERT语句的值映射到组合了两个表的数据的ORDER_INFO视图。