Oracle存储过程接收多请求ID参数编译错误与实现求助

原代码的问题包含以下几点：

• 调用代码语法错误：变量err_msg未指定数据类型，需声明为varchar2类型
• 存储过程名称不匹配：定义的存储过程名为sp_stage_target，调用时使用的sp_main_target不存在
• 依赖表缺失：代码中写入数据的reject_tab未提前创建，执行时会触发对象不存在错误
• 逻辑不符合需求：原代码对所有传入的req_id统一统计拒绝记录数，没有按要求对每个req_id单独处理、单独走阈值判断
• 逻辑漏洞：当参考表tab_ref或tab_ref_2无数据时，直接给输出参数赋值后没有终止流程，后续仍会执行统计、插入汇总表的逻辑，会触发未初始化变量错误

前置依赖表创建 #

首先创建缺失的拒绝表：

CREATE TABLE REJECT_TAB
(
    E_ID NUMBER(10),
    E_NAME VARCHAR2(30),
    E_LOC VARCHAR2(30),
    VALIDATION_STATUS varchar2(30),
    VALIDATION_MSG varchar2(30)
);

存储过程代码 #

create or replace procedure sp_stage_target(iv_req_id IN sys.OdciNumberList,ov_err_msg OUT varchar2) is
  lv_succ_rec number(30) := 0;
  lv_fail_rec number(30) := 0;
  lv_count_ref number(10);
  lv_count_ref2 number(10);
  lv_threshold_cnt number(10) := 5;
  lv_RejectedCount number(10);
  lv_status varchar2(30);
  lv_current_req_id number(10);
begin
  -- 校验参考表是否有数据
  select count(1) into lv_count_ref from tab_ref;
  select count(1) into lv_count_ref2 from tab_ref_2;
    
  if lv_count_ref = 0 or lv_count_ref2 = 0 then
    ov_err_msg := '参考表不存在数据，流程终止';
    return; -- 校验不通过直接终止后续逻辑
  end if;
  
  dbms_output.put_line('参考表数据校验通过');

  -- 遍历每个传入的req_id单独处理
  for i in 1..iv_req_id.count loop
    lv_current_req_id := iv_req_id(i);
    lv_RejectedCount := 0;

    -- 1. 校验当前req_id下的staging数据，更新校验失败记录
    update staging
    set validation_status = 'Fail',
        validation_msg = case when e_id is null then 'Id is not present' else 'Id is longer than expected' end
    where req_id = lv_current_req_id
    and (e_id is null OR LENGTH(e_id) > 4);
    lv_RejectedCount := SQL%ROWCOUNT;

    -- 2. 按阈值判断处理逻辑
    if lv_RejectedCount <= lv_threshold_cnt then
      lv_status := 'Success';
      -- 校验通过的记录写入目标表
      merge into target_tab t
      using (
        select e_id, e_name, e_loc
        from   staging
        where  validation_status is null 
        and req_id = lv_current_req_id
      ) s
      on (t.e_id = s.e_id)
      when matched then 
        update set t.e_name = s.e_name, t.e_loc = s.e_loc
      when not matched then 
        insert (t.e_id,t.e_name,t.e_loc) values (s.e_id,s.e_name,s.e_loc);
      lv_succ_rec := lv_succ_rec + SQL%ROWCOUNT;

      -- 校验失败的记录写入拒绝表
      insert into reject_tab
      select e_id, e_name, e_loc, validation_status,validation_msg
      from   staging 
      where  validation_status = 'Fail' 
      and req_id = lv_current_req_id;
      lv_fail_rec := lv_fail_rec + SQL%ROWCOUNT;
    else
      lv_status := 'Fail';
      -- 阈值超出，所有当前req_id的记录都写入拒绝表
      update staging set validation_status = 'Fail', validation_msg = '拒绝数超出阈值' where req_id = lv_current_req_id;
      insert into reject_tab
      select e_id, e_name, e_loc, validation_status,validation_msg
      from   staging 
      where  req_id = lv_current_req_id;
      lv_fail_rec := lv_fail_rec + SQL%ROWCOUNT;
    end if;

    -- 每个req_id单独写入汇总表，如需全局汇总可将此语句移到循环外
    insert into summary_tab(tot_records, success_records, failed_records, process_status)
    values (lv_succ_rec + lv_fail_rec, lv_succ_rec, lv_fail_rec, lv_status);
    commit;
  end loop;

  ov_err_msg := 'Procedure completed successfully';
exception
  when others then
    ov_err_msg := '执行异常：'||sqlerrm;
    rollback;
end;
/

调用代码 #

set serveroutput on;
declare
  err_msg varchar2(4000);
begin
  sp_stage_target(sys.OdciNumberList(1,2),err_msg);
  dbms_output.put_line(err_msg);
end;
/

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