#381
Rozdział 17.
Procedury składowane w języku PL/SQL (Oracle)

Procedury składowane w Oracle'u tworzone są w specjalnym języku proceduralnym PL/SQL, który rozszerza możliwości standardowego języka SQL. PL/SQL odchodzi od standardu języka SQL w większym stopniu niż inne języki proceduralne. Jest to szczególnie widoczne biorąc pod uwagę wymagania tego języka co do struktury kodu. Wszystkie programy PL/SQL muszą składać się z trzech sekcji: sekcji deklaracji, sekcji poleceń wykonywalnych i sekcji obsługi wyjątków. PL/SQL różni się również od innych języków pod względem sposobu przetwarzania i zwracania danych.
Kod tworzony w języku PL/SQL może być zapisywany w postaci procedur, które z kolei mogą być przechowywane w formie pakietów. Istnieje ponadto możliwość tworzenia bloków anonimowych, które są wykonywane, tak jak każdy program, ale nie są nigdzie zapamiętywane. W pierwszej kolejności omówiona zostanie zasada tworzenia bloków anonimowych, później zajmiemy się tworzeniem procedur składowanych oraz pakietów. Zaczniemy od rzeczy najbardziej podstawowej, czyli od wyjaśnienia roli trzech wymienionych wcześniej sekcji. Wszystkie programy PL/SQL muszą być zgodne z następującą formą:

DECLARE
wyrażenia deklaracyjne
BEGIN
polecenia wykonywalne
EKCEPTION
wyrażenia obsługi błędów
END;

Zanim omówione zostaną szczegóły działania poleceń języka PL/SQL, przedstawimy krótki fragment kodu źródłowego przykładowego zapytania, które b;d>�s stanowić podstawę do dalszej dyskusji na ten temat. Listing 17.1 zawiera kod zapytania, które zwraca dochód osiągnięty przez dany film. W pierwszej wersji kodu wartości zmiennych zostały ustalone wewnątrz zapytania, ale oczywiście można to zrobić również w inny sposób, o czym będzie mowa w dalszej części rozdziału.
#382
Poniższy przykład wstawia wartość dochodu do nowej tabeli składającej się z dwóch kolumn: identyfikatora filmu i kwoty dochodu.
----------------------------------
Listing 17.1. Zapytanie zwracające dochód osiągnięty przez film

DECLARE
gross NUMBER;
budget NUMBER;
profit NUMBER;
movie_id INT(6);
BEGIN
gross := 50;
budget := 75;
movie_id:= 1;
profit := budget - gross;
INSERT INTO Movie_Profit VALUES (movie_id, profit);
END;
.
/
PL/SOL procedurę successfully completed.
----------------------------------

Kropka występująca samodzielnie w wierszu wskazuje koniec bloku kodu PL/SQL, natomiast slash (/) uruchamia ostatnie wprowadzone polecenie lub blok kodu.

Sekcja deklaracji

W sekcji deklaracji muszą zostać zidentyfikować wszystkie zmienne, stałe oraz kursory, które będą używane w części wykonywalnej kodu PL/SQL. W przykładzie z listingu 17.1 zadeklarowane zostały cztery zmienne, z których trzy są typu NUMBER, a jedna typu INT. Typy danych nadawane zmiennym w języku PL/SQL są takie same, jak typy przewidziane dla kolumn tabel Oracle'a. Lista typów danych dostępnych w Oracle'u znajduje się w rozdziale 18.
Zmiennym tworzonym w sekcji deklaracyjnej można od razu nadawać wartości początkowe. Listing 17.2 jest wiernym odzwierciedleniem listingu 17.1, z tą różnicą, że wartości zmiennych są ustalane w sekcji deklaracyjnej, a nie tak jak wcześniej, w sekcji wykonywalnej. ----------------------------------
Listing 17.2. Ustalanie wartości początkowych zmiennych w sekcji deklaracyjnej

DECLARE
gross NUMBER := 75;
budget NUMBER := 50;
profit NUMBER;
movie_id INT(6) := 1;
BEGIN
profit := budget - gross;
INSERT INTO Movie_Profit VALUES (movie_id, profit);
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------
#383
Ponieważ wartości zmiennych zostały ustalone w sekcji deklaracyjnej, część wyrażeń realizująca to samo zadanie w sekcji wykonywalnej mogła zostać usunięta. Wartości początkowe nadawane zmiennym w sekcji deklaracyjnej mogą być swobodnie modyfikowane w sekcji wykonywalnej, tak jak pokazuje to listing 17.3.
----------------------------------
Listing 17.3. Przypisywanie nowych wartości zmiennym

DECLARE
gross NUMBER :- 15;
budget NUMBER := 50;
profit NUMBER;
movie_id INT(6) := 1;
BEGIN
gross := 77;
budget := 56;
movie_id := 8;
profit := budget - gross;
INSERT INTO Movie_Profit VALUES (movie_id, profit);
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------

State

Stałe są tworzone w sekcji deklaracyjnej na podobnych zasadach, jakie obowiązują zmienne. Różnica między nimi polega na tym, że stałej nie da się zmodyfikować w sekcji wykonywalnej przez przypisanie, można jedynie odwoływać się do jej wartości ustalonej raz na początku (w sekcji deklaracyjnej).
=======================
Rada
Stałe stosowane są zazwyczaj wszędzie tam, gdzie zachodzi potrzeba częstego odwoływania się do pewnej niezmiennej wartości. Na przykład, jeżeli w zapytaniu wielokrotnie używana jest wartość podatku VAT, można zastąpić ją stałą o nazwie podatek_VAT. Korzyści płynące z takiego rozwiązania, to przede wszystkim łatwość modyfikacji kodu (JeżeN stawka podatku VAT ulegnie zmianie, wystarczy poprawić jedną wartość w sekcji deklaracyjnej), poza tym kod jest bardziej czytelny, ponieważ nazwa stałej lepiej kojarzy się z jej przeznaczeniem, niż zwykła liczba.
=====================

Aby utworzyć stałą należy wstawić słowo kluczowe CONSTANT między jej nazwę a typ danych. Oto przykład deklaracji stałej o nazwie movie_id:

movie id CONSTANT INT(6) := 1;


Deklarowanie kursorów

Każdy kursor użyty w bloku kodu musi zostać wcześniej zadeklarowany w sekcji deklaracyjnej. Sposób deklarowania kursorów wyjaśniony został w rozdziale 14. "Transakcje i kursory" (podrozdział "Kursory w języku PL/SQL"). Tutaj ograniczymy się jedynie do krótkiego przypomnienia.
#384
Kursory deklarowane są przy użyciu następującej składni:

CURSOR nazwa_kursora IS
wyrażenie SELECT

Po zadeklarowaniu kursora można utworzyć zmienną, w której umieszczane będą dane pobierane z kursora. Jeżeli użyjemy konstrukcji %ROWTYPE, zmienna przyjmie strukturę wiersza zwracanego przez zapytanie związane z kursorem. Aby tak się mogło stać, typem danych deklarowanej zmiennej musi być nazwa kursora z dołączonym na końcu wyrażeniem %ROWTYPE:

nazwa_zmiennej nazwa_kursora%ROWTYPE

Listing 17.4 zawiera sekcję deklaracyjną, w której utworzony został kursor do pobierania danych, niezbędnych do wyliczenia dochodu filmu.
----------------------------------
Listing 17.4. Przykład definicji kursora w kodzie PL/SQL

DECLARE
profit NUMBER;
CURSOR movies_cursor IS
SELECT movie_id, budget, gross
FROM Movies; movies rec movies cursor%ROWTYPE
----------------------------------

Sekcja poleceń wykonywalnych

