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