请各位高手指点一下游标的使用问题

[caocao81]

我的希望的操作是将一个表A里的部分数据先保存出来到B（临时表），然后修改B的数据之后再重新插入到A中，A中有一个字段number值是自增的。我想到的方法是使用游标，将B中的数据一条条插入到A中，但是在存储过程中使用游标的时候，只向A中插入了一条数据，后面就报错了“服务器: 消息 16916，级别 16，状态 1，过程 CopyMail，行 44
名为 'CustomerCus' 的游标不存在。”
请高手帮忙指点一下。
另外除了我这个方法之外是否还有更好的方法？源代码如下：
use SmartCRM

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CopyMail' AND type = 'P')
DROP PROCEDURE CopyMail

GO

CREATE PROCEDURE CopyMail @OldOwner varchar(50),@NewOwner varchar(50)

AS

Begin

IF OBJECT_ID ( 'IO_Usermail_temp', 'U' ) IS NOT NULL
DROP table IO_Usermail_temp

--将临时帐户中的邮件信息复制到临时表
select * into IO_Usermail_temp from IO_UserMail where UserName = @OldOwner order by Number

--设置IO_Usermail_temp表中的邮件所有者为新的帐户
update IO_Usermail_temp
set UserName = @NewOwner

--使用游标向IO_UserMail插入数据

DECLARE
@UserName nvarchar(30),@Parent int,@Catalog nvarchar(250),@Tag int,@Readed bit,@Deleted bit,@DA_Status int,
@DA_UserName int,@DA_Date datetime,@DA_Content nvarchar(250),@RE_Title nvarchar(100),@RE_Time datetime,@RE_Finished bit,
@Remark nvarchar(250),@Handled bit,@NeedFollow bit, @FollowFrom nvarchar(30),@FollowLimitDate datetime,
@Followed bit,@HandledFlag int

DECLARE IO_UserMail_Cur CURSOR Global FOR
select UserName,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag
from IO_Usermail_temp

open IO_UserMail_Cur

fetch IO_UserMail_Cur into @UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag
while(@@fetch_status=0)
begin
insert into IO_UserMail(UserName,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag)
values(@UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag)

fetch next from CustomerCus into @UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag
end

close IO_UserMail_Cur
deallocate IO_UserMail_Cur

--删除临时表
--DROP table IO_Usermail_temp

end

[ggtggt]

study

[claro]

帮顶

[Ny-6000]

尽量不要使用.

完全可以使用循环,等别的方法代替.

[JonasFeng]

能不用游标的尽量不要用。

负责数据量大，或者并发多的时候会带来很多问题。

[ChinaJiaBing]

use SmartCRM

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CopyMail' AND type = 'P')
DROP PROCEDURE CopyMail

GO

CREATE PROCEDURE CopyMail @OldOwner varchar(50),@NewOwner varchar(50)

AS

Begin

IF OBJECT_ID ( 'IO_Usermail_temp', 'U' ) IS NOT NULL
DROP table IO_Usermail_temp

--将临时帐户中的邮件信息复制到临时表
select * into IO_Usermail_temp from IO_UserMail where UserName = @OldOwner order by Number

--设置IO_Usermail_temp表中的邮件所有者为新的帐户
update IO_Usermail_temp
set UserName = @NewOwner

--使用游标向IO_UserMail插入数据

DECLARE
@UserName nvarchar(30),@Parent int,@Catalog nvarchar(250),@Tag int,@Readed bit,@Deleted bit,@DA_Status int,
@DA_UserName int,@DA_Date datetime,@DA_Content nvarchar(250),@RE_Title nvarchar(100),@RE_Time datetime,@RE_Finished bit,
@Remark nvarchar(250),@Handled bit,@NeedFollow bit, @FollowFrom nvarchar(30),@FollowLimitDate datetime,
@Followed bit,@HandledFlag int

DECLARE IO_UserMail_Cur CURSOR Global FOR
select UserName,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag
from IO_Usermail_temp

open IO_UserMail_Cur

fetch IO_UserMail_Cur into @UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag
while(@@fetch_status=0)
begin
insert into IO_UserMail(UserName,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag)
values(@UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag)

fetch next from CustomerCus into --这一句@UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag
end

close IO_UserMail_Cur
deallocate IO_UserMail_Cur

--删除临时表
--DROP table IO_Usermail_temp

end

fetch next from io_userMail into @UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date , 
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate , 
@Followed ,@HandledFlag 

[QQQQAnnie]

为什么要用游标呢，效率低。

[QQQQAnnie]

你根本就没建产CustomerCus这个游标，

只有在最后用了。

fetch next from CustomerCus into @UserName,@Parent ,@Catalog,@Tag ,@Readed ,@Deleted ,@DA_Status ,@DA_UserName ,@DA_Date ,
@DA_Content ,@RE_Title ,@RE_Time ,@RE_Finished ,@Remark ,@Handled ,@NeedFollow , @FollowFrom ,@FollowLimitDate ,
@Followed ,@HandledFlag

[sdhdy]

楼主可以参考一下这个：
http://blog.csdn.net/sdhdy/archive/2009/05/14/4184412.aspx

[sdhdy]

--没必要用游标和临时表，也不必存储过程，只需要2个变量，一条语句就可以搞定！
--注意红色的字体
insert into IO_UserMail(UserName,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag)
select @NewOwner,Parent,Catalog,Tag,Readed,Deleted,DA_Status,DA_UserName,DA_Date,DA_Content, RE_Title,RE_Time,RE_Finished,
Remark,Handled,NeedFollow,FollowFrom, FollowLimitDate,Followed,HandledFlag from IO_UserMail where UserName = @OldOwner

[mybelta2]

学习