Sqlserver双层游标

ALTER  PROCEDURE sp_sessionseatType_lf AS

begin

--lf 20161117

--分析场次中定义票种类型和实际出票类型差异 并且输出备注信息

declare    --声明变量

 @cSessionSeatTypeID varchar(50),--声明座位类型ID列

 @SeatPriceTypeName varchar(50), --声明座位名称列

 @sumtype int,  --声明座位类型汇总列

 @command varchar(1000),  --备注列 （用来输出或者打印信息）

 @PriceTypeName  varchar(50),

 @ticketCount int,

 @order_count int

 

--外层游标

declare cursor_session cursor for

select 

cSessionSeatTypeID ,SeatPriceTypeName,COUNT(*) as sumtype

from [sessionxxxx]

LEFT JOIN [SessionSeatType]  ON  SeatPriceTypeSessionId =xxxx AND  cSessionSeatTypeID=SeatPriceTypeId

where  cSessionOrderId!=0   group by cSessionSeatTypeID,SeatPriceTypeName 

 --打开游标

open cursor_session

--获取游标的下一行数据

fetch next from cursor_session into @cSessionSeatTypeID, @SeatPriceTypeName,@sumtype

--FETCH语句执行成功

while @@fetch_status = 0

begin

--set @command ='-'+cast(@cSessionSeatTypeID as varchar(10)) + '-'+@SeatPriceTypeName+'-'+ cast(@sumtype as varchar(10))

set @command =@SeatPriceTypeName+'　定义数 '+ cast(@sumtype as varchar(10))

--声明内层游标

declare cursor_order cursor for

select SST.SeatPriceTypeName as PriceTypeName,sum(O.ticketCount ) as ticketCount  from [Order] O

LEFT JOIN [SessionSeatType] SST ON  SST.SeatPriceTypeSessionId =xxxx AND  O.orderSpecialPfid=SST.SeatPriceTypeId

where O.orderId in (

select cSessionOrderId   from [Sessionxxxx]  where cSessionSeatTypeID =@cSessionSeatTypeID and cSessionOrderId !=0

)group by SST.SeatPriceTypeName

--打开内层游标

open cursor_order

--获取内层游标的下一行数据

fetch next from cursor_order into @PriceTypeName, @ticketCount

set @order_count=0

while @@fetch_status = 0

begin

if(@order_count=0)

begin

set @command +='  打印信息：'+@PriceTypeName+' '+cast(@ticketCount as varchar(10))+'张　'

set @order_count=1

end 

else

begin

set @command +=@PriceTypeName+' '+cast(@ticketCount as varchar(10))+'张　'

end 

fetch next from cursor_order into @PriceTypeName, @ticketCount

end

 --关闭内层游标

close cursor_order

--释放内层游标

deallocate cursor_order 

set @order_count=0

print @command

set @command=''

--EXEC (@command)

fetch next from cursor_session into @cSessionSeatTypeID, @SeatPriceTypeName,@sumtype

end

 --关闭外层游标

close cursor_session

--释放外层游标

deallocate cursor_session 

end

GO

--Exec sp_sessionseatType_lf