Sql游标使用2例

SQL订单状态更新与积分处理

最新推荐文章于 2025-01-05 16:54:27 发布

转载最新推荐文章于 2025-01-05 16:54:27 发布 · 50 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lilei107/archive/2011/07/26/2117032.html

本文介绍了一段SQL过程，该过程用于自动更新订单状态并处理用户积分。当订单付款成功超过7天且无异常时，将订单状态设置为已完成，并自动转移相应款项给卖家。同时，对于退券操作，每小时检查符合条件的记录并更新商品状态，增加用户的积分余额。

------------------------------------------------------
付款成功7日内无异常自动到卖家

declare @OID int
declare @SerialNumber nvarchar(100)
declare @UserID int
DECLARE @Prices_Total decimal
DECLARE @Amount decimal
declare cursor1 cursor for
SELECT [OID],[SerialNumber],[UserID],[Prices_Total] FROM [Quan_Orders] WHERE STATUS = 1 AND datediff(day,[AddTime],getdate()) > 7
open cursor1
fetch next from cursor1 into @OID,@SerialNumber,@UserID,@Prices_Total
while @@fetch_status=0
begin
DECLARE @Total decimal
SET @Total= @Prices_Total
IF (EXISTS(select Amount from Quan_Amount where UserID=@UserID))
BEGIN
select top 1 @Amount=Amount from Quan_Amount where UserID=@UserID order by id desc
SET @Total=@Total + @Amount
END
update Quan_Orders set Status=3 WHERE [SerialNumber] =@SerialNumber
INSERT INTO [Quan_Amount] ([UserID],[Type],[Detail],[Income],[Expense],[Amount],[Addtime]) VALUES (@UserID,'收入','订单:'+@SerialNumber+',收取货款',@Prices_Total,0,@Total,getdate())

fetch next from cursor1 into @OID,@SerialNumber,@UserID,@Prices_Total
end
close cursor1
deallocate cursor1
----------------------------------
退券每小时

declare @ID int
declare @BuyID int
declare @UID int
DECLARE @ExpectedPrice decimal
declare cursor1 cursor for
SELECT [ID],[BuyID],[ExpectedPrice],[UID] FROM [G_IdleCommodityLog] WHERE STATUS = 0 AND datediff(day,[AddTime],getdate())> -2
open cursor1
fetch next from cursor1 into @ID,@BuyID,@ExpectedPrice,@UID
while @@fetch_status=0
begin
update G_IdleCommodityLog set Status = 1 where id=@id
update KyUsers set yellowboy=yellowboy+@ExpectedPrice where userid=@UID
insert into yellowBoy_Detail(typeid,yellowBoy,avenueRelative,avenue,uid,addtime) values(2,@ExpectedPrice,'','退券',@UID,getdate())
update G_account set status=-2 where buyid=@BuyID
update G_buyList set status=3 where buyid=@BuyID
fetch next from cursor1 into @ID,@BuyID,@ExpectedPrice,@UID
end
close cursor1
deallocate cursor1

转载于:https://www.cnblogs.com/lilei107/archive/2011/07/26/2117032.html