Oracle存储过程执行后reject_tab未写入失败记录问题排查

问题根因 #

核心问题出在循环内的UPDATE语句WHERE条件错误，直接导致校验失败的行没有被正确标记状态，最终reject_tab无数据：

• 你在UPDATE时的判断条件仅基于循环变量c的属性，没有关联到staging_tab的实际行，如果你的实际代码写的是where e_id = c.e_id，那e_id为null时，Oracle中null = null的判断结果永远为假，不会匹配到任何行，校验失败的行的validation_status仍然为null
• 即使你按贴出的代码写的是where c.e_id is null，该条件等价于恒真，会把整个staging_tab所有行的状态都更新为失败，不符合预期
• 原有插入目标表的逻辑冗余，不需要重复查询staging表，直接用循环变量的字段值即可

修复后的存储过程代码 #

create or replace procedure sp_stage_target_rej(ov_err_msg OUT varchar2)
is
lv_count number(30);
lv_tot_rec number(30);
lv_succ_rec number(30);
lv_fail_rec number(30);
begin

lv_succ_rec := 0;
lv_fail_rec := 0;

-- 循环时带出ROWID用于唯一标识每行，避免匹配错误
for c in (select t.*, t.rowid from staging_tab t)loop

-- 校验e_id是否为空
if c.e_id is null then
    dbms_output.put_line('E ID is null '||c.e_id);
    -- 用ROWID精准更新当前行
    update staging_tab 
    set validation_status = 'Fail', validation_msg ='Id is not present' 
    where rowid = c.rowid;
    lv_fail_rec := lv_fail_rec + 1;
-- 校验e_id长度不超过4
elsif length(c.e_id) > 4 then
    update staging_tab 
    set validation_status = 'Fail', validation_msg ='Id length is more than expected' 
    where rowid = c.rowid;
    lv_fail_rec := lv_fail_rec + 1;
else
    -- 直接用循环变量插入，无需重复查表
    dbms_output.put_line('Inserting into Target table');
    insert into target_tab(e_id, e_name, e_loc ) 
    values(c.e_id, c.e_name, c.e_loc);
    lv_succ_rec := lv_succ_rec + 1;
end if;

end loop;

select count(1) into lv_count from staging_tab where validation_status = 'Fail';
dbms_output.put_line('Failed rows '||lv_count);

if lv_count >0 then
    dbms_output.put_line('Inserting into Reject table');
    insert into reject_tab
    select e_id,e_name,e_loc,validation_status,validation_msg from staging_tab where
    validation_status = 'Fail';
end if;

-- 统计总记录数
Select count(1) into lv_tot_rec from staging_tab;

-- 插入汇总表
dbms_output.put_line('Inserting into Summary table');
insert into summary_tab(tot_records, success_records, failed_records)
values(lv_tot_rec,lv_succ_rec,lv_fail_rec);

commit;
ov_err_msg := 'Procedure completed succesfully';

EXCEPTION
 When others then 
 ov_err_msg := 'Procedure end up with errors'|| sqlerrm;
 ROLLBACK;

end sp_stage_target_rej;

执行验证 #

修改后再次执行调用代码：

set serveroutput on;
declare
err_msg varchar2(4000);
begin
sp_stage_target_rej(err_msg);
dbms_output.put_line(err_msg);
end;
/

即可得到预期结果：

• target_tab 成功插入3条e_id非空的记录
• reject_tab 成功插入3条e_id为null的失败记录
• summary_tab 统计值为总记录数6、成功3、失败3

内容的提问来源于stack exchange，提问作者Vicky