游标使用

CREATE PROC SyncFlagOperate_Proc
AS
BEGIN
-- 声明表变量，数据存在内存中、没有索引、没有索机制、不会生成SQL日志。适用于较小数据量
DECLARE  @A TABLE(ID INT,ActionType INT,Operator VARCHAR(50),OperateDate DATETIME,
		ItemId INT,FlagId VARCHAR(50),Unit VARCHAR(5),LabId INT,ItemName VARCHAR(500))  
INSERT INTO @A
	SELECT  B1.ID
		,B1.ActionType
		,B1.Operator
		,B1.OperateDate
		,B3.ItemId
		,B2.FlagId
		,B3.Unit
		,B1.LabId
		,B3.ItemName
	FROM [NCLab_ClientOperateLog] AS B1
		LEFT JOIN [NCLab_RfidFlag] AS B2
			ON B1.FlagId = B2.FlagId
		LEFT JOIN [NCLab_Item] AS B3
			ON B2.ItemId = B3.ItemId
	WHERE SignStatus = 0 AND ActionType = 10 AND B2.FlagId IS NOT NULL




DECLARE cursor1 CURSOR Static  -- 游标遍历
FOR SELECT * FROM @A

OPEN cursor1  -- 打开游标
DECLARE @ID INT,@ActionType INT,@Operator VARCHAR(50),@OperateDate DATETIME,
		@ItemId INT,@FlagId VARCHAR(50),@Unit VARCHAR(5),@LabId INT,@ItemName VARCHAR(500)
FETCH NEXT FROM cursor1 INTO 
	@ID,@ActionType,@Operator,@OperateDate,
	@ItemId,@FlagId,@Unit,@LabId,@ItemName  --从游标变量中读取值
WHILE (@@FETCH_STATUS=0) --判断FETCH语句是否执行成功
	BEGIN
	select @LabId,@FlagId,@ItemName,@Operator,@OperateDate,@ID,@OperateDate
		
		FETCH NEXT FROM cursor1 INTO 
	@ID,@ActionType,@Operator,@OperateDate,
	@ItemId,@FlagId,@Unit,@LabId,@ItemName--读取游标变量中的数据
	END
END


EXEC SyncFlagOperate_Proc

　　

转载于:https://www.cnblogs.com/LTEF/p/10939459.html