№06 视图§基础

视图基础

        很难概括性地说明什么是视图，很多书和博客上的定义会让人误解视图，忽略了视图的其他特性。因此有一段时间，我一直以为视图就是一种查询技术，以某种特定的视角来查看表的物理存储，就像报表一样，过滤掉在当前视角下不关心的数据、抽象复杂度高的物理结构为逻辑结构……这种想法很容易让人以为视图是只读的，是数据库中真实表的影子。但是视图是可更新的，可以插入、更新、删除。

---

语法

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     } 

• schema_name：模式名。
• view_name：创建的目标视图名。
• column：视图的列，视图的结构与表很相似，也是用行列来标志数据。
• WITH <view_attribute>：设置视图属性，可用列表在创建语法的下半部分。
  • ENCRYPTION：加密，加密后的对象无法用文档方式查看，也就是你不能看到其源码，网络上存在一些解密的方式，我没有实践过。
  • SCHEMABINDING：模式绑定，将此视图所依赖的所有对象绑定到此视图，从此以后，这些被绑定的对象的任何修改一旦影响到了视图的功能，这些修改就会被阻止。
  • VIEW_METADATA：metadata是元数据的意思，这个属性使DB-LIB、ODBC、OLE-DB客户觉得视图像一个真实的表，视图返回的元数据就是视图本身的信息。如果没有这个属性，视图返回的元数据是它依赖的基本表。
    
• As select_statement：指定视图的主体，即创建视图，让视图返回什么样的数据。
• WITH CHECK OPTION：检测选项（我不知道这个怎么翻译，就按字面意思），它约束一种规则：往视图中插入或更新数据时，更新后的数据必需通过视图可见。这一个选项会在后文详解。

创建简单视图

因为涉及的操作会更改表，所以使用下面的脚本创建表，只对新建的表进行操作，不会对数据库中其它表有影响，因此可以随便选择一个数据库。

IF OBJECT_ID('husband','U') IS NOT NULL
	DROP TABLE husband;
	CREATE TABLE husband(
	id VARCHAR(10) PRIMARY KEY,
	name VARCHAR(20)
	)
	INSERT husband(id,name)VALUES('1','Sun')
	INSERT husband(id,name)VALUES('2','Sam')

下面创建一个视图用来显示所有id为奇数的husband表中的数据：

USE AdventureWorks2008R2;
if OBJECT_ID('HusbandView','V') IS NOT NULL
DROP VIEW HusbandView;
GO
CREATE VIEW HusbandView
(
	husid,
	husname
)
AS
SELECT id,name FROM husband
WHERE id%2 = 1
WITH CHECK OPTION

PS：如果不指定column，视图返回的列名就是SELECT语句中的列名。

现在可以像操作表那样操作这个视图：

操作	结果	SELECT * FROM HusbandView	SELECT * FROM husband
SELECT  *  FROM HusbandView	成功	husid husname 1 Sun	id name 1 Sun 2 Sam
INSERT HusbandView(husid,husname) VALUES ('3','Tom')	成功	husid husname 1 Sun 3 Tom	id name 1 Sun 2 Sam 3 Tom
INSERT HusbandView(husid,husname) VALUES('4','Jim')	失败	husid husname 1 Sun 3 Tom	id name 1 Sun 2 Sam 3 Tom
UPDATE HusbandView SET husid='9'  WHERE husid='3';	成功	husid husname 1 Sun 9 Tom	id name 1 Sun 2 Sam 9 Tom
UPDATE HusbandView SET husid='8'  WHERE husid='9';	失败	husid husname 1 Sun 9 Tom	id name 1 Sun 2 Sam 9 Tom
DELETE FROM husbandView	成功	空	id name 1 Sun

第三个（ INSERT HusbandView(husid,husname)VALUES('4','Jim') ）操作抛出的异常：

消息 550，级别 16，状态 1，第 1 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION约束。

看，这就是CHECK OPTION约束的作用，因为通过视图只能显示id为奇数的数据，则此时插入id为4的数据是不被允许的。

