Sql 使用游标
标签：
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did118543
Sql 使用游标
data_cursor CURSOR
FOR
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 T1
Go
OPEN data_cursor
DECLARE @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_cursor
DEALLOCATE data_cursor
阅读：23次