sql游标使用、循环临时表

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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
循环临时表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202

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--删除临时表