這種模式提供一種在相似對象列表中遍歷對象的標(biāo)準(zhǔn)化方法。在SQL Server數(shù)據(jù)庫中的同義詞是游標(biāo)。

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

注：游標(biāo)的清理代碼：在CLOSE后緊跟DEALLOCATE,實(shí)際上可以只運(yùn)行DEALLOCATE，并且游標(biāo)也能自動關(guān)閉。但這不是最自然，也不是最常見的方法。大家可以理解為：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

注：實(shí)現(xiàn)集合交集還有許多變種方法。但是慣例方法就是方法1，方法2在實(shí)現(xiàn)左(右)聯(lián)接時，條件的表示及結(jié)果都可能出現(xiàn)問題，SQL SERVER的后續(xù)版本將會取消此種聯(lián)接方式。

三、QUALIFIER(限定)

限定數(shù)據(jù)等價于篩選查詢所返回的行數(shù)。

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子句的目的是在結(jié)果集被檢索出來后再篩選查詢。實(shí)際上，SQL SERVER內(nèi)在地轉(zhuǎn)換HAVING子句為WHERE子句(兩種方法查詢的執(zhí)行計(jì)劃是相同的)，如果SQL SERVER不執(zhí)行此優(yōu)化，則針對包含大量數(shù)據(jù)行的表，因需要在篩選前從表中檢索所有行，則性能方面可能會遭受重大損失。

四、EXECTOR(運(yùn)行)

提供創(chuàng)建并執(zhí)行動態(tài)T-SQL字符串的模板

--中斷除當(dāng)前連接之外的所有用戶連接

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,因?yàn)榕cexec()相比，sp_executesql支持參數(shù)化查詢，并可從動態(tài)T-SQl調(diào)用返回一個結(jié)果代碼。如果動態(tài)代碼產(chǎn)生一個嚴(yán)重級達(dá)到或超過11的錯誤，sp_executesql將在它的結(jié)果代碼中返回錯誤碼。

五、Conveyor(傳送)

提供一種通過存儲過程鏈傳送信息的機(jī)制。與GoF的責(zé)任鏈模式(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

注：上述代碼使用了存儲過程的結(jié)果代碼從過程向過程傳遞原始返回碼的方法，即A調(diào)用B，B又調(diào)用C，C運(yùn)行時如出現(xiàn)了錯誤，則將錯誤代碼-1傳送給A。

2、通過輸出參數(shù)傳送消息

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

注：可以使用任何數(shù)據(jù)類型(包括游標(biāo))來返回任何想要的信息

3、傳送真實(shí)錯誤代碼

CREATE PROC procC

AS

DECLARE @err int

IF @@TRANCOUNT = 0 --此全局變量返回當(dāng)前連接的活動事務(wù)數(shù)

ROLLBACK TRAN --有意設(shè)置的出錯語句，因未使用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(恢復(fù))

此模式提供一種在出錯時清理資源的機(jī)制。為避免孤立一個事務(wù)，當(dāng)事務(wù)活動時，適當(dāng)?shù)靥幚沓鲥e條件極其重要。

1、出錯時回滾事務(wù)

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 --設(shè)置一個錯誤

SET @err = @@ERROR

IF @err <> 0

BEGIN

ROLLBACK TRAN

RETURN (@err)

END

COMMIT TRAN

GO

DECLARE @res int

EXEC @res = procR

SELECT @res

注：此模式的關(guān)鍵部分是將錯誤碼@@error緩存至變量@err中，如果不緩存@@error，下一執(zhí)行成功的語句將重置@@error，緩存它后，如出現(xiàn)錯誤，將檢查@errr的值并回滾該活動事務(wù)。

2、出錯時清除臨時表

CREATE PROC procR

AS

DECLARE @err int

CREATE TABLE ##myglobal(c1 int)

INSERT ##myglobal DEFAULT VALUES

SELECT 1/0 --設(shè)置一個錯誤

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、主動執(zhí)行恢復(fù)模式

