sql游标使用、循环临时表

一、游标的作用：
Select时，返回的是一个结果集，若需要为结果集返回的过程中，读取到一行数据。需要对此行数据进行处理，比如按读取到的数据作为查询条件返回一个查询结果集等等，应用都需要用到游标。
游标可允许 用户 查询下一行、上一行、第一行或最后一行，并对这些读取到的行进行处理。
二、游标举例

CREATE FUNCTION GetMRPlnFullBom  --创建一函数，根据单据电键查询单据中每行，并对每行数据进行处理。   
(  
	@DocEntry int  
)  
RETURNS @TAB TABLE   -- 将最终查询的结果集定义临时表，返回。   
(  
    DocEntry int,  
    LineNum int,  
    LineNumLevel nvarchar(100),  
    ItmID nvarchar(20),   
    ItmName nvarchar(100),  
    LineType char(1),  
    Qty numeric(19,9),  
    BomLevel int,  
    ParentEntry int,  
    ParentItmID nvarchar(20),  
    TopEntry int,  
    TopItmID nvarchar(20) ,  
    BaseEntry int ,  
    BaseLineNum int ,  
    BaseType int  
)  
AS  
BEGIN  
     Declare  @ItmID varchar(50) , @Qty int ,@LineNum int ,@ObjType int   --声明局部变量   
     --声明一游标，声明游标时 XXX前不能加@，比如下面的MRPlnA_ItmID ，格式是DECLARE XXX CURSOR FOR        
     DECLARE MRPlnA_ItmID CURSOR FOR   
     SELECT ItmID , Qty ,LineNum , ObjType FROM MRPlnA  Where DocEntry = @DocEntry --语句要在DECLARE XXX CURSOR FOR与OPEN XXX之间。   
     Open MRPlnA_ItmID      --打开游标   
     FETCH NEXT FROM MRPlnA_ItmID INTO @ItmID , @Qty ,@LineNum , @ObjType --FETCH NEXT FROM XXX INTO ... 更新游标指定记录即换行，并将Select出来的数据，存入临时变量中。  FETCH格式上除了NEXT还有PRIOR、FRIST、LAST。分别是上一行、第一行及最后一行。   
     WHILE @@FETCH_STATUS = 0 --@@FETCH_STATUS全局变量，用于查询FETCH最后一次状态，控制循环。当读取完时是0，读取失败是-1，记录被删除是-2。   
     BEGIN  
         Insert Into @TAB  
         Select DocEntry,LineNum,LineNumLevel,ItmID,ItmName,LineType,Qty*@Qty,BomLevel,ParentEntry,   
                ParentItmID,TopEntry,TopItmID , @DocEntry SourceEntry ,@LineNum BaseLineNum ,@ObjType BaseType   
         From GetBomFullItems(@ItmID, 'V 1.0', GetDate()) TD --GetBomFULLItmes是另一查询函数。   
         FETCH NEXT FROM MRPlnA_ItmID INTO @ItmID  , @Qty ,@LineNum , @ObjType -- Select移到下一行。   
     END  
     CLOSE MRPlnA_ItmID       --关闭游标   
     DEALLOCATE MRPlnA_ItmID  --释放游标   
     RETURN
END

参考：https://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html
循环临时表：

