SQL 游标的使用

DECLARE @TempTable TABLE(
DepName VARCHAR(50),
GwName VARCHAR(50),
Code VARCHAR(50),
MaxNum INT
)

INSERT INTO @TempTable VALUES('总办本部','高管','A3',1)
INSERT INTO @TempTable VALUES('总办本部','高管','a5',2)
INSERT INTO @TempTable VALUES('总办本部','高管助理','A3',1)
INSERT INTO @TempTable VALUES('总办本部','高管助理','a2',1)
INSERT INTO @TempTable VALUES('总办战略发展处','战略发展经理','A3',1)
INSERT INTO @TempTable VALUES('总办战略发展处','战略发展经理','a5',2)

SELECT * FROM @TempTable

DECLARE myCursor CURSOR FOR SELECT * FROM @TempTable
OPEN myCursor
DECLARE @DepName VARCHAR(50)
DECLARE @GwName VARCHAR(50)
DECLARE @Code VARCHAR(50)
DECLARE @MaxNum INT
DECLARE @DepCode VARCHAR(50)
DECLARE @GwCode INT
DECLARE @FailNum INT =0
DECLARE @SuccessNum INT =0

FETCH NEXT FROM myCursor INTO @DepName,@GwName,@Code,@MaxNum
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DepName,@GwName
SELECT sDepCode=@DepCode,lgwCode=@GwCode FROM A5_Wdepartmentcls A INNER JOIN K4_JobDistribute B ON A.sDepCode=B.sDepCode INNER JOIN K4_wNewGwCls C ON B.lgwCode=C.AutoCode WHERE A.sDepName=@DepName AND C.sGwName=@GwName
IF (@DepCode IS NOT NULL AND @GwCode IS NOT NULL)
BEGIN
SET @SuccessNum=@SuccessNum+1
IF(NOT EXISTS (SELECT * FROM K0_formDepartItems WHERE ItemCode=@Code AND DepCode=@DepCode AND GwCode=@GwCode))
INSERT INTO K0_formDepartItems(ItemCode,DepCode,GwCode,MaxNum) VALUES(@Code,@DepCode,@GwCode,@MaxNum)
END
ELSE SET @FailNum=@FailNum+1

FETCH NEXT FROM myCursor INTO @DepName,@GwName,@Code,@MaxNum
END
CLOSE myCursor
DEALLOCATE myCursor

SELECT * FROM K0_formDepartItems
SELECT CAST(@SuccessNum AS VARCHAR(50)) AS 成功数量,CAST(@FailNum AS VARCHAR(50)) AS 失败数量

转载于:https://www.cnblogs.com/landry1234/p/8274555.html