最近做一个项目,其中有个需求要求轻量级备份数据库数据,结果备受T-SQL折磨。后悔以前写T-SQL的时候不总结经验,项目开发的时候要到处找资料。
现在将一些自以为有用的T-SQL总结总结,以免以后要用的时候又到处找
一、声明的变量,不赋值的时候默认都为NULL
例:DECLARE @iIndex int
DECLARE @nvchString nvarchar(400)
在做字符串联的时候 NULL+任何字符串 结果都为NULL
在做整数相乘的时候 NULL*任何整数 结果都为NULL
所以最好是在声明的变量的时候给它赋初始i值
二、用table 数据类型代替游标可以减少死锁的机会
table 数据类型的使用请参考T-SQL的帮助(输入"table 数据类型" 即可找到)
下面举个例子说明table数据类型的使用:(1)声明一个表类型变量@table,有两个列,一个是自增的id列 (2)增加一条记录到表变量里(3)查看表变量的数据记录
DECLARE @table TABLE
(
id int identity(1,1),
name nvarchar(20)
)
insert into @table
(
name
)
values
(
'eric_cheung'
)
select * from @table
三、表值函数
主要利用表值函数来存储全局的信息。(见综合例子)
四、执行bcp和DOC命令
主要是用 master..xp_cmdshell 来执行。(见综合例子)
五、动态SQL
主要是用 master..sp_executesql 来执行动态SQL。(见综合例子)
六、综合例子。
下面给出一个T-SQL的综合例子。
注意:此综合例子是项目开发中写的存储过程和函数,要有项目上下文环境,所以代码复制未必可以运行通过。
1、定义一个表值函数
CREATE FUNCTION tableList ()
RETURNS @retTableList
TABLE (id int identity(1,1) NOT NULL ,
table_name nvarchar(64) NOT NULL,
isLog int,--0表示非日志,1表示日志
type int--1表示有时间,2表示没有时间
/**//*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
INSERT @retTableList(isLog,table_name,type) values(0,'tbl_ftp',1)
INSERT @retTableList(isLog,table_name,type) values(1,'tbl_suc_log',1)
INSERT @retTableList(isLog,table_name,type) values(0,'tbl_monitor',1)
INSERT @retTableList(isLog,table_name,type) values(1,'tbl_mail_log',1)
INSERT @retTableList(isLog,table_name,type) values(0,'tbl_process',2) --没有时间
INSERT @retTableList(isLog,table_name,type) values(1,'tbl_daemon_log',1)
INSERT @retTableList(isLog,table_name,type) values(1,'tbl_download',1)
INSERT @retTableList(isLog,table_name,type) values(1,'tbl_sms_log',2) --没有时间
RETURN
END
2、执行bcp和DOC命令
CREATE procedure dbo.backupData @vchServer varchar(400), --服务器
@vchUser varchar(16), --用户名
@vchPassword varchar(32), --密码
@vchdirectory nvarchar(400), --目录
@vchStartTime varchar(30), --起始时间
@vchEndTime varchar(30), --截止时间
@vchDataBase varchar(200) --数据库名称
as
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--E:zmworkspace sgprsdataBackupaa dd中文中 文?51101-20051101-20070411144610
set @vchdirectory= REPLACE(@vchdirectory,' ','" "')--将空格转化
DECLARE @vchShell varchar(1024)
DECLARE @iIndex int --下标
DECLARE @iCount int --最多表数
DECLARE @vchTableName varchar(64)
DECLARE @iType int --1表示有时间,2表示没有时间
DECLARE @iError int --0(成功)或 1(失败)
DECLARE @param int
SET @param=0
SET @iError=1
SET @iIndex=1
--建立目录
DECLARE @vchMkDir nvarchar(400)
SET @vchMkDir='"mkdir '+@vchdirectory+'"'
PRINT @vchMkDir
SET EXEC @iError=master..xp_cmdshell @vchMkDir
IF @iError<>0--建立目录失败
BEGIN
RETURN 2
END
Set nocount on
BEGIN TRANSACTION
select @iCount=count(*) from tableList()--查找最大数
WHILE @iIndex<=@iCount
BEGIN
/**//*
导出表结构定义,以用来导入数据
EXEC master..xp_cmdshell 'bcp northwind..table1 format nul -f d: ormat1.fmt -c -T -S (local)eric -U sa -P 123321 '
*/
select @vchTableName=table_name,@iType=type from tableList() where id=@iIndex
SET @vchShell='bcp '+@vchDataBase+'..'+@vchTableName+' format nul -f '
SET @vchShell=@vchShell+@vchdirectory+@vchTableName+'.fmt -c -T -S '+@vchServer+' -U '+@vchUser+' -P '+@vchPassword
SET EXEC @iError=master..xp_cmdshell @vchShell
IF @iError<>0
BEGIN
print '导出结构出错'
print @vchShell
print @iError
ROLLBACK TRANSACTION
BREAK;
END
/**//*
按查询导出表内容
exec xp_cmdshell 'bcp "select * from northwind..table1 where column1>100 and column1<1000 " queryout d:Test.txt -c -S (local)eric -U sa -P 123321 '
EXEC master..xp_cmdshell 'bcp "select * from fsgprs2..tbl_ftp where gen_datetime>=''2006-11-12 12:09:55'' " queryout d:Test.txt -c -S 127.0.0.1 -U sa -P 123321 '
字符要有两个''
*/
SET @vchShell='bcp "select * from '+@vchDataBase+'..'+@vchTableName+' '
IF @iType=1 --按时间查
BEGIN
IF @vchStartTime is not null --查询时间不为空
BEGIN
SET @vchStartTime= Rtrim(Ltrim(@vchStartTime))
IF @vchStartTime<>''
BEGIN
SET @param=1 -- 有参数
SET @vchShell=@vchShell+' where gen_datetime>='''+@vchStartTime+''' '
END
END
IF @vchEndTime is not null --查询时间不为空
BEGIN
SET @vchEndTime= Rtrim(Ltrim(@vchEndTime))
IF @vchEndTime<>''
BEGIN
IF @param>0 --有参数
BEGIN
SET @vchShell=@vchShell+' and gen_datetime<='''+@vchStartTime+''' '
END
ELSE--还没有参数
BEGIN
SET @vchShell=@vchShell+' where gen_datetime<='''+@vchStartTime+''' '
END
END
END
--SET @vchShell=@vchShell+' where gen_datetime>='''+@vchStartTime+''' and gen_datetime<='''+@vchEndTime+''' '
END
SET @vchShell=@vchShell+'" queryout '+@vchdirectory+@vchTableName+'.dat -c -S '+@vchServer+' -U '+@vchUser+' -P '+@vchPassword
PRINT @vchShell
SET EXEC @iError=master..xp_cmdshell @vchShell
IF @iError<>0
BEGIN
print '导出内容出错'
print @iError
ROLLBACK TRANSACTION
BREAK;
END
SET @iIndex=@iIndex+1
END
IF @iError=0
BEGIN
COMMIT TRANSACTION
END
IF @iError<>0
BEGIN
--删除目录
SET @vchMkDir='"del /Q '+@vchdirectory+'"'
DECLARE @iDeleteDir int
SET @iDeleteDir=1
SET EXEC @iDeleteDir=master..xp_cmdshell @vchMkDir
IF @iDeleteDir<>0--删除目录失败
BEGIN
RETURN 3
END
END
RETURN @iError
SET QUOTED_IDENTIFIER OFF
GO
3、动态SQL
CREATE PROCEDURE restoreData3
--@vchServer varchar(400), --服务器
--@vchUser varchar(16), --用户名
--@vchPassword varchar(32), --密码
--@vchdirectory nvarchar(400)--, --目录
--@vchStartTime varchar(30), --起始时间
--@vchEndTime varchar(30) --截止时间
@vchDataBase nvarchar(200) --数据库名称
AS
--恢复数据,删除临时表
--E:zmworkspace sgprsdataBackupaa dd中文中 文?51101-20051101-20070411144610
--set @vchdirectory= REPLACE(@vchdirectory,' ','" "')--将空格转化
DECLARE @vchShell nvarchar(1024)
DECLARE @vchParam varchar(1024) --参数
DECLARE @iIndex int --下标
DECLARE @iCount int --最多表数
DECLARE @vchTableName varchar(64)
DECLARE @iType int --1表示有时间,2表示没有时间
DECLARE @iError int --0(成功)或 不为0(失败)--sql帮助错误sp_executesql,失败时返回的不一定是1,有可能是其他值
SET @iError=1
SET @iIndex=1
select @iCount=count(*) from tableList()--查找最大数
--执行单表恢复数据,从根节点到叶子节点
SET @iIndex=1
WHILE @iIndex<=@iCount
BEGIN
/**//****恢复数据********/
select @vchTableName=table_name,@iType=type from tableList() where id=@iIndex
SET @vchShell=@vchDataBase+'..restore_'+@vchTableName
print @vchShell
--执行恢复
SET EXEC @iError=master..sp_executesql @vchShell
print @iError
--删除临时表
SET @vchShell='use '+@vchDataBase+';if exists (select * from dbo.sysobjects where id = object_id(N'''+@vchDataBase+'.[dbo].[temp_'+@vchTableName+']'') and '
SET @vchShell=@vchShell+' OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+@vchDataBase+'.[dbo].[temp_'+@vchTableName+']'
print @vchShell
SET @vchShell='drop table '+@vchDataBase+'..temp_'+@vchTableName
SET EXEC @iError=master..sp_executesql @vchShell
SET @iIndex=@iIndex+1
END
RETURN 0
GO
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/eric_cheung/archive/2007/05/01/1594060.aspx