SQLSERVER 游标问题

发布网友发布时间：2022-04-23 04:50

共3个回答

懂视网时间：2022-05-01 03:19

插入客户信息 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

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

sqlserver游标使用误区

标签：

热心网友时间：2022-05-01 00:27

你的用的是什么数据库？？？
第4行，定义游标的完全不通呀？
定义游标时不能用斌值 select @sql='if
select 1 from 表名
语法：print varchar 。不要使用"",应该使用''。

例如：(环境 sql server 2008)
declare @what varchar(80);
set @what='大屏显示系统';
declare @sql varchar(8000);
declare tablecursor cursor local
for
select o.name +'.'+ c.name from sys.sysobjects o inner join sys.syscolumns c on o.id=c.id
where o.name like @what;
open tablecursor;
fetch next from tablecursor into @sql;
while(@@FETCH_STATUS=0)

begin
print '所在的表以及字段:'+ @sql
fetch next from tablecursor into @sql;
end
close tablecursor;
deallocate tablecursor;

注：一般不使用游标，因为游标逐行进行数据操作。一般用在update几百万几千万行的时候，bulk处理.可以删一些提交一些，回滚少。日志文件写入快，避免数据缓冲
打字不易，如满意，望采纳。追问我搞定了，谢谢。
我把
declare csor...这一整句
全方都放进了@SQL，然后exec(@SQL)，就可以了。

热心网友时间：2022-05-01 01:45

可以采用临时表解决：

declare @SQL nvarchar(max)
--检测名为#temp的临时表是否存在，如存在则删除
if exists(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp') and type='U')
begin
    drop table #temp
    print 'Dropped'
end
--执行动态SQL语句：创建临时表#temp
set @SQL = N'select [UserId] into #temp from ' + @Tables + ' where [Num_' + @Num + '] != 0'
exec sp_executesql @SQL
--对#temp创建游标
declare cur cursor for select * from #temp
open cur
……
close cur
deallocate cur

全部频道

SQLSERVER 游标问题