执行存储过程一直处于运行状态,并且内存一直占用很大,强制停止后查看数据,要做的操作又已经完成,请问可能是什么原因呢?(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;
ENDmysql存储过程
以下是存储过程:
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;
ENDmysql存储过程
end while;没有循环下一条记录?
你的循环判断有问题
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
多谢了,粗心了
把“declare CONTINUE HANDLER FOR SQLSTATE '02000' SET floHid = 0,devid = 0;”
改成“declare CONTINUE HANDLER FOR SQLSTATE '02000' SET proid = 0;”
就好啦