一个sql游标使用例子

     前几天要处理一批数据，发现使用表连接，或者子查询都很难解决客户的需求，所以后来只能使用sql游标，加sql程序块来处理。

    我想自己以后用得着。

begin try
    begin transaction;

    declare @currentTime            datetime;
    select @currentTime = sysdate from (select Getdate() as sysdate) sys;
    declare @var_userID int
    declare @var_currentUserPoint int   --当前用户可用积分
    declare @var_Total2012Point int   --2012年总积分
    declare @var_Admin2012Point int   --2012管理员调整积分
    declare @var_Userd2012Point int   --2012使用积分
    declare @var_Total2013Point int   --2013年总积分
    declare @var_Admin2013Point int   --2013管理员调整积分
    declare @var_Userd2013Point int   --2013使用积分
    declare @var_currentPoint int --应保留积分
    declare @var_cleanPoint int --应扣除积分
    
    declare cursor_user CURSOR 
 FOR select u.UserId, u.Point
          from UserInfo u
         where u.DepartmentId=2 
         and u.DepartmentPropertyId in (4,12)--客户类型工业市场客户、分销商
         and u.UserId not in (10000046,10000208,10000209,10000222,10000223)

  
 OPEN cursor_user 

 --循环处理积分
 FETCH NEXT FROM cursor_user 
  INTO @var_userID, @var_currentUserPoint
 while @@FETCH_STATUS = 0
  begin
    --2012年总积分
        select @var_Total2012Point = isnull(sum(quantity*point),0) from BuyHistory where userid = @var_userID  and ( buydate >= '2012-01-01 00:00:00' and buydate <= '2012-12-31 23:59:59') and ProductName !='部门管理员调整积分'
     --2012管理员调整积分
         select @var_Admin2012Point = isnull(sum(quantity*point),0)  from BuyHistory where userid = @var_userID and ( buydate >= '2012-01-01 00:00:00' and buydate <= '2012-12-31 23:59:59') and ProductName ='部门管理员调整积分'
         --2012使用积分
      select @var_Userd2012Point = isnull(sum(Quantity*point),0) from RedemptionDetail where RedemptionID in (select RedemptionID from GiftRedemption where userId = @var_userID and statusNumber not In ( '100003','100007')  and  redemptionDate >= '2012-01-01 00:00:00' and redemptionDate <= '2012-12-31 23:59:59' )
     --2013总积分
     select @var_Total2013Point = isnull(sum(quantity*point),0)  from BuyHistory where userid = @var_userID  and ( buydate >= '2013-01-01 00:00:00' and buydate <= '2013-12-31 23:59:59') and ProductName !='部门管理员调整积分'
     --2013管理员调整积分
        select @var_Admin2013Point = isnull(sum(quantity*point),0) from BuyHistory where userid = @var_userID and ( buydate >= '2013-01-01 00:00:00' and buydate <= '2013-12-31 23:59:59') and ProductName ='部门管理员调整积分'
        --2013 使用积分
        select @var_Userd2013Point = isnull(sum(Quantity*point),0) from RedemptionDetail where RedemptionID in (select RedemptionID from GiftRedemption where userId = @var_userID and statusNumber not In ( '100003','100007')  and  redemptionDate >= '2013-01-01 00:00:00' and redemptionDate <= '2013-12-31 23:59:59' )
            -- Add BuyHistory 
     --计算公式 当前可用积分=当前用户积分-（当前总积分-2012年总积分-2012年管理员调整-2013年总积分-2013年调整+2012年使用积分+2013年使用积分）
     --要扣除的积分
    set @var_cleanPoint = @var_currentUserPoint - @var_Total2012Point - @var_Admin2012Point- @var_Total2013Point - @var_Admin2013Point+ @var_Userd2012Point+@var_Userd2013Point
    if @var_cleanPoint<=0 set @var_cleanPoint =0
    --计算要保留积分
    set @var_currentPoint = @var_currentUserPoint - @var_cleanPoint
     -- Add PointAdjust
      insert BuyHistory(
                   BuyDate,
                   Quantity,
                   ProductName,
                   SubmitDate,
                   Unit,
                   Remark,
                   Point,
                   UserId,
                   Price,
                   Type,
                   TotalPoint
                 )
           Values( @currentTime, 
                   1,
                   '部门管理员调整积分',
                   @currentTime,
                   '次',
                   '',
                   -@var_cleanPoint,
                   @var_userID,
                   0,
                   100002,
                   -@var_currentUserPoint
                 )
            insert PointAdjust(
                   Remark,
                   Point,
                   UserId,
                   AdjustDate
                 )
           Values( '管理员调整',
                   -@var_cleanPoint,
                   @var_userID,
                   @currentTime
                 )
             
             -- Update UserInfo
             update UserInfo 
                set Point = @var_currentPoint
              where DepartmentId=2
                and UserId = @var_userID 
     print @var_userID
             -- Update UserInfo
             FETCH NEXT FROM cursor_user 
    INTO @var_userID, @var_currentUserPoint
  end  
 CLOSE cursor_user
 DEALLOCATE cursor_user
   
    commit transaction;
end try
begin catch
        print @@TRANCOUNT
        -- Rollback any active OR uncommittable transactions before
        if @@TRANCOUNT > 0
        rollback transaction;
end catch