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