Wymyśliłem coś takiego, ale nie działa, będę wdzięczny za pomoc
CREATE PROCEDURE dodaj_gatunek(p_gatunek IN VARCHAR2)
IS
BEGIN
IF NOT EXIST (SELECT gatunek FROM Ksiazki_gatunki WHERE gatunek=p_gatunek)
BEGIN
INSERT INTO Ksiazki_gatunki (id_gatunku, gatunek) VALUES (seq_gatunki.nextval, p_gatunek);
END;
END dodaj_gatunek;Najprościej jest odczytać rekord w niejawnym kursorze. (select … into).
A jeżeli nie znajdzie (zgłosi błąd) to wtedy dodać.
Przykładowa procedura ma postać:
create procedure dodaj_gatunek(p_gatunek in varchar2)
zv_gatunek varchar2(100);
is
begin
select gatunek
into zv_gatunek
from ksiazki_gatunki
where gatunek=p_gatunek;
exception
when no_data_found then
insert into ksiazki_gatunki (id_gatunku, gatunek)
values (seq_gatunki.nextval, p_gatunek);
end dodaj_gatunek;A nie lepiej dodać unikalny index na kolumnie “gatunek” w tabeli “Ksiazki_gatunki” gwarantujący unikalność wartości w tej kolumnie?
Wtedy nie będzie trzeba za każdym razem sprawdzać czy taki rekord już istnieje, bo będzie to za nas robił system.
Tutaj jest składnia polecenia: http://www.techonthenet.com/oracle/indexes.php
Polecam takie rozwiązanie, ponieważ jest dużo wydajniejsze niż zwykłe sprawdzanie. Dzieje się tak dlatego, że system będzie trzymał swoją strukturę indexu i przy każdej operacji zapisu do tabeli będzie sprawdzał indeks pod kątem powtórzeń. Operacje na indexie są bardzo szybkie.
Tylko wtedy trzeba w procedurze “przechwycić” błąd indeksu, tak aby w aplikacji nie rzucało, żadnymi ORA-mi.
Składnia w pseudokodzie byłaby następująca:
Nagłowek procedury
....
Insert into Ksiazki_gatunki (..)...
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
end procedury;
Mój poprzedni kod PL/SQL jest nieco bardziej czytelny - widać logikę postępowania. W przypadku kodu z DUP_VAL_ON_INDEX zawsze będzie pytanie “jaki indeks”?
W przypadku tablicy Ksiazki_gatunki (mało rekordów) nie ma to większego znaczenia.
W przypadku dużej tablicy Twoja metoda będzie nieco szybsza, ale kosztem czytelności kodu.
(oczywiście zakładam istnienie indeksu w tabeli Ksiazki_gatunki -wtedy “select into” też będzie szybki)
– Dodane 11.03.2013 (Pn) 20:33 –
Postanowiłem sprawdzić, czy faktycznie jest to dużo wydajniejsze.
Utworzyłem odpowiednie skrypty testowe PL/SQL i kilka razy dodałem 100 tys. rekordów do tabeli w bazie Oracle.
Moja metoda była 2 razy wolniejsza, jeżeli wstawiany rekord nie istniał w bazie.
Za to jeżeli rekord już był, to moja procedura była 10x szybsza niż zdanie się na system, który pilnuje indeksu unikalnego!!
Pełne wyniki pomiarów wydajności procedur PL/SQL umieściłem na blogu:http://blog.bazydanych.net.pl/?p=15
CREATE PROCEDURE dodaj_gatunek(p_gatunek IN VARCHAR2)
IS
v_licz number := 0;
BEGIN
– zliczam sobie rekordy dla podanego parametru
SELECT count(1) into v_licz FROM Ksiazki_gatunki WHERE lower(gatunek)=lower(p_gatunek);
if v_licz = 0 then
BEGIN
INSERT INTO Ksiazki_gatunki (id_gatunku, gatunek) VALUES (seq_gatunki.nextval, p_gatunek);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
end if;
END dodaj_gatunek;