日期:2014-05-17  浏览次数:20376 次

游标问题,为什么会取不到数据呢?
SQL code

declare ee scroll cursor
for
select company,team,client from t group by company,team,client
open ee
declare @rows int,@rand int 
set @rows=(select count(1) from (select company,team,client from t group by company,team,client) b)
declare @company nvarchar(30),@team nvarchar(30),@client nvarchar(30)
while(@@Fetch_Status=0)
begin
select @rand=cast( floor(rand()*100) as int)
while(@rand>0)
begin
Fetch next from ee into @company,@team,@client
insert ss (company,team,client) values(@company,@team,@client)
select @rand=@rand-1
end
end
close ee
deallocate ee

select * from ss



其中语句"select company,team,client from t group by company,team,client
"能查出279条记录,第一次运行的时候能取到数据,但之后每次提取的时候,ss都是没有数据的,我刚刚学游标,不懂这是什么原因,请各位大哥赐教。

------解决方案--------------------
SQL code

-->try
declare ee scroll cursor
for
select company,team,client from t group by company,team,client
open ee
declare @rows int,@rand int 
select @rows=count(1) from t group by company,team,client
declare @company nvarchar(30),@team nvarchar(30),@client nvarchar(30)
Fetch from ee into @company,@team,@client
while(@@Fetch_Status=0)
begin
    select @rand=cast( floor(rand()*100) as int)
    while(@rand>0)
    begin
        Fetch next from ee into @company,@team,@client
        insert ss (company,team,client) values(@company,@team,@client)
        select @rand=@rand-1
    end
end
close ee
deallocate ee

select * from ss

------解决方案--------------------
你监控一下这个值,会不会是第二次就为0了?select @rand=@rand-1
------解决方案--------------------
没对游标操作 怎么上来就 @@Fetch_Status
------解决方案--------------------
一楼 正解
------解决方案--------------------


在while前面在一行

Fetch next from ee into @company,@team,@client

while(@@Fetch_Status=0)