分享
 
 
 

带你深入了解"T-SQL"的十一种设计模式

王朝mssql·作者佚名  2008-06-26
窄屏简体版  字體: |||超大  

一、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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有