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

最新推荐文章于 2024-11-26 14:33:05 发布

原创最新推荐文章于 2024-11-26 14:33:05 发布 · 3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #存储 #sql server

SQL Server 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个SQL Server存储过程示例，该过程使用游标嵌套来处理未关闭的订单。外层游标遍历状态为'卖票中'且超过7天的订单，内层游标则遍历这些订单中的每张车票进行进一步操作。通过这种方式，存储过程实现了对订单和车票状态的批量更新。

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

ALTER PROCEDURE dbo.TaxiBatchClose

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