关于这段游标写法，为什么报只读？The cursor is READ ONLY.

[hansonblog]

我写了一个游标程序，但是执行后报游标是只读的，但是我排除不了问题，特在此求解！

	delete from [dbo].[Sup_ExcessInventory]
	--定义变量
		DECLARE 
		@bu varchar(20),
		@Partno varchar(20),
		@item int,
		@Sloc varchar(4),
		@Model varchar(30),
		@PlanQty int,
		@BuildDate date,
		@BPDemand numeric(18,3),
		@OnlineInventory numeric(18,3),
		@WIPDemand numeric(18,3),
		@ExcessQty numeric(18,3),
		@RemaingQty numeric(18,3),
		@SMInventory numeric(18,3),
		@UP2H numeric(18,3)
		
		insert into [dbo].[Sup_ExcessInventory] 
					([BU]
				   ,[Sloc]
				   ,[Assy#]
				   ,[Plan Qty]
				   ,[Build Date]
				   ,[Material]
				   ,[BPDemand]
				   ,[OnlineInventory]
				   ,[WIPDemand]
				   ,[ExcessQty]
				   ,[RemaingQty]
				   ,[SMInventory]
				   ,[UP2H])
           SELECT		a.BU,
						 c.SLoc,
						 a.Assy#,
						 a.[Plan Qty], 
						 a.[Build Date], 
						 b.[Component P/N] AS Material, 
						 ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000) AS BPDemand,
						 ISNULL(SUM(c.Unrestricted), 0.000) AS OnlineInventory, 
						 ISNULL(b.[Qty Per] * d.WIP_Qty, 0.000) AS WIPDemand, 
						 ISNULL(SUM(c.Unrestricted) - b.[Qty Per] * d.WIP_Qty, 0.000) AS ExcessQty, 
						 ISNULL((SUM(c.Unrestricted) - (b.[Qty Per] * d.WIP_Qty)-(b.[Qty Per] * a.[Plan Qty])),ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000)) AS RemaingQty,
						 isnull(f.AvailableQty,0.000) AS SMInventory,g.[UPH*2(material)] 
						 
			FROM         dbo.Sup_BuildPlan AS a left JOIN
						 dbo.Sup_BOM AS b ON a.[Assy#] = b.[Parent P/N] left JOIN
						 dbo.Sup_OnlineInventory AS c ON c.Material = b.[Component P/N] inner JOIN
						 dbo.Sup_WIP AS d ON d.[Assy#] = a.[Assy#] AND d.[Assy#] = b.[Parent P/N] AND d.BU = a.BU left JOIN 
						Sup_Inventory f on f.Material =b.[Component P/N] join 
						Sup_Bas_UPH g on g.[Parent P/N] = a.Assy# and g.[Component P/N] = b.[Component P/N] 							 
			--where (b.[Component P/N] = @PartNo and a.BU = @BU)				 		
			where (b.[Component P/N] = '700-19058-01' and a.BU = 'sbu' )							
			GROUP BY a.BU, c.sloc,a.Assy#, a.[Build Date], a.[Plan Qty], b.[Component P/N], b.[Qty Per],d.WIP_Qty,f.AvailableQty ,g.[UPH*2(material)]		
			ORDER BY Material,a.[Build Date] asc

		--定义游标
		DECLARE Mycursor cursor scroll dynamic FOR --非SCROLL
			select * from [dbo].[Sup_ExcessInventory] order by ITEM  asc
		--定义这个游标是可以读写的
		for update
		
		--打开游标Mycursor
		OPEN Mycursor; 
		FETCH NEXT FROM Mycursor
			INTO @BU,@Sloc,@Model ,@PlanQty ,@BuildDate,@PartNo,@BPDemand,@OnlineInventory,@WIPDemand,@ExcessQty,@RemaingQty,@SMInventory,@UP2H,@ITEM
		
		FETCH NEXT FROM Mycursor
		
		WHILE @@FETCH_STATUS = 0 --        0-FETCH statement was successful.
		BEGIN
				   		   
		   update [Sup_ExcessInventory] set 
				OnlineInventory =@RemaingQty,
				ExcessQty=@RemaingQty-WIPDemand,
				RemaingQty=@RemaingQty-BPDemand-WIPDemand
		   WHERE
				CURRENT OF Mycursor;	
			--游标指向下一行数据并重新为变量赋值		
			FETCH NEXT  FROM Mycursor
				INTO @BU,@Sloc,@Model ,@PlanQty ,@BuildDate,@PartNo,@BPDemand,@OnlineInventory,@WIPDemand,@ExcessQty,@RemaingQty,@SMInventory,@UP2H,@ITEM
			 --读取上一行计算到的remaingqty			 
			 set @RemaingQty = (Select RemaingQty from [dbo].[Sup_ExcessInventory] where item = @item -1)
		END
		
		--关闭游标
		CLOSE Mycursor
		DEALLOCATE Mycursor

执行后报错：

(5 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)
Msg 16929, Level 16, State 1, Line 74
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 74
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 74
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 74
The cursor is READ ONLY.
The statement has been terminated.

[--小F--]

游标没有打开吧？？怎么是只读状态？

[快溜]

没必要定义这么复杂，一个静态游标就可以。

[hansonblog]

有打开呀，代码有写，如下：

--打开游标Mycursor
        OPEN Mycursor; 
        FETCH NEXT FROM Mycursor
            INTO @BU,@Sloc,@Model ,@PlanQty ,@BuildDate,@PartNo,@BPDemand,@OnlineInventory,@WIPDemand,@ExcessQty,@RemaingQty,@SMInventory,@UP2H,@ITEM
        
  

[hansonblog]

一个静态游标怎么写，求指教。

[hansonblog]

你们无解，我自行结贴！

http://support.microsoft.com/kb/158773/zh-cn

对于错误 16929 最常见的原因是引用不具有主键约束或唯一索引的表的游标。在这种情况下只需创建一个主键约束或唯一索引在表上。如果使用 16929 错误的原因是 select 语句中声明 CURSOR 命令违反了用于可更新游标限制，请参阅 select 语句可以 recoded。

[唐诗三百首]

也就是说, 给Sup_ExcessInventory创建主键就ok了是吗?

[hansonblog]

是的。完全正确。