sqlserver 存储过程中游标嵌套循环

SQL Server的又表示可以嵌套的，下面是程序中的一个存储过程

 

 

ALTER PROCEDURE dbo.TaxiBatchClose

AS

begin
 /* SET NOCOUNT ON */
 SET NOCOUNT ON

 declare @NowTime datetime
 set @NowTime=getdate()

……

   --利用游标修改每一个没有关闭的订单

 

 DECLARE TaxiForms_cursor CURSOR FOR
  SELECT [OrderID],[UserName],[StartTime]
  FROM TaxiForms WITH (UPDLOCK)
  WHERE OrderStatus='卖票中' AND datediff(day,StartTime,@NowTime)>7
 OPEN TaxiForms_cursor
 FETCH NEXT FROM TaxiForms_cursor
 INTO @OrderID,@SellerName,@StartTime
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --开始内循环游标，处理每一张车票
  DECLARE Ticket_cursor CURSOR FOR
   SELECT [TicketID],[BuyerName],[Count]*[Price],[TicketStatus]
   FROM Tickets WITH (UPDLOCK)
   WHERE OrderID=@OrderID AND TicketStatus<>'temp' AND TicketStatus<>'关闭' AND AppealStatus<>'--'
  OPEN Ticket_cursor
  FETCH NEXT FROM Ticket_cursor
  INTO @TicketID,@BuyerName,@fee,@TicketStatus
  WHILE @@FETCH_STATUS = 0
  BEGIN
     
     
   ……

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM Ticket_cursor
   INTO @TicketID,@BuyerName,@fee,@TicketStatus
  END
  CLOSE Ticket_cursor
  DEALLOCATE Ticket_cursor
    
    

  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM TaxiForms_cursor
  INTO @OrderID,@SellerName,@StartTime
 END
 CLOSE TaxiForms_cursor
 DEALLOCATE TaxiForms_cursor

 RETURN
end