如何让存储过程报错后继续执行?
本白 2018-03-23 09:40:51 代码如下:
create or replace procedure PROC_L_SXBZXR1
is
v_sql Varchar2(20000) Default '';
v_table varchar2(200) Default '';
begin
for rs in
(
select upper(a.table_en_name) table_en_name
from DEP_MATTERS_LIST@XYQH a
where id in (select DEP_MATTERS_LIST_ID from DEP_MATTERS_LIST_COLUMN@xyqh
where upper(en_name) = upper('xy20214001') and del_flag=0)
and is_build=1 and del_flag=0 and SYS_CREDIT_CORPUS_CODE='L' and state=1
)loop
begin
v_table :=rs.table_en_name;
v_sql :='MERGE INTO t_l_c060102 t1 USING (
select
id,
xy10101001,
xy10101002,
xy10101003,
xy10101004,
xy10101005,
xy10101006,
xy20214001,
xy20214003,
xy20214004,
xy20214005,
xy20214006,
xy20214007,
xy20214008,
xy20214009,
xy20214010,
xy20214011,
xy20214012,
xy20214013,
xy20214014,
xy10101007,
xy10101008,
xy10101009,
shield_sate,
innerid,
sub_type,
clean_date,
is_mate,
dep_id,
m_010101,
user_id,
modify_date,
create_date,
city_code,
region_code,
day_id,
than_date,
is_clean,
is_peat,
province_code,
publish_state,
publish_valid_date,
rule_id,
shield_date,
shield_user_id,
publish_date,
publish_user_id
from '||rs.table_en_name||'@XYQH) t2
ON ( t1.id=t2.id)
WHEN MATCHED THEN
UPDATE SET
t1.xy10101001 = t2.xy10101001 ,
t1.xy10101002 = t2.xy10101002 ,
t1.xy10101003 = t2.xy10101003 ,
t1.xy10101004 = t2.xy10101004 ,
t1.xy10101005 = t2.xy10101005 ,
t1.xy10101006 = t2.xy10101006 ,
t1.rule_id = t2.rule_id ,
t1.innerid = t2.innerid ,
t1.is_mate = t2.is_mate ,
t1.m_010101 = t2.m_010101 ,
t1.modify_date = t2.modify_date ,
t1.create_date = t2.create_date ,
t1.region_code = t2.region_code ,
t1.day_id = t2.day_id ,
t1.than_date = t2.than_date ,
t1.is_peat = t2.is_peat ,
t1.province_code = t2.province_code ,
t1.city_code = t2.city_code ,
t1.bzxr = t2.xy10101001 ,
t1.dmlx = 0 ,
t1.dm = t2.xy10101002 ,
t1.fddbrxm = t2.xy10101007 ,
t1.fddbrzj = t2.xy10101009 ,
t1.ah = t2.xy20214001 ,
t1.zxfy = t2.xy20214003 ,
t1.zxyj = t2.xy20214006 ,
t1.lasj = t2.xy20214012 ,
t1.zczxdw = t2.xy20214007 ,
t1.sxflwsqddyw = t2.xy20214008 ,
t1.bzxrlxqk = t2.xy20214009 ,
t1.sxbzxrxwjtqx = t2.xy20214010
WHEN NOT MATCHED THEN
insert (
t1.id,
t1.xy10101001,
t1.xy10101002,
t1.xy10101003,
t1.xy10101004,
t1.xy10101005,
t1.xy10101006,
t1.rule_id,
t1.innerid,
t1.is_mate,
t1.m_010101,
t1.modify_date,
t1.create_date,
t1.region_code,
t1.day_id,
t1.than_date,
t1.is_peat,
t1.province_code,
t1.city_code,
t1.bzxr,
t1.dmlx,
t1.dm,
t1.fddbrxm,
t1.fddbrzj,
t1.ah,
t1.zxfy,
t1.zxyj,
t1.lasj,
t1.zczxdw,
t1.sxflwsqddyw,
t1.bzxrlxqk,
t1.sxbzxrxwjtqx
)
values
(
t2.id ,
t2.xy10101001 ,
t2.xy10101002 ,
t2.xy10101003 ,
t2.xy10101004 ,
t2.xy10101005 ,
t2.xy10101006 ,
t2.rule_id ,
t2.innerid ,
t2.is_mate ,
t2.m_010101 ,
t2.modify_date ,
t2.create_date ,
t2.region_code ,
t2.day_id ,
t2.than_date ,
t2.is_peat ,
t2.province_code ,
t2.city_code ,
t2.xy10101001 ,
0 ,
t2.xy10101002 ,
t2.xy10101007 ,
t2.xy10101009 ,
t2.xy20214001 ,
t2.xy20214003 ,
t2.xy20214006 ,
t2.xy20214012 ,
t2.xy20214007 ,
t2.xy20214008 ,
t2.xy20214009 ,
t2.xy20214010
)';
Execute Immediate v_sql ;
commit;
exception
when others then null;
end;
end loop;
end;
我写这段代码的本意是,希望存储过程在报错的情况下,继续执行,但是在报错后,它就直接跳出了,我不知道问题出在哪,报错的信息是‘xy10101009’这个字段无法识别,我查过了,是我要导入的其中一个表里没有这个字段的原因。请教下各位,如何让它在报错后,继续执行呢?