首页 > 代码库 > sql游标

sql游标

2024-10-17 18:43:38   211人阅读

记录下游标的使用，也可以在游标中嵌入循环，即双重循环，代码如下

 

declare @GName  varchar(50),@GcName varchar(50)--临时变量，用来保存游标值DECLARE My_Cursor CURSOR --定义游标FOR (SELECT GoodsName,GcName FROM dbo.Tc_RegInStore GROUP BY GoodsName,GcName) --查出需要的集合放到游标中OPEN My_Cursor; --打开游标FETCH NEXT FROM My_Cursor INTO @GName,@GcName ; --读取第一行数据WHILE @@FETCH_STATUS = 0    BEGIN    SELECT @GName AS GoodName,@GcName AS BrandName, (select ISNULL(Sum(convert(bigint,RegEndCode)-convert(bigint,RegStartCode))+count(*),0) AS InNum FROM dbo.Tc_RegInStore  WHERE GoodsName =@GName AND GcName=@GcName) AS InNum,(SELECT  count(*) AS OutNum FROM TC_LogisticsCode t INNER JOIN dbo.Tc_RegOutStore b ON convert(bigint,t.FLgsBar) BETWEEN convert(bigint,b.RegStartCode) AND convert(bigint,b.RegEndCode)WHERE  t.FGoodsName=@GName AND t.BrandName=@GcName) AS OutNum        FETCH NEXT FROM My_Cursor INTO @GName,@GcName; --读取下一行数据    ENDCLOSE My_Cursor; --关闭游标DEALLOCATE My_Cursor; --释放游标

　　

游标中嵌入循环，如下：

 

技术分享
 DECLARE @i INT; DECLARE @FAntiQryID INT; DECLARE @FFAntiFakeCode VARCHAR(50)--临时变量，用来保存游标值 DECLARE My_Cursor CURSOR --定义游标 FOR    ( SELECT    FFAntiFakeCode      FROM      dbo.temp0706      WHERE     FSystime > ‘2017-06-26‘                AND FsearchNum = 0      GROUP BY  FFAntiFakeCode    ) --查出需要的集合放到游标中 OPEN My_Cursor; --打开游标 FETCH NEXT FROM My_Cursor INTO @FFAntiFakeCode; --读取第一行数据 WHILE @@FETCH_STATUS = 0     BEGIN        IF NOT OBJECT_ID(‘Tempdb..#B‘) IS NULL             DROP TABLE #B        CREATE TABLE #B ( [FAntiQryID] INT )        INSERT  #B                SELECT  FAntiQryID                FROM    dbo.temp0706                WHERE   FFAntiFakeCode = @FFAntiFakeCode                ORDER BY FSystime DESC         --SELECT * FROM #B                SET @i = 0;        --循环码的记录数        WHILE EXISTS ( SELECT   FAntiQryID                       FROM     #B )             BEGIN                SET ROWCOUNT 1                SET @i = @i + 1                SELECT  @FAntiQryID = FAntiQryID                FROM    #B                UPDATE  dbo.temp0706                SET     FsearchNum = @i                WHERE   FAntiQryID = @FAntiQryID  --按时间递增更新查询次数                SET ROWCOUNT 0                DELETE  FROM #B                WHERE   FAntiQryID = @FAntiQryID            END        DROP TABLE #B                FETCH NEXT FROM My_Cursor INTO @FFAntiFakeCode; --读取下一行数据    END CLOSE My_Cursor; --关闭游标 DEALLOCATE My_Cursor; --释放游标
View Code

 

sql游标


联系
我们