一、游标的作用：
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--删除临时表
