SQL双重游标（双重循环）--笔记

declare
@_substoreid varchar(50)='',
@_depart varchar(50)='',
@_win_name varchar(50)=''
--创建游标
declare @cursor cursor
--设定游标欲操作的数据集
set @cursor=cursor for
select substoreid,depart from cur_user group by substoreid,depart
--打开游标
open @cursor
--移动游标指向到第一条数据，提取第一条数据存放在变量中
fetch next from @cursor into @_substoreid,@_depart
--如果上一次操作成功则继续循环
while(@@fetch_status=0)begin
--操作提出的数据
    declare @cursor_new cursor
    set @cursor_new=cursor for
    select win_name from win_name
    
    open @cursor_new
    fetch next from @cursor_new into @_win_name
    while(@@fetch_status=0)begin
--         begin tran
            insert into user_win values('',@_win_name,'Y','',@_substoreid,@_depart)
--             if @@Error<>0
--             begin 
--                 print @_win_name+','+@_substoreid+','+@_depart
--                 ROLLBACK TRANSACTION--事务回滚语句
--             end
--             else
--             begin
--                 COMMIT TRANSACTION--事务提交语句
--             end
--         
--         end
        
        fetch next from @cursor_new into @_win_name
    end
    close @cursor_new
    deallocate @cursor_new
    
    --继续提下一行
    fetch next from @cursor into @_substoreid,@_depart
end
--关闭游标
close @cursor
--删除游标
deallocate @cursor

 

declare
@_substoreid varchar(50)='',
@_date varchar(50)='2014-08-20',--开始时间
@_endDate varchar(50)='2014-08-22'--getdate()--结束时间(不包含当天)

while(DATEDIFF(day,@_date,@_endDate)>0)begin
--创建游标
declare @cursor cursor
--设定游标欲操作的数据集
set @cursor=cursor for
select substoreid from hotelid
--打开游标
open @cursor
--移动游标指向到第一条数据，提取第一条数据存放在变量中
fetch next from @cursor into @_substoreid
--如果上一次操作成功则继续循环
while(@@fetch_status=0)begin
--操作提出的数据
    if not exists(select id from receive_report where substoreid=@_substoreid and the_date=@_date and name='房型统计' and id='总房数')
    begin
        declare @curroomnums decimal(10,1)
        select @curroomnums=isnull(all_rooms,-1) from receive_report where substoreid=@_substoreid and the_date=@_date and name='租类' and id='合计'
        if @curroomnums is not null and @curroomnums>0
        begin
            declare @the_class varchar(20)='',
            @total decimal(10,1)

            declare @cursor_new cursor
            set @cursor_new=cursor for
            select the_class,count(*) as total from home where substoreid=@_substoreid group by the_class

            open @cursor_new
            fetch next from @cursor_new into @the_class,@total

            while(@@fetch_status=0)begin
                insert into receive_report(id,substoreid,day_rooms,the_date,name,sort_flag)
                     values(@the_class,@_substoreid,@total,@_date,'房型统计','24')
            
            fetch next from @cursor_new into  @the_class,@total
            end

            close @cursor_new
            deallocate @cursor_new

            insert into receive_report(id,substoreid,day_rooms,the_date,name,sort_flag)
                 values('总房数',@_substoreid,@curroomnums,@_date,'房型统计','24')
            print @_substoreid+' '+@_date
        end
    end
    
    --继续提下一行
    fetch next from @cursor into @_substoreid
end
--关闭游标
close @cursor
--删除游标
deallocate @cursor

set @_date= convert(varchar(12), DATEADD(day,1,@_date),23)
end