T-SQL经验总结

又很久没有写 T-SQL了（以前做sp的时候产品开发都是用T-SQL来做）
         最近做一个项目，其中有个需求要求轻量级备份数据库数据，结果备受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