我只將記錄插入到target_tab中，但有3條記錄失敗，應進入拒絕表。這不起作用

create table staging_tab
(e_id number(10),
e_name varchar2(30),
e_loc varchar2(30),
validation_status varchar2(30),
validation_msg varchar2(30));

insert into staging_tab values(1,'A','AA',null,null);
insert into staging_tab values(2,'B','BB',null,null);
insert into staging_tab values(3,'C','CC',null,null);
insert into staging_tab values(null,'D','DD',null,null);
insert into staging_tab values(null,'E','EE',null,null);
insert into staging_tab values(null,'F','GG',null,null);

create table target_tab
(e_id number(10),
e_name varchar2(30),
e_loc varchar2(30));

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 table summary_tab
(tot_records number(10),
success_records number(10),
failed_records number(10)
);

Stored Procedure 

   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;

for c in (select * from staging_tab)loop

--validate e_id whether it is present or not
if c.e_id is null then

dbms_output.put_line('E ID is null '||c.e_id);
update staging_tab set validation_status = 'Fail',
validation_msg ='Id is not present' where c.e_id is null;

lv_fail_rec := lv_fail_rec   1;

--validate e_id lengh is not more than 4
elsif length(c.e_id) > 4 then

update staging_tab set validation_status = 'Fail',
validation_msg ='Id length is more than expected' where length(c.e_id) > 4;

lv_fail_rec := lv_fail_rec   1;

else

--Validation process is success and we need to insert into target table
dbms_output.put_line('Inserting into Target table');
insert into target_tab(e_id, e_name, e_loc ) 
select e_id, e_name, e_loc from staging_tab where
e_id = c.e_id and validation_status is null;

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 failed records into reject table
insert into reject_tab
select * from staging_tab where
validation_status = 'Fail';
--lv_fail_rec := lv_fail_rec   1;
end if;

--end loop;

--Total number of records
Select count(1) into lv_count from staging_tab;
lv_tot_rec := lv_count;

--Inserting into summary table
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;

執行 SP

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

基本上，該程序用于將成功記錄插入目標表并將失敗記錄插入拒絕表。最后，我將處理后的記錄跟蹤到匯總表中。我希望我的程式能夠處理這個問題。

在臨時表中，共有 6 行，其中 3 個 e_id 為 null 應進入拒絕表，3 行不包含 null e_id 應進入目標表

在匯總表中，它應該給出總記錄 = 6 條成功記錄 = 3 條失敗記錄 = 3

但是我在拒絕表中沒有得到任何記錄。有人可以幫我弄這個嗎 ？

uj5u.com熱心網友回復：

您可以在沒有回圈的情況下執行此操作：

create or replace procedure sp_stage_target_rej(ov_err_msg OUT varchar2)
is
  lv_succ_rec number(30);
  lv_fail_rec number(30);
begin
  lv_succ_rec := 0;
  lv_fail_rec := 0;
  
  UPDATE staging_tab
  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 e_id is null
  OR    LENGTH(e_id) > 4;

  INSERT INTO target_tab(e_id, e_name, e_loc )
  SELECT e_id,
         e_name,
         e_loc
  FROM   staging_tab
  WHERE  validation_status IS NULL;

  lv_succ_rec := SQL%ROWCOUNT;
  
  insert into reject_tab
  select s.*
  from   staging_tab s
  WHERE  validation_status = 'Fail';

  lv_fail_rec := SQL%ROWCOUNT;
  
  dbms_output.put_line('Inserting into Summary table');
  insert into summary_tab(
    tot_records,
    success_records,
    failed_records
  ) values (
    lv_succ_rec   lv_fail_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;
/

db<>在這里擺弄

標籤：甲骨文 plsql

上一篇：SQL將資料從一列復制到另一個表的另一列

下一篇：此查詢結果的格式時間