当前位置: 电脑软硬件应用网 > 设计学院 > 网络编程 > 数据库 > 正文 |
|
|||
带你深入了解"T-SQL"的十一种设计模式 | |||
2008-6-25 19:10:39 文/Alizze 出处:赛迪网 | |||
这种模式提供一种在相似对象列表中遍历对象的标准化方法。在SQL Server数据库中的同义词是游标。 DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES FOR READ ONLY
WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_help @table FETCH tables INTO @table END
DEALLOCATE tables
二、INTERSECTOR(交集) 这种模式是表示集合交集的一种模板。 1、推荐方法: SELECT c.companyname,o.orderid FROM customer c INNER JOIN orders o ON c.customerid = o.customerid
SELECT c.companyname,o.orderid FROM customer c ,orders o WHERE c.customerid = o.customerid
三、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%'
提供创建并执行动态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
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
DEALLOCATE spids
提供一种通过存储过程链传送信息的机制。与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
AS DECLARE @res int EXEC @res = procC RETURN (@res) GO
AS DECLARE @res int EXEC @res = procB SELECT @res GO
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
@msg varchar(128) OUT AS EXEC procC @msg OUT GO
AS DECLARE @msg varchar(128) EXEC procB @msg OUT SELECT @msg GO
CREATE PROC procC AS DECLARE @err int IF @@TRANCOUNT = 0 --此全局变量返回当前连接的活动事务数 ROLLBACK TRAN --有意设置的出错语句,因未使用BEGIN TRANSACTION语句 SET @err = @@ERROR RETURN (@err) GO
AS DECLARE @res int EXEC @res = procC RETURN (@res) GO
AS DECLARE @res int EXEC @res = procB SELECT @res GO
此模式提供一种在出错时清理资源的机制。为避免孤立一个事务,当事务活动时,适当地处理出错条件极其重要。 1、出错时回滚事务 IF OBJECT_ID('procR') IS NOT NULL DROP PROC procR GO
AS DECLARE @err int
SELECT 1/0 --设置一个错误 SET @err = @@ERROR
BEGIN ROLLBACK TRAN RETURN (@err) END
GO
EXEC @res = procR SELECT @res
CREATE PROC procR AS DECLARE @err int
SET @err = @@ERROR
BEGIN DROP TABLE ##myglobal RETURN (@err) END
GO
EXEC @res = procR SELECT @res |
|||
关于45IT | About 45IT | 联系方式 | 版权声明 | 网站导航 | |
Copyright © 2003-2011 45IT. All Rights Reserved 浙ICP备09049068号 |