在使用游标出现逻辑错误时，查阅资料，只有改掉while中的游标取值在while循环最后，没有说明while以前会出现的错误，而且没有具体说明原因，今天在工作中解决了这个问题，写了这个博客，希望对使用游标少的园友有帮助，也给自己提个醒。
按照游标的规则使用时，在使用游标取到数据时后进行操作在逻辑上正确，但是会造成数据异常，在正常流程下逻辑正确，所以很难发现其中的逻辑错误，下面是工作时的例子，以及解决的具体流程。
好久没用游标，今天查了下资料开始使用，但是出现问题：
--插入客户信息 create synonym synonym_project for [192.168.159.134].[VisualNet].[dbo].project declare @prjkey varchar(50) declare @customername varchar(50) declare @khbh char(4) declare cursor_project cursor for select prjname,prjkey from synonym_project open cursor_project fetch next from cursor_project into @customername,@prjkey if not exists(select 1 from Customers where gsmc = @customername) select @khbh = MAX(khbh)+1 from Customers insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@customername,‘‘,‘‘) while @@FETCH_STATUS=0 begin fetch next from cursor_project into @customername,@prjkey if not exists(select 1 from Customers where gsmc = @customername) select @khbh = MAX(khbh)+1 from Customers insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@customername,‘‘,‘‘) end close cursor_project deallocate cursor_project drop synonym synonym_project
这里使用同义词和游标远程查询数据。
因为：
open cursor_project fetch next from cursor_project into @customername,@prjkey
打开游标后需要先取一条数据才能进行while循环读取，所以我在取到数据后进行插入操作
然后while循环中也会再进行数据读取，在读取后再进行插入操作。
但是执行后发生错误：违反唯一约束
我查看了远程表，所有的数据都完整的插入到表中，并没有遗失的数据，不知道从哪里多了一条数据，所以讲数据处理换为打印查看数据：
--插入客户信息 create synonym synonym_project for [192.168.159.134].[VisualNet].[dbo].project declare @prjkey varchar(50) declare @customername varchar(50) declare @khbh char(4) declare cursor_project cursor for select prjname,prjkey from synonym_project open cursor_project fetch next from cursor_project into @customername,@prjkey print @customername while @@FETCH_STATUS=0 begin fetch next from cursor_project into @customername,@prjkey print @customername end close cursor_project deallocate cursor_project drop synonym synonym_project
结果如下：
Test1
南通心智慧
港闸区
港闸区
但是在远程表中‘港闸区‘只有一个，所以是最后一条数据重复了，然后百度一下看看别人是不是出现过：http://zhidao.baidu.com/link?url=wNi65XcABENt3DV_VJJHILlHWTNYdsMYzX7b25RmGACPIwsLjAUkWVz0qIFzlfwC7fK5S5-71t5196I5wJ4gRa
回答是说取数据应该是在while后面，所以我把游标获取数据放在循环最后：
--插入客户信息 create synonym synonym_project for [192.168.159.134].[VisualNet].[dbo].project declare @prjkey varchar(50) declare @customername varchar(50) declare @khbh char(4) declare cursor_project cursor for select prjname,prjkey from synonym_project open cursor_project fetch next from cursor_project into @customername,@prjkey print @customername while @@FETCH_STATUS=0 begin print @customername fetch next from cursor_project into @customername,@prjkey end close cursor_project deallocate cursor_project drop synonym synonym_project
运行结果：
Test1
Test1
南通心智慧
港闸区
发现第一条数据重复，也就是说两次打印有一次是多余的，因为要遍历数据，所以循环里面的打印肯定是必须的，上面的打印可能是多余的，如果把上面的打印删除，游标取数据在打印之后，循环内部打印在游标取数据之前，就不会有上一条数据丢失：
--插入客户信息 create synonym synonym_project for [192.168.159.134].[VisualNet].[dbo].project declare @prjkey varchar(50) declare @customername varchar(50) declare @khbh char(4) declare cursor_project cursor for select prjname,prjkey from synonym_project open cursor_project fetch next from cursor_project into @customername,@prjkey while @@FETCH_STATUS=0 begin print @customername fetch next from cursor_project into @customername,@prjkey end close cursor_project deallocate cursor_project drop synonym synonym_project
结果如下：
Test1
南通心智慧
港闸区
这样就正确了，相同于打印， 对数据进行插入操作的逻辑也是这样，讲打印改成需要的数据操作：
--插入客户信息 create synonym synonym_project for [192.168.159.134].[VisualNet].[dbo].project declare @prjkey varchar(50) declare @customername varchar(50) declare @khbh char(4) declare cursor_project cursor for select prjname,prjkey from synonym_project open cursor_project fetch next from cursor_project into @customername,@prjkey while @@FETCH_STATUS=0 begin if not exists(select 1 from Customers where gsmc = @customername) select @khbh = MAX(khbh)+1 from Customers insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@customername,‘‘,‘‘) fetch next from cursor_project into @customername,@prjkey end close cursor_project deallocate cursor_project
这样就正确了，对游标使用很少经常会出现这个问题，因为普通的逻辑就会出现最后数据重复，必须使用游标的逻辑格式。
原文：http://www.cnblogs.com/xiaoguangit/p/5413808.html