Serii o ładowaniu danych za pomocą SQL i logowaniu błędów LOG ERRORS ciąg dalszy. Im dalej w las tym ciekawiej :D
Poprzednio do weryfikacji, czy wystąpił błąd użyłam Global Temporary Table i zwykłej tabeli. Dzisiaj do tego samego zadania wykorzystam zmienne globalne PL/SQL.
Przypomnę, że korzystam ze standardowego schematu HR(jak odblokować schemat HR). Na potrzeby testu tworzę pustą tabelę myEmp, jako kopię tabeli Employees. Następnie zmniejszam długość kolumny last_name na 9 znaków (w tabeli employees są 4 rekordy z last_name > 9) by wymusić podczas ładowania danych błąd. W tabeli źródłowej mamy 107 rekordów ( w moim przykładzie), z czego spodziewamy się 4 błędów.
Jak zatem wykorzystując zmienne globalne sprawdzić czy ładowanie danych spowodowało błąd?
1. Zmienną gnCnt do zapisu ilości rekordów zadeklarowałam w body pakietu. Można też w specyfikacji, jak kto woli :)
2. Utworzyłam funkcję upd_cnt(pcnt number) aktualizującą zmienną globalną gnCnt
Funkcja ustawia zmienną gnCnt na wartość otrzymaną w parametrze wejściowym pnCnt.
function upd_cnt(pncnt number) return number is
begin
-- all rows counter
gnCnt := pncnt;
-- counter for test
gtest := nvl(gtest,0) + 1;
return 0;
exception when others then dbms_output.put_line(sqlerrm);
end;
3. Do ładowania danych użyłam INSERT ALL. Dla pierwszego ładowanego rekordu wywołuję funkcję upd_cnt z parametrem ilości rekordów do załadowania. Funkcja zawsze zwraca 0 i wykorzystuję to, by zawsze wykonać insert wiersza.
insert all
when rownum = 1 and upd_cnt (cnt) = 0 then into myEmp (
employee_id (...)
4. Ilość rekordów wyliczam w klauzuli SELECT polecenia INSERT ALL za pomocą funkcji analitycznej count(*) over
select rownum , count(*) over () cnt, employee_id (...)
5. Pozostałe wiersze ładowane są bez udziwnień
else
into myEmp( employee_id,
6. Ilość poprawnie zainsertowanych rekordów to
ncnt_ins := sql%rowcount.
7. Ilość błędów zatem to będzie ilość rekordów do załadowania - ilość rekordów zainsertowanych
gnCnt - ncnt_ins
Oczywiście w insertach używam klauzulę
log errors reject limit unlimited
Teraz uruchomienie:
begin
delete from myEmp;
delete from err$_myEmp;
commit;
test_log_err.gtest := 0;
end;
/
Function upd_cnt executed 1 time/s.
all rows: 107 , Inserted: 103, Errors: 4
PL/SQL procedure successfully completed.
I voila! Gotowe!
107 rekordów pobranych do ładowania, 103 rekordy zainsertowane, 4 błędy.
select count(*) from Employees;
COUNT(*)
----------
107
select count(*) from myEmp;
COUNT(*)
----------
103
select count(*) from err$_myEmp;
COUNT(*)
----------
4
Jeszcze muszę wyjaśnić zmienną gTest. Zmienna ta jest ustawiana w funkcji upd_cnt by udowodnić, że funkcja uruchomi się tylko raz. Funkcje PL/SQL w SQL są mocno niewydajne, ale jedno uruchomienie nie powinno mieć większego wpływu na wydajność.
Trzeba jednak pamiętać, że jeśli będziemy insertowali dane do więcej niż jednej tabeli - nie będziemy mogli wyliczyć ilości błędów dla każdej tabeli osobno. Będziemy mogli wyliczyć tylko sumaryczne ilości insertowanych oraz błędnych rekordów. Jeśli jednak nie będzie potrzeba takich detali, rozwiązanie jest proste i łatwe do zastosowania.
Code:
create or replace package test_log_err is
--gnCnt number ; -- all rows counter
gTest number; --counter forc checking how many times function is executed
function upd_cnt ( pncnt number) return number ;
procedure ins;
end;
/
-----------------------------------------------------------------------------
create or replace package body test_log_err is
gnCnt number ; -- all rows counter
-----------------------------------------------------------------------------
function upd_cnt(pncnt number) return number is
begin
-- all rows counter
gnCnt := pncnt;
-- counter for test
gtest := nvl(gtest,0) + 1;
return 0;
exception when others then dbms_output.put_line(sqlerrm);
end;
-----------------------------------------------------------------------------
procedure ins is
ncnt_ins number;
begin
insert all
when rownum = 1 and upd_cnt (cnt) = 0 then into myEmp (
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id) values ( employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id)
log errors reject limit unlimited
else
into myEmp( employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id) values ( employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id)
log errors reject limit unlimited
select rownum , count(*) over () cnt, employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
from employees ;
ncnt_ins := sql%rowcount;
dbms_output.put_line('Function upd_cnt executed '|| gTest || ' time/s.');
dbms_output.put_line ('all rows: '||nvl(gnCnt,0) ||' , Inserted: ' ||ncnt_ins||', Errors: '||to_char(nvl(gnCnt,0) - ncnt_ins));
end;
end;
/
Komentarze
Prześlij komentarz