sqlserver游标使用误区

在使用游标出现逻辑错误时，查阅资料，只有改掉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

　　这样就正确了，对游标使用很少经常会出现这个问题，因为普通的逻辑就会出现最后数据重复，必须使用游标的逻辑格式。