SQL SERVER数据库中的游标

 declare opener Cursor for   

         select CRM_WL_MaterielBatchId,CRM_WL_MaterielListId,Psort,Date,Name from #temp

         where Date>=@BeginDate  order by CRM_WL_MaterielListId,Date,Name

    open  opener

    fetch next from opener into @CRM_WL_MaterielBatchId,@CRM_WL_MaterielListId,@Psort,@Date,@Code

 

    while(@@fetch_status=0)--循环开始

begin

        set @StartNumber=(select isnull(sum(StartNumber),0)  from #b where  CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId  and CRM_WL_MaterielListId=@CRM_WL_MaterielListId)

       if(@Psort='借(收入)方')

        begin

set @In=(select isnull(sum(Number),0) from #temp where Psort='借(收入)方'

and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name <=@Code

and CRM_WL_MaterielListId=@CRM_WL_MaterielListId )

set @Out=(select isnull(sum(Number),0)  from #temp where Psort='贷(支出)方'

and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId 

        and Name  in(select Name from #temp1)

and CRM_WL_MaterielListId=@CRM_WL_MaterielListId )

        end

       else

         begin

  set @In=(select isnull(sum(Number),0) from #temp where Psort='借(收入)方'

and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId 

        and Name  in(select Name from #temp1)

and CRM_WL_MaterielListId=@CRM_WL_MaterielListId )

set @Out=(select isnull(sum(Number),0)  from #temp where Psort='贷(支出)方'

and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name <=@Code

and CRM_WL_MaterielListId=@CRM_WL_MaterielListId )

         end

         

       insert into #temp1

select @Date,@Code,Remark,

@StartNumber+@In-@Out,CostPrice,(@StartNumber+@In-@Out)*CostPrice ,GroupName,@CRM_WL_MaterielBatchId,

@CRM_WL_MaterielListId,'结存' from #temp where 

CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Psort=@Psort and Date=@Date

and CRM_WL_MaterielListId=@CRM_WL_MaterielListId and Name =@Code

     

    fetch next from opener into @CRM_WL_MaterielBatchId,@CRM_WL_MaterielListId,@Psort,@Date,@Code

    end --循环结束     

close opener--关闭游标

deallocate opener