Sql 使用游标
时间：2021-07-01 10:21:17
帮助过：14人阅读
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
Sql 使用游标
标签：
