create procedure Calcu_BanTotal(in starttime varchar(15),in endtime varchar(15),in totaltype varchar(15)) begin declare stopFlag1 int default 0; declare stopFlag2 int default 0; declare v_tagname varchar(40); declare D_tagname varchar(40); declare D_unit varchar(20); declare D_value float; declare D_valtmp float; declare D_conf int; declare v_Fetch cursor for select tagname from TagList; declare continue handler for not found set stopFlag1=1; open v_Fetch; repeat fetch v_Fetch into v_tagname; if stopFlag1 = 0 then begin declare D_Fetch cursor for Select tagname,units,value,conf FROM IP_PHD_RAW WHERE TAGNAME =v_tagname and timestamp>=starttime and timestamp<=endtime order by tagname,timestamp; declare continue handler for not found set stopFlag2=1; open D_Fetch; repeat fetch D_Fetch into D_tagname,D_units,D_value,D_conf; if stopFlag2 = 0 then begin if D_conf= 100 then 计算累计值D_valtmp
end if end end if until stopFlag2 = 1 end repeat; insert into ribao(ddate,tagname,totaltype) values(adddate(current_date,interval -1 day),D_tagname,D_valtmp); update ribao set device=(select device from TagList where tagname= D_tagname) where ddate= adddate(current_date,interval -1 day) and tagname= D_tagname; update ribao set unit=(select unit from TagList where tagname= D_tagname) where ddate= adddate(current_date,interval -1 day) and tagname= D_tagname; close D_Fetch; end; end if; until stopFlag1 = 1 end repeat; close v_Fetch; end 我自己研究着写了,但计算累计值那块不会写,不知道该咋写,实现function total的功能,比对D_fetch的第一条、和最后一条记录value的大小,若value(last)> value(first)则D_valtmp= value(last)- value(first);若value(last)< value(first)则从后遍查D_fetch与value(first)比对,找到value(x)> value(first),则D_valtmp= value(x)- value(first)+ value(last)。这块不会写了,请高人一起研究下该咋写代码实现这功能!谢谢
begin
declare stopFlag1 int default 0;
declare stopFlag2 int default 0;
declare v_tagname varchar(40);
declare D_tagname varchar(40);
declare D_unit varchar(20);
declare D_value float;
declare D_valtmp float;
declare D_conf int;
declare v_Fetch cursor for
select tagname from TagList;
declare continue handler for not found set stopFlag1=1;
open v_Fetch;
repeat
fetch v_Fetch into v_tagname;
if stopFlag1 = 0 then
begin
declare D_Fetch cursor for
Select tagname,units,value,conf
FROM IP_PHD_RAW WHERE TAGNAME =v_tagname
and timestamp>=starttime and timestamp<=endtime
order by tagname,timestamp;
declare continue handler for not found set stopFlag2=1;
open D_Fetch;
repeat
fetch D_Fetch into D_tagname,D_units,D_value,D_conf;
if stopFlag2 = 0 then
begin
if D_conf= 100 then
计算累计值D_valtmp
end if
end
end if
until stopFlag2 = 1 end repeat;
insert into ribao(ddate,tagname,totaltype)
values(adddate(current_date,interval -1 day),D_tagname,D_valtmp);
update ribao set device=(select device from TagList where tagname= D_tagname)
where ddate= adddate(current_date,interval -1 day) and tagname= D_tagname;
update ribao set unit=(select unit from TagList where tagname= D_tagname)
where ddate= adddate(current_date,interval -1 day) and tagname= D_tagname;
close D_Fetch;
end;
end if;
until stopFlag1 = 1 end repeat;
close v_Fetch;
end
我自己研究着写了,但计算累计值那块不会写,不知道该咋写,实现function total的功能,比对D_fetch的第一条、和最后一条记录value的大小,若value(last)> value(first)则D_valtmp= value(last)- value(first);若value(last)< value(first)则从后遍查D_fetch与value(first)比对,找到value(x)> value(first),则D_valtmp= value(x)- value(first)+ value(last)。这块不会写了,请高人一起研究下该咋写代码实现这功能!谢谢