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,'房型统计','')

            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,'房型统计','')

            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