sybase(ase)存储过程，请问为什么它只执行一个cursor？见代码:
sybase，请问为什么它只执行一个cursor？在第二个游标处，只会打印一条记录，正常的应该是遍历所有的记录的。见代码：
create proc test(@sTime datetime,@eTime datetime)
as
begin
declare
@areaID varchar(50),
@dnsAnalyseDelay numeric(10,2),
@dbConnTimeLen numeric(10,2),
@product numeric(10,2),
@kpi1 numeric(10,2),
@kpi2 numeric(10,2),
@webDownRate numeric(10,2),
@kpi3 numeric(10,2),
@kqi3 numeric(10,2),
@areaIDAttach varchar(40),
@attachSucRate numeric(10,2),
@dbTimeLen numeric(10,2)
select @kpi1=0
--解析tableA
declare cur_tableA
cursor for
select
vcAreaID areaID,
round(avg(dbDnsDelay)*1.00,2) dnsAnalyseDelay,
round(avg(dbTimeLen)*1.00,2) dbConnTimeLen,
round(avg(ftWebDownRate)*1.00,2) webDownRate
from
tableA
where 1=1
and dtEndTime>=@sTime
and dtEndTime<@eTime
and intSystemID=1
group by vcAreaID
open cur_tableA
while(@@sqlstatus=0)
begin
fetch cur_tableA into @areaID,@dnsAnalyseDelay,@dbConnTimeLen,@webDownRate
select @product = @dnsAnalyseDelay*@dbConnTimeLen
print "dnsAnalyseDelay= %1!dbConnTimeLen=%2! product= %3! areaID= %4! webDownRate=%5!",@dnsAnalyseDelay,@dbConnTimeLen,@product,@areaID,@webDownRate
--分类统计,取到kpi2
if @product>=3000 select @kpi2 = 0
else if 10<@product and @product<3000 select @kpi2 = round((3000-@product)*1.00/(3000-10)*100,2)
else if @product<=10 select @kpi2=100
print "kpi2= %1!",@kpi2
--分类统计,取到kpi3
if @webDownRate>=2000 select @kpi3 = 100
else if 128<@webDownRate and @webDownRate<3000 select @kpi3 = round((2000-@webDownRate)*1.00/(2000-128)*100,2)
else if @webDownRate<=128 select @kpi3=0
print "kpi3= %1!",@kpi3
--分类统计,取到kqi3
select @kqi3 = round(0.2*@kpi1+0.5*@kpi2+0.3*@kpi3,2)
print "kqi3= %1!",@kqi3
end
close cur_tableA
DEALLOCATE CURSOR cur_tableA
--解析tableB这里不会执行，或者只会打印一条记录，正常的应该是遍历所有的记录的。
declare cur_tableB
cursor for
select
vcAreaID areaIDAttach,
round(sum(intSuc)*1.00/count(1),4) attachSucRate,
round(avg(dbTimeLen)*1.00,2) dbTimeLen
from tableB
where 1=1
and dtEndTime>=@sTime
and dtEndTime<@eTime
and intSystemID=3
group by vcAreaID
open cur_tableB
while(@@sqlstatus=0)
begin
fetch cur_tableB into @areaIDAttach,@attachSucRate,@dbTimeLen
print "areaIDAttach=%1!,attachSucRate=%2!,dbTimeLen=%3!",@areaIDAttach,@attachSucRate,@dbTimeLen
end
close cur_tableB
end
你游标的使用方法有问题
end前面再加fetch一次才可以，你不加当然就只循环一次就停了。{:1_1:}
页:
[1]