T-SQL经验总结----网摘

来源：互联网发布：安德罗波夫知乎编辑：程序博客网时间：2024/06/04 00:44

最近做一个项目，其中有个需求要求轻量级备份数据库数据，结果备受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