Po zadeklarowaniu wszystkich niezbędnych zmiennych, stałych oraz kursorów, przychodzi kolej na napisanie kodu, który faktycznie zrealizuje nasze zadanie. W przykładowym zapytaniu z listingu 17.1, sekcja wykonywalna obliczała jedynie zysk z filmu i wstawiała tę wartość jako nowy wiersz do tabeli Movie_Prof it. Teraz opiszemy kilka bardziej skomplikowanych operacji, jakie można wykonać przy użyciu języka PL/SQL.

Wyprowadzanie danych

Język PL/SQL został zaprojektowany do przetwarzania danych - jest w stanie przyjmować dane, przetwarzać je, a na końcu zapisać lub zwracać wygenerowane wyniki. Zanim przejdziemy do omawiania procesu przetwarzania danych, warto powiedzieć kilka słów na temat tego, w jaki sposób wydobyć użyteczne dane z wnętrza bloku programu.
PL/SQL jest nastawiony na manipulowanie danymi w bazie danych, a większość zapytań w tym języku służy do przetwarzania danych w połączeniu z wyrażeniami INSEKT, UPDATE i DELETE. W planach twórców języka PL/SQL nie znalazły miejsca mechanizmy tworzenia zaawansowanych zapytań zwracających gotowe tabele danych lub raporty. To, czego brakuje językowi PL/SQL, posiada inne narzędzie Oracle'a znane jako SQL*Plus - w programie tym przewidziano szereg wyspecjalizowanych opcji przeznaczonych do formatowania wyników zapytań i generowania raportów.
#385
Jeżeli zachodzi potrzeba zwrócenia określonych wartości z bloku kodu, można umieścić go w procedurze lub funkcji, z których obie są w stanie przekazywać wartości do wywołującego je zapytania. Można też skorzystać z pakietu DBMS_OUTPUT, aby wyprowadzić informacje na konsolę. DBMS_OUTPUT służy na ogół do generowania informacji w czasie testowania zapytań, o czym będzie mowa w dalszej części rozdziału.

Przypisania

Nadawanie wartości zmiennym jest jedną z najczęściej wykonywanych operacji. W języku PL/SQL operator przypisania ma postać : =. Pojedynczy znak równości (=) zarezerwowany jest dla porównań logicznych.
Oto kilka przykładowych przypisań (zaczerpniętych z listingu 17.1):

gross := 50;
budget := 75;
movie_id := 1;
profit := budget - gross;


Wyrażenia warunkowe

PL/SQL umożliwia stosowanie wyrażeń l F, ELSIF oraz ELSE. Koniec bloku l F wyznacza wyrażenie END IF. Jeżeli warunek wyrażenia i F jest prawdziwy, wykonywany jest blok kodu występujący bezpośrednio za tym wyrażeniem, w przeciwnym wypadku wykonane zostaje polecenie (lub blok poleceń) za słowem kluczowym ELSE (pod warunkiem, że programista zastosował tę klauzulę). Słowo kluczowe ELSIF umożliwia tworzenie bloków wyrażeń warunkowych. Ogólna konstrukcja wyrażenia warunkowego wygląda następująco:

IF warunek THEN
polecenia
ELSIF warunek THEN
polecenia
ELSE
polecenia
END IF;

Proces wykonania wyrażenia warunkowego polega na sprawdzaniu kolejnych warunków, aż do chwili napotkania wartości prawdziwej. Jeżeli wszystkie waranki są fałszywe, wykonany zostaje blok kodu za słowem ELSE. Jeśli wyrażenie nie zawiera klauzuli ELSE, wykonywana jest dalsza część programu (żadna z części wyrażenia warunkowego nie zostaje wykonana). Przykład złożonego wyrażenia warunkowego znajduje się w listingu 17.5.
----------------------------------
Listing 17.5. Złożone wyrażenie warunkowe

DECLARE
crow INT := 10;
BEGIN
IF crow < 10 THEN
INSEKT INTO
Test VALUES (crow);
#386
ELSIF crow > 10 THEN
INSEKT INTO Test VALUES
(crow * 2);
ELSE
INSEKT INTO Test
VALUES (crow / 2) ;
END IF;
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------

Łatwo można zauważyć, że w powyższym programie wykonana zostanie część kodu za klauzulą ELSE, ponieważ zmienna crow nie jest ani większa od 10, a nie jest też mniejsza od tej liczby. Niezależnie od tego, jaką wartość przyjęłaby zmienna, wykonana może zostać tylko jedna z klauzul całego wyrażenia warunkowego.
Wyrażenia i F mogą być również zagnieżdżane wewnątrz siebie (za słowami kluczowymi i F lub ELSE). Przykład takiego zagnieżdżenia pokazuje listing 17.6.
----------------------------------
Listing 17.6. Zagnieżdżone wyrażenie warunkowe

DECLARE
crow INT := 10; BEGIN
IF crow > 10 THEN
IF crow < 15 THEN
INSEKT INTO Test
VALUES (crow);
END IF;
END IF;
END;

PL/SQL procedurę successfully completed.
----------------------------------

Ilość możliwych zagnieżdżeń jest nieograniczona. W powyższym przykładzie zagnieżdżone zostało jedynie proste wyrażenie IF, ale równie dobrze mogły mu towarzyszyć klauzule ELSIF i ELSE.

Pętle

Proste pętle w języku PL/SQL definiowane są przy użyciu słów kluczowych LOOP i END LOOP. Do zdefiniowania warunku decydującego o zakończeniu pętli służy wyrażenie EXIT WHEN, umieszczane wewnątrz ciała pętli. Przykładowa pętla znajduje się w listingu 17.7.
----------------------------------
Listing 17.7. Pętla LOOP

DECLARE
crow INT := 1;
BEGIN
LOOP
INSEKT INTO Test
VALUES (crow);
EXIT WHEN crow = 10;
#387
crow := crow + 1;
END LOOP;
END;
.
/
PL/SOL procedurę successfully completed.
----------------------------------

Pętla kończy się w chwili, kiedy warunek za wyrażeniem EXIT WHEN przyjmie wartość prawdziwą. W powyższym przykładzie instrukcja EXIT WHEN umieszczona została przed wyrażeniem zwiększającym wartość zmiennej, użytej w warunku pętli. Pętla zostaje wykonana dziesięć razy, po czym kończy się tuż przed zwiększeniem zmiennej crow do 11.

Iteracyjne przetwarzanie kursora

Pętle są często używane do iteracyjnego przetwarzania informacji zwracanych przez kursor. W procesie tym często stosowane są specjalne zmienne związane z kursorami, które ułatwiają kontrolowanie pętli (lub innych wyrażeń warunkowych, jak np. IF) w oparciu o dane pobierane z kursora. Lista tych zmiennych znajduje się w tabeli 14.1.
Do zbudowania pętli przetwarzającej wszystkie wiersze zwracane przez kursor, można posłużyć się atrybutem %NOTFOUND, umieszczając go jako warunek w wyrażeniu EXIT WHEN.
Dodatkowe informacje na temat iteracyjnego przetwarzania danych z kursora znaleźć można w rozdziale 14. (podpunkt "Iteracyjne przetwarzanie kursora").

Pętla FOR

Pętle tworzone przy użyciu wyrażenia LOOP są konstrukcjami elastycznymi, ponieważ koniec działania takiej pętli może zostać obłożony dowolnego typu warunkiem, natomiast polecenie przerywające jej działanie (EXIT WHEN) może zostać umieszczone w dowolnym miejscu ciała pętli. Przykładem takiego działania był listing 17.7, w którym polecenie EXIT WHEN zostało ustawione w taki sposób, aby wykonane zostały wszystkie wyrażenia pętli, oprócz ostatniego, inkrementującego wartość zmiennej pełniącej rolę licznika.
Pętle FOR mają bardziej uściśloną konstrukcję. Licznik wykonań pętli, będący jednocześnie warunkiem jej działania, umieszczany jest zawsze w tym samym miejscu, tzn. za słowem kluczowym FOR. Do wskazania liczby wykonań pętli FOR stosowany jest operator zakresu. Przykład pętli FOR znajduje się w listingu 17.8.
----------------------------------
Listing 17.8. Pętla FOR