CREATE PROC procR

AS

IF @@TRANCOUNT <> 0 --啟動新事務(wù)前先回滾舊事務(wù)

ROLLBACK TRAN

DECLARE @err int

BEGIN TRAN

UPDATE customers SET city = 'Dallas'

SELECT 1/0 --設(shè)置一個錯誤

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可知有活動事務(wù)，執(zhí)行ROLLBACK回滾當(dāng)前活動連接的所有事務(wù)。當(dāng)SQL Server使用連接池時（對WEB服務(wù)器而言相當(dāng)常見），

在實(shí)際應(yīng)用中編寫此種邏輯就非常重要。由于一個虛連接可以留下一個打開的事務(wù)，該事務(wù)會影響使用同一物理連接的后續(xù)用戶，因此，通過主動地

執(zhí)行Restorer模式，讓代碼知道如何保護(hù)自己免受“無賴”事務(wù)和其他意外殘余的影響。

重要提示

T－SQL的錯誤處理結(jié)構(gòu)也并非無懈可擊，它經(jīng)常不是按預(yù)期的方式或它應(yīng)該的方法運(yùn)行。例如，存在許多嚴(yán)重的足以中斷當(dāng)前命令批處理的錯誤，當(dāng)這些錯誤出現(xiàn)時，它讓那些可能緊跟在其后的錯誤處理代碼根本沒有機(jī)會去執(zhí)行。因此，當(dāng)出現(xiàn)問題時，即使使用@@ERROR執(zhí)行代碼檢查并調(diào)用ROLLBACK，還會有錯誤禁止ROLLBACK執(zhí)行。這可能是導(dǎo)致孤立事務(wù)存在的根本原因，而且也是在開始一個事務(wù)前應(yīng)檢查孤立事務(wù)的原因?！?

七、PROTOTYPE(原型)

此模式的目標(biāo)：使用一種原型實(shí)例指定要創(chuàng)建對象的類型，并且通過復(fù)制原型創(chuàng)建新的對象。

1、最常見的實(shí)現(xiàn)方式是SELECT...INTO結(jié)構(gòu)

SELECT *

INTO newCustomers

FROM Customers

SELECT *

INTO newCustomers

FROM Customers

WHERE country='UK'

注：通過指定一個列列表、WHERE子句、GROUP BY或HAVING子句，可在傳送過程中修改原型。

2、復(fù)制表結(jié)構(gòu)(T-SQL慣例中也曾提過)

SELECT *

INTO newCustomers

FROM Customers

WHERE　1 = 2

或

SELECT　TOP 0 *

INTO newCustomers

FROM Customers

注：通過錯誤的WHERE條件或不存在的行實(shí)現(xiàn)了復(fù)制表結(jié)構(gòu)的功能

3、復(fù)制表時指定新數(shù)據(jù)

SELECT IDENTITY(int,1,1) AS CustNo,*

INTO newCustomers

FROM Customers

注：還可指定新列、通過聯(lián)接選取來自其他表或視圖的列、約束或函數(shù)等許多的可能性。

八、Singleton(單例）

此模式目標(biāo)：確保在任何給定時間只存在一個類實(shí)例并且提供訪問該實(shí)例的路徑。

嚴(yán)格說，在關(guān)系數(shù)據(jù)庫中，對于面向?qū)ο箢惖牡葍r物是表。類的一個實(shí)例就是表中的一行。因此，Singleton模式的最顯而意見的實(shí)現(xiàn)就是確保表中只包含一行。

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) --由于觸發(fā)器的原因，插入失敗

GO

SELECT * FROM LastCustNo

注：由于觸發(fā)器的原因，在任何時刻只允許在表中插入一行，如果表中已包含至少一行，在試圖插入新的一行時將導(dǎo)致錯誤并回滾事務(wù)。

