SQLServer数据库循环表操作每一条数据（游标的使用）

DECLARE @FunctionCode VARCHAR(20)--声明游标变量
DECLARE curfuntioncode CURSOR FOR SELECT FunctionalityCode FROM dbo.SG_Functionality WHERE Type=2 ORDER BY TimeStamp --创建游标
OPEN curfuntioncode --打开游标
FETCH NEXT FROM curfuntioncode INTO @FunctionCode --给游标变量赋值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行执行成功
BEGIN 
PRINT @FunctionCode  --打印数据（对每一行数据进行操作）
FETCH NEXT FROM curfuntioncode INTO @FunctionCode --下一个游标变量赋值
END 
CLOSE curfuntioncode --关闭游标
DEALLOCATE curfuntioncode --释放游标

 

 

示例

CREATE PROC USP_AddPermissions_Admin ( @usercode NVARCHAR(50) ) AS

DECLARE @UserID UNIQUEIDENTIFIER

BEGIN

SET @UserID=(SELECT UserID FROM dbo.SG_User WHERE UserCode=@usercode)

DECLARE @FunctionCode VARCHAR(20)

DECLARE curfuntioncode CURSOR FOR SELECT FunctionalityCode FROM dbo.SG_Functionality WHERE Type=2 ORDER BY TimeStamp

OPEN curfuntioncode FETCH NEXT FROM curfuntioncode INTO @FunctionCode

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO SG_UserFunctionality (UserFunctionalityID,UserID,FunctionalityCode,Type)VALUES(NEWID(),@UserID,@FunctionCode ,2)

FETCH NEXT FROM curfuntioncode INTO @FunctionCode

END

CLOSE curfuntioncode

DEALLOCATE curfuntioncode

END

 操作多个字段版本

DECLARE coupon CURSOR FOR select CouponID,BatchMakeCouponID from CRM_Coupon WHERE CouponType='9' and State=4 and IssueDate<=GETDATE() --创建游标
OPEN coupon --打开游标

DECLARE @CouponID UNIQUEIDENTIFIER,@BatchMakeCouponID UNIQUEIDENTIFIER--声明游标变量
FETCH NEXT FROM coupon INTO @CouponID,@BatchMakeCouponID --给游标变量赋值

--开始循环
WHILE @@FETCH_STATUS=0
BEGIN

if (select StartDate from CRM_BatchMakeCoupon WHERE BatchMakeCouponID =@BatchMakeCouponID) is null--开始时间为空
begin 
update CRM_Coupon set State = 0, IssueBy = null, IssueDate = null, MemberID = null, MemberNO = null , StartDate = null, EndDate = null, UpdateBy = null, UpdateDate=null where CouponID=@CouponID
end
else
begin 
update CRM_Coupon set State = 0, IssueBy = null, IssueDate = null, MemberID = null, MemberNO = null , UpdateBy = null, UpdateDate=null where CouponID=@CouponID
end
 
  FETCH NEXT FROM coupon INTO @CouponID, @BatchMakeCouponID
END

CLOSE coupon --关闭游标
DEALLOCATE coupon --释放游标