//这个存储过程用来从两张表中向一张表中倒数据，存储过程
编译通过，但是在执行到*****号那一行报错，不知道为什么
调试时发现如下错误
错误号01403：未找到数据
06512：
CREATE OR REPLACE PROCEDURE EDMS.sp_fgbchange is
v_metid number(11);
v_midmettime varchar2(19);
v_instcode varchar2(12);
v_yhh varchar2(8);
v_cbkh varchar2(3);
v_mettime date;
v_mettime1 date;
v_mettime2 date;
v_lasttime date;
v_meanstype number(1);
cursor code_cur is select instcode,yhh,cbkh from id_change;
--要插入接口表中的值
v_zds number(10,2);
v_pddsnumber(10,2);
v_fddsnumber(10,2);
v_gddsnumber(10,2);
v_wgbds number(10,2);
v_xlbds number(10,2);
v_fzdsnumber(10,2);
v_fpdds number(10,2);
v_ffdds number(10,2);
v_fgdds number(10,2);
v_fwgbds number(10,2);
begin
--循环处理每一个测点
open code_cur;
fetch code_cur into v_instcode,v_yhh,v_cbkh;
while code_cur%found
loop
--根据instcode找出metid
select metid,meastype into v_metid,v_meanstype from ainstfact where instcode=v_instcode;
v_lasttime := to_date('1900','yyyy');--初始化测量时间
loop
--从cmetvalue2和cmetvalue3取出比当前时间大的最小时间记录(此测点）
select nvl(min(mettime),to_date('1900','yyyy')) into v_mettime1 from cmetvalue2 where metid=v_metid and mettime>v_lasttime;
select nvl(min(mettime),to_date('1900','yyyy')) into v_mettime2 from cmetvalue3 where metid=v_metid and mettime>v_lasttime;
if v_mettime1> v_mettime2 then
v_mettime :=v_mettime2;
else
v_mettime :=v_mettime1;
end if;
if to_char(v_mettime,'yyyy')='1900' then
--没有找到这个测量时间，,即此测点无记录,或者查找完毕
exit;
else--有记录
--根据这个最小时间和测点查找这个中间表是否有记录*****
select nvl(cbrq,'1900') into v_midmettime from jk_fk_ydmis where yhh=v_yhh and cbkh=v_cbkh and cbrq=to_char(v_mettime,'yyyy/mm/dd hh24:mi:ss');
if v_midmettime='1900' then --中间表无记录,插入记录
--插入记录
--正向总读数
select nvl(rawvalue,0) into v_zds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=9 and valuetype=1 and mettime=v_mettime;
--正向平段
selectnvl(rawvalue,0) into v_pdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=2 and valuetype=1 and mettime=v_mettime;
--正向峰段
selectnvl(rawvalue,0) into v_fdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=1 and valuetype=1 and mettime=v_mettime;
--正向谷段
selectnvl(rawvalue,0) into v_gdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=3 and valuetype=1 and mettime=v_mettime;
--正向无功
selectnvl(rawvalue,0) into v_wgbds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=9 and valuetype=3 and mettime=v_mettime;
--反向总读数
selectnvl(rawvalue,0) into v_fzds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=9 and valuetype=2 and mettime=v_mettime;
--反向平段读数
selectnvl(rawvalue,0) into v_fpdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=2 and valuetype=2 and mettime=v_mettime;
--反向峰段读数
selectnvl(rawvalue,0) into v_ffdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=1 and valuetype=2 and mettime=v_mettime;
--反向谷段读数
selectnvl(rawvalue,0) into v_fgdds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=3 and valuetype=2 and mettime=v_mettime;
--反向无功表读数
select rawvalue into v_fwgbds from cmetvalue2 where metid=v_metid and meanstype=v_meanstype and toutype=9 and valuetype=4 and mettime=v_mettime;
--需量表读数
selectnvl(rawvalue,0) into v_xlbds from cmetvalue3 where metid=v_metid and meastype=v_meanstype and valuetype=11 and mettime=to_char(v_mettime,'yyyy/mm/dd hh24:mi:ss');
insert into jk_fk_ydmis values(v_yhh,v_cbkh,v_mettime,v_zds,v_pdds,v_fdds,v_gdds,v_wgbds,v_xlbds,v_fzds,v_fpdds,v_ffdds,v_gdds,v_fwgbds);
end if;
end if;
v_lasttime :=v_mettime;
end loop;
fetch code_cur into v_instcode,v_yhh,v_cbkh;
end loop;
close code_cur;
end;
/ |