(1)IF (SELECT COUNT(*) FROM LastCustNo)必須用 > 1 ，而不能用＝1，因?yàn)槌齀nstead Of觸發(fā)器外，T－SQL觸發(fā)器只在操作已完成，但還沒提交給數(shù)據(jù)庫前運(yùn)行,這表明從觸發(fā)器角度看，在事務(wù)回滾前，LastCustNo表看起來總是包含兩行。

(2)禁止使用IF EXISTS(SELECT COUNT(*) FROM LastCustNo)測試表中的行，因?qū)τ谟|發(fā)器來說，新插入的行直到事務(wù)被回滾才出現(xiàn)在表中，因此，即使在插入前表為空，也將禁止向表中插入行。

實(shí)際應(yīng)用：禁止一個應(yīng)用的多個實(shí)例連接至服務(wù)器

方法1：應(yīng)用程序鎖

--鎖定應(yīng)用程序資源

DECLARE @res int

BEGIN TRAN

EXEC @res = sp_getapplock @Resource = 'Check Writer',@LockMode = 'Exclusive'

--返回到應(yīng)用程序

--當(dāng)檢測到應(yīng)用程序時執(zhí)行以下代碼(釋放鎖資源)

EXEC @res = sp_releaseapplock @Resource = 'Check Writer'

ROLLBACK TRAN

注：可在啟動應(yīng)用程序時啟用一個鎖，在關(guān)閉時釋放該鎖。通過以獨(dú)占方式啟用鎖，可在釋放該鎖前禁止運(yùn)行應(yīng)用程序的另一個實(shí)例。

但這種方法讓一個事務(wù)長期保持為打開狀態(tài)。一般來說，不應(yīng)該長時間或當(dāng)一個用戶被提示輸入時讓一個事務(wù)保持打開狀態(tài)。

方法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插入一個用戶自定義值，每次啟動程序時檢查該值，如果存在，則包含特定的記號連接已存在，因此產(chǎn)生一個錯誤并中止自己的連接。如不存在，則將該值保存在sysprocesses中，并繼續(xù)加載應(yīng)用。

九、FACADE（外觀）

此模式目標(biāo)：它給位于子系統(tǒng)的一個接口集合提供統(tǒng)一的接口。

在T-SQL中與此模式類似的是包含INSTEAD OF觸發(fā)器的視圖(INSTEAD OF觸發(fā)器接受對視圖的更新，并將它們分配給適當(dāng)?shù)牡讓颖?。

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

注：因想在加入到基表前處理數(shù)據(jù)，所以使用INSTEAD OF觸發(fā)器分析輸入并執(zhí)行插入數(shù)據(jù)，即對視圖的簡單插入被轉(zhuǎn)換為對基表稍微復(fù)雜一些的插入。

十、Chain Of Responsibility(職責(zé)鏈)

此模式目標(biāo)：為避免通過多個對象提供機(jī)會處理請求，合并請求的發(fā)送者與接收者。為實(shí)現(xiàn)該模式，必須串聯(lián)接收對象并沿此鏈傳送請求，直到某個對象處理它。

前面的Conveyor模式已接到責(zé)任鏈模式，這里再作深入討論。在T－SQL中最接近此模式所描述行為的是嵌套觸發(fā)器(觸發(fā)器的執(zhí)行導(dǎo)致其他觸發(fā)器激活并實(shí)現(xiàn)串聯(lián)行為的操作)。

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 可以禁止觸發(fā)器嵌套,同時觸發(fā)器最多嵌套32次。從功能角度考慮，在觸發(fā)器未設(shè)定激活順序情況下，插入請求從一個觸發(fā)器傳送給另一個。在任何情況下，如果其中的任一觸發(fā)器拒絕插入并回滾事務(wù)，則整個操作都將被取消。

十一、COMMAND(命令)

這種模式目標(biāo)：將請求一個對象來封裝，允許你參數(shù)化包含不同請求、隊(duì)列或日志請求的客戶端，并支持可撤消操作。在T－SQL中與此模式對應(yīng)的是事務(wù)。

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