关于 SQL Server 游标的问题。(50分)

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #1

请各位大虾帮忙看看下面的代码，我一执行就出下面的错误：
服务器: 消息 16929，级别 16，状态 1，过程 addbar，行 15
游标是 READ ONLY 的。
语句已终止。

CREATE PROCEDURE [dbo].addbar AS
declare @recno int

declare add_bar SCROLL cursor
for select std_bar, std_bar_card from std_mstr order by std_entry

open add_bar
set @recno = 0
fetch first from add_bar

while (@@fetch_status=0)
begin
set @recno = @recno + 1
update std_mstr
set std_mstr.std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno), std_mstr.std_bar_card = '@' + std_bar
where current of add_bar
fetch next from add_bar
end
deallocate add_bar
GO

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #2

create proc xxtmp
as
declare @recno int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar SCROLL cursor
for select std_bar, std_bar_card from std_mstr order by std_entry

open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card

while (@@fetch_status=0)
begin
set @recno = @recno + 1
update std_mstr
set std_mstr.std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno), @std_bar_card = '@' + @std_bar
--where current of add_bar
fetch next from add_bar into @std_bar,@std_bar_card
end
close add_bar
deallocate add_bar
--试一下这样

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #3

我照你的方法试了还是出现上面的错误信息，怎么回事？

 

唐

唐太宗

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #4

王公子的答案在我的机器上执行通过了.

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #5

把SCROLL 去掉看看

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #6

我又试了一下，这次没出错了，但是也没有把值写到那两个字段里去。是什么原因？

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #7

alter proc xxtmp
as
declare @recno int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar cursor
for select std_bar, std_bar_card from std_mstr order by std_entry
for update

open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card

while (@@fetch_status=0)
begin
set @recno = @recno + 1
update std_mstr
set std_mstr.std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno), @std_bar_card = '@' + @std_bar
where current of add_bar
fetch next from add_bar into @std_bar,@std_bar_card
end
close add_bar
deallocate add_bar

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #8

又出错了，这次的错误提示是：
服务器: 消息 16957，级别 16，状态 4，过程 xxtmp，行 6
在 READ ONLY 游标上不能指定 FOR UPDATE。

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #9

alter proc xxtmp
as
declare @recno int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar cursor SCROLL_LOCKS
for select std_bar, std_bar_card from std_mstr order by std_entry
for update

open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card

while (@@fetch_status=0)
begin
set @recno = @recno + 1
update std_mstr
set std_mstr.std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno), @std_bar_card = '@' + @std_bar
where current of add_bar
fetch next from add_bar into @std_bar,@std_bar_card
end
close add_bar
deallocate add_bar
--再不行，你看看帮助了

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #10

alter proc xxtmp
as
declare @recno int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar cursor SCROLL_LOCKS
for select std_bar, std_bar_card from std_mstr order by std_entry
for update

open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card

while (@@fetch_status=0)
begin
set @recno = @recno + 1
print '002' + replicate('0',4-len(str(@recno))) + str(@recno)
update std_mstr
set std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno),
std_bar_card = '@' + @std_bar --刚在这打错了
where current of add_bar
fetch next from add_bar into @std_bar,@std_bar_card
end
close add_bar
deallocate add_bar

--我这可以呀，不过不好意思，刚打错了，我在我机器上试过了，可以的

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #11

create procedure xxtmp
as
declare @recno int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar cursor
for select std_bar, std_bar_card from std_mstr order by std_entry
open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card

while (@@fetch_status=0)
begin
set @recno = @recno + 1
update std_mstr
set std_mstr.std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno), std_bar_card = '@' + @std_bar
where current of add_bar
fetch next from add_bar into @std_bar,@std_bar_card
end
close add_bar
deallocate add_bar

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #12

还是出上面的READ ONLY错误，我将 where current of add_bar 去掉后到是可以将值写
入字段，但是写入的却是最后一个值。也就是说如果加上 where current of add_bar就
出现READ ONLY错误，如果去掉这句游标就无法循环定位到某一条记录进行更新，而是把
所有记录都更新为当前值了。我的程序跟帮助里的例子也没什么不同的地方呀，为什么
死活就是不行呢？

 

王

王公子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-20

• #13

--那你把的表有没有自增的字段，如有可用来定位或标识行呀(设为ID)
--十脆这样算了
alter proc xxtmp
as
declare @recno int
declare @id int
declare @std_bar varchar(100),@std_bar_card varchar(100)
declare add_bar cursor SCROLL_LOCKS
for select std_bar, std_bar_card,@id from std_mstr order by std_entry
for update
open add_bar
set @recno = 0
fetch next from add_bar into @std_bar,@std_bar_card,@id

while (@@fetch_status=0)
begin
set @recno = @recno + 1
print '002' + replicate('0',4-len(str(@recno))) + str(@recno)
update std_mstr
set std_bar = '002' + replicate('0',4-len(str(@recno))) + str(@recno),
std_bar_card = '@' + @std_bar
where id=@id
fetch next from add_bar into @std_bar,@std_bar_card,@id
end
close add_bar
deallocate add_bar

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #14

接受答案了.

 

R

RedBeret

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-25

• #15

找到原因了，把order by std_entry这句去掉就行了。