使用Transact-SQL编程

一、变量的默认值和作用域
变量的作用域（变量的生命周期）只能是当前的批处理。
新声明的变量的默认值为空值，在表达式中使用它们之前必须先为它们赋初值。
下面的例子来说明：
declare @test int,           --声明变量和数据类型
        @test2 nvarchar(25)
select @test,@test2        --查询变量的值
查询结果：
-------------------------------------
NULL             NULL

declare @test int,           --声明变量和数据类型
        @test2 nvarchar(25)
select @test,@test2        --查询变量的值
set @test=1                   --给变量赋值
set @test2='a value'
select @test,@test2        --查询变量的值
查询结果：
--------------------------------------------------------------------
NULL                       NULL

-------------------------------------------------------------------
1                             a value    

declare @test int,           --声明变量和数据类型
        @test2 nvarchar(25)
select @test,@test2        --查询变量的值
set @test=1                   --给变量赋值
set @test2='a value'
select @test,@test2        --查询变量的值
go                                 --批处理结束符
select @test,@tese2
查询结果：
----------------------------------------------------
服务器: 消息 137，级别 15，状态 2，行 8
必须声明变量 '@tese2'。

从例子可以看到：go  将该批处理分成了两个批处理，所以最后一句提示：必须声明变量 '@test'。
二、使用set和select命令
2-1  set命令和select命令都可以使用表达式为变量赋值。它们之间的主要区别在于：
select可以从表、子查询或者视图中查询数据，并且也可以包含其他的select子句；
而set命令则只能从表达式中获取数据。
set和select命令中都可以使用函数。

select语句可以查询多列，而每个列中的数据都可以赋值给一个变量。如果select语句返回了多个行，
将会把结果集中最后一行的数据赋值给变量，系统不会报告任何错误。
下面例子可以说明：
declare @ID nvarchar(25),          --声明变量和数据类型
        @Name varchar(25)
set @ID=1433                           --给变量赋值          
select @ID=userid,
       @Name=username
       from j_user  order by userid     -- 将查询的值赋给变量
select @ID,@Name
查询结果：
-------------------------------------------
T002               特卖员2

将返回的73行中的最后一行的数据赋给了变量。

如果select语句没有返回任何行,那么它就不会改变变量的值。
请看下面的例子：
declare @ID nvarchar(25),          --声明变量和数据类型
        @Name varchar(25)
set @ID=1433                           --给变量赋值          
select @ID=userid,                      --给变量赋值          
       @Name=username
       from j_user
       where userid='100'      --查找编号为100的用户
  order by userid     -- 将查询的值赋给变量
select @ID,@Name
查询结果：
-----------------------------------------
1433                   NULL

使用下面的语法也可以为变量赋值：
declare @value nvarchar(25)
select @value =username  from j_user where userid='1433'
select @value
查询结果：
--------------------------------------------
梁江平

2-2  在SQL查询中使用变量
在SQL查询中任意可以使用表达式的地方都可以使用变量。
declare @value nvarchar(25)        --声明变量
set @value='1433'                     --给变量赋值
select username from j_user
where userid=@value                  --查询条件
查询结果：
---------------------------------------
梁江平

三、流程控制
3-1   if

与很多其他语言一样，T-SQL也提供了 if 这个古老的编程语句。但T-SQL if 命令的特别之处在于：
得用它只对其后的一条语句的执行与否进行控制——一个 if 只能控制一条命令。同时它没有 then ，
以及用来结束 if 块的 end if 命令。

if 1=0  print '世界乱套！'    --if 结果不为真，不执行后面的一句，跳到下一句。
print '这是不可能滴！'
执行结果：
-------------------------------
这是不可能滴！

3-2    begin/end 

 一个 if 命令吸能控制一条语句的执行与否，这显然缺乏实用性。
要解决这个问题，可以使用 begin/end 块，它可以将多条命令作为一个
整体构成 if 命令的下一条命令。
if 1=1
  begin
     print '第一句！'
     print '第二句！'
     print '做为整体来执行！'
  end
执行结果：
-----------------------------------
第一句！
第二句！
做为整体来执行！

尽快 if 命令看起来功能有限，但实际上并非如此，你可以在它的条件子句中作用一些功能非常
强大的SQL特性，如： if exists() 和  if…in() ，其功能与 where 子句中的相应特性类似。