DECLARE
crow INT;
BEGIN
FOR crow IN 1 .. 10 LOOP
INSEKT INTO Test
VALUES (crow);
END LOOP;
#388
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------

Jak łatwo można zauważyć, w pętli FOR nie występuje wyrażenie EXIT WHEN - nie jest ono potrzebne, ponieważ kryterium zakończenia pętli jest ustalane na samym jej początku. Przyjrzyjmy się strukturze pętli, pierwszym elementem za wyrażeniem FOR jest zmienna stanowiąca licznik pętli (tutaj crow). Specyfikacja wartości, jakie będą przypisywane zmiennej licznika, następuje za słowem kluczowym IN. W listingu 17.8, wartości te zostały zdefiniowane za pomocą operatora zakresu. Wyrażenie 1..10 oznacza "od 1 do 10." Licznik jest zwiększany o jeden przy każdym obiegu pętli, aż do momentu kiedy osiągnie wartość 10, po czym następuje ostatnie wykonanie pętli i przejście do dalszej części programu. Ciało pętli rozpoczyna się od słowa kluczowego LOOP.

Kursory w pętlach FOR

Pętla FOR nadaje się doskonale do przetwarzania danych kursora. Aby użyć kursora w pętli FOR, jego nazwę należy umieścić za słowem kluczowym IN w deklaracji pętli. Każda iteracja pętli powoduje przypisanie kolejnego wiersza kursora do zmiennej. Cały proces kończy się automatycznie, kiedy kursor zwróci wszystkie posiadane wiersze. Zakończenie pętli FOR powoduje ponadto zamknięcie kursora. Przykład pętli FOR przetwarzającej kursor znajduje się w listingu 17.9.
----------------------------------
Listing 17.9. Pętla FOR przetwarzająca kursor

DECLARE
profit NUMBER;
temp NUMBER;
CURSOR movies_cursor IS
SELECT movie_id, budget, gross
FROM Movies;
movies_rec movies_cursor%ROWTYPE;
BEGIN
FOR movies_rec IN movies_cursor LOOP
temp := movies_rec.gross - movies_rec.budget;
INSERT INTO Movie_Profit
VALUES {movies_rec.movie_id, temp);
END LOOP; END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------

Pętla WHILE

Pętla WHILE jest wykonywana tak długo, jak długo prawdziwy jest warunek umieszczony na jej początku. Podobnie jak w przypadku pętli FOR, struktura pętli WHILE jest sztywna i nie może być modyfikowana. Pętla WHILE inicjalizowana jest przy użyciu następującego wyrażenia:

WHILE warunek LOOP
#389
Pętla WHILE jest bardzo podobna do zwykłej pętli LOOP, z tą różnicą, że warunek jej działania nie jest określany wyrażeniem EXIT WHEN, lecz podawany w jej nagłówku. Podobnie, jak w przypadku zwykłej pętli LOOP, wartości występujące w warunku działania pętli WHILE, muszą być modyfikowane w jej wnętrzu, aby umożliwić jej ostateczne zakończenie.

Przykładowa pętla WHILE znajduje się w listingu 17.10.
----------------------------------
Listing 17.10. Pętla WHILE

DECLARE
crow INT := 1;
BEGIN
WHILE crow <= 10 LOOP
INSERT INTO Test
VALUES (crow);
crow := crow + 1;
END LOOP;
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------

Kursory w pętlach WHILE

Do przetworzenia wszystkich wierszy kursora w pętli WHILE używany jest atrybut o nazwie %FOUND. Atrybut ten przyjmuje wartość prawdziwą tak długo, jak długo kursor jest w stanie zwracać kolejne wiersze. Kiedy zasób wierszy kursora zostanie wyczerpany, atrybut %FOUND przyjmuje wartość fałszywą, powodując tym samym przerwanie pętli.
Przykład pętli WHILE korzystającej z atrybutu %FOUND znajduje się w listingu 17.1 1.
----------------------------------
Listing 17.11. Iteracja po wierszach kursora przy użyciu pętli WHILE

DECLARE
profit NUMBER;
CURSOR movies_crsr IS
SELECT movie_id, budget, gross
FROM Movies;
movies_rec movies_crsr%ROWTYPE;
BEGIN
OPEN movies_crsr;
WHILE raovies_crsr%FOUND LOOP
FETCH movies_crsr INTO movies_rec;
INSERT INTO Movie_Profit
VALUES (movies_rec.movie_id, (movies_rec. gross movies_rec. budget });
END LOOP;
END;
.
/
PL/SQL procedurę successfully completed.
----------------------------------
#390
GOTO

GOTO jest ostatnim z wyrażeń służących do kontrolowania przebiegu programu PL/SQL. Polecenie GOTO wymaga umieszczenia w kodzie etykiet, czyli celów, do których będą wykonywane skoki. Etykieta jest identyfikatorem ujętym w podwójne nawiasy:

�etkieta

Używając polecenia GOTO można przenieść punkt wykonania kodu do miejsca oznaczonego etykietą, w sposób następujący:

GOTO etykieta;

Mimo istnienia konstrukcji GOTO, nie zaleca się jej stosowania. Panuje słuszny pogląd, iż skoki GOTO zaciemniają obraz działania kodu i utrudniają jego analizę. Jeżeli jakieś zadanie zrealizowane zostało skokami GOTO, niemal na pewno można je rozwiązać o wiele lepiej, stosując wyrażenia warunkowe i pętle.

Pobieranie wartości do zmiennych

Wartości zwracane przez zapytanie mogą być umieszczane w zmiennych poprzez kursor (o czym była mowa w rozdziale 14. "Transakcje i kursory"). Inna metoda opiera się na wyrażeniu SELECT. Jeżeli wyrażenie tego typu zwraca pojedynczy wiersz, poszczególne jego pola można wstawić do zmiennych bez pomocy kursora, stosując konstrukcję SELECT INTO.
Wyrażenie SELECT INTO ma pewną przewagę nad kursorami, pod warunkiem, że zwraca faktycznie pojedynczy wiersz danych. Po pierwsze, wyrażenie SELECT nie musi być deklarowane w sekcji deklaracji bloku kodu PL/SQL.
Można je najzwyczajniej w świecie umieścić w sekcji wykonywalnej, co jest dobrym rozwiązaniem, jeżeli planujemy użycie dużej ilości zapytań w kodzie (w przeciwieństwie do kursorów, żadne z nich nie musi być wcześniej deklarowane).
Po drugie, wyrażenie SELECT INTO jest znacznie prostsze w użyciu niż kursory, o czym przekonasz się już za chwilę. W przypadku wyrażenia SELECT INTO nie stosuje się poleceń otwarcia, pobrania i zamknięcia. Aby pobrać liczbę filmów z tabeli Movies i umieścić ją w zmiennej movie_count wystarczy użyć następującego wyrażenia:

SELECT COUNTI*) INTO movie_count
FROM Movies

Oczywiście zmienna movie_count wymaga zadeklarowania, ale mimo wszystko samo polecenie wstawienia wartości do zmiennej jest o wiele prostsze w porównaniu z użyciem kursora.
Można również wydobyć kilka pól tabeli i umieścić je w strukturze podobnej do tej, jaka używana była w połączeniu z kursorami. W tym celu należy zadeklarować zmienną z wykorzystaniem konstrukcji %ROWTYPE:

movie rec Movies%ROWTYPE
#391
Teraz używając wyrażenia SELECT INTO można umieścić w zmiennej wartości wszystkich pól zwróconych przez zapytanie:

SELECT movie_id, movie_title, budget, gross
INTO movie_rec
FROM Movies
WHERE movie_id =1

