SQL游标的基本使用方式

<pre name="code" class="sql">SELECT * INTO  #tmpForVif FROM (
	SELECT	b.id as Account_Id
		  ,a.[RealName] AS 姓名
		  ,b.[UserMobile] AS 手机号
		  ,CASE b.[UserType] 
		   WHEN 4 THEN '普通用户' 
		   WHEN 6 THEN '经纪人'
		   WHEN 8 THEN '组长' 
		   WHEN 10 THEN '自由经纪人' 
		   end AS 角色
		  ,a.[BankCard_Number] AS 收款账号
		  ,a.BankCard_BankName as 开户行
		  ,c.[Total] AS 总金额
		  ,c.[Available] AS 可用金额
		  ,c.[Available_cash] AS 可提现金额
		  ,c.[Frozen] AS 冻结金额
		  ,CASE a.[Gender] WHEN 1 THEN '女'
						   WHEN 2 THEN '男'
						   WHEN 4 THEN '保密' END as 性别
		,a.createat as 创建时间
		,getDate() as 保存时间
		,a.id as Member_ID
		,(SELECT SUM(dbo.CFPlan.ExpectedAnnualizedRate*CFPlan.Price*dbo.OrderInfo.Count*Days*0.01/360)+SUM(CFPlan.Price*dbo.OrderInfo.Count) AS YDJR 
		FROM dbo.[Plan] INNER JOIN dbo.CFPlan ON CFPlan.Plan_id = [Plan].Id LEFT JOIN dbo.OrderInfo ON OrderInfo.Plan_id = CFPlan.Plan_id WHERE orderinfo.Status=2 AND  Member_id=a.Id) as 应兑付金额
		,(SELECT SUM(dbo.CFPlan.ExpectedAnnualizedRate*0.01*CFPlan.Price*dbo.OrderInfo.Count*Days/360)+SUM(CFPlan.Price*dbo.OrderInfo.Count) AS CGDFJR FROM dbo.[Plan] INNER JOIN dbo.CFPlan ON CFPlan.Plan_id = [Plan].Id LEFT JOIN dbo.OrderInfo ON OrderInfo.Plan_id = CFPlan.Plan_id WHERE OrderInfo.status=2 and Cash=2 and Member_id=a.Id and OrderInfo.IsCash=1
		) as 成功兑付金额

		FROM VirtualAccount b  
		inner join [member] a  on a.VirtualId = b.id and a.IsDeleted=0 inner join VirtualCapital c on b.id=c.Account_Id

	) AS t 
	
	DECLARE @pay10 decimal(18,2),--10天内充值金额
	@koukuan10 decimal(18,2),--10天内扣款
	@amount10 decimal(18,2),--10天内第一笔充值后消费金额
	@cash decimal(18,2),--可提现的
	@Total decimal(18,2),--可用金额
	@accountId uniqueidentifier,--虚拟账户id
	@memberId uniqueidentifier--用户id

	DECLARE My_Cursor CURSOR --定义游标
	FOR (SELECT Account_Id,Member_ID,可用金额 FROM #tmpForVif) --查出需要的集合放到游标中
	OPEN My_Cursor; --打开游标
	FETCH NEXT FROM My_Cursor into @accountId,@memberId,@Total; --读取第一行数据
	WHILE @@FETCH_STATUS = 0
		BEGIN
			
			select @pay10=sum(isnull(Pay_amount,0)) from virtualorderinfo where account_id=@accountId and CreateAt>= dateadd(Day,-10,getdate())  and CreateAt <= getdate() and status=2 and payflag=1
			select @koukuan10=sum(isnull(Pay_amount,0)) from virtualorderinfo where account_id=@accountId and CreateAt>= dateadd(Day,-10,getdate())  and CreateAt <= getdate() and status=2 and payflag=-1
			select @amount10= sum(isnull(Payamount,0))
			from orderinfo where 
			member_id=@memberId
			and CreateAt>= (select ISNULL(min(createat),dateadd(Day,-10,getdate())) from virtualorderinfo where  account_id=@accountId and CreateAt>= dateadd(Day,-10,getdate())  and CreateAt <= getdate() and status=2 and payflag=1) 
			and CreateAt <=getdate()  and status=2
			if (@pay10 - @amount10 + @koukuan10 >= 0)
				set @cash = @Total - @pay10 +@amount10 - @koukuan10;
			else
				set @cash = @Total;
			
			update #tmpForVif set 可提现金额 = @cash  WHERE CURRENT OF My_Cursor;
			FETCH NEXT FROM My_Cursor into @accountId,@memberId,@Total; --读取下一行数据
		END
	CLOSE My_Cursor; --关闭游标
	DEALLOCATE My_Cursor; --释放游标
	GO

	select * from #tmpForVif order by 手机号 asc

	DROP TABLE #tmpForVif