這種模式提供一種在相似對象列表中遍歷對象的標準化方法。在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

WHERE　1 = 2

或

SELECT　TOP 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