declare
v_region_code varchar2(6);
v_ps_net_code varchar2(10);
v_imsi varchar2(20);
v_sql varchar2(4000);
begin
for cur in (select * from cmmm_nokia_yuyl)
loop
select nvl(sum(imsi),0) into v_imsi from res_phone_number@BOSS30FLCP where res_id = cur.res_id;
--可能有NO_DATA_FOUND的异常 select nvl(sum(region_code),0),nvl(sum(ps_net_code),0)
into v_region_code,v_ps_net_code
from ps_net_number
where number_segment = substr(cur.res_id,1,7);
--可能有NO_DATA_FOUND的异常 v_sql := 'update cmmm_nokia_yuyl set IMSI = :imsi,REGION_CODE = :region_code,ps_net_code = :ps_net_code,STATUS = 0
WHERE RES_ID = :res_id';
execute immediate v_sql using v_imsi,v_region_code,v_ps_net_code,cur.res_id;
commit;
end loop;
end;
如果第一个异常出现后,要继续执行后面的过程。如果第二个异常出现后,也要继续执行后面的过程应该怎么办,请高手指点。最好有源码
v_region_code varchar2(6);
v_ps_net_code varchar2(10);
v_imsi varchar2(20);
v_sql varchar2(4000);
begin
for cur in (select * from cmmm_nokia_yuyl)
loop
select nvl(sum(imsi),0) into v_imsi from res_phone_number@BOSS30FLCP where res_id = cur.res_id;
--可能有NO_DATA_FOUND的异常 select nvl(sum(region_code),0),nvl(sum(ps_net_code),0)
into v_region_code,v_ps_net_code
from ps_net_number
where number_segment = substr(cur.res_id,1,7);
--可能有NO_DATA_FOUND的异常 v_sql := 'update cmmm_nokia_yuyl set IMSI = :imsi,REGION_CODE = :region_code,ps_net_code = :ps_net_code,STATUS = 0
WHERE RES_ID = :res_id';
execute immediate v_sql using v_imsi,v_region_code,v_ps_net_code,cur.res_id;
commit;
end loop;
end;
如果第一个异常出现后,要继续执行后面的过程。如果第二个异常出现后,也要继续执行后面的过程应该怎么办,请高手指点。最好有源码
v_region_code varchar2(6);
v_ps_net_code varchar2(10);
v_imsi varchar2(20);
v_sql varchar2(4000);
begin
for cur in (select * from cmmm_nokia_yuyl)
loop
begin
select nvl(sum(imsi),0) into v_imsi from res_phone_number@BOSS30FLCP
where res_id = cur.res_id;
exception when others then null; -- 有错误,我也继续
end;
--可能有NO_DATA_FOUND的异常
begin
select nvl(sum(region_code),0),nvl(sum(ps_net_code),0)
into v_region_code,v_ps_net_code
from ps_net_number
where number_segment = substr(cur.res_id,1,7);
--可能有NO_DATA_FOUND的异常
when others then null;
end; v_sql := 'update cmmm_nokia_yuyl set IMSI = :imsi,REGION_CODE = :region_code,ps_net_code = :ps_net_code,STATUS = 0
WHERE RES_ID = :res_id';
execute immediate v_sql using v_imsi,v_region_code,v_ps_net_code,cur.res_id;
commit;
end loop;
end;
v_region_code varchar2(6);
v_ps_net_code varchar2(10);
v_imsi varchar2(20);
v_sql varchar2(4000);
begin
for cur in (select * from cmmm_nokia_yuyl)
loop
begin
select nvl(sum(imsi),0) into v_imsi from res_phone_number@BOSS30FLCP
where res_id = cur.res_id;
exception when others then null; -- 有错误,我也继续
end;
--可能有NO_DATA_FOUND的异常
begin
select nvl(sum(region_code),0),nvl(sum(ps_net_code),0)
into v_region_code,v_ps_net_code
from ps_net_number
where number_segment = substr(cur.res_id,1,7);
--可能有NO_DATA_FOUND的异常
exception when others then null;
end; v_sql := 'update cmmm_nokia_yuyl set IMSI = :imsi,REGION_CODE = :region_code,ps_net_code = :ps_net_code,STATUS = 0
WHERE RES_ID = :res_id';
execute immediate v_sql using v_imsi,v_region_code,v_ps_net_code,cur.res_id;
commit;
end loop;
end;
谢谢啊,
可是为什么是when others then 而不是when no_data_found then呢
when others then 的意思是说当一出现异常则进行then的处理,不需要指定具体是什么异常,范围比no_data_found 要广。
no_data_found相当于是 others 的子集。
与no_data_found异常平级的还有,too_many_rows
value_error个人是这样理解的供参考。