Procedury i funkcje

Tworzenie i usuwanie podprogramów składowanych

Procedury i funkcje są to bloki PL/SQL przechowywane w bazie danych zarówno w postaci kodu jak i w postaci skompilowanej.

Tworzenie procedury:


CREATE OR REPLACE PROCEDURE nazwa_procedury
IS
BEGIN
  ...
END;

Przykład

Stworzyć procedurę, która usunie z tabeli klienta o numerze 1001 wraz z wszystkimi podległymi rekordami.


CREATE OR REPLACE PROCEDURE del_kli_1001
IS
BEGIN
  -- usunięcie pozycji zamówień złożonych przez klienta 1001
  DELETE pozycje_zamowien
   WHERE pzm_zam_id IN (SELECT zam_id
                          FROM zamowienia
                         WHERE zam_klt_id = 1001);
  -- usunięcie zamowień złożonych przez klienta 1001
  DELETE zamowienia
   WHERE zam_klt_id = 1001;
  -- usunięcie klienta
  DELETE klienci
   WHERE klt_id = 1001;
END;

Tworzenie funkcji


CREATE OR REPLACE FUNCTION nazwa_funkcji RETURN typ_zwracany
IS
BEGIN
  ...
  RETURN wartość;
  ...
END;

Przykład

Stworzyć funkcję, która sprawdzi, czy jakieś zamówienie czeka na realizację dłużej niż miesiąc. Jeżeli takie zamówienia istnieją ma zostać zwrócona prawda, w przeciwnym wypadku fałsz.


CREATE OR REPLACE FUNCTION opoznienia
  RETURN boolean
IS
  v_opoznione binary_integer;
BEGIN
  SELECT count(*) INTO v_opoznione
    FROM zamowienia
   WHERE zam_data_realizacji IS NULL
     AND add_months(zam_data_zamowienia,1) < sysdate;
  IF v_opoznione > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

Wywołanie procedury w kodzie PL/SQL polega na podaniu jej nazwy nazwa_procedury;
Ponieważ każda funkcja zwraca jakąś wartość, to wartość ta musi zostać „zagospodarowana”. W związku z tym funkcja musi być wywoływana jako składnik wyrażenia, w najprostszym przypadku:
zmienna := nazwa_funkcji;

Przykład

Napisać blok anonimowy, który wywoła wcześniej stworzoną procedurę i funkcję.


DECLARE
  v_opoznienia boolean;
BEGIN
  del_kli_1001;
  v_opoznienia := opoznienia;
END;

Usuwanie procedury


                            DROP PROCEDURE nazwa_procedury;

Usuwanie funkcji


                            DROP FUNCTION nazwa_funkcji;

Przykład

Usunąć stworzoną poprzednio procedurę i funkcję.


DROP PROCEDURE del_kli_1001;
DROP FUNCTION opoznienia;

Parametry

Sposób deklaracji


                            CREATE PROCEDURE | FUNCTION nazwa_podprogramu(
                            nazwa typ typ_wartości DEFAULT wartość
                            {, nazwa typ typ_wartości DEFAULT wartość}
                            )
                            IS
                            ...

typ_wartości nie może zawierać precyzji. Precyzja jest pobierana z parametru aktualnego a nie z formalnego.

Przykład


-- dobrze
CREATE PROCEDURE p1 (p_1 IN varchar2) IS ...
-- źle
CREATE PROCEDURE p1 (p_1 IN varchar2(64) ) IS ...

Typy parametrów

W PL/SQL istnieją trzy typy parametrów: IN, OUT, IN OUT. Jeżeli żaden z nich nie zostanie wyspecyfikowany, przyjmowany domyślnie jest typ IN.

IN – wartość parametru aktualnego jest przekazywana do podprogramu. Parametr formalny zachowuje się tak jakby był stałą (tylko do odczytu). Po wyjściu z podprogramu parametr aktualny się nie zmienia.

OUT – wartość parametru aktualnego jest ignorowana. Parametr formalny ma wartość NULL do momentu inicjalizacji. Można go czytać i zapisywać. W momencie wyjścia z podprogramu wartość jest przepisywana z parametru formalnego do aktualnego.

IN OUT – jest to kombinacja obu poprzednich typów. W momencie wywołania parametr aktualny zostaje skopiowany do parametru formalnego, który zachowuje się jak zmienna zainicjalizowana. Przy wychodzeniu z podprogramu wartość parametru formalnego zostaje przekopiowana do parametru aktualnego.

Jeżeli podprogram wyrzuci wyjątek i nie zostanie on obsłużony wewnątrz tego podprogramu, to kopiowanie parametru formalnego do aktualnego w parametrach OUT i IN OUT nie występuje.

Sposób wywoływania

Wywoływać podprogramy z parametrami można na dwa sposoby:

