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