首页 > 代码库 > Sql 使用游标

Sql 使用游标

DECLARE data_cursor CURSORFOR    WITH    T0              AS ( SELECT   COUNT(f.DeptID) SubmitCount ,                            f.DeptID                   FROM     biz.FormCollect f                            INNER JOIN biz.Results r ON f.IID = r.ObjectID                            INNER JOIN biz.NumberLimit n ON n.DeptID = f.DeptID                   WHERE    r.[Status] = 1                   GROUP BY f.DeptID                 ),            T1              AS ( SELECT   b.BatchID ,                            c.BatchName ,                            n.IID ,                            n.DeptName ,                            t0.SubmitCount                   FROM     biz.FormCollect f                            INNER JOIN biz.BatchRelationShips b ON f.IID = b.FormID                            INNER JOIN T0 ON T0.DeptID = f.DeptID                            INNER JOIN biz.NumberLimit n ON n.DeptID = t0.DeptID                            INNER JOIN bas.ChooseBatch c ON c.IID = b.BatchID                   WHERE    c.ModuleID = 2                 )    SELECT DISTINCT            IID ,            BatchID ,            SubmitCount    FROM    T1GoOPEN data_cursorDECLARE @IID INT ;DECLARE @BatchID INT ;DECLARE @SubmitCount INT ;WHILE @@FETCH_STATUS = 0     BEGIN        FETCH NEXT FROM data_cursor INTO @IID, @BatchID, @SubmitCount                PRINT @IID;        PRINT @BatchID;        PRINT @SubmitCount;        PRINT ‘‘;                INSERT  INTO biz.NumberLimitCount                ( NumberLimitID ,                  BatchID ,                  LimitCount ,                  CurrentCount ,                  ModifyTime                )        VALUES  ( @IID , -- NumberLimitID - int                  @BatchID , -- BatchID - int                  0 , -- LimitCount - int                  @SubmitCount , -- CurrentCount - int                  GETDATE()  -- ModifyTime - datetime                )    END    CLOSE data_cursorDEALLOCATE data_cursor

 

Sql 使用游标