用游标来循环处理数据（ 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