请问:在执行以下语句时报错。
declare
v_spec varchar2(255);
begin
for r in (select subne.id, subne.subneassetid from subne where subne.subneassetid >0)
loop
select subnetype.subnemodel into v_spec from subnetype , subne
wheresubne.id = r.id and subne.subnetypeid = subnetype.id;
update asset set specformula = v_spec where id = r.subneassetid and specformula is null;
end loop;
end;
ORA-01403: 未找到数据
ORA-06512: 在line 6
我在网上查有人提议添加异常处理,可是那样loop里其它就不能被执行到了呀,请问各位高手该怎么解决呢??thx
加个独立的块,
begin
select subnetype.subnemodel into v_spec from subnetype , subne
where subne.id = r.id and subne.subnetypeid = subnetype.id;
update asset set specformula = v_spec where id = r.subneassetid and specformula is null;
exception
when other then
null;
end;
这样在no_data_found时,就不会跳出了!
declare
v_spec varchar2(255);
begin
for r in (select subne.id, subne.subneassetid from subne where subne.subneassetid >0)
loop
begin
select subnetype.subnemodel into v_spec from subnetype , subne
where subne.id = r.id and subne.subnetypeid = subnetype.id;
execpiton when others then
dbms_output.put_line('无数据');
end;
update asset set specformula = v_spec where id = r.subneassetid and specformula is null;
end loop;
end;
SELECT ........INTO .............语句和游标性质是一样的.而游标如果没有结果是不会报错的.
declare
v_spec varchar2(255);
cursor ss is
select subnetype.subnemodelfrom subnetype , subne
where subne.id = r.id and subne.subnetypeid = subnetype.id;
begin
for r in (select subne.id, subne.subneassetid from subne where subne.subneassetid >0)
loop
for i inss loop
if ss%found and ss%rowcount = 1 then
v_spec:= i.subnemodel;
else
v_spec := null;
end if;
end loop;
if v_spec is not null then
update asset set specformula = v_spec where id = r.subneassetid and specformula is null;
end if;
end loop;
end;