sqlserver运用了值函数和游标结合

--欠款实收的函数
ALTER  FUNCTION [dbo].[getAllottedDate](@assetsid varchar(32),@type varchar(32))  
RETURNS varchar(512)    
AS  
BEGIN  
    DECLARE @result varchar(512)
    DECLARE @debtMoney float
    DECLARE @debtDate varchar(32)
    DECLARE @recoveriesMoney float
    declare @temp varchar(50)

    if(@type='shishou')
    begin
        select @debtMoney=isnull(sum(field007),0)*(-1) from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field007<0 and field001=@assetsid
        select @recoveriesMoney=isnull(sum(field007),0) from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field007>0 and field001=@assetsid
    if(@recoveriesMoney>=@debtMoney)------还清
        begin
              return @result
        end
    else----未还清,循环比较月份欠款额
            begin
            declare mycursor cursor fast_forward for select (field009+'-'+field010) as debtDate,sum(field007) as debtMoney from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field001=@assetsid and field007<0 group by field009,field010 order by (field009+field010) asc
            --打开游标
            open mycursor
            --从游标里取出数据赋值到我们刚才声明的2个变量中
            fetch next from mycursor into @debtDate,@debtMoney     
            --判断游标的状态
            -- 0 fetch语句成功    
            ---1 fetch语句失败或此行不在结果集中    
            ---2 被提取的行不存在
            while (@@fetch_status=0)
            begin     
            --显示出我们每次用游标取出的值
            set @recoveriesMoney=@debtMoney+@recoveriesMoney
            if(@recoveriesMoney<0)
            begin
                if(@result is null)
                    begin
                        set @result=@debtDate+','
                    end
                else
                    begin
                        set @result=@result+@debtDate+','
                    end            
            end 
            --用游标去取下一条记录
            fetch next from mycursor into @debtDate,@debtMoney
            end
            close mycursor    --关闭游标
            deallocate mycursor    --释放游标
            end
    end
  RETURN @result 
END 

 

posted @ 2022-08-02 14:09  java璀璨小菜鸟  阅读(50)  评论(0)    收藏  举报