带你深入了解"T-SQL"的十一种设计模式
一、ITERATOR(迭代)
这种模式提供一种在相似对象列表中遍历对象的标准化方法。在SQL Server数据库中的同义词是游标。
DECLARE tables CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FOR READ ONLY
DECLARE @table varchar(40)
OPEN tables
FETCH tables INTO @table
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_help @table
FETCH tables INTO @table
END
CLOSE tables
DEALLOCATE tables
注：游标的清理代码：在CLOSE后紧跟DEALLOCATE,实际上可以只运行DEALLOCATE，并且游标也能自动关闭。但这不是最自然，也不是最常见的方法。大家可以理解为：CLOSE抵消OPEN，DEALLOCATE与DECLARE则相反，这样可以使代码保持对称并且合乎逻辑。
二、INTERSECTOR(交集)
这种模式是表示集合交集的一种模板。
1、推荐方法：
SELECT c.companyname,o.orderid
FROM customer c INNER JOIN orders o ON c.customerid = o.customerid
2、旧式语法(不推荐使用)
SELECT c.companyname,o.orderid
FROM customer c ,orders o
WHERE c.customerid = o.customerid
注：实现集合交集还有许多变种方法。但是惯例方法就是方法1，方法2在实现左(右)联接时，条件的表示及结果都可能出现问题，SQL SERVER的后续版本将会取消此种联接方式。
三、QUALIFIER(限定)
限定数据等价于筛选查询所返回的行数。
1、常用法：WHERE子句限定
SELECT city,count(*) AS NumberCity
FROM customers
WHERE city like 'A%'
GROUP BY city
2、不自然的筛选：HAVING子句限定
SELECT city,count(*) AS NumberCity
FROM customers
GROUP BY city
HAVING city like 'A%'
注：HAVING子句的目的是在结果集被检索出来后再筛选查询。实际上，SQL SERVER内在地转换HAVING子句为WHERE子句(两种方法查询的执行计划是相同的)，如果SQL SERVER不执行此优化，则针对包含大量数据行的表，因需要在筛选前从表中检索所有行，则性能方面可能会遭受重大损失。
四、EXECTOR(运行)
提供创建并执行动态T-SQL字符串的模板
--中断除当前连接之外的所有用户连接
DECLARE @s int,@sql nvarchar(128)
DECLARE spids CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE spid <> @@SPID AND net_address<>''
FOR READ ONLY
OPEN spids
FETCH spids INTO @s
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = 'KILL ' + CAST(@s AS varchar)
EXEC sp_executesql @sql
FETCH spids INTO @s
END
CLOSE spids
DEALLOCATE spids
注：上述语句中的sp_executesql可以用exec()替换，但推荐使用sp_executesql,因为与exec()相比，sp_executesql支持参数化查询，并可从动态T-SQl调用返回一个结果代码。如果动态代码产生一个严重级达到或超过11的错误，sp_executesql将在它的结果代码中返回错误码。
五、Conveyor(传送)
提供一种通过存储过程链传送信息的机制。与GoF的责任链模式(Chain of Responsibility)相类似。
1、传送返回码
CREATE PROC procC
AS
IF OBJECT_ID('no_exist') IS NOT NULL
SELECT * FROM no_exist
ELSE
RETURN (-1)
GO
CREATE PROC procB
AS
DECLARE @res int
EXEC @res = procC
RETURN (@res)
GO
CREATE PROC procA
AS
DECLARE @res int
EXEC @res = procB
SELECT @res
GO
EXEC procA
注：上述代码使用了存储过程的结果代码从过程向过程传递原始返回码的方法，即A调用B，B又调用C，C运行时如出现了错误，则将错误代码-1传送给A。
2、通过输出参数传送消息
CREATE PROC procC
@msg varchar(128) OUT
AS
IF OBJECT_ID('no_exist') IS NOT NULL
SELECT * FROM no_exist
ELSE
SET @msg = 'Table dosen''t exist!'
GO
CREATE PROC procB
@msg varchar(128) OUT
AS
EXEC procC @msg OUT
GO
CREATE PROC procA
AS
DECLARE @msg varchar(128)
EXEC procB @msg OUT
SELECT @msg
GO
EXEC procA
注：可以使用任何数据类型(包括游标)来返回任何想要的信息
3、传送真实错误代码
CREATE PROC procC
AS
DECLARE @err int
IF @@TRANCOUNT = 0 --此全局变量返回当前连接的活动事务数
ROLLBACK TRAN --有意设置的出错语句，因未使用BEGIN TRANSACTION语句
SET @err = @@ERROR
RETURN (@err)
GO
CREATE PROC procB
AS
DECLARE @res int
EXEC @res = procC
RETURN (@res)
GO
CREATE PROC procA
AS
DECLARE @res int
EXEC @res = procB
SELECT @res
GO
EXEC procA
六、Restorer(恢复)
此模式提供一种在出错时清理资源的机制。为避免孤立一个事务，当事务活动时，适当地处理出错条件极其重要。
1、出错时回滚事务
IF OBJECT_ID('procR') IS NOT NULL
DROP PROC procR
GO
CREATE PROC procR
AS
DECLARE @err int
BEGIN TRAN
UPDATE customers SET city = 'Dallas'
SELECT 1/0 --设置一个错误
SET @err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRAN
RETURN (@err)
END
COMMIT TRAN
GO
DECLARE @res int
EXEC @res = procR
SELECT @res
注：此模式的关键部分是将错误码@@error缓存至变量@err中，如果不缓存@@error，下一执行成功的语句将重置@@error，缓存它后，如出现错误，将检查@errr的值并回滚该活动事务。
2、出错时清除临时表
CREATE PROC procR
AS
DECLARE @err int
CREATE TABLE ##myglobal(c1 int)
INSERT ##myglobal DEFAULT VALUES
SELECT 1/0 --设置一个错误
SET @err = @@ERROR
IF @err <> 0
BEGIN
DROP TABLE ##myglobal
RETURN (@err)
END
DROP TABLE ##myglobal
GO
DECLARE @res int
EXEC @res = procR
SELECT @res
3、主动执行恢复模式
CREATE PROC procR
AS
IF @@TRANCOUNT <> 0 --启动新事务前先回滚旧事务
ROLLBACK TRAN
DECLARE @err int
BEGIN TRAN
UPDATE customers SET city = 'Dallas'
SELECT 1/0 --设置一个错误
SET @err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRAN
RETURN (@err)
END
COMMIT TRAN
GO
DECLARE @res int
EXEC @res = procR
SELECT @res
注：通过@@TRANCOUNT<>0可知有活动事务，执行ROLLBACK回滚当前活动连接的所有事务。当SQL Server使用连接池时（对WEB服务器而言相当常见），
在实际应用中编写此种逻辑就非常重要。由于一个虚连接可以留下一个打开的事务，该事务会影响使用同一物理连接的后续用户，因此，通过主动地
执行Restorer模式，让代码知道如何保护自己免受“无赖”事务和其他意外残余的影响。
重要提示
T－SQL的错误处理结构也并非无懈可击，它经常不是按预期的方式或它应该的方法运行。例如，存在许多严重的足以中断当前命令批处理的错误，当这些错误出现时，它让那些可能紧跟在其后的错误处理代码根本没有机会去执行。因此，当出现问题时，即使使用@@ERROR执行代码检查并调用ROLLBACK，还会有错误禁止ROLLBACK执行。这可能是导致孤立事务存在的根本原因，而且也是在开始一个事务前应检查孤立事务的原因。
七、PROTOTYPE(原型)
此模式的目标：使用一种原型实例指定要创建对象的类型，并且通过复制原型创建新的对象。
1、最常见的实现方式是SELECT...INTO结构
SELECT *
INTO newCustomers
FROM Customers
SELECT *
INTO newCustomers
FROM Customers
WHERE country='UK'
注：通过指定一个列列表、WHERE子句、GROUP BY或HAVING子句，可在传送过程中修改原型。
2、复制表结构(T-SQL惯例中也曾提过)
SELECT *
INTO newCustomers
FROM Customers
WHERE1 = 2
或
SELECTTOP 0 *
INTO newCustomers
FROM Customers
注：通过错误的WHERE条件或不存在的行实现了复制表结构的功能
3、复制表时指定新数据
SELECT IDENTITY(int,1,1) AS CustNo,*
INTO newCustomers
FROM Customers
注：还可指定新列、通过联接选取来自其他表或视图的列、约束或函数等许多的可能性。
八、Singleton(单例）
此模式目标：确保在任何给定时间只存在一个类实例并且提供访问该实例的路径。
严格说，在关系数据库中，对于面向对象类的等价物是表。类的一个实例就是表中的一行。因此，Singleton模式的最显而意见的实现就是确保表中只包含一行。
CREATE TABLE LastCustNo
(LastCustNo int)
GO
INSERT LastCustNo VALUES(1)
GO
CREATE TRIGGER LastCustNoInsert ON LastCustNo
FOR INSERT
AS
IF (SELECT COUNT(*) FROM LastCustNo) > 1
BEGIN
RAISERROR('You May Not insert more than one row into this table!',16,10)
ROLLBACK TRAN
END
GO
INSERT LastCustNo VALUES(2) --由于触发器的原因，插入失败
GO
SELECT * FROM LastCustNo
注：由于触发器的原因，在任何时刻只允许在表中插入一行，如果表中已包含至少一行，在试图插入新的一行时将导致错误并回滚事务。
(1)IF (SELECT COUNT(*) FROM LastCustNo)必须用 > 1 ，而不能用＝1，因为除Instead Of触发器外，T－SQL触发器只在操作已完成，但还没提交给数据库前运行,这表明从触发器角度看，在事务回滚前，LastCustNo表看起来总是包含两行。
(2)禁止使用IF EXISTS(SELECT COUNT(*) FROM LastCustNo)测试表中的行，因对于触发器来说，新插入的行直到事务被回滚才出现在表中，因此，即使在插入前表为空，也将禁止向表中插入行。
实际应用：禁止一个应用的多个实例连接至服务器
方法1：应用程序锁
--锁定应用程序资源
DECLARE @res int
BEGIN TRAN
EXEC @res = sp_getapplock @Resource = 'Check Writer',@LockMode = 'Exclusive'
--返回到应用程序
--当检测到应用程序时执行以下代码(释放锁资源)
EXEC @res = sp_releaseapplock @Resource = 'Check Writer'
ROLLBACK TRAN
注：可在启动应用程序时启用一个锁，在关闭时释放该锁。通过以独占方式启用锁，可在释放该锁前禁止运行应用程序的另一个实例。
但这种方法让一个事务长期保持为打开状态。一般来说，不应该长时间或当一个用户被提示输入时让一个事务保持打开状态。
方法2：使用SET CONTEXT_INFO(推荐方法)
IF EXISTS (SELECT * FROM master..sysprocesses WHERE context_info = 0x123456)
RAISERROR('You Can run only one copy of this application at a time',20,1) WITH LOG
ELSE
SET CONTEXT_INFO 0x123456
注：使用SET CINTEXT_INFO命令在启动时间向sysprocesses插入一个用户自定义值，每次启动程序时检查该值，如果存在，则包含特定的记号连接已存在，因此产生一个错误并中止自己的连接。如不存在，则将该值保存在sysprocesses中，并继续加载应用。
九、FACADE（外观）
此模式目标：它给位于子系统的一个接口集合提供统一的接口。
在T-SQL中与此模式类似的是包含INSTEAD OF触发器的视图(INSTEAD OF触发器接受对视图的更新，并将它们分配给适当的底层表)。
CREATE TABLE AussieArtists
(ArtistID int identity,
LastName varchar(30),
FirstName varchar(30))
GO
INSERT AussieArtists VALUES('Gibb','Barry')
INSERT AussieArtists VALUES('Crowe','Russell')
INSERT AussieArtists VALUES('Hogan','Paul')
GO
CREATE VIEW VAussieArtists
AS
SELECT FirstName + '' + LastName AS Name FROM AussieArtists
GO
CREATE TRIGGER VAussieArtists_Insert ON VAussieArtists INSTEAD OF INSERT
AS
INSERT AussieArtists(FirstName,LastName)
SELECT LEFT(Name,ISNULL(NULLIF(CHARINDEX(' ',NAME),0),255)-1),
SUBSTRING(Name,NULLIF(CHARINDEX(' ',Name),0)+1,255)
FROM inserted
GO
INSERT VAussieArtists(Name) VALUES('Gerg Ham')
GO
SELECT * FROM AussieArtists
GO
DROP TABLE AussieArtists
DROP VIEW VAussieArtists
GO
注：因想在加入到基表前处理数据，所以使用INSTEAD OF触发器分析输入并执行插入数据，即对视图的简单插入被转换为对基表稍微复杂一些的插入。
十、Chain Of Responsibility(职责链)
此模式目标：为避免通过多个对象提供机会处理请求，合并请求的发送者与接收者。为实现该模式，必须串联接收对象并沿此链传送请求，直到某个对象处理它。
前面的Conveyor模式已接到责任链模式，这里再作深入讨论。在T－SQL中最接近此模式所描述行为的是嵌套触发器(触发器的执行导致其他触发器激活并实现串联行为的操作)。
CREATE TABLE employee (id int identity ,name varchar(10))
GO
CREATE TABLE laborage (id int ,salary int)
GO
CREATE TABLE laborage2(id int,number int)
GO
INSERT employee (name) values('zs')
INSERT laborage values(101,101)
INSERT laborage2 values(101,102)
GO
CREATE TRIGGER up_employee ON Employee
FOR UPDATE
AS
UPDATE laborage Set Salary = Salary + 100 WHERE id =101
GO
CREATE TRIGGER up_laborage ON laborage
FOR UPDATE
AS
UPDATE laborage2 SET number = number + 200 WHERE id = 101
GO
UPDATE Employee SET Name = 'Zxm' WHERE id = 1
GO
注：SP_CONFIGURE 'NESTED TRIGGER',0 可以禁止触发器嵌套,同时触发器最多嵌套32次。从功能角度考虑，在触发器未设定激活顺序情况下，插入请求从一个触发器传送给另一个。在任何情况下，如果其中的任一触发器拒绝插入并回滚事务，则整个操作都将被取消。
十一、COMMAND(命令)
这种模式目标：将请求一个对象来封装，允许你参数化包含不同请求、队列或日志请求的客户端，并支持可撤消操作。在T－SQL中与此模式对应的是事务。
CREATE PROC prClearLS
@intLsID int
AS
BEGIN TRAN
UPDATE Inventory SET Lease = 0 WHERE LsID = @intLsID
IF @@ERROR <> 0
GOTO PROBLEM
UPDATE LeaseSchedule
SET PeriodTotalAmount = 0
WHERE ScheduleID = @intLsID
IF @@ERROR <> 0
GOTO PROBLEM
COMMIT TRAN
RETURN 0
PROBLEM:
PRINT 'Unable to eliminate lease amounts from the database.'
ROLLBACK TRAN
RETURN 1
