处理oracle查询数据,一向处于履行状况ITeye - 牛牛娱乐

处理oracle查询数据,一向处于履行状况ITeye

2019年03月24日15时43分37秒 | 作者: 星泽 | 标签: 一向,存储,进程 | 浏览: 2088

履行存储进程一向处于运转状况,而且内存一向占用很大,强制中止后检查数据,要做的操作又现已完结,请问可能是什么原因呢?(MYSQL 5.1)rn以下是存储进程:rnCREATE PROCEDURE `heating`.`shareCalculateFloorTwo`(in floHid int,in currDate DateTime)rnBEGINrn declare betenminDate DateTime;rn declare agoTemp int;rn declare randTemp int;rn declare devid int;rn declare proid int;rn declare proArea float;rn declare proDegree float;rn declare proFlowvalue float;rn declare heDevid int;rn declare tenminTotalHeat double;rn declare totalArea float;rn declare totalDegree int;rn declare totalFlowvalue float;rn declare singleAreaHeat double;rn declare singleDegreeHeat double;rn declare singleFlowHeat double;rn declare tenminSingleHeat double;rn declare proTotalUnitHeat double;rn declare betenminTotalHeat double;rn declare currTotalHeat double;rn declare curProperty cursor for select pr.propertyid,pr.area,de.heatDevid,de.deviceid from property prrn inner join hierarchy hi on (hi.hierarchyid = pr.hierarchyid and hi.parentid=floHid)rn inner join device de on (pr.hierarchyid=de.hierarchyid and de.status 2 and pr.status 2);rn declare CONTINUE HANDLER FOR SQLSTATE 02000 SET floHid = 0,devid = 0; rn set betenminDate = DATE_SUB(currDate,INTERVAL 10 MINUTE);rnrn open curProperty;rn fetch curProperty into proid,proArea,heDevid,devid;rn while (proid 0) dorn if ((select count(TempAcquisitionId) from arctempacquisition where AcquisitionDateTime=currDate and deviceid=devid)=0) thenrn insert into arctempacquisition(TempAcquisitionId,Degree,AcquisitionDateTime,DeviceId,FlowValue,RunMode)rn value(1000000000,round(24 + (RAND() * 3),1),currDate,devid,0.5,default);rn end if;rnrn set betenminTotalHeat = (select HeatingValue from heatmeterinfo where GatherTime=betenminDate and deviceid=devid);rn set currTotalHeat = (select HeatingValue from heatmeterinfo where GatherTime=currDate and deviceid=devid);rn if(currTotalHeat=0) thenrn set tenminSingleHeat = 0;rn else rn if(betenminTotalHeat=0) thenrn set betenminTotalHeat = (select heatingvalue from heatmeterinfo where deviceid=devid and gathertime currDate and heatingvalue 0 order by gathertime desc limit 1);rn end if;rn rn set tenminTotalHeat = (currTotalHeat-betenminTotalHeat);rn select sum(pr.area),sum(te.degree),sum(te.flowvalue) into totalArea,totalDegree,totalFlowvalue from property prrn inner join device de on (pr.hierarchyid=de.hierarchyid and de.status 2 and pr.status 2)rn inner join arctempacquisition te on (te.deviceid = de.deviceid) where de.heatDevid=heDevid and te.AcquisitionDateTime=currDate;rn select te.degree,te.flowvalue into proDegree,proFlowvalue from arctempacquisition te rn inner join device de on (de.deviceid = te.deviceid) where de.deviceid=devid and AcquisitionDateTime=currDate;rn set singleAreaHeat = (proArea/totalArea)*(tenminTotalHeat*0.5);rn set singleDegreeHeat = (proDegree/totalDegree)*(tenminTotalHeat*0.3);rn set singleFlowHeat = (proFlowvalue/totalFlowvalue)*(tenminTotalHeat*0.2);rn set tenminSingleHeat = singleAreaHeat + singleDegreeHeat + singleFlowHeat;rn end if;rnrn if((select count(id) from unitheat where propertyid=proid and unittime=betenminDate) = 0) thenrn set proTotalUnitHeat=0;rn elsern set proTotalUnitHeat = (select ifnull(totalunitheat,0) from unitheat where propertyid=proid and unittime=betenminDate);rn end if;rn replace into unitheat(UnitHeat,UnitTime,PropertyId,TotalUnitHeat) value(ifnull(tenminSingleHeat,0),currDate,proid,(ifnull(tenminSingleHeat,0)+proTotalUnitHeat));rn end while;rn close curProperty;rnEND

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章