用游标来循环处理数据( DECLARE the_cursor CURSOR )

 

这是我的理解,请各位品评、品评

 

用游标循环抽奖
USE [wwdthdb00]
GO
/****** Object:  StoredProcedure [dbo].[wwdadm_lottery]    Script Date: 03/18/2013 21:53:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[wwdadm_lottery]
@F_member_id char(10)      //全局变量
AS
begin transaction
DECLARE the_cursor CURSOR      //声明游标
FOR
select F_member_id,F_cm from T_member_information WHERE F_cm>0
declare   @F_cm money     //局部变量


OPEN the_cursor  
FETCH NEXT FROM the_cursor into @F_member_id, @F_cm   //获取第一条信息
set @F_cm=(SELECT F_cm FROM T_member_information  WHERE F_member_id=@F_member_id)
while @F_cm>0
BEGIN 

set @F_cm=@F_cm-1

//局部变量
 declare @F_month_cm_id int,@F_member_lottery_cm int,@F_field_set_id int,@Sui_ji_count int,@Sui_ji int
    set @F_month_cm_id=(SELECT F_month_cm_id FROM T_month_cm where F_flag='0')
    set @Sui_ji_count=cast(ceiling(rand()*5)as int)
    set @F_field_set_id=(SELECT F_field_set_id FROM T_lottery_field_set WHERE F_lottery_id=@Sui_ji_count)
    set @Sui_ji=(SELECT F_field_set_pm FROM T_lottery_field_set WHERE F_lottery_id=@Sui_ji_count)
       update T_member_information set F_cm=F_cm-1,F_pm=F_pm+@Sui_ji where F_member_id=@F_member_id
       INSERT INTO T_member_lottery([F_month_cm_id],[F_member_id],[F_field_set_id],[F_member_lottery_cm],[F_flag]) VALUES(@F_month_cm_id, @F_member_id, @F_field_set_id, @Sui_ji,'1')

//最重要的是以下这两句

if  @F_cm=0  //判断,如果F_cm=0 的值为0,
FETCH NEXT FROM the_cursor into @F_member_id, @F_cm   //就取下一条数据,再循环做,直到数据被循环处理完。
END
CLOSE the_cursor
DEALLOCATE the_cursor
----select  F_month_cm_id,F_member_id,F_field_set_id,F_member_lottery_cm ,F_lottery_time ,F_flag from T_member_lottery where  F_lottery_time=(getdate())or F_lottery_time>(getdate())and F_flag=1

if @@error=0
 begin
  commit
  
 end
 else
 begin
  rollback
  --return 0
  end
 
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值