#oracle #plsql
Вопрос:
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 строки, которые не содержат нулевого e_id, должны попасть в целевую таблицу
И в сводной таблице должно быть указано общее количество записей = 6 успешных записей = 3 неудачных записи = 3
Но я не получаю никаких записей в таблице отклонений. Может ли кто-нибудь помочь мне с этим?
Ответ №1:
Вы можете сделать это без циклов:
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<>fiddle здесь