if exists() 结构根据SQL select命令返回的结果集是否包含有行来进行判断。它只需要查看结果集有没有行，
所在在为它编写 select 语句时应当查询全部的列（select *  ），这种方法的速度更快，if exists() 中只要查询结果
返回了一条记录，就可以停止执行查询了，转而执行批处理中其它的语句了。
if exists(select * from d_retail where sure<>1 )
begin
   print '您还有单据没有完成！'
end
执行结果：
------------------------------
您还有单据没有完成！

     if/else
else 命令是可选的，只有当  if  条件的计算结果为假时，才会执行放在 else 命令后面的代码。
else 只能对其后的一条语句或者 begin/end 块的执行与否进行控制。
if  (条件)
    语句
else
    语句或者语句块

3-3   while
    在条件为真的情况下，while 命令可以循环地执行代码。与 if 命令类似，while 命令只能循环
执行其后的一条SQL语句。如果想循环执行一组命令，则需要使用 begin/end 。
declare @temp int
  set @temp=0
  while @temp<3
    begin
        print '循环次数：'+str(@temp)
        set @temp=@temp+1
    end
执行结果：
--------------------------------------------
循环次数：         0
循环次数：         1
循环次数：         2

可以使用 continue 和 break 命令来增强 while 命令的功能，使其能够构造更为复杂的循环体。
   使用 continue 命令可以从循环体中跳回到 while 命令，并按照正常的方式测试循环条件。（继续循环）
   使用 break 命令将会立即终止循环地，并继续执行循环体以后的代码，就像 while 条件为假时一样。

以下代码代码演示输出20以内的奇数，但如果变量值为9就直接退出：
declare @temp int                    --声明变量
   set @temp=0                       --给变量赋值0
   while @temp<20                  --循环条件：变量要小于20
       begin
             if (@temp % 2=0)        --判断是否为偶数
                begin
                    set @temp=@temp+1    --变量增长1
                     continue                       --退出当前循环，继续下次循环
                 end
             if(@temp=9)                  --判断变量值是否等于9
                 break                         --终止循环体
      print str(@temp)                    --输出变量值
       set @temp=@temp+1            --变量增长1
      end

四、临时表和表变量
   4-1  局部临时表
          创建临时表的方法与创建用户定义表的方式基本相同，只是在创建临时表时必须使用由#打头的表名。
           实际上临时表是创建并保存在 tempdp 数据库中的。
               create table #TableName
               (
                      ID  int primary key,
                      Name  navrchar(25)
                )
       临时表的生命周期很短暂，当创建它的批处理或者过程结束时，临时表就被删除了。临时表的作用域是有限的。
只有创建局部临时表的连接人才能够看到它。

 4-2   全局临时表
         全局临时表与局部临时表类似，但其作用域却更为广泛，所有的用户都可以引用全局临时表。并且与局部临时表相
比，其生命周期更长，只有当最后一个引用它的会话结束之后才会将它删除。
          创建临时表需要使用邮两个##号打头的表名（##TableName），创建时首先要检查指定的全局临时表是否存在
    if  not  exists(select * from tempdp.dbo.sysobjects  where name='##TableName')
       create table  ##TableName
             (
                      ID    int,
                      Name   navrchar(25)
               )
每次SQL server 重新启动时它都会清除并重建 tempdb 数据库。

  4-3  表变量
        表变量类似于临时表，其优点在于它只存在于内存之中。表变量与变量具有同样的作用域和生命周期，只有创建它们的
 批处理、过程或者函数才能够看到它们。当这些批处理、过程或者函数结束的时候，相应的表变量也就不再存在了。
        对于表变量存在以下限制：
        1. 不能使用  select * into  或者 insert  into @tablename exec  的方法来创建表变量；
        2. 不能在函数中创建表变量；
        3. 在完整情约束方面表变量不能使用外部键或者 check 约束，只能使用主键、默认值、空值和 unique 约束；
        4. 表变量不能拥有依赖它的对象，例如触发器或者外部键；

         表变量是作为变量来声明的，不需要使用 SQL DDL 语句来创建它们。在SQL查询中引用表变量时，可以像使用
  普通表一样使用它，只不是这个表的名字是一个变量名而已。
          下面的脚本必须作为一个批处理来执行，否则就会出错：
    declare @workTable table
  (
   pk int primary key,
   coll int not null
  )
    insert into @workTable(pk,coll)
     values(1,101)
    select pk,coll from @workTable
   执行结果：
pk                    coll
-----------------------------------------
1                      101