sql 流程控制

选择分支结构

1、if...else...

语法：

if 条件
    begin
        ...
    end
else
    begin
        ...
    end

exp:

declare @balance money
select @balance = (select CardMoney from BankCard where CardNo='622334589887')
if @balance >= 5000
  begin
      update BankCard set CardMoney = CardMoney - 5000
  end
else
  begin
      print '余额不足'
  end

2、case...when...

语法：

case
  when 条件 then ...
  when 条件 then ...
  ...
  else ...
end

-- 等同于
case 条件
    when 值1 then ...
    when 值2 then ...
    ...
    else ...
end

exp:

select CardNo 卡号，CardMoney 余额
case 
    when CardMoney > 300000 then 'VIP用户'
    else  '普通用户'
end 用户等级，
case CardState
    when 1 then '正常'
    when 2 then '挂失'
    else '异常'
end 银行卡状态
from BankCard

循环结构（while）

1、循环打印 1-10

declare @i int = 1
while @i < 10
begin
    print @i
    set @i = @i + 1
end

2、循环打印九九乘法表

declare @i int = 1
while @i < 9
    begin
        declare @str varchar(1000)
        declare @j int = 1
        while @j < @i
            begin
                set @str = @str + cast(@i as varchar(1)) + '*' + cast(@j as varchar(1)) + '=' + cast(@i*@j as varchar(2)) + char(9)  -- 拼接字符串，1*2 = 3
                set @j= @j + 1
            end
            print @str
    end

• char(9)：特殊字符，制表符。同样 char(10)，换行符