SQL SERVER游标问题--所请求的行不在提取缓冲区内 - 编程论坛

#2

cnfarer2010-01-31 13:56

IF OBJECT_ID ('dbo.Tab', 'U') IS NOT NULL
    DROP TABLE dbo.Tab;
GO
CREATE TABLE dbo.Tab
    (id varchar(15), address varchar(15));
GO
INSERT INTO dbo.Tab VALUES ('1', '10');
INSERT INTO dbo.Tab VALUES ('2', '10');
INSERT INTO dbo.Tab VALUES ('3', '10');
INSERT INTO dbo.Tab VALUES ('4', '40');
GO

declare cur_tab cursor for
select ID,address from tab
for update of ID
open cur_tab

declare @xuhao1 varchar(15),@xuhao2 varchar(15),@addr1 varchar(15),@addr2 varchar(15)
fetch next from cur_tab into @xuhao1,@addr1
fetch next from cur_tab into @xuhao2,@addr2
while @@fetch_status=0
begin

  if @addr1=@addr2
    update tab set ID=@xuhao1 where current of cur_tab
  else
    update tab set ID=@xuhao1+1 where current of cur_tab
  set @xuhao1=@xuhao2
  set @addr1=@addr2
  fetch next from cur_tab into @xuhao2,@addr2
end
close cur_tab
deallocate cur_tab

#3

qianliemao2010-01-31 15:59

谢谢楼上,这样不报错,不过结果还是不对.
按楼上给的测试数据,执行结果中ID 应该依次为 1,1,1,2
但是实际执行结果是: 1,1,2,3

可见还是没有达到目的!

#4

cnfarer2010-02-01 08:02

那是你的算法有问题(按照你写的东西,就是这样的结果)加上红色部分就行了
declare cur_tab cursor for
select ID,address from tab
for update of ID
open cur_tab

declare @xuhao1 varchar(15),@xuhao2 varchar(15),@addr1 varchar(15),@addr2 varchar(15)
fetch next from cur_tab into @xuhao1,@addr1
fetch next from cur_tab into @xuhao2,@addr2
while @@fetch_status=0
begin

  if @addr1=@addr2
    update tab set ID=@xuhao1 where current of cur_tab
  else
begin
    update tab set ID=@xuhao1+1 where current of cur_tab
  set @xuhao1=@xuhao1+1
  set @addr1=@addr2
end
  fetch next from cur_tab into @xuhao2,@addr2
end
close cur_tab
deallocate cur_tab

[ 本帖最后由 cnfarer 于 2010-2-2 05:34 编辑 ]