USE [LT]
GO
/****** Object:  StoredProcedure [dbo].[proc_StoreTaskToLT]    Script Date: 2021/9/27 15:15:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--https://www.cnblogs.com/lusunqing/p/3660190.html
ALTER proc [dbo].[proc_StoreTaskToLT]
as

declare @ASNOutId int,
@OrderId bigint,
@asncount int,
@storeoutlinecount int,
@storeinventorycount int,
@k int,
@j int,
@l int,
@TenantId int,
@OutLineId bigint,
@DeliveryQuantity int,
@IsPicked bit,
@Comment nvarchar(max),
@Remark nvarchar(max),
@CreatorName nvarchar(max),
@DeleterName nvarchar(max),
@LastModifierName nvarchar(max),
@LastModificationTime datetime,
@IsDeleted bit,
@DeleterUserId bigint,
@DeletionTime datetime,
@LastModifierUserId bigint,
@CreationTime datetime,
@CreatorUserId bigint,
@ItemId bigint,
@LocationId bigint,
@CalQuantity int,
@InventoryId bigint,
@CurrentQuantity int
-- 查找所有已同步到WMS_trial，并且已经出库的出库单
select * into #temp_ASNOutIds from
(
	select ASNOutId,ROW_NUMBER() over(order by ASNOutId asc) as rowindex1 from LMS.WMS.dbo.ASNOut 
	where ASNOutId in
	(
		select AsnId from dbo.Store_TaskOrderOut 
		where AsnId is not null and Is2Wms is not null 
		and Is2Wms=1 and (Is2A56 is null or Is2A56=0)
	)
	and Time_Outbound is not null
) as tab
select @asncount = COUNT(1) from #temp_ASNOutIds
set @k = 1
while (@asncount >= @k)
	begin
		select @ASNOutId=ASNOutId from #temp_ASNOutIds where rowindex1=@k
		--更改 Store_TaskOrderOut Is2A56 为 1，更改 Store_OutLine DeliveryQuantity 实发数量
		update dbo.Store_TaskOrderOut set Is2A56=1 where AsnId=@ASNOutId

		update t1 set t1.DeliveryQuantity=t3.PickedQty 
		from dbo.Store_OutLine as t1 
		inner join dbo.Store_TaskOrderOutLine as t2 on t1.Id = t2.LineId
		inner join LMS.WMS.dbo.CargoOut as t3 on t2.CargoId = t3.CargoOutId
		where t2.TaskId in
		(
			select Id from dbo.Store_TaskOrderOut where AsnId=@ASNOutId
		)

		update sl set sl.DeliveryQuantity=ISNULL(sl.DeliveryQuantity,0)+t3.PickedQty,OutboundDiscountAmount = CONVERT(DECIMAL(18,2),ROUND(CONVERT(DECIMAL(18,2),ISNULL(sl.DeliveryQuantity,0)+t3.PickedQty)/ConfirmQuantity*SalesDiscountAmount,2)) 
		from dbo.Store_OutLine as t1 
		INNER JOIN dbo.Marketing_SalesOut so ON t1.Id=so.OutLineId
		INNER JOIN dbo.Marketing_SalesLine sl ON sl.Id=so.SalesLineId
		inner join dbo.Store_TaskOrderOutLine as t2 on t1.Id = t2.LineId
		inner join LMS.WMS.dbo.CargoOut as t3 on t2.CargoId = t3.CargoOutId
		where t2.TaskId in
		(
			select Id from dbo.Store_TaskOrderOut where AsnId=@ASNOutId
		)

		--Milestone Outbound 出库 120
		update t1 set t1.Milestone=120
		from dbo.Store_Out as t1
		where t1.Id in (select OrderId from dbo.Store_TaskOrderOut where AsnId=@ASNOutId)

		select *,ROW_NUMBER() over(order by OutLineId asc) as rowindex2 into #temp_Store_OutLine from
		(
			select 
			t1.[TenantId]
           ,t1.Id as [OutLineId]
           ,t1.DeliveryQuantity
           ,1 as [IsPicked]
           ,t1.[Comment]
           ,t1.[Remark]
           ,t1.[CreatorName]
           ,t1.[DeleterName]
           ,t1.[LastModifierName]
           ,t1.[LastModificationTime]
           ,t1.[IsDeleted]
           ,t1.[DeleterUserId]
           ,t1.[DeletionTime]
           ,t1.[LastModifierUserId]
           ,t1.[CreationTime]
           ,t1.[CreatorUserId]
		   ,t1.ItemId
		   ,t2.LocationId
			from dbo.Store_OutLine as t1 
			inner join dbo.Store_Out as t2 on t1.OutId = t2.Id
			where t1.OutId in(select OrderId from dbo.Store_TaskOrderOut where AsnId=@ASNOutId)
		) as tab2
		--循环出库明细
		select @storeoutlinecount = COUNT(1) from #temp_Store_OutLine
		set @j = 1
		while (@storeoutlinecount >= @j)
		begin
			--出库明细
			select @ItemId=ItemId,@LocationId=LocationId,
			@TenantId =TenantId,@OutLineId =OutLineId,@DeliveryQuantity =DeliveryQuantity,@IsPicked =1,
			@Comment =Comment,@Remark =Remark,@CreatorName =CreatorName,@DeleterName =DeleterName,@LastModifierName =LastModifierName,
			@LastModificationTime =LastModificationTime,@IsDeleted =IsDeleted,@DeleterUserId =DeleterUserId,@DeletionTime =DeletionTime,
			@LastModifierUserId =LastModifierUserId,@CreationTime =CreationTime,@CreatorUserId=CreatorUserId
			from #temp_Store_OutLine where rowindex2=@j
			--库存明细
			select *,ROW_NUMBER() over(order by InboundDate asc) as rowindex3 into #temp_Store_Inventory
			from
			(
				select Id,CurrentQuantity,ItemId,LocationId,InboundDate
				from dbo.Store_Inventory where ItemId=@ItemId and LocationId=@LocationId
			) as tab3
			--循环扣除库存
			select @storeinventorycount = COUNT(1) from #temp_Store_Inventory
			set @l = 1
			while(@storeinventorycount>=@l)
			begin
				select @CurrentQuantity=CurrentQuantity,@InventoryId=Id from #temp_Store_Inventory where rowindex3=@l
				if(@DeliveryQuantity>=@CurrentQuantity)
				begin
					set @CalQuantity = @CurrentQuantity
					set @DeliveryQuantity = @DeliveryQuantity - @CalQuantity
					
				end
				else
				begin
					set @CalQuantity = @DeliveryQuantity 
					set @DeliveryQuantity=0
				end
				--插入[Store_PickingList]，扣除Store_Inventory库存CurrentQuantity和AvailableQuantity，@DeliveryQuantity减去@CalQuantity
				INSERT INTO [dbo].[Store_PickingList]
				([TenantId]
				,[OutLineId]
				,[InventoryId]
				,[Quantity]
				,[IsPicked]
				,[Comment]
				,[Remark]
				,[CreatorName]
				,[DeleterName]
				,[LastModifierName]
				,[LastModificationTime]
				,[IsDeleted]
				,[DeleterUserId]
				,[DeletionTime]
				,[LastModifierUserId]
				,[CreationTime]
				,[CreatorUserId])
				values(
				@TenantId,
				@OutLineId ,
				@InventoryId,
				@CalQuantity,
				@IsPicked,
				@Comment,
				@Remark,
				@CreatorName,
				@DeleterName,
				@LastModifierName,
				@LastModificationTime,
				@IsDeleted,
				@DeleterUserId,
				@DeletionTime ,
				@LastModifierUserId,
				@CreationTime,
				@CreatorUserId
				);
				update dbo.Store_Inventory set CurrentQuantity=CurrentQuantity-@CalQuantity,AvailableQuantity = AvailableQuantity - @CalQuantity  where Id=@InventoryId;
				update dbo.StockBalances set CurrentQuantity = CurrentQuantity - @CalQuantity
				where LocationId=@LocationId and ItemId=@ItemId and TenantId=@TenantId

				if(@DeliveryQuantity=0)
				begin
					break
				end
				set @l = @l + 1
			end
			drop table #temp_Store_Inventory
			set @j = @j + 1
		end
		drop table #temp_Store_OutLine
		set @k = @k + 1
	end
drop table #temp_ASNOutIds--删除临时表