SQL Server双层游标嵌套

在工作中解决一个问题，想到用双层游标嵌套来解决，我知道这个效率低，可没想到好的解决办法。。。

下面贴出源码记录一下吧

--1.需要传入的变量 TB_Bill.dt_DealDate1    TB_Bill.dt_DealDate2    TB_DraftBill.s_Draftor   


create proc MyProcJian
--@sname varchar(100),
@dt_DealDate1 date,
@dt_DealDate2 date,
@s_Draftor varchar(10),
@IsRight int output    --传出参数
as 


--1.在账单表中找出符合条件的s_BillNum,用s_BillNum在TB_DraftBill表中找出有多条数据的，取出这些数据的TB_DraftBill.n_DBillID。
--再用TB_DraftBill.n_DBillID去表TB_DBillList查出对应的数据 取到TB_DBillList.n_CStatusFeeID 
--用这个TB_DBillList.n_CStatusFeeID 去表 TB_BillList 中查对应的数据 ，取出数据中的代理费 n_AttrFee，将之减去。

declare @JianAttFee int -- 记录要减去的总代理费
declare @mycount int --s_BillNum在TB_DraftBill表中找出有多条数据的的个数
set @JianAttFee = 0
declare @s_BillNum sysname  --账单号
declare @tempJian int        --单个账单号对应的临时要减去的值
declare @n_DBillID sysname    --TB_DraftBill表的主键

declare my_cursor cursor for  --声明主游标

select distinct TB_Bill.s_BillNum from TB_Bill 
inner join TB_BillList on TB_Bill.n_BillID = TB_BillList.n_BillID 
inner join TB_DraftBill on TB_Bill.s_BillNum = TB_DraftBill.s_BillNum
where TB_Bill.dt_DealDate >= @dt_DealDate1    --'2016-12-01 00:00:00' 
AND TB_Bill.dt_DealDate <=  @dt_DealDate2   --'2016-12-31 23:59:59' 
and TB_DraftBill.s_Draftor like @s_Draftor  --'%孙威龙%'

open my_cursor

fetch next from my_cursor 
into @s_BillNum
while(@@fetch_status=0)
begin
    set @mycount =(select count(*) from (select TB_DraftBill.n_DBillID from TB_DraftBill where TB_DraftBill.s_BillNum like @s_BillNum)T)
    if(@mycount>1)  --如果是有多个撰写人
    begin
        declare my_cursor2 cursor for  --声明子游标
        select TB_DraftBill.n_DBillID from TB_DraftBill where TB_DraftBill.s_BillNum like @s_BillNum and TB_DraftBill.s_Draftor not like @s_Draftor --'%孙威龙%'    
        open my_cursor2    
        fetch next from my_cursor2 
        into @n_DBillID
        while(@@fetch_status=0)
            begin
                set @tempJian = (select sum(TB_BillList.n_AttrFee) from TB_BillList inner join TB_DBillList on TB_BillList.n_CStatusFeeID = TB_DBillList.n_CStatusFeeID
                inner join TB_DraftBill on  TB_DraftBill.n_DBillID = TB_DBillList.n_DBillID
                where @mycount > 1 and TB_DraftBill.n_DBillID = @n_DBillID)
                    
                set @JianAttFee = @JianAttFee + @tempJian
            fetch next from my_cursor2 
            into @n_DBillID
            end
        close my_cursor2        --关闭子游标
        deallocate my_cursor2
        
        set @mycount = 0 --将一个账单号对应的数据数再次设置为0
    end
    else
        begin
            set @mycount = 0
        end    
        
fetch next from my_cursor into @s_BillNum
end

close my_cursor
deallocate my_cursor

--print @JianAttFee

go


--下面开始执行
declare @JianAttFee int 
exec MyProcJian '2016-12-01 00:00:00','2016-12-31 23:59:59','孙威龙', @JianAttFee output
--select @JianAttFee
print @JianAttFee

 

转载于:https://www.cnblogs.com/surfacebook007/p/6406544.html