mysq存储过程一直处于执行状态

[chunyanclover]

执行存储过程一直处于运行状态，并且内存一直占用很大，强制停止后查看数据，要做的操作又已经完成，请问可能是什么原因呢？（MYSQL 5.1）
以下是存储过程：
CREATE PROCEDURE `heating`.`shareCalculateFloorTwo`(in floHid int,in currDate DateTime)
BEGIN
declare betenminDate DateTime;
declare agoTemp int;
declare randTemp int;
declare devid int;
declare proid int;
declare proArea float;
declare proDegree float;
declare proFlowvalue float;
declare heDevid int;
declare tenminTotalHeat double;
declare totalArea float;
declare totalDegree int;
declare totalFlowvalue float;
declare singleAreaHeat double;
declare singleDegreeHeat double;
declare singleFlowHeat double;
declare tenminSingleHeat double;
declare proTotalUnitHeat double;
declare betenminTotalHeat double;
declare currTotalHeat double;
declare curProperty cursor for select pr.propertyid,pr.area,de.heatDevid,de.deviceid from property pr
inner join hierarchy hi on (hi.hierarchyid = pr.hierarchyid and hi.parentid=floHid)
inner join device de on (pr.hierarchyid=de.hierarchyid and de.status<>2 and pr.status<>2);
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET floHid = 0,devid = 0;
set betenminDate = DATE_SUB(currDate,INTERVAL 10 MINUTE);

open curProperty;
fetch curProperty into proid,proArea,heDevid,devid;
while (proid <> 0) do
if ((select count(TempAcquisitionId) from arctempacquisition where AcquisitionDateTime=currDate and deviceid=devid)=0) then
insert into arctempacquisition(TempAcquisitionId,Degree,AcquisitionDateTime,DeviceId,FlowValue,RunMode)
value(1000000000,round(24 + (RAND() * 3),1),currDate,devid,0.5,default);
end if;

set betenminTotalHeat = (select HeatingValue from heatmeterinfo where GatherTime=betenminDate and deviceid=devid);
set currTotalHeat = (select HeatingValue from heatmeterinfo where GatherTime=currDate and deviceid=devid);
if(currTotalHeat=0) then
set tenminSingleHeat = 0;
else
if(betenminTotalHeat=0) then
set betenminTotalHeat = (select heatingvalue from heatmeterinfo where deviceid=devid and gathertime < currDate and heatingvalue<>0 order by gathertime desc limit 1);
end if;

set tenminTotalHeat = (currTotalHeat-betenminTotalHeat);
select sum(pr.area),sum(te.degree),sum(te.flowvalue) into totalArea,totalDegree,totalFlowvalue from property pr
inner join device de on (pr.hierarchyid=de.hierarchyid and de.status<>2 and pr.status<>2)
inner join arctempacquisition te on (te.deviceid = de.deviceid) where de.heatDevid=heDevid and te.AcquisitionDateTime=currDate;
select te.degree,te.flowvalue into proDegree,proFlowvalue from arctempacquisition te
inner join device de on (de.deviceid = te.deviceid) where de.deviceid=devid and AcquisitionDateTime=currDate;
set singleAreaHeat = (proArea/totalArea)*(tenminTotalHeat*0.5);
set singleDegreeHeat = (proDegree/totalDegree)*(tenminTotalHeat*0.3);
set singleFlowHeat = (proFlowvalue/totalFlowvalue)*(tenminTotalHeat*0.2);
set tenminSingleHeat = singleAreaHeat + singleDegreeHeat + singleFlowHeat;
end if;

if((select count(id) from unitheat where propertyid=proid and unittime=betenminDate) = 0) then
set proTotalUnitHeat=0;
else
set proTotalUnitHeat = (select ifnull(totalunitheat,0) from unitheat where propertyid=proid and unittime=betenminDate);
end if;
replace into unitheat(UnitHeat,UnitTime,PropertyId,TotalUnitHeat) value(ifnull(tenminSingleHeat,0),currDate,proid,(ifnull(tenminSingleHeat,0)+proTotalUnitHeat));
end while;
close curProperty;
END

[WWWWA]

fetch curProperty into proid,proArea,heDevid,devid; end while; 没有循环下一条记录？

[chunyanclover]

引用 1 楼 WWWWA 的回复:

fetch curProperty into proid,proArea,heDevid,devid; end while; 没有循环下一条记录？

有的，是我刚刚去掉了，想看看是不是这的问题。

[WWWWA]

你的循环判断有问题 declare CONTINUE HANDLER FOR NOT FOUND SET done=1; ... fetch curProperty into proid,proArea,heDevid,devid; while done=0 do .... fetch curProperty into proid,proArea,heDevid,devid; end while; 在每1个有WHERE的语句后加上 SET done=0

[chunyanclover]

引用 3 楼 WWWWA 的回复:

你的循环判断有问题 declare CONTINUE HANDLER FOR NOT FOUND SET done=1; ... fetch curProperty into proid,proArea,heDevid,devid; while done=0 do .... fetch curProperty into proid,proArea,heDevid,devid; en……

多谢了，粗心了 把“declare CONTINUE HANDLER FOR SQLSTATE '02000' SET floHid = 0,devid = 0;” 改成“declare CONTINUE HANDLER FOR SQLSTATE '02000' SET proid = 0;” 就好啦

[chunyanclover]

只是过程还有待优化呀。。太慢了。。一次要两三秒钟

[rucypli]

在循环里面select打印东西出来 看看是不是预期