Notacja pozycyjna

W notacji pozycyjnej parametry aktualne są przyporządkowywane do parametrów formalnych na podstawie pozycji, na której wystąpiły poszczególne parametry w momencie deklaracji i wywołania.

Przykład
```
-- deklarecja
CREATE PROCEDURE p1 (
par1 IN varchar2,
par2 IN number,
par3 OUT number
)
IS ...
-- wywołanie
p1(’Ala ma kota’,
   5,
   v_num
);
                        
```

Notacja imienna

W notacji imiennej kolejność wpisywania parametrów nie ma znaczenia. W momencie wywoływania określamy ściśle, który parametr aktualny ma być przypisany do którego parametru formalnego.

Przykład


-- deklarecja
CREATE PROCEDURE p1 (
par1 IN varchar2,
par2 IN number,
par3 OUT number
)
IS ...
-- wywołanie
p1(par1 => ’Ala ma kota’,
   par2 => 5,
   par3 => v_num
);

Przykład

Stworzyć i wywołać procedurę, która dla zadanego przez numer zamówienia zwróci rabat, jaki przysługuje do tego zamówienia w procentach i kwotowo.


CREATE OR REPLACE PROCEDURE oblicz_rabat
                  (p_numer IN number,
                   p_rabat_proc OUT number,
                   p_rabat_kw OUT number)
IS
  v_zam_id  zamowienia.zam_id%TYPE;
  v_kwota   number(6);
BEGIN
  -- odczytanie identyfikatora zamówienia
  SELECT zam_id INTO v_zam_id
    FROM zamowienia
   WHERE zam_numer = p_numer;
  -- policzenie kwoty zamówienia
  SELECT sum(PZ.pzm_ilosc*T.tow_cena) INTO v_kwota
    FROM pozycje_zamowien PZ, towary T
   WHERE PZ.pzm_tow_id = T.tow_id
     AND PZ.pzm_zam_id = v_zam_id;
  -- odczytanie przysługujacego rabatu
  SELECT grc_rabat, grc_rabat*v_kwota/100
    INTO p_rabat_proc, p_rabat_kw
    FROM grupy_cen
   WHERE v_kwota BETWEEN grc_cena_od AND grc_cena_do;
END;
--
-- wywołanie procedury
DECLARE
 v_pr number(2);
 v_kw number(10);
BEGIN
  oblicz_rabat(124, v_pr, v_kw);
  dbms_output.put_line('Rabat: '
                      ||to_char(v_pr, '999G9990D99')||' %');
  dbms_output.put_line('Rabat: '
                      ||to_char(v_kw, '999G9990D99')||' PLN');
END;

Przywileje związane z podprogramami, kontekst podprogramów

Aby wykonać jakikolwiek podprogram potrzebne jest uprawnienie EXECUTE na tym podprogramie. Użytkownik wykonujący podprogram nie musi mieć praw do obiektów, do których odwołuje się podprogram. Podprogramy są wykonywane w kontekście danych i obiektów właściciela podprogramu, a nie użytkownika, który wykonuje podprogram.

Obsługa błędów i wyjątków

Błędy w bazie danych Oracle

Jeżeli użytkownik lub jakikolwiek proces próbuje wykonać czynność, której serwer bazy danych nie potrafi wykonać, to serwer bazy danych generuje błąd. W takim wypadku mówimy, że pewna konkretna instrukcja spowodowała wystąpienie błędu.

Polecenie może zakończyć się błędem, gdy:

nie ma fizycznych możliwości wykonania się instrukcji (np. koniec miejsca przeznaczonego dla tabeli podczas instrukcji INSERT),
użytkownik nie ma prawa wykonania instrukcji (np. użytkownik próbuje zmodyfikować dane w tabeli, na której nie ma prawa UPDATE),
instrukcja odwołuje się do nieistniejących lub niepoprawnych obiektów (np. próba wykonania procedury, która jest skompilowana z błędami),
próba modyfikacji danych powodująca naruszenie więzów integralności, itp.

W bazie danych Oracle jest zdefiniowanych kilka tysięcy różnych błędów odpowiadających różnym nie przewidzianym sytuacjom. Każdy błąd, jaki jest zdefiniowany w bazie danych Oracle ma swój unikalny numer i odpowiadający mu komunikat.

Przykład

Wykonać instrukcję, która spowoduje wystąpienie błędu.


SQL> DROP USER sys;
DROP USER sys
*
ERROR w linii 1:
ORA-01031: insufficient privileges

Jeżeli próba wykonania instrukcji spowoduje błąd, to oznacza to, że dana instrukcja się nie wykonała, nie powoduje to natomiast wycofania transakcji, w której była podjęta próba wykonania się instrukcji.

Wyjątki predefiniowane

Jeżeli błąd zostanie spowodowany przez pojedynczą instrukcję SQL wpisaną przez jakiegoś użytkownika, to instrukcja nie zostanie wykonana, w sesji użytkownika pojawi się numer i komunikat błędu i sterowanie powróci do konsoli użytkownika, który będzie mógł odpowiednio zareagować na powstanie nieprzewidzianej sytuacji. Jeżeli natomiast błąd wystąpi podczas wykonywania się kodu PL/SQL, to użytkownik nie może bezpośrednio na niego zareagować, natomiast może przewidzieć wystąpienie błędu i odpowiednio przygotować podprogram w celu obsługi takiej sytuacji. Bardzo ważnym elementem każdej aplikacji jest właśnie obsługa błędów. Jeżeli w trakcie wykonywania się kodu PL/SQL wygenerowany zostanie błąd bazy danych Oracle, to w kodzie PL/SQL zgłaszany jest tzw. wyjątek.

W PL/SQL zdefiniowanych jest kilkanaście standartowych wyjątków, które są zgłaszane przez Oracle w przypadku wystąpienia pewnych błędów podczas wykonywania się kodu.

Poniżej przedstawiono najczęściej zgłaszane wyjątki:

invalid_cursor - Jeśli użyjemy nie otwartego kursora.
cursor_already_open - Jeśli siłujemy otworzyć już otwarty kursor.
dup_val_on_index - Naruszenie unikalności wartości w kolumnie bazodanowej.
no_data_found - Brak danych do odszukania lub przetworzenia.
too_many_rows - Zbyt wiele wierszy, gdy spodziewano się jednego.
zero_divide - Próba wykonania dzielenia przez zero.
rowtype_mismatch - Niezgodność typów danych kursora.
invalid_number - Zmienna znakowa została użyta jako numeryk.

Wyjątek i błąd to nie to samo.

Wywołanie wyjątku najczęściej jest powodowane wystąpieniem błędu, ale zdecydowana większość błędów nie powoduje zgłoszenia żadnego wyjątku. Mogą także istnieć wyjątki, które nie są powodowane błędem na bazie danych Oracle.

Obsługa wyjątków

W momencie wystąpienia jakiegokolwiek błędu (nie tylko związanego ze zdefiniowanym wyjątkiem), wykonywanie bloku jest przerwane, a sterowanie przechodzi do sekcji obsługi wyjątków w bloku, w którym błąd wystąpił.

Sekcja obsługi wyjątków składa się z sekwencji następujących po sobie ciągów instrukcji, z których każdy jest dedykowany obsłudze określonego wyjątku.


BEGIN
  ...
EXCEPTION
  WHEN wyjątek1 THEN
    ciąg_instrukcji1
  WHEN wyjątek2 THEN
    ciąg_instrukcji2
  WHEN OTHERS THEN
    ciąg_instrukcji3
END;

Wyjątek OTHERS służy do przechwycenia wszystkich błędów (nie tylko wyjątków). W powyższym przykładzie ciąg_instrukcji3 wykona się w przypadku wystąpienia każdego błędu lub wyjątku poza wyjątkami wyjątek1 i wyjątek2.

Po obsłużeniu wyjątku sterowanie nie powraca już do bloku, w którym wystąpił błąd.

Przykład

Wstawić do tabeli uczniowie osobę o imieniu Jan i nazwisku Kowalski ale tylko pod warunkiem, że w tabeli jeszcze taka osoba nie jest wstawiona.


BEGIN
  INSERT INTO uczniowie (imie, nazwisko) 
       VALUES (‘Jan’, ‘Kowalski’);
EXCEPTION
  WHEN dup_val_on_index THEN
    NULL;
END;

Definiowanie własnych wyjątków i instrukcja RAISE

W bloku PL/SQL może wystąpić sytuacja błędna, pomimo tego, że Oracle nie zgłasza żadnego błędu. Błąd ten może wynikać z logiki aplikacji, np., gdy w wyniku pewnych obliczeń dostaniemy liczbę ujemną na określenie wieku pewnej osoby.

W takim wypadku można zdefiniować własny wyjątek, następnie wywołać go instrukcją RAISE i obsłużyć w sekcji obsługi wyjątków.


DECLARE
  wyjątek1 EXCEPTION;
BEGIN
  ...
  RAISE wyjątek1;
  ...
EXCEPTION
  ...
  WHEN wyjątek1 THEN
    ciąg_instrukcji
  ...
END;

Tak zdefiniowany i wywołany wyjątek jest traktowany na równi z pozostałymi, predefiniowanymi wyjątkami i podlega wszystkim prawom propagacji omówionym poniżej.

Przykład


DECLARE
  v1 number;
  v2 number;
  e_zly_wiek EXCEPTION;
BEGIN
  ...
  v1 := v1 – v2 * 365;
  IF v1 < 0 THEN
    RAISE e_zly_wiek;
  END IF;
  ...
EXCEPTION
  ...
  WHEN e_zly_wiek THEN
    INSERT INTO err_msg VALUES (USER, SYSDATE, ‘Wiek < 0’);
  ...
END;

Obsługa innych błędów Oracle

Wystąpienie wszystkich błędów, nie tylko tych powiązanych z predefiniowanymi wyjątkami, powoduje przejście do sekcji obsługi wyjątków. Błędy te są przyporządkowane wyjątkowi OTHERS.

SQLCODE i SQLERRM

Aby podczas obsługi wyjątku OTHERS można było rozpoznać i w różny sposób obsłużyć różne błędy, należy skorzystać z funkcji SQLCODE i SQLERRM. Zawierają one odpowiednio kod i komunikat błędu, który spowodował przejście do sekcji obsługi wyjątków.

Przykład


BEGIN
  ...
  INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje błąd
  ...
EXCEPTION
  ...
  WHEN OTHERS THEN
    IF SQLCODE = -14400 THEN
      ciąg_instrukcji
    ELSIF ...
    ...
END;

Pragma EXCEPTION_INIT

Aby nie korzystać z funkcji SQLCODE, można zdefiniować własny wyjątek, a następnie powiązać go z konkretnym błędem Oracle.


DECLARE
  wyjątek EXCEPTION;
  PRAGMA EXCEPTION_INIT(wyjątek, numer);
BEGIN

W takim przypadku, każde wystąpienie błędu o numerze numer będzie powodowało zgłoszenie wyjątku wyjątek.

Przykład


DECLARE
  e_zly_zakres_partycji EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_zly_zakres_partycji, -14400);
BEGIN
  ...
  INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje błąd
  ...
EXCEPTION
  WHEN e_zly_zakres_partycji THEN
      ciąg_instrukcji
END;

Propagowanie się wyjątków

Jeżeli wyjątek zostanie obsłużony w bloku, w którym został zgłoszony, to po tym obsłużeniu wykonywanie bloku zostaje zakończone, ale blok zewnętrzny traktuje blok z błędem tak, jakby zakończył się on pomyślnie.

Jeżeli wyjątek nie zostanie obsłużony w bloku wewnętrznym, to blok ten kończy się po wystąpieniu błędu, ale w bloku zewnętrznym w momencie powrotu sterowania z bloku wewnętrznego zostaje zgłoszony nie obsłużony w bloku wewnętrznym błąd, a zatem sterowanie natychmiast przechodzi do sekcji obsługi błędów w bloku zewnętrznym.

Jeżeli błąd również w bloku zewnętrznym nie został obsłużony, to sytuacja się powtarza do momentu obsłużenia błędu na którymś poziomie zagnieżdżenia lub do momentu osiągnięcia najwyższego poziomu.

RAISE_APPLICATION_ERROR

W PL/SQL można nie tylko zdefiniować własne wyjątki, ale także własne błędy.

Służy do tego procedura RAISE_APPLICATION_ERROR.

raise_application_error(numer, komunikat);

Oracle pozostawia użytkownikom do wykorzystania numery od –20,999 do –20,000. Komunikat może mieć maksymalnie 512 znaków długości.

Wywołanie tej funkcji spowoduje błąd, a SQLCODE i SQLERRM przybiorą odpowiednie wartości podane jako parametry.

Przykład


DECLARE
  v1 number;
  v2 number;
BEGIN
  v1 := v1 – v2 * 365;
  IF v1 < 0 THEN
    raise_application_error(-20001, ’Wiek mniejszy od zera’);
  END IF;
END;

Po wykonaniu tego bloku pojawi się komunikat:


ORA-20001: Wiek mniejszy od zera
ORA-06512: at line 7

Tworzenie i usuwanie podprogramów składowanych

Przykład

Tworzenie funkcji

Przykład

Przykład

Usuwanie procedury

Usuwanie funkcji

Przykład

Parametry

Sposób deklaracji

Przykład

Typy parametrów

Sposób wywoływania

Notacja pozycyjna

Przykład

Notacja imienna

Przykład

Przykład

Przywileje związane z podprogramami, kontekst podprogramów

Obsługa błędów i wyjątków

Błędy w bazie danych Oracle

Przykład

Wyjątki predefiniowane

Obsługa wyjątków

Przykład

Definiowanie własnych wyjątków i instrukcja RAISE

Przykład

Obsługa innych błędów Oracle

SQLCODE i SQLERRM

Przykład

Pragma EXCEPTION_INIT

Przykład

Propagowanie się wyjątków

RAISE_APPLICATION_ERROR

Przykład