第五个操作失败的原因同上。

对于视图只依赖一个表，上述操作很顺利，当有多个表里，则需要使用INSTEAD OF 触发器来辅助通过视图对依赖表进行更新。

INSTEAD OF触发器

如果视图 V 只依赖于一个表 T，且 T 必要字段都呈现在 V 中，往 V 中插入数据时，DBMS 可以很轻松地完成向 T 中插入数据的操作，如果 T 中某些重要字段没有包含在视图V中呢？这个时候向 V 中插入数据，会被禁止。更常见的情况是视图可能依赖于多个表的连接，此时向视图中插入数据，到底要插到哪里？

 此时就需要INSTEAD OF触发器，如果你不了解触发器，请参阅（。。。）

使用以下脚本创建一个测试环境：

IF EXISTS(SELECT * FROM sys.databases WHERE name='OurInsteadOfTest')
DROP DATABASE OurInsteadOfTest;
GO
CREATE DATABASE OurInsteadOfTest;
GO
USE OurInsteadOfTest;
--Customers
CREATE TABLE Customers
(
CustomerID VARCHAR(5) NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
);
--Orders
CREATE TABLE Orders
(
OrderID int IDENTITY NOT NULL PRIMARY KEY,
CustomerID VARCHAR(5) NOT NULL
	REFERENCES Customers(CustomerID),
OrderDate DATETIME NOT NULL
)
--Products
CREATE TABLE Products
(
ProductID int IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL,
UnitPrice MONEY NOT NULL
)
--OrderItems
CREATE TABLE OrderItems
(
OrderID INT NOT NULL REFERENCES Orders(OrderID),
ProductID INT NOT NULL REFERENCES Products(ProductID),
UnitPrice Money NOT NULL,
Quantity INT NOT NULL
CONSTRAINT PKOrderItem PRIMARY KEY CLUSTERED (OrderID,ProductID)
)
--init test data
INSERT Customers VALUES('ABCDE','Bob''s Pretty Good Garage');
INSERT Orders VALUES('ABCDE',CURRENT_TIMESTAMP);
INSERT Products VALUES('Widget',5.55),('Thingamajiq',8.88);
INSERT OrderItems VALUES(1,1,5.55,3);

下面创建一个显示订单项详细信息的 View

USE OurInsteadOfTest;
GO
CREATE VIEW CustomerOrders_vm
WITH SCHEMABINDING
AS
SELECT  o.OrderID,
        o.OrderDate,
	od.ProductID,
	p.Name,
	od.Quantity,
	od.UnitPrice
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS od
ON o.OrderID = od.OrderID
JOIN dbo.Products AS p
ON od.ProductID=p.ProductID;

对于这个视图，如果直接向其中插入数据，显然会被禁止，它包含了三张表Orders、OrderItems、Products，插入数据DBS完全不知道这是要干什么……此时，就需要INSTEAD OF触发器：

CREATE TRIGGER TR_insteadOf ON dbo.CustomerOrders_vm
INSTEAD OF INSERT
AS
BEGIN
	IF (SELECT COUNT(*) FROM inserted)>0
	BEGIN
		INSERT INTO dbo.OrderItems
		SELECT i.OrderID,
		       i.ProductID,
		       i.UnitPrice,
		       i.Quantity			
		FROM inserted AS i
		JOIN dbo.Orders AS o
		ON i.OrderID = o.OrderID
		JOIN dbo.Products AS p
		ON i.ProductID = p.ProductID
		IF @@ROWCOUNT=0
		RAISERROR('No matching Orders.Can''t perform insert',10,1)
	END
END

这个触发器的语意是：每当对CustomerOrders_vm执行插入操作时，则跳到触发器中的主体部分执行（原来的插入操作被  INSTEAD 了）。

下面对CustomerOrders_vm进行插入：

INSERT dbo.CustomerOrders_vm
(OrderID,OrderDate,ProductID,Quantity,UnitPrice)
VALUES(1,GETDATE(),2,10,6.0)

SELECT * FROM CustomerOrders_vm：