3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
怎么样更新游标？请高手指教，在线等。。。
怎么样循环更新游标中每一条记录的值？请高手指教
下面的不对。
declare @cateid decimal
select @cateid=207
declare updatecateid cursor for select cate_id from newscategorys for update of cate_id
open updatecateid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM updatecateid
update updatecateid=@cateid+1
END
CLOSE updatecateid
下面的不对。
declare @cateid decimal
select @cateid=207
declare updatecateid cursor for select cate_id from newscategorys for update of cate_id
open updatecateid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM updatecateid
update updatecateid=@cateid+1
END
CLOSE updatecateid
...全文
请发表友善的回复…
发表回复
zyb_8022 2003-12-19
能不能具体解释一下其含义比哪sysname是什么意思？
zjcxc--个人微信公共号同名 2003-12-19
<>里面是楼主需要根据你的情况调整的内容
zyb_8022 2003-12-19
楼上的关于游标模板看不太明白能解释一下吗？谢谢
尖括号里的东西都代表什么意思呢？
尖括号里的东西都代表什么意思呢？
zjcxc--个人微信公共号同名 2003-12-19
--楼主仔细看下面的更新游标的模板
-- =============================================
-- Declare and using an UPDATE cursor
-- =============================================
DECLARE <@variable_1, sysname, @v1> <datatype_for_variable_1, sysname, varchar(20)>,
<@variable_2, sysname, @v2> <datatype_for_variable_2, sysname, varchar(40)>
DECLARE <cursor_name, sysname, test_cursor> CURSOR
FOR SELECT <column_1, sysname, au_fname>, <column_2, sysname, au_lname> FROM <table_name, sysname, pubs.dbo.authors>
FOR UPDATE of <column_1, sysname, au_fname>
DECLARE @count smallint
SELECT @count = 1
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1, sysname, @v1>, <@variable_2, sysname, @v2>
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user-defined code here...'
-- eg
PRINT 'updating record of ' + @v1 + ' ' + @v2
UPDATE pubs.dbo.authors
SET au_fname = @v1 + '-' + CAST(@count AS varchar(4))
WHERE au_lname = @v2
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1, sysname, @v1>, <@variable_2, sysname, @v2>
SELECT @count = @count + 1
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO
-- =============================================
-- Declare and using an UPDATE cursor
-- =============================================
DECLARE <@variable_1, sysname, @v1> <datatype_for_variable_1, sysname, varchar(20)>,
<@variable_2, sysname, @v2> <datatype_for_variable_2, sysname, varchar(40)>
DECLARE <cursor_name, sysname, test_cursor> CURSOR
FOR SELECT <column_1, sysname, au_fname>, <column_2, sysname, au_lname> FROM <table_name, sysname, pubs.dbo.authors>
FOR UPDATE of <column_1, sysname, au_fname>
DECLARE @count smallint
SELECT @count = 1
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1, sysname, @v1>, <@variable_2, sysname, @v2>
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user-defined code here...'
-- eg
PRINT 'updating record of ' + @v1 + ' ' + @v2
UPDATE pubs.dbo.authors
SET au_fname = @v1 + '-' + CAST(@count AS varchar(4))
WHERE au_lname = @v2
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO <@variable_1, sysname, @v1>, <@variable_2, sysname, @v2>
SELECT @count = @count + 1
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO
zjcxc--个人微信公共号同名 2003-12-19
--类似这样处理
--创建测试的表
create table newscategorys(id int identity(1,1),cate_id decimal)
insert into newscategorys
select 1
union all select 2
union all select 3
go
--定义更新处理的游标
declare updatecateid cursor for select id from newscategorys for update of cate_id
open updatecateid
declare @id int
FETCH NEXT FROM updatecateid into @id
WHILE @@FETCH_STATUS = 0
BEGIN
update newscategorys set cate_id=cate_id+1 where id=@id
FETCH NEXT FROM updatecateid into @id
END
CLOSE updatecateid
deallocate updatecateid
go
select * from newscategorys
drop table newscategorys
/*--测试结果
id cate_id
----------- --------------------
1 2
2 3
3 4
（所影响的行数为 3 行）
--*/
--创建测试的表
create table newscategorys(id int identity(1,1),cate_id decimal)
insert into newscategorys
select 1
union all select 2
union all select 3
go
--定义更新处理的游标
declare updatecateid cursor for select id from newscategorys for update of cate_id
open updatecateid
declare @id int
FETCH NEXT FROM updatecateid into @id
WHILE @@FETCH_STATUS = 0
BEGIN
update newscategorys set cate_id=cate_id+1 where id=@id
FETCH NEXT FROM updatecateid into @id
END
CLOSE updatecateid
deallocate updatecateid
go
select * from newscategorys
drop table newscategorys
/*--测试结果
id cate_id
----------- --------------------
1 2
2 3
3 4
（所影响的行数为 3 行）
--*/
zyb_8022 2003-12-19
declare @cateid decimal
select @cateid=207
declare updatecateid cursor for select cate_id from newscategorys for update of cate_id
open updatecateid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM updatecateid
select updatecateid=@cateid
select @cateid=@cateid+1
END
CLOSE updatecateid
deallocate updatecateid
知道了怎么更新了，可是游标更新了数据库里的值并没有更新，游标不可以更新数据库里的值吗
select @cateid=207
declare updatecateid cursor for select cate_id from newscategorys for update of cate_id
open updatecateid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM updatecateid
select updatecateid=@cateid
select @cateid=@cateid+1
END
CLOSE updatecateid
deallocate updatecateid
知道了怎么更新了，可是游标更新了数据库里的值并没有更新，游标不可以更新数据库里的值吗