W obu przykładach zapewnione zostały warunki umożliwiające zapytaniu zwrócenie wyłącznie jednego wiersza. W pierwszym przypadku użyta została funkcja agregująca zwracająca pojedynczą wartość, natomiast w drugim przykładzie, w klauzuli WHERE użyty został klucz główny, który ograniczył liczbę wierszy pobranych przez zapytanie do jednego.

Obsługa wyjątków

Trzecią główną częścią bloku kodu PL/SQL jest sekcja obsługi wyjątków. W przeciwieństwie do dwóch poprzednich, ta sekcja jest opcjonalna i jeśli występuje, to stanowi część sekcji wykonywalnej. Sekcja obsługi wyjątków nabiera znaczenia, jeżeli w trakcie wykonania programu PL/SQL wystąpi wyjątek systemowy lub zdefiniowany przez użytkownika. Pojawienie się wyjątku powoduje przeniesienie wykonania do tej sekcji, w celu znalezienia odpowiedniego fragmentu kodu, który będzie w stanie zareagować na tę wyjątkową sytuację.
Sekcja obsługi wyjątków wchodzi w skład sekcji wykonywalnej, dokładnie rzecz biorąc jako ostatnia jej część, tuż przed słowem kluczowym END:

DECLARE deklaracje
BEGIN
wyrażenia
EXCEPTION
kod obsługi wyjątków
END;

Sekcja obsługi wyjątków składa się z klauzul WHEN, z których każda przechowuje kod wykonywany w sytuacji wystąpienia określonego typu wyjątku. Oprócz tego można również zastosować wyrażenie WHEN OTHERS, będące odpowiednikiem klauzuli ELSE. Kod zawarty w klauzuli WHEN OTHERS obsługuje wyjątki, dla których nie zdefiniowano indywidualnych metod obsługi (w postaci klauzul WHEN).
Przyjrzyjmy się fragmentowi kodu obsługującego wyjątek. Oracle posiada szereg wyjątków systemowych (patrz dodatek A), wśród nich również INVALID_NUMBER, generowany w wypadku błędu niejawnej konwersji. Przykładowy kod znajduje się w listingu 17.12.
----------------------------------
Listing 17.12. Kod obsługujący wyjątek

DECLARE
crow INT;
bar VARCHAR(20);
#392
BEGIN
crow := 5;
bar := 'baz';
INSERT INTO Test
VALUES (crow + bar);
EXCEPTION
WHEN INVALID_NUMBER THEN
INSERT INTO Test
VALUES ('Błąd');
END;
.
/
PL/SOL procedurę suocessfully completed.

SELECT *
FROM Test

TEST
--------
Błąd
----------------------------------

Jak widać, zamiast sumy zmiennych crow i bar do tablicy wstawiony został łańcuch 'Błąd'. Próba zsumowania liczby 5 i wartości zmiennej bar, którą jest 'baz', powoduje wygenerowanie wyjątku niejawnej konwersji INVALID_NUMBER. Program przechodzi natychmiast do sekcji obsługi wyjątków. Tam typ wyjątku jest rozpoznawany przez odpowiednią sekcję (w przykładzie występuje tylko jedna), która wstawia do tablicy poprawną wartość.
OTHERS jest specjalnym identyfikatorem, odnoszącym się do wszystkich wyjątków, dla których nie została zdefiniowana indywidualna sekcja obsługi. Gdyby identyfikator INVALID_NUMBER w listingu 17.12 został zastąpiony przez OTHERS, ogólny efekt działania kodu byłby taki sam. Jedyna różnica polegałaby na tym, że sekcja OTHERS wyłapywałaby również inne wyjątki wygenerowane w czasie wykonania bloku kodu.

Wyjątki definiowane przez użytkownika

Język PL/SQL pozwala użytkownikom definiować własne wyjątki oraz komunikaty błędów wyświetlane w chwili powstawania wyjątków. Nowe wyjątki tworzone są w sekcji deklaracji. Deklaracja wyjątku przypomina deklarację zmiennej z typem danych EXCEPTION:

DECLARE nazwa_wyjatku EXCEPTION;

Tak zdefiniowany wyjątek musi zostać odpowiednio zaprogramowany w sekcji obsługi wyjątków. Do generowania wyjątków służy polecenie RAISE, jego składnia wygląda następująco:

RAISE nazwa_wyjątku;

Polecenie RAISE jest na ogół stosowane wewnątrz instrukcji warunkowej.

PL/SQL udostępnia specjalną procedurę wbudowaną, RAISE_APPLICATION_ERROR, która definiuje komunikat błędu prezentowany użytkownikowi w chwili powstania wyjątku oraz przerywa zapytanie ze specyficznym kodem błędu, zdefiniowanym przez programistę.
#393
Wywołanie procedury RAISE_APPLICATION_ERROR następuje wewnątrz klauzuli WHEN wyjątku, do którego ma być dołączony komunikat oraz kod błędu:

WHEN nazwa_wyjątku THEN
RAISE_APPLICATION_ERROR (kod_błedu, 'komunikat błędu');

Komunikat błędu jest zwykłym łańcuchem, natomiast kod błędu jest liczbą z przedziału od -20001 do -20999. Przyjrzyj się fragmentowi kodu, w którym zastosowane zostały wymienione wyżej reguły. W przykładzie z listingu 17.13 zdefiniowany został wyjątek użytkownika, a także procedura jego obsługi oraz fragment kodu, generujący ten wyjątek. ----------------------------------
Listing 17.13. Wyjątek użytkownika oraz procedura jego obsługi

DECLARE . crow INT;
number_too_large EKCEPTION;
BEGIN
FOR crow IN 1..100 LOOP
IF crow = 99 THEN
RAISE number_too_large;
END IF;
END LOOP;
EKCEPTION
WHEN number_too_large THEN
RAISE_APPLICATION_ERROR (-20001, 'Liczba jest zbyt duża.');
END;
.
/
ERROR at linę 1:
ORA-20001: Liczba jest zbyt duża.
ORA-06512: at linę 12
----------------------------------

Tworzenie procedur składowanych

Procedury składowane to nic innego, jak blok kodu PL/SQL, składający się z omawianych wcześniej sekcji i zaopatrzony w specjalny nagłówek.
==============
Rada
Procedury składowane mogą być tworzone jedynie przez użytkowników posiadających przywilej CREATE PROCEDURĘ lub należących do grupy RESOURCE. Przywilej CREATE ANY PROCEDURĘ umożliwia tworzenie procedur w dowolnym schemacie. O tym, jak nadać tego typu przywileje użytkownikom można się dowiedzieć z rozdziału 11.
====================

W bazie danych Oracle możliwe jest tworzenie trzech różnych typów procedur składowanych. Najprostszą konstrukcją jest zwykła procedura, wywofywana w sposób jawny i nie zwracająca żadnej wartości w sposób bezpośredni. Drugim typem jest funkcja, która różni się tym od zwykłej procedury, że po wywołaniu zwraca wartość w sposób bezpośredni.
#394
Zarówno procedury, jak i funkcje mogą wymieniać dane poprzez specjalne zmienne deklarowane w chwili definiowania procedury (parametry). Trzecim rodzajem procedury jest wyzwalacz, uruchamiany automatycznie w wyniku zaistnienia określonego zdarzenia.
Oprócz wymienionych typów procedur składowanych istnieją również pakiety, stanowiące pomoc w budowie aplikacji baz danych. Pakiet jest specjalnym pojemnikiem umożliwiającym pisanie użytkownikom wyrażeń SQL, procedur oraz funkcji korzystających ze wspólnego zestawu zmiennych i kursorów. Pakiety oferują środki umożliwiające organizowanie w sposób logiczny grup procedur składowanych w celu współdzielenia danych oraz łatwiejszego zarządzania nimi. Sposób tworzenia pakietów zostanie omówiony w dalszej części tego rozdziału.

Tworzenie procedur

Procedury są tworzone przy użyciu wyrażenia CREATE PROCEDURĘ. Jeżeli tworzona procedura ma zastąpić już istniejącą, trzeba posłużyć się wyrażeniem CREATE OR REPLACE PROCEDURĘ. Następnymi elementami definicji procedury są: jej nazwa oraz lista zmiennych wejściowych i wyjściowych używanych przez procedurę. Właściwy kod procedury umieszczany jest na samym końcu.
Format polecenia CREATE PROCE DURE jest następujący:

CREATE [OR REPLACE] PROCEDURĘ nazwa_procedury
[{
parametr (IN | OUT | IN OUT] typ_danych
[, parametr (IN | OUT | IN OUT] typ_danych, ...]
}]
AS blok_poleceń

Parametry procedury mogą zostać zdefiniowane na trzy różne sposoby: IN, OUT lub IN OUT. Parametrom IN są przypisywane wartości przekazywane do procedury przez wywołujące ją zapytanie. Parametry OUT przekazują dane z powrotem do zapytania. Parametry IN OUT służą zarówno do przyjmowania danych, a także do zwracania ich zapytaniu wywołującemu procedurę. Wywołując procedurę z wnętrza zapytania trzeba ściśle przestrzegać porządku parametrów przewidzianych w jej definicji. Listing 17.14 definiuje procedurę na podstawie kodu z listingu 17.1.
----------------------------------
Listing 17.14. Tworzenie nowej procedury

CREATE OR REPLACE PROCEDURE calculate_profit
{
gorss IN NUMBER,
budget IN NUMBER,
progit OUT NUMBER,
movie_id IN OUT INT(6)
)
AS
BEGIN
profit : = budget - gross;
INSERT INTO Movie_Profit VALUES (movie_id, profit);
END;
----------------------------------
#395
Uruchamianie procedur składowanych

Do uruchomienia procedury składowanej służy wyrażenie EKECUTE, wymagające nazwy procedury oraz ujętej w nawiasy listy jej parametrów. Aby uruchomić procedurę stworzoną w listingu 17.14 należy posłużyć się następującym poleceniem:

EKECUTE calculate_profit (40, 25, 1);

Jak widać, do procedury przekazywane są trzy parametry, mimo że wyrażeniu CREATE PROCEDURĘ w listingu 17.14 towarzyszą cztery definicje zmiennych. Wyjaśnienie jest bardzo proste - do procedury przekazywane są tylko parametry zdefiniowane jako IN lub IN OUT. Jeżeli chcemy pominąć którąkolwiek z wartości przekazywanych do procedury (przy założeniu, że nie jest ona wymagana), wstawiamy w jej miejsce wartość NULL:

EXECUTE calculate_profit (NULL, 25, 1);

Procedura calculate_prof it nie może działać z pustą wartością parametru gross, są jednak takie sytuacje, w których przekazanie wartości NULL okazuje się rozwiązaniem bardzo korzystnym.
Oprócz własnych procedur, można również wykonywać procedury innych użytkowników. Notacja umożliwiająca wywoływanie cudzych procedur jest taka sama, jak w przypadku odwoływania się do tabel innych użytkowników. Przed nazwę procedury należy wstawić nazwę jej właściciela oraz kropkę. Jeżeli zatem chcielibyśmy wywołać procedurę calculate_prof it, będącą własnością użytkownika o identyfikatorze patryc j a, musielibyśmy użyć następującej składni:

EXECUTE patrycja.calculate_profit(40, 25, 1);

Procedurę można wywołać z innej bazy danych dołączając jej nazwę za nazwą procedury (separatorem obu nazw jest znak @). Jeżeli procedura calculate_prof it znajdowałby się w bazie danych o nazwie finanse, do jej wywołania należałoby użyć polecenia:

EXECUTE calculate_profit@finanse(40, 25, 1);

W obu przypadkach użytkownik wywołujący procedurę musi posiadać do tego celu odpowiednie przywileje. Aby móc wywołać procedurę, należy posiadać przywilej EXECUTE dla tej procedury. Nie jest wymagane prawo dostępu do obiektów schematu, do których odwołuje się kod wewnątrz procedury. Jeżeli użytkownik nie posiada uprawnień do modyfikowania wierszy tabeli, ale posiada prawo uruchamiania procedury, która wykonuje takie zadanie, może uaktualnić tabelę poprzez wywołanie procedury. Jak widać, przy użyciu procedur oraz modelu bezpieczeństwa języka SQL można w bardziej restrykcyjny sposób wpływać na prawa użytkowników do modyfikowania i przeglądania tabel, niż w przypadku zwykłego mechanizmu praw dostępu oraz widoków.
Synonimy Oracle'a, omawiane szczegółowo w rozdziale 18., umożliwiają tworzenie odwołań do obiektów należących do innych użytkowników lub przechowywanych w innych bazach danych.
#396
Odwołanie takie tworzone jest przy użyciu polecenia CREATE SYNONYM. Po utworzeniu synonimu procedury, użytkownicy korzystający z bieżącego schematu bazy mogą wywołać ją, tak jakby była ona lokalnym obiektem tego schematu. Składnia polecenia CREATE SYNONYM wygląda następująco:

CREATE SYNONYM oalculate_profit
FOR patrycja.calculate_profit


Tworzenie i stosowanie funkcji

Funkcja jest szczególną odmianą procedury składowanej, która może zwracać pojedynczą wartość, niezależnie do wszelkich innych wartości przekazywanych przez zmienne OUT i IN OUT. Główną zaletą funkcji jest możliwość stosowania ich niemal wszędzie tam, gdzie dopuszczalne jest użycie funkcji wbudowanych języka PL/SQL. Tak jak można używać funkcji UPPERO i TRIM() w liście argumentów instrukcji SELECT, tak samo można używać tam funkcji stworzonych samodzielnie.
Zacznijmy od stworzenia funkcji, która przyjmuje argument w postaci identyfikatora filmu i zwraca jego dochód. Funkcja tworzona jest przez wyrażenie CREATE FUNCTION, bardzo podobne do CREATE PROCEDURĘ, z jedną różnicą polegającą na konieczności użycia klauzuli RETURN w celu określenia wartości zwracanej przez funkcję. Wyrażenie tworzące opisaną funkcję znajduje się w listingu 17.15.
----------------------------------
Listing 17.15. Funkcja zdefiniowana przez użytkownika

CREATE OR REPLACE FUNCTION get_proft
(in_movie_id IN INT)
RETURN NUMBER
IS profit NUMBER;
BEGIN
SELECT gross - budget INTO profit
WHERE movie_id = in_movie_id;
RETURN (profit);
END;
----------------------------------

Typ wartości zwracanej przez funkcję określa klauzula RETURN NUMBER, natomiast zmienna poprzez którą zwracane będą dane umieszczana jest za klauzulą is. W ciele funkcji wyrażenie SELECT oblicza dochód filmu wyspecyfikowanego poprzez parametr in_movie_id i umieszcza go w zmiennej profit. Faktyczne zwrócenie wartości przez funkcję następuje w miejscu wywołania polecenia RETURN (profit).
Po utworzeniu funkcji, można korzystać z niej we wszystkich własnych zapytaniach. Poniższe zapytanie (listing 17.16) na przykład wybiera z bazy tytuły wszystkich filmów i oblicza dochód każdego z nich, używając do tego celu funkcji get_prof it.
----------------------------------
Listing 17.16. Zapytanie wywołujące funkcję zdefiniowaną przez użytkownika

SELECT movie_title, get_profit(movie_id)
FROM Movies
----------------------------------
#397
Dochód każdego filmu jest obliczany na bieżąco i dołączany do wyników generowanych przez zapytanie.

Umieszczanie procedur i funkcji w pakietach

Pakiety są jedną z cech języka PL/SQL, która ma za zadanie ułatwiać budowę aplikacji o dużym stopniu złożoności. Pakiety są grupami funkcji, procedur oraz współdzielonego przez nie dodatkowego kodu.
Stosowanie pakietów przynosi znaczące korzyści. Po pierwsze, różne procedury i funkcje należące do tego samego pakietu mogą korzystać ze wspólnego zestawu zmiennych i kursorów. Druga zaleta, to możliwość deklarowania funkcji i procedur jako publicznych lub prywatnych. Prywatne funkcje i procedury mogą być wywoływane wyłącznie z wnętrza pakietu, w którym się znajdują, podczas gdy procedury i funkcje publiczne są dostępne również poza nim. Pakiety mogą być wyposażane w dodatkowy fragment kodu, uruchamiany za każdym razem, gdy wywoływana jest jedna z jego procedur lub funkcji, dzięki czemu programista unika konieczności powielania kodu inicjalizującego w każdej z procedur.
Pakiet składa się z dwóch części: specyfikacji oraz ciała. Specyfikacja pakietu jest listą wszystkich zawartych w nim obiektów publicznych. Każdy obiekt występujący w pakiecie, ale nie uwzględniony na liście jego specyfikacji, jest dostępny wyłącznie dla innych członków tego pakietu. W ciele pakietu znajdują się rzeczywiste implementacje wszystkich procedur i funkcji (publicznych i prywatnych), kod inicjalizacji pakietu, uruchamiany przy każdym odwołaniu do niego, a także deklaracje wszelkich zmiennych i kursorów tego pakietu. Specyfikacja pakietu i jego ciała tworzone są niezależnie, przy użyciu wyrażeń CREATE PACKAGE oraz CREATE PACKAGE BODY.

Specyfikacja pakietu

Załóżmy, że chcemy stworzyć pakiet wspomagający zapytania związane z dochodami filmów. Wewnątrz tego pakietu umieścimy funkcję zwracającą dochód filmu
0 podanym identyfikatorze oraz procedurę wpisującą do odpowiedniej tablicy dochód studia filmowego w chwili jej wywołania (procedura taka może być wywoływana np. co tydzień, aby tworzyć historyczny rejestr dochodów dla każdego studia). Pierwszym krokiem do utworzenia pakietu jest zdefiniowanie jego procedur i funkcji w jego specyfikacji.
Na razie w pakiecie znajdą się jedynie dwa obiekty: funkcja get_prof it, zwracająca dochód filmu o podanym identyfikatorze oraz procedura store_studio_profit, tworząca historię dochodów w tabeli wymagającej oddzielnego zdefiniowania. Pakiet będzie nosił nazwę movie_prof it. Odwołania do obiektów wewnątrz pakietu tworzone są przez złożenie nazwy pakietu i nazwy obiektu z kropką jako separatorem.
#398
Ogólna składnia wyrażenia CREATE PACKAGE wygląda następująco:

CREATE [OR REPLACE] PACKAGE [użytkownik.]nazwa_pakietu
{ IS | AS }
specyfikacja_pakietu
END nazwa_pakietu;

Specyfikacja zawiera listę wszystkich publicznych funkcji i procedur znajdujących się w pakiecie. Każda pozycja specyfikacji pakietu składa się z typu tworzonego obiektu, jego nazwy oraz parametrów (wejściowych i wyjściowych). Specyfikacja pakietu movie_profit znajduje się w listingu 17.17.
----------------------------------
Listing 17.17. Specyfikacja pakietu mavie_profit

CREATE OR REPLACE PACKAGE movie_profit
AS
FUNCTION get_profit
(movie_id IN INT);
PROCEDURĘ store_studio_profit
(studio IN NUMBER,
profit IN NUMBER);
END movie_profit;
----------------------------------

Wyrażenie z listingu 17.17 tworzy nowy (lub zastępuje istniejący) pakiet o nazwie movie_prof it, składający się z dwóch obiektów get_prof it i store_studio_ profit. Porównując listing 17.17 z listingiem 17.15 można zauważyć, że definicje zmiennej dla funkcji get_prof it w obu przypadkach sąjednakowe.
Miejscem, w którym umieszczane są implementacje procedur i funkcji oraz inne fragmenty kodu, jest ciało pakietu, tworzone wyrażeniem CREATE PACKAGE BODY. W listingu 17.18 znajduje się wyrażenie tego typu, tworzące ciało pakietu movie_profit.
----------------------------------
Listing 17.18. Ciato pakietu movie_profit

CREATE OR REPLACE PACKAGE BODY movie_profit
AS
FUNCTION get_profit
(in_movie_id IN INT)
RETURN NUMBER IS profit NUMBER;
BEGIN
SELECT gross - budget INTO profit
WHERE movie_id = in_movie_id;
RETURN (profit);
END get_profit;
PROCEDURE store_studio_profit
(studio IN NUMBER,
profit IN NUMBER)
AS
BEGIN
INSERT INTO studio_profit
(studio_id, date_of_entry, studio_profit)
VALUES
(studio, SYSDATE, profit);
END store_studio__profit;
END movie_profit;
----------------------------------
#399
Ciało pakietu zawiera kod źródłowy dwóch obiektów wymienionych w specyfikacji. Lista zmiennych wejściowych i wyjściowych obu obiektów jest identyczna z listą użytą w wyrażeniu CREATE PACKAGE. W specyfikacji pakietu można również umieścić kod inicjalizacji, który zostanie wykonany przy pierwszym uruchomieniu jakiegokolwiek obiektu pakietu. Zadaniem takiego kodu jest na ogół deklarowanie kursorów lub zmiennych używanych przez więcej niż jeden obiekt pakietu.
Testowanie programów PL/SQL
Główny problem z wykrywaniem błędów w kodzie PL/SQL wynika z faktu, iż język ten nie został zaprojektowany do generowania wyników w tradycyjnym tego słowa znaczeniu. Większość języków posiada wbudowane polecenia pozwalające im wysyłać generowane wyniki na konsolę, do okna lub jakiegokolwiek innego środowiska, w którym pracują. PL/SQL nie posiada tego typu wyrażeń, ponieważ jest językiem nastawionym na manipulowanie danymi wewnątrz bazy danych lub przekazywanie ich do użytku innym aplikacjom. Niemniej jednak, istnieją dwa elementy wspomagające testowanie kodu PL/SQL. Pierwszym z nich jest polecenie SHOW ERRORS, wyświetlające wszystkie błędy powstałe w czasie kompilacji zapytania. Drugim elementem jest pakiet DBMS_OUTPUT, który umożliwia stosowanie poleceń wyświetlających dane wyjściowe na konsoli.

SHOW ERRORS

Polecenie SHOW ERRORS wyświetla listę błędów napotkanych w kodzie PL/SQL. Jeżeli uruchomione zostało wyrażenie tworzące procedurę, funkcję lub pakiet, w którego kodzie znajdują się błędy, program SQL*Plus wyświetla następujący komunikat:

Warning: Function created with compilation errors.[ Ostrzeżenie: tworzona funkcja zawiera błędy kompilacji.]

Po otrzymaniu tego komunikatu można użyć polecenia SHOW ERRORS, aby obejrzeć błędy kompilacji. Poniżej przedstawiony został przykładowy wynik uruchomienia polecenia SHOW ERRORS dla funkcji GET_PROFIT z listingu 17.15:

Errors for FUNCTION GET_PROFIT:

LINE/COL ERROR
----------------------------------
7/1 PLS-00103: Encountered the symbol "WHERE" when expectlng one of the following:
( , % from

8/1 PLS-00103: Encountered the symbol "RETURN"
9/1 PLS-00103: Encountered the symbol "END"
#400
DBMS OUTPUT

Pakiet DBMS_OUTPUT udostępnia dwa polecenia służące do wyświetlania danych wyjściowych na konsoli. Aby móc oglądać dane wyjściowe trzeba włączyć opcję SERVEROUTPUT, używając do tego celu komendy:

SET SERVEROUTPUT ON

Funkcja PUT pakietu DBMS_OUTPUT wyświetla łańcuch przekazany jej jako argument. Jeżeli chcemy, aby po wyświetleniu łańcucha nastąpiło przejście do nowego wiersza, zamiast PUT należy użyć funkcji PUT_LINE. Wysłanie samego znaku powrotu kursora umożliwia PUT w połączeniu ze stałą NEW_LINE.
Zapytanie z listingu 17.19 pobiera kolejne wiersze z kursora movies_curosr i wyświetla je na ekranie przy użyciu polecenia PUT_LINE.
---------------------------
Listing 17.19. Wyświetlanie wyników zapytania przy użyciu funkcji PUT_LINE

DECLARE
CURSOR movies_cursor IS
SELECT movie_title
FROM Movies
WHERE studio_id - 1;
movie_rec movies_cursor%ROWTYPE;
BEGIN
WHILE movies_cursor%FOUND LOOP
FETCH movies_cursor INTO movie_rec;
PUT_LINE (movie_rec.movie_title) ;
END LOOP;
END;
.
/
Vegetable House
Prince Kong
The Code Warrior
Bili Durham
Codependence Day
The Linux Files
SQL Strikes Back
The Programmer
Hard Cose
The Rear Windows
Test Movie
---------------------------

Wyzwalacze

Wyzwalacz jest specyficzną formą procedury składowanej, która wykonywana jest w odpowiedzi na wystąpienie określonego typu zdarzenia. Istnieją dwie kategorie wy-zwalaczy: działające na poziomie wierszy i na poziomie wyrażeń. Pierwsze z nich uruchamiane są dla każdego wiersza transakcji. Oznacza to, że dla polecenia UPDATE uaktualniającego dziesięć wierszy, wyzwalacz uruchomiony zostanie również dziesięć razy (po jednym razie dla każdego modyfikowanego wiersza). Wyzwalacze działające na poziomie wyrażeń wywoływane są tylko raz dla każdego wyrażenia.
#401
Jeżeli to samo wyrażenie UPDATE zostanie wykonane i uruchomi wyzwalacz poziomu wyrażenia, wyzwalacz wywołany zostanie tylko raz.
Wyzwalacze przydzielane są trzem wyrażeniom modyfikacji danych: UPDATE, DE-LETE i INSEKT. Dodatkowo można również określić, kiedy dokładnie wyzwalacz ma być uruchomiony - przed zdarzeniem (BEFORE), po zdarzeniu (AFTER) lub zamiast zdarzenia (INSTEAD OF), z którym został związany. Mówiąc inaczej, istnieje możliwość stworzenia czternastu różnych wyzwalaczy:

BEFORE UPDATE wiersz
BEFORE UPDATE wyrażenie
BEFORE DELETE wiersz BEFORE DELETE wyrażenie
BEFORE INSEKT wiersz
BEFORE INSERT wyrażenie
AFTER UPDATE wiersz
AFTER UPDATE wyrażenie
AFTER DELETE wiersz
AFTER DELETE wyrażenie
AFTER INSERT wiersz
AFTER INSERT wyrażenie
INSTEAD OF wiersz
INSTEAD OF wyrażenie

Wyzwalacze tworzone są przy użyciu następującej składni:

CREATE (OR REPLACE) TRIGGER nazwa_wyzwalacza
{ BEFORE l AFTER l INSTEAD OF }
( DELETE | INSERT | UPDATE [OF kolumna [, kolumna] ..) }
[OR dodatkowe zdarzenie]
ON [użytkownik.](tabela | widok)
[REFERENCING {new [AS] new | old [AS] old) ... ]
[FOR EACH (RÓW l STATEMENT)]
[WHEN (warunek)]
kod PL/SQL
---------------------------

Jak widać budowa wyzwalacza jest dosyć złożona. Omówię ją po kolei. W pierwszym wierszu wyrażenia CREATE TRIGGER należy określić nazwę wyzwalacza. Druga sekcja specyfikuje zdarzenie lub zdarzenia, których wystąpienie będzie uruchamiać wyzwalacz. Każdy wyzwalacz może być wywoływany dowolną kombinacją zdarzeń.
Klauzula ON wiąże wyzwalacz z określoną tabelą lub widokiem. Jeżeli tabela lub widok jest częścią schematu innego użytkownika, jego nazwę trzeba umieścić przed nazwą tablicy lub widoku (stosując separator w postaci kropki). W wyzwalaczu dostępne są dwa identyfikatory new i old, pozwalające odwoływać się do nowych wartości umieszczanych w bazie lub do wartości, które są usuwane lub zastępowane. Nazwy new i old można zmienić przy użyciu klauzuli REFERENCING.
Klauzula FOR EACH określa czy wyzwalacz działa na poziomie wierszy, czy też na poziomie wyrażeń. Domyślnie wyzwalacz działa na poziomie wyrażeń. Utworzenie wyzwalacza działającego na poziomie wierszy wymaga podania parametru FOR EACH ROW.
Ostatnia klauzula WHEN ogranicza zasięg działania wyzwalacza. Jej budowa przypomina klauzulę WHERE zwykłego zapytania. Wyzwalacz przetwarza jedynie te
#402
wiersze, które spełniają kryterium określone przez warunek w klauzuli WHEN, natomiast pozostałe są ignorowane.
Poniżej (listing 17.20) znajduje się przykład wyzwalacza, który uniemożliwia wstawienie do tabeli filmu o budżecie mniejszym niż 10 milionów dolarów. Niemal cały kod wyzwalacza zawarty jest w klauzulach poprzedzających blok PL/SQL stanowiący jego ciało. Ponieważ wiersze spełniające warunek wstawienia są z góry odrzucane przez klauzulę WHEN, rola ciała wyzwalacza ogranicza się jedynie do wygenerowania wyjątku.
---------------------------
Listing 17.20. Przykładowy wyzwalacz

CREATE OR REPLACE TRIGGER movies_insert
BEFORE INSERT ON Movies
FOR EACH RÓW WHEN (new.budget < 10)
DECLARE
budget_too_low EXCEPTION;
BEGIN
RAISE budget_too_low;
EXCEPTION
WHEN budget_too_low THEN
RAISE_APPLICATION_ERROR (-20001,
'Budżet filmu jest zbyt niski.');
END;

Trigger created.
---------------------------

Po stworzeniu wyzwalacza można przetestować jego działanie. Zapytanie w listingu 17.21 wstawia wiersz z budżetem filmu powyżej 10 milionów, natomiast zapytanie z listingu 17.22 usiłuje wstawić rekord z budżetem poniżej wartości akceptowanej przez wyzwałacz.
---------------------------
Listing 17.21. Instrukcja INSERT, która zostanie zaakceptowana przez wyzwalacz

INSERT INTO Movies
(movie_id, movie_title, studio_id, director_id, budget, gross, release_date)
VALUES
(15, 'Test', 1, 2, 15, 15, '10-May-00');

1 row created.
---------------------------
---------------------------
Listing 17.22. Instrukcja INSERT, która nie zostanie zaakceptowana przez wyzwalacz

INSERT INTO Movies
(movie_id, movie_title, studio_id, director_id, budget, gross, release_date)
VALUES
(16, 'Test', 1, 2, 5, 5, '10-May-00');

ERROR at linę 1:
ORA-20001: Budżet filmu jest zbyt niski.
ORA-06512: at "DEVELOPMENT.MOVIES_INSERT", linę 7
ORA-04088: error during execution of trigger 'DEVELOPMENT.MOVIES_INSERT'
---------------------------

Wyzwalacz movies_insert działa na poziomie wierszy i jest uruchamiany tylko, jeśli wartość kolumny budget jest mniejsza od 10. Jego uruchomienie następuje
#403
przed wstawieniem rekordów do tabeli Movies. Jedyną rzeczą faktycznie wykonywaną przez wyzwalacz, jest wygenerowanie wyjątku zdefiniowanego przez programistę.
===============
Rada
Chcąc odwołać się do jednej z wartości old lub new w ciele wyzwalacza, trzeba poprzedzić odpowiedni kwalifikator dwukropkiem. Tak więc, odwołując się do starej wartości pola budget należy użyć notacji:
:old.budget
Podobnie w przypadku nowej wartości tego pola:
:new.budget
===============

Po uruchomieniu wyzwalacza dostępne stają się dwa kwalifikatory: old i new (ich nazwy można zmienić stosując klauzulę REFERENCING wyrażenia CREATE TRI-GGER). Poprzedzając nazwę kolumny kwalifikatorem new można odwołać się do wartości, która zostanie wstawiona lub zastąpi wartość istniejącą w tabeli.
Analogicznie, używając kwalifikatora old, można dotrzeć do wartości usuniętej lub zastąpionej przez zapytanie. W wyzwalaczu z listingu 17.20 użyte zostało odwołanie do wstawianej wartości polabudet (new.budget).

Wyzwalacze INSTEAD OF

Wyzwalacz INSTEAD OF przerywa działanie wyrażenia, które go wywołało i zamiast niego wykonuje kod zawarty w swoim ciele. Dzięki temu możliwe jest zastępowanie wyrażeń, które z jakiegoś powodu nie powinny zadziałać, kodem faktycznie realizującym zadanie będące pierwotną intencją użytkownika. Na przykład, jeżeli dysponujemy widokiem niezdolnym do uaktualnienia, możemy napisać wyzwalacz, który zablokuje wyrażenia z góry skazane na porażkę i zamiast nich uruchomi zapytania modyfikujące tabele będące źródłem tego widoku.
============
Uwaga
Wyzwalacze INSTEAD OF mogą być tworzone tylko dla widoków i są dostępne dopiero w wersji 8 Oracle.
============

Do pokazania przykładu wyzwalacza INSTEAD OF potrzebny jest widok. Będzie nim lista ról z filmu "The Code Warrior" - listing 17.23.
---------------------------
Listing 17.23. Niemodyfikowalny widok

CREATE VIEW Movie_Roles_Test (movie_id, title, role)
AS
SELECT Movies.movie_id, movie_title, role
FROM Movies, Cast_Movies
WHERE Movies.movie_id = 3
AND Movies.movie id = Cast Movies .movie id
---------------------------
#404
W normalnych warunkach powyższy widok były niemodyfikowalny, ponieważ został stworzony przez zapytanie, które używa operacji złączenia. Problem ten można jednak obejść, pisząc odpowiedni wyzwalacz INSTEAD OF, który umożliwi użytkownikom wprowadzanie zmian do widoku - listing 17.24.
---------------------------
Listing 17.24. Wyzwalacz umożliwiający użytkownikom wprowadzanie zmian do niemodyfikowalnego widoku

CREATE OR REPLACE TRIGGER movie_roles_update
INSTEAD OF UPDATE ON Movies_Roles_Test
FOR EACH ROW
BEGIN
UPDATE Movies
SET title = :new.movie_id;
UPDATE Cast_Movies
SET role = rnew.role
WHERE movie_id =: new.movie_id
AND role =:old.role;
END;
---------------------------

Po uruchomieniu wyrażenia UPDATE w odniesieniu do widoku, zostaje ono zablokowane przez wyzwalacz, który pobiera z zapytania niezbędne wartości i samodzielnie modyfikuje odpowiednie tablice.
Wyzwalacze INSTEAD OF są cechą opcjonalną. Aby móc z nich skorzystać, trzeba poprosić administratora o ich udostępnienie.

Zarządzanie wyzwalaczami

Do usuwania wyzwalaczy służy polecenie DROP TRIGGER:

DROP TRIGGER nazwa_trigera;

Wyzwalacz może zostać wyłączony z użytku bez konieczności jego usuwania, przy użyciu komendy ALTER TRIGGER:

ALTER TRIGGER nazwa_wyzwalacza DISABLE;

Aby ponownie przywrócić wyzwalacz do użytku, parametr DISABLE należy zastąpić przez ENABLE:

ALTER TRIGGER nazwa_wyzwalacza ENABLE;

Używając polecenia ALTER TABLE można zablokować lub udostępnić wszystkie wyzwalacze związane z określoną tabelą:

ALTER TABLE nazwa_tabeli DISABLE ALL TRIGGERS;
ALTER TABLE nazwa tabeli ENABLE ALL TRIGGERS;
#405
W praktyce

Na koniec wrócimy jeszcze do tematu pakietów, aby wskazać ich kolejne zalety, widoczne szczególnie podczas pracy nad dużymi projektami. Jak wiadomo, pakiety grupują funkcje i procedury, pozwalając im na korzystanie z tego samego kodu. Być może jeszcze istotniejszą cechą pakietów jest to, że umożliwiają one utrzymywanie kodu w pewnej usystematyzowanej formie.
Większość dużych projektów składa się z określonej liczby samodzielnych komponentów, z których każdy posiada własne funkcje oraz procedury. Już na pierwszy rzut oka widać, że stworzenie pakietów dla poszczególnych komponentów znacznie ułatwi zarządzanie całym projektem.
Posłużymy się ponownie przykładem z komercyjną witryną WWW, dla której wszystkie reguły biznesowe zaimplementowane zostały w postaci procedur składowanych bazy danych. Strona składa się z kilku komponentów: katalogu towarów, modułu zarządzającego księgowością, modułu kart zakupu i samego systemu zakupów. Dla każdego z tych komponentów można stworzyć samodzielne moduły. Moduł związany z katalogiem towarów składałby się z procedur realizujących następujące zadania:
* Pobieranie listy towarów z bazy danych.
* Pobieranie szczegółowej informacji na temat danego produktu.
* Pobieranie informacji o dostępności produktu.
W module systemu zakupów znalazłoby się trochę więcej procedur, przeznaczonych do następujących operacji:
* Utworzenia zamówienia na podstawie towarów zaznaczonych w karcie zakupów.
* Weryfikacji metody zapłaty oraz formy dostarczenia towaru.
* Dodania informacji o formie dostarczenia towaru do rachunku.
* Dodania informacji o formie płatności do rachunku.
* Dodania do rachunku podatku oraz opłaty za przesyłkę.
* Przekazania zamówienia do realizacji.
Zawartość pozostałych pakietów związana byłaby ze sposobem realizacji samych modułów. Nad każdym z modułów mógłby pracować niezależny zespół. Ludzie pracujący nad samą witryną musieliby znać jedynie nazwy procedur i ich argumenty (czyli informacji, które można znaleźć w specyfikacji pakietu). Przy takiej organizacji pracy, każdy z zespołów ludzi zajmowałby się wyłącznie określonym wycinkiem całego projektu, bez potrzeby zaglądania w kod pakietów nie będących przedmiotem ich pracy.
Tworzenie pakietów pozwala również na wielokrotne stosowanie tego samego kodu. Na przykład wszystkie procedury modułu kart zakupu mogłyby korzystać z tego samego kursora tworzącego listę produktów wybranych przez użytkownika strony.
#406
Jeżeli procedury nie byłyby częścią jednego pakietu, kursor musiałby być inicjali-zowany w każdej z procedur oddzielnie. Pakiet umożliwia zadeklarowanie kursora na samym początku, a następnie użycie go przez każdą z jego procedur. Pakiety korzystające ze wspólnego kodu (jakim jest np. kursor) powinny być grupowane razem, aby zminimalizować przypadki duplikowania kodu.
Jeżeli pracujesz nad dużym projektem aplikacji związanym z wykorzystaniem procedur składowanych bazy danych, skorzystaj z pakietów, aby odpowiednio zorganizować cały tworzony kod, a przez to ułatwić zarządzanie nim. Jeśli tylko tworzona aplikacja wymaga więcej niż kilku procedur, mocno polecam użycie pakietów.