前几天要处理一批数据,发现使用表连接,或者子查询都很难解决客户的需求,所以后来只能使用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