sql server 游标
---创建游标
DECLARE @ID INT,@EXAMINE INT ----申明变量
DECLARE @COUNT INT=0 ----申明变量
DECLARE MY_CURSOR CURSOR FOR SELECT ID,EXAMINE FROM dbo.CUSTOMERS WHERE EXAMINE>=90 ---定义游标并向游标里加入数据
OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @ID,@EXAMINE ----打开游标,将游标下移一行,并将数据赋值给变量
WHILE @@FETCH_STATUS=0 -----判断是否取到数据
BEGIN
IF @EXAMINE>90 ----绩效考核大于90的加200
BEGIN
UPDATE dbo.CUSTOMERS SET SALARY=SALARY+200 WHERE id=@ID
SELECT @COUNT=@COUNT+1 -----累计修改数据的条数
PRINT @ID; -----打印修改数据的行的id
FETCH NEXT FROM MY_CURSOR INTO @ID,@EXAMINE ---将游标下移一行,并将数据赋值给变量
END
ELSE -------绩效考核等于90的加20
BEGIN
UPDATE dbo.CUSTOMERS SET SALARY=SALARY+20 WHERE id=@ID
SELECT @COUNT=@COUNT+1 -----累计修改数据的条数
PRINT @ID; -----打印修改数据的行的id
FETCH NEXT FROM MY_CURSOR INTO @ID,@EXAMINE ---将游标下移一行,并将数据赋值给变量
END
END
PRINT @COUNT
CLOSE MY_CURSOR ----关闭游标
DEALLOCATE MY_CURSOR -------销毁游标
表数据如下图所示: