#335
Część V
Procedury składowane

Rozdział 15.
Procedury składowane

O procedurach składowanych była już mowa przy okazji omawiania kursorów. W tym rozdziale przedstawione zostaną zasady ich tworzenia oraz zadania, do realizacji których wykorzystać można procedury składowane.
Procedury składowane, chociaż są dostępne w wielu bazach danych, nie należą tak naprawdę do standardu języka SQL. Procedury składowane w Oracle'u nie są pisane przy użyciu języka SQL, lecz PL/SQL. Bazy danych Sybase i MS SQL Server umożliwiają pisanie procedur w języku Transact-SQL. Ze względu na brak specyfikacji procedur w standardzie SQL, ich implementacje różnią się między sobą w zależności od typu bazy danych. Przeniesienie procedury składowanej z Oracle'a do Sybase Ad-aptive Servera jest równoznaczne z napisaniem jej od podstaw.
Mimo widocznych różnic między bazami danych, filozofia tworzenia procedur składowanych jest w miarę jednolita. Procedury składowane mają za zadanie umożliwienie programistom tworzenia złożonych programów wykonywanych przez bazę danych. Otrzymują oni w ten sposób do rąk kompletny proceduralny język programowania bazy danych. Sama baza przestaje być zwykłym repozytorium danych i może być wyposażana w reguły biznesowe, które wcześniej implementowane były wyłącznie po stronie aplikacji.
Użycie procedur składowanych wiąże się z kilkoma korzyściami. Po pierwsze, dzięki nim baza może udostępniać nie tylko dane, ale także rządzące nimi reguły. Dla przykładu załóżmy, że prowadzimy księgarnię, której towarem zarządza relacyjna baza danych. Każdy zakup książki przez klienta jest wprowadzany do kasy, która uaktualnia stan książek w bazie danych. Śledzenie stanu książek w czasie rzeczywistym umożliwia wykrycie w odpowiednim czasie pojawiających się braków i złożenie stosownego zamówienia w hurtowniach. W ten sposób mamy pewność, że w księgarni nigdy nie zabraknie popularnych tytułów.
Załóżmy dodatkowo, że aplikacja pracująca w kasie jest programem typu klient-serwer, napisanym w PowerBuilderze. Jeśli jest to jedyna aplikacja korzystająca z bazy danych, można w całości zaimplementować w niej wszelkie mechanizmy kontroli stanu książek. #338
Kiedy klient pojawi się z książką przy kasie, stworzona w PowerBuilderze aplikacja uruchomi wyrażenie UPDATE zmniejszające o jeden liczbę egzemplarzy tej konkretnej książki w bazie danych księgarni.
Po pewnym czasie jednak, księgarnia postanawia otworzyć sklep internetowy zarządzany aplikacją stworzoną w Javie. Aplikacja musi mieć dostęp do bazy danych stanu książek, aby móc go aktualizować w oparciu o zakupy dokonywane w sieci.
Ponieważ cały kod zarządzający stanem książek został umieszczony w aplikacji klient-serwer pracującej w kasie, trzeba ponownie zaimplementować go w aplikacji Javy. W ten sposób powstają dwie kopie kodu realizujące to samo zadanie w różnych językach programowania. Dalszy rozwój systemu wymaga sprawowania opieki nad tym kodem w każdej z aplikacji.
Lepsze rozwiązanie polega na napisaniu kodu zarządzającego inwentarzem w postaci zestawu procedur składowanych. Aplikacje wymagające dostępu do bazy danych nie muszą już teraz korzystać do tego celu z własnego kodu - wystarczy, że będą wywoływać odpowiednie procedury zapisane w bazie danych. Rozwiązanie takie posiada kilka istotnych zalet:

- Wymaga napisania i aktualizowania wyłącznie jednej kopii kodu, która użytkowana jest przez obecne i przyszłe aplikacje współpracujące z bazą danych.
- Izoluje programistów od rzeczywistej struktury bazy danych. Nie muszą oni operować bezpośrednio na bazie danych, zamiast tego odwołują się do niej poprzez pewien interfejs programowania aplikacji, jakim jest zestaw procedur zapisanych w bazie. Zmiany wprowadzone do modelu danych pociągają za sobą konieczność modyfikacji procedur składowanych, ale nie wpływają w żaden sposób na aplikację, która te procedury wywołuje.
- Może zapewnić lepszą wydajność bazy danych, ponieważ oprogramowanie oraz zapytania SQL należące do procedur składowanych przechowywane są w wersji skompilowanej po stronie bazy danych i nie muszą być ponownie analizowane przy każdym wywołaniu.

Niestety, nie ma możliwości przedstawienia w sposób ogólny metod pisania procedur składowanych, ponieważ ich konstrukcja jest w dużym stopniu zależna od rodzaju bazy danych. Dlatego, w pierwszej kolejności zajmiemy się procedurami w języku Transact-SQL (temat ten będzie kontynuowany w rozdziale 16.), natomiast później, w rozdziale 17., wyjaśnimy ideę procedur z punktu widzenia Oracle'a, czyli języka PL/SQL.
Jednym ze szczególnych typów procedur składowanych, jaki zostanie omówiony w tym rozdziale, jest wyzwalacz. Wyzwalacze (ang. triggers) są procedurami składowanymi uruchamianymi automatycznie w wyniku zaistnienia określonego zdarzenia. Wyzwalacz może np. zostać uruchomiony w następstwie wstawienia wiersza do tabeli. Wy-zwałacze są często stosowane jako mechanizmy weryfikujące dane przed wstawieniem do pól, zamiast więzów integralności tabel.
#339
Pisanie procedur składowanych

Procedury składowane tworzone są wyrażeniem CREATE PROCEDURĘ. Wyrażenie to nadaje po prostu identyfikator pewnemu fragmentowi kodu w języku Transact-SQL lub PL/SQL i zapisuje go w celu ponownego użycia.
Równie dobrze, wszystkie polecenia procedury można zapisać bez wyrażenia CREATE PROCEDURĘ - zostaną one wykonane, tak jak każde inne zapytanie, ale nie zostaną nigdzie zapisane.
Struktura wyrażenia CREATE PROCEDURĘ wygląda następująco:

CREATE PROCEDURĘ nazwa_procedury
[@nazwa_parametru typdanych[(długość)], ...]
AS Wyrażenie SQL

Wyrażenie CREATE PROCEDURĘ służy do zidentyfikowania procedury składowanej oraz zdefiniowania wszelkich parametrów, przekazywanych jej w chwili wywołania. W swojej najprostszej formie procedura składowana jest zapamiętanym zapytaniem.
Przykład tego typu procedury, składającej się z pojedynczego wyrażenia SELECT, przedstawia listing 15.1.
----------------------------
Listing 15.1. Prosta procedura składowana

CREATE PROCEDURĘ get_movie_titles
AS
SELECT movie_title
FROM Movies

GO
----------------------------

Istnieje kilka różnych metod wywoływania procedur składowanych. Najprostsza polega na wpisaniu jej nazwy jako zapytania - listing 15.2.
----------------------------
Listing 15.2. Wywołanie procedury składowanej

get_movie_titles

movie title
-----------------
Minerał House
The Code Warrior
Bili Durham
Codependence Day
The Linux Files
SQL Strikes Back
The Programmer
Hard Code
The Rear Windows

(9 row(s) affeoted)
----------------------------
#340
Przekazywanie parametrów do procedur składowanych

Umieszczenie statycznego zapytania wewnątrz procedury składowanej nie przynosi żadnych szczególnych korzyści. Ten sam cel równie dobrze można zrealizować przy użyciu widoku. Zaleta stosowania procedur składowanych polega na tym, że w trakcie wywoływania można przekazać jej kilka parametrów, a następnie użyć tych wartości w zapytaniach wewnątrz procedury. Wszystkie parametry, z jakich korzystać będzie procedura, trzeba uwzględnić w jej deklaracji (wywołując procedurę składowaną nie można pominąć żadnego z nich).
Np. procedura z listingu 15.1 mogłaby zostać napisana w taki sposób, aby przyjmować jako parametr identyfikator studia (studio_id), a następnie zwracać wszystkie pochodzące z niego filmy (movie_title). Procedurę taką tworzy wyrażenie CREATE PROCEDURĘ w listingu 15.4.
Zanim jednak do niej dotrzemy, wyjaśnienia wymaga pewna ogólna cecha procedur -brak możliwości ich edycji. Aby wprowadzić zmiany do procedury, trzeba usunąć jej istniejącą wersję, a następnie w jej miejsce utworzyć nową. Narzędzia wielu baz danych wykonują te czynności za nas, pobierając procedurę z bazy danych, a następnie zastępując ją, gdy zapisane zostaną jakiekolwiek zmiany. Jeżeli jednak zamierzamy edyto-wać procedurę składowaną w sposób ręczny, korzystając z bezpośredniego interfejsu bazy danych, musimy najpierw usunąć jej istniejącą wersję, aby móc utworzyć nową procedurę o tej samej nazwie. Kod realizujący to zadanie znajduje się w listingu 15.3.
----------------------------
Listing 15.3. Fragment kodu Transact-SQL usuwający procedurę składowaną get_movie_title

IF object_id('get_movie_titles') IS NOT NULL BEGIN
DROP PROCEDURĘ get_movie_titles
END

GO

This conunand did not return data, and it did not return any rows
----------------------------

Aktualizując istniejącą procedurę można dołączyć powyższy fragment kodu (wstawiając oczywiście odpowiednią nazwę procedury w miejsce get_movie_title) przed wyrażeniem CREATE PROCEDURĘ - w ten sposób istniejąca procedura zostanie usunięta przed utworzeniem nowej. Oracle wyeliminował tego typu problem poprzez wprowadzenie klauzuli OR REPLACE, która automatycznie usuwa procedurę składowaną o takiej samej nazwie, jak procedura tworzona.
Wracając do get_movie_titles, po usunięciu istniejącej procedury, można utworzyć nową, akceptującą parametr w postaci identyfikatora studia - listing 15.4.
----------------------------
Listing 15.4. Nawa procedura składowana get_movie_titles, przyjmująca pojedynczy parametr

CREATE PROCEDURĘ get_movie_titles @studio_id INTEGER
AS
SELECT movie_title
FROM Movies
WHERE studio_id - 8studio_id
GO
----------------------------
#341
Listę parametrów podaje się za nazwą procedury składowanej. W tym przypadku, procedura przyjmuje jeden parametr @studio_id, typu całkowitego (INTEGER). Wewnątrz procedury składowanej, parametr @studio_id jest zastępowany jego faktyczną wartością, która użyta zostaje w klauzuli WHERE. Znak @ wskazuje, iż określony parametr jest zmienną, a nie nazwą kolumny tabeli lub innego typu parametru.
Do uruchomienia procedury można posłużyć się jedną z poniższych metod:

execute get_movie_titles 1

exec get_movie_titles 1

execute get_movie_titles @studio_id =1

execute movies.dbo.get_movie_titles 1

get_movie_titles 1

Wynik wywołania przedstawia listing 15.5. Jest on taki sam, niezależnie od sposobu wywołania procedury składowanej (przy założeniu, że nie został zmieniony parametr).
----------------------------
Listing 15.5. Wywołanie procedury składowanej z parametrem

get_movie_titles 1

movie title
----------------------------
Minerał House
Codependence Day
The Rear Windows

(3 row(s) affected)
----------------------------

Użycie kilku parametrów wymaga oddzielenia ich od siebie przecinkami, tak jak widać to w listingu 15.6.
----------------------------
Listing 15.6. Procedura składowana z kilkoma parametrami

IF object_id('get_movie_titles') IS NOT NULL
BEGIN
DROP PROCEDURĘ get_movie_title
END

CREATE PROCEDURĘ get_movie_titles @studio_id INTEGER
@director_id INTEGER
AS SELECT movie_title
FROM Movies WHERE studio_id = @studio_id
AND director_id - @director_id

GO

get_movie_titles 3, 9

movie_title
------------
Bili Durham
SQL Strikes Back

(2 row(s) affected)
----------------------------
#342
=================
Rada
Parametrom przekazywanym do procedury składowanej można nadawać wartości domyślne. W poniższym przykładzie parametrowi @studio_id przypisana zostaje wartość domyślna, równa 1:
CREATE PROCEDURĘ get_movie_titles
@studio_id INT = 1
AS
SELECT movie_title
FROM Movies
WHERE studio id = gstudio id
=================


Zmienne

Przekazywanie parametrów do procedur składowanych jest jednym ze sposobów tworzenia zmiennych. W Transact-SQL, zmienne identyfikowane są przez występujący na początku nazwy znak @, który odróżnia je od identyfikatorów kolumn i tabel (zarówno jeśli chodzi o nazwy, jak i aliasy). Podobnie, jak w przypadku niemal każdego innego języka programowania, zadaniem zmiennych jest przechowywanie danych potrzebnych w trakcie wykonania programu. Zatem, kiedy do procedury składowanej get_movie_titles przekazany zostanie identyfikator studia, jego wartość dostępna jest w środku poprzez zmienną @studio_id tak długo, jak długo wykonuje się sama procedura. W tym przypadku składa się ona wyłącznie z jednego zapytania, w związku z czym zmienna zostaje zastosowana w klauzuli WHERE, a następnie przestaje istnieć z chwilą zakończenia programu.
Jeżeli chcemy użyć zmiennej bez przekazywania jej jako parametru procedury składowanej, trzeba utworzyć ją przy użyciu wyrażenia DECLARE. Każdej zmiennej w Transact-SQL, podobnie jak każdej kolumnie w tabeli, musi zostać nadany typ danych. Tak się akurat składa, że typy danych dla zmiennych i kolumn tabel są jednakowe, z jednym wyjątkiem - zmiennym nie można nadawać typów TEXT i IMAGE.
Zmienne deklarowane są przy użyciu następującej składni:

DECLARE nazwa_zmiennej TYPDANYCH(długość)

Dwa parametry używane w procedurze get_movie_titles można zatem zadeklarować w sposób następujący.

DECLARE studio_id INTEGER
DECLARE director_id INTEGER

Deklaracje obu zmiennych można zapisać w jednym wierszu (co minimalnie przyspieszy działanie programu):

DECLARE studio_id INTEGER, director_id INTEGER

Nazwy zmiennych w Transact-SQL nie mogą być dłuższe niż 29 znaków i muszą być zgodne z zasadami tworzenia identyfikatorów przedstawionymi w rozdziale 19.
#343
================
Rada
Procedury składowane tworzone są w formie sekcji, z których pierwsza jest na ogół sekcją deklaracji. W miejscu tym powinny znaleźć się wszystkie wyrażenia DECLARE programu. W ten sposób programista nie tylko unika użycia zmiennych przed ich faktyczną deklaracją, ale także ma szybki dostęp do listy zmiennych użytych w procedurze składowanej.
=================

Nadawanie wartości zmiennym

Po zadeklarowaniu zmiennej, można przypisać jej wartość. Jednym ze znanych sposobów nadawania wartości zmiennym jest przekazanie ich jako parametrów procedury składowanej. Wartości przekazywane do procedur składowanych są automatycznie umieszczane w parametrach, zdefiniowanych w chwili deklaracji procedury.
Do przypisywania wartości zmiennym służy wyrażenie SELECT. Aby nadać wartość zmiennej, trzeba umieścić ją na liście argumentów wyrażenia SELECT i posłużyć się znakiem równości. W języku Transact-SQL przypisanie zmiennej wartości, będącej wynikiem podzapytania lub zawartością określonej kolumny, nie wymaga użycia klauzuli FROM. Najprościej przypisuje się zmiennej wartość stałą listing 15.7.
----------------------------
Listing 15.7. Przypisanie statycznej wartości do zmiennej

DECLARE 8studio_id VARCHAR(25)
SELECT @studio_id = '3'
PRINT Bstudio_id

{1 row(s) affected)
----------------------------
=================
Rada
Funkcja PRINT może wyprowadzać na ekran tylko wartości typu CHAR lub YARCHAR. Nie ma konwersji niejawnych. Aby wyświetlić datę lub wartość liczbową, trzeba wcześniej odpowiednio przekon-wertować typ danych, używając do tego celu funkcji CONYERT.
===============

Zmiennej można również nadać wartość w kontekście standardowego wyrażenia SQL. Przykład takiej operacji pokazuje listing 15.8.
----------------------------
Listing 15.8. Przypisanie wartości zmiennej wewnątrz zapytania

DECLARE @cnt INT
SELECT @cnt - COUNT(*) FROM Movies
PRINT CONVERT(VARCHAR(25) , @cnt)
GO

(1 row(s) affected)
9
----------------------------
#344
Zmiennym mogą być przypisywane wartości będące wynikiem podzapytania, jak w listingu 15.9.
----------------------------
Listing 15.9. Przypisanie wartości będącej wynikiem podzapytania

DECLARE @average VARCHAR(25)

SELECT 8average = CONVERT(VARCHAR(25), (SELECT AVG(budget), FROM Movies))

PRINT @average

GO

(l row(s) affected)

28.777777
----------------------------

Z przypisaniem wyniku zapytania do zmiennej nie ma problemu w tym przypadku, ponieważ zwraca ono tylko jedną wartość (efekt użycia funkcji agregującej). Co się jednak stanie, gdy zmiennej przypisane zostanie zapytanie zwracające więcej niż jedną wartość, tak jak mato miejsce w listingu 15.10.

Listing 15.10. Przypisanie do zmiennej zapytania zwracającego kilka wierszy

DECLARE @title VARCHAR(25)

SELECT @title = movie_title
FROM Movies

PRINT etitle

GO

(9 row(s) affected)

The Rear Windows
----------------------------

Po uruchomieniu zapytania na ekranie wyświetlony został tylko jeden, ostatni wiersz spośród wszystkich zwróconych przez zapytanie. Stało się tak ponieważ za każdym razem, kiedy zapytanie wydobywało kolejny wiersz z bazy, zmiennej @title przypisywana była nowa wartość pola movie_title (należąca do bieżącego rekordu). Po wykonaniu zapytania w całości, w zmiennej etitle pozostawała wartość pola movie_title, należąca do rekordu zwróconego na końcu. Wszystkie wartości pola movie_title zostały przypisane zmiennej gtitle, ale każda kolejna zastępowała poprzednią, w związku z czym tylko ostatnia z nich miała szansę pozostać tam na stałe.
Należy szczególnie uważać na zapytania zwracające wartość pustą. Próba przypisania wartości null do zmiennej kończy się błędem, dlatego trzeba zadbać o to, aby sytuacja taka nie miała nigdy miejsca lub, kiedy jest to niemożliwe, obsłużyć ją w odpowiedni sposób z zastosowaniem funkcji ISNULL. ISNULL umożliwia określenie wartości domyślnej, która przypisana zostanie zmiennej, jeżeli wartość jaka miała być jej przypisana pierwotnie, okaże się wartością pustą. Zapytanie z listingu 15.11 spowoduje błąd, natomiast zapytanie z listingu 15.12, dzięki użyciu funkcji ISNULL, zakończy się wynikiem 0.
#345
----------------------------
Listing 15.11. Zapytanie zwracające wartość NULL

DECLARE @var VARCHAR(25)

SELECT @var = CONYERT(VARCHAR(25) , AVG(budget))
FROM Movies
WHERE budget < 0

PRINT @var

GO

(1 row(s) affected)
----------------------------
----------------------------
Listing 15.12. Zapytanie korzystające z funkcji ISNULL

DECLARE @var DEC
SEELCT @var = ISNULL (AVG (budget) , 0)
FROM Movies
WHERE budget < 0

PRINT @var

GO

(1 row(s) affected)

0.000000
----------------------------

Definiowanie bloków kodu

Blok wyrażeń definiowany jest w języku SQL przy użyciu wyrażeń BEGIN i END. Słowa BEGIN i END pełnią podobną rolę, jak nawiasy w niektórych językach programowania, tzn. umożliwiają grupowanie poleceń wszędzie tam, gdzie wymagane jest użycie pojedynczego wyrażenia.
Na przykład kiedy warunek konstrukcji IF przyjmuje wartość prawdziwą, wykonywane jest pojedyncze polecenie występujące bezpośrednio za nim. Zastąpienie tego polecenia blokiem kodu sprawi, że wykonane zostaną wszystkie zawarte w nim wyrażenia. Bloki kodu mogą być stosowane do grupowania kodu w dowolnych miejscach procedur.
Składnia służąca do grupowania wyrażeń SQL wygląda następująco:

BEGIN
wyrażenia SQL
END

Bloki kodu BEGIN. .END mogą być zagnieżdżane wewnątrz innych bloków. Ma to szczególnie znaczenie podczas tworzenia bardzo rozbudowanych konstrukcji warunkowych. Poniżej przedstawiony został przykład zagnieżdżania bloków wyrażeń w języku SQL:

BEGIN
wyrażenia SQL
BEGIN
wyrażenia SQL
#346
END
wyrażenia SQL
BEGIN
wyrażenia SQL
BEGIN
wyrażenia SQL
END
wyrażenia SQL
END
wyrażenia SQL
END
----------------------------

Wyrażenia warunkowe

Do tworzenia wyrażeń warunkowych wewnątrz procedur składowanych służy słowo kluczowe l F. Wyrażenia IF sprawdzają wartość boolowską podanego warunku i na tej podstawie decydują o tym, czy określone wyrażenie (lub blok wyrażeń) powinno być wykonane, czy też nie. Jeżeli wyrażenie warunkowe jest prawdziwe, związany z nim blok kodu jest wykonywany, w przeciwnym wypadku blok ten jest pomijany. Dodając słowo kluczowe ELSE można zdefiniować fragment kodu wykonywany, gdy wynikiem wyrażenia warunkowego jest fałsz.
Wyrażenia warunkowe są obliczane w taki sam sposób, jak wyrażenia występujące w klauzuli WHERE zwykłego zapytania. Wartość prawdziwa lub fałszywa może być określona na podstawie operatora porównania lub wyniku zapytania. Przyjmuje się, że zapytania zwracające jakiekolwiek rezultaty traktowane są jako prawda, natomiast zapytania zwracające NULL, jako fałsz.
Listing 15.13 zawiera przykład wyrażenia warunkowego IF, które testuje wartość zmiennej, a następnie wykonuje zapytanie, jeżeli wynikiem tego porównania jest prawda.
----------------------------
Listing 15.13. Wyrażenie warunkowe IF w języku SQL

DECLARE @num INT
SELECT @num =10
IF @num > 5
UPDATE Movies
SET release_date = GETDATE ()
WHERE movie_id = @num
GO

(1 row(s) affected)
----------------------------

Tabela Movies zostanie uaktualniona, ponieważ wartość zmiennej @num jest większa od pięciu. W wyrażeniach warunkowych, podobnie jak w klauzuli WHERE, możliwe jest stosowanie wyrażeń łączonych przy użyciu operatorów logicznych. Na przykład weźmy zapytanie z listingu 15.14, w którym wyrażenie IF składa się z dwóch części.
----------------------------
Listing 15.14. Złożone wyrażenie warunkowe

DECLARE @num INT
SELECT @num = 10
IF @num > 5 AND @num < 10
#347
UPDATE Movies
SET release_date = GETDATE()
WHERE movie_id - @num
GO

(1 row(s) affected)
----------------------------

Polecenie UPDATE w listingu 15.14 nie zostało wykonane, ponieważ wartość @num nie zawiera się w przedziale między 5 a 10. Warunek @num<10 jest nieprawdziwy, w związku z czym wynikiem całego wyrażenia warunkowego jest fałsz. Jedyną rzeczą, której nie da się zrobić w wyrażeniu IF, jest użycie specjalnych operatorów porównania, stosowanych w klauzuli WHERE. IF nie może być łączony ze słowami kluczowymi BETWEEN, LIKE, IN oraz innymi specjalnymi operatorami klauzuli WHERE.

Zapytania w wyrażeniach warunkowych

Zapytanie może zostać użyte w wyrażeniu warunkowym na dwa różne sposoby. Jeśli zapytanie zwraca pojedynczą wartość, może zostać użyte bezpośrednio w wyrażeniu warunkowym. Drugie rozwiązanie polega na użyciu zapytania w połączeniu ze słowem kluczowym EKISTS. Wynikiem takiego wyrażenia jest prawda, gdy zapytanie zwraca jakąkolwiek wartość różną od NULL (w przypadku wartości NULL wynikiem wyrażenia jest fałsz).
Przyjrzyj się poniższemu wyrażeniu warunkowemu, które korzysta z wartości zwróconej przez umieszczone w nim zapytanie. Aby zapytanie mogło zostać użyte w zwykłym wyrażeniu musi zwracać pojedynczą wartość. Wyrażenie z listingu 15.15 uruchamia polecenie SELECT, jeżeli tabela Movies zawiera mniej niż 15 wierszy.
----------------------------
Listing 15.15. Użycie wartości zwróconej przez zapytanie w wyrażeniu warunkowym IF

IF (SELECT COUNTI*) FROM Movies)< 15
SELECT movie_title
FROM Movies

movie title
--------------
Minerał House
The Code Warrior
Bili Durham
Codependence Day
The Linux Files
SQL Strikes Back
The Programmer
Hard Code
The Rear Windows

(9 row(s) affected)
----------------------------

W następnym przykładzie (listing 15.16) wyrażenie warunkowe zostało użyte w połączeniu z zapytaniem, ale bez zastosowania operatora porównania. Zamiast niego do stworzenia warunku typu prawda-fałsz użyte zostało słowo kluczowe EKISTS. Polecenie SELECT za wyrażeniem warunkowym l F nie jest wykonywane, ponieważ zapytanie użyte w warunku nie zwraca żadnego wyniku.
#348
----------------------------
Listing 15.16. Użycie zapytania jako warunku wyrażenia IF

IF EXISTS (SELECT movie_id FROM Movies WHERE studio_id = 10)
SELECT DISTINCT studio_id
FROM Movies

This command did not return data, and it did not return any rows
----------------------------

Połączenie wyrażenia warunkowego z blokiem kodu

Pojedyncze polecenie wykonywane przez wyrażenie warunkowe może zostać zastąpione blokiem kodu definiowanym przez słowa BEGIN i END. Użycie bloku jest bardzo proste - wystarczy umieścić słowo BEGIN bezpośrednio za wyrażeniem warunkowym, a po nim wstawić ciało bloku, zakończone słowem END. Przykład pokazuje listing 15.17.
----------------------------
Listing 15.17. Wykonanie bloku kodu w wyrażeniu warunkowym IF

DECLARE @new_revenue DEC

IF EXISTS (SELECT movie_id FROM Movies WHERE studio_id = 1)
BEGIN
SELECT @new_revenue = 50

UPDATE Movies SET gross = @new_revenue
WHERE studio_id = 1
END

(1 row(s) affected)

(3 row(s) affected)
----------------------------

Wyrażenie IF...ELSE

Wyrażenie IF uruchamia polecenie, gdy zawarty w nim warunek jest prawdziwy. Stosując słowo kluczowe ELSE, można wzbogacić wyrażenie warunkowe o polecenie lub blok poleceń, które zostaną wykonane, jeżeli warunek okaże się fałszywy. Struktura wyrażenia IF. . . ELSE wygląda następująco:

IF warunek
wyrażenie SQL
ELSE wyrażenie SQL

Jeżeli warunek wyrażenia l F jest prawdziwy, wykonywane jest polecenie występujące bezpośrednio za nim. Z kolei, gdy warunek jest fałszywy, wykonane zostaje polecenie lub blok poleceń za słowem kluczowym ELSE. Przykład użycia konstrukcji IF...ELSE przedstawia listing 15.18.
----------------------------
Listing 15.18. Użycie konstrukcji IF...ELSE

IF EKISTS (SELECT * FROM Movies WHERE studio_id = 8)
SELECT COUNT(+) FROM Movies
WHERE studio id = 8
#349
ELSE
PRINT "Nie znaleziono pasujących filmów."
Nie znaleziono pasujących filmów.
----------------------------

Zagnieżdżanie wyrażeń warunkowych

Zagnieżdżając wyrażenia warunkowe wewnątrz innych, można budować bardzo złożone konstrukcje warunkowe. Wyrażenie IF, użyte wewnątrz innego wyrażenia tego typu, traktowane jest jako pojedyncze polecenie, nawet jeśli występuje w połączeniu ze słowem ELSE. Oznacza to, że wyrażenia warunkowe mogą być zagnieżdżane wewnątrz siebie bez użycia bloków, chociaż konstrukcje tego typu są oczywiście dopuszczalne. Przykład zagnieżdżania wyrażeń warunkowych przedstawia listing 15.19.
----------------------------
Listing 15.19. Zagnieżdżanie wyrażeń warunkowych

DECLARE Snum INT
SELECT @num = 7
IF @num > 5
IF @numa > 10
PRINT "Zmienna num jest większa od 10. "
ELSE
PRINT "Zmienna num jest większa od 5. "
ELSE
PRINT "Zmienna num jest mniejsza lub równa 5. "

(1 row(s) affected)
----------------------------

Zmienna num jest większa od 5.

W powyższym listingu, wyrażenie warunkowe IF...ELSE zostało zagnieżdżone w drugim, zewnętrznym wyrażeniu tego typu. Jak łatwo można się domyślić, wewnętrzne wyrażenie jest wykonywane, ponieważ wartość zmiennej @numjest większa od 5.
Zagnieżdżanie wyrażeń warunkowych ma sens jedynie wtedy, gdy są one używane w połączeniu z klauzulą ELSE. Zagnieżdżanie samych wyrażeń IF jest nieopłacalne, ponieważ ten sam efekt można uzyskać o wiele prościej - stosując operatory logiczne. Popatrz na przykłady w listingach 15.20 i 15.21.
----------------------------
Listing 15.20. Zagnieżdżone wyrażenie warunkowe bez klauzuli ELSE

DECLARE @crov INT
DECLARE @bar INT

SELECT @crov = 1
SELECT bar = 2
IF @crow = 1
IF @bar = 1 PRINT "Oba warunki są prawdziwe."

(1 row(s) affected)

Oba warunki są prawdziwe.
----------------------------
#350
----------------------------
Listing 15.21. Wyrażenie -warunkowe z operatorem logicznym

DECLARE @crov INT
DECLARE @bar INT

SELECT @crov = 1
SELECT bar = 2
IF @crow = 1
AND @bar = 2
PRINT "Oba warunki są prawdziwe.

(1 row(s) affected)

{1 row{s) affected)

Oba warunki są prawdziwe.
----------------------------

Oba rozwiązania są możliwe do zaakceptowania (generują taki sam wynik), ale preferowane jest drugie z nich (listing 15.21), ze względu na prostotę i łatwość w interpretacji, w porównaniu z wyrażeniem z listingu 15.20.
Wyrażenia warunkowe bardzo często stosowane są w połączeniu z blokami kodu. Zagnieżdżanie wyrażeń warunkowych z blokami kodu, nie różni się niczym od zagnieżdżania wyrażeń z pojedynczym poleceniem. Przykład konstrukcji warunkowej z użyciem bloków znajduje się w listingu 15.22.
----------------------------
Listing 15.22. Zagnieżdżone wrażenia warunkowe, używające bloków kodu

DECLARE @crow INT
DECLARE @bar INT
DECLARE @display VARCHAR(25)
SELECT @crow = 1
SELECT @bar = 2

IF @crow > 10
BEGIN
SELECT @display = CONVERT(VARCHAR(25), @crov - @bar)
PRINT @display
END
ELSE
BEGIN
SELECT @display = CONYERT (VARCHAR (25) , @crow + @bar)
PRINT @display
END

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

3
----------------------------

Pętle

Pętle umożliwiają cykliczne wykonywanie tego samego zestawu poleceń do momentu, kiedy spełniony zostanie określony warunek. W pewnym sensie, wyrażenie SELECT zachowuje się tak samo jak pętla, tzn. przetwarza każdy wiersz w tabeli (lub w złączeniu
#351
tabel) - warunkiem kończącym wyrażenie jest brak dalszych wierszy do przetwarzania. W trakcie iteracyjnego przetwarzania wierszy (z tabel wymienionych w klauzuli FROM), SELECT analizuje każdy z nich i wyprowadza odpowiednie wyniki.
SELECT wykonuje w pojedynczym wyrażeniu to, co w innych językach programowania wymagałoby wielu poleceń. Ponieważ SQL jest wysoce specjalizowanym językiem służącym do obsługi uporządkowanych zbiorów danych, w jego wnętrzu zaszyte zostały mechanizmy innych języków umożliwiające iteracyjne przetwarzanie zbiorów wierszy w tabeli.
Czasami jednak możliwości oferowane przez SELECT (lub UPDATE i INSEKT) są niewystarczające do realizacji określonego zadania i dla ich uzupełnienia trzeba samodzielnie dobudować konstrukcję posiadającą cechy pętli. Jak już wcześniej wspomniano, pętla wykonuje się cyklicznie do chwili, kiedy spełniony zostanie zadany warunek. W języku SQL warunkiem tym może być nadanie odpowiedniej wartości zmiennej, pobranie wszystkich wierszy z kursora lub fałszywa wartość dowolnego innego wyrażenia logicznego.
Transact-SQL oferuje tylko jeden typ pętli - WHILE - w przeciwieństwie do innych języków programowania, posiadających kilka różnych konstrukcji tego typu (o których mowa będzie w dalszej części książki). WHILE jest najbardziej elastyczną konstrukcją spośród wszystkich pętli. Przy jej użyciu można realizować wszystkie zadania, jakie są w stanie wykonać inne pętle, chociaż w pewnych przypadkach użycie innych pętli zwiększa czytelność kodu.

Pętla WHILE

Struktura pętli WHILE jest bardzo podobna do wyrażenia warunkowego IF. Różnica polega na tym, że polecenie lub blok poleceń nie jest wykonywany raz, ale wielokrotnie, do momentu kiedy warunek pętli okaże się fałszywy lub zostanie ona przerwana w sposób jawny poleceniem BREAK. Struktura pętli WHILE wygląda następująco:

WHILE warunek
wyrażenie

Jeżeli zamiast pojedynczego wyrażenia chcemy użyć bloku wyrażeń, budowa pętli ulega nieznacznej zmianie:

WHILE warunek
BEGIN
wyrażenia END

Przykład z listingu 15.23 demonstruje bardzo istotną kwestię dotyczącą pętli WHILE, a mianowicie konieczność umieszczenia w ciele pętli kodu, który sprawi, że warunek sterujący jej działaniem przyjmie ostatecznie wartość fałszywą. Jeżeli szczegół ten zostanie pominięty, zbudowana pętla stanie się pętlą nieskończoną, skutecznie uniemożliwiając wykonanie dalszych poleceń (program będzie działał w nieskończoność, wykonując na okrągło polecenia zawarte w pętli). Za chwilę omówiona zostanie inna metoda pozwalająca uniknąć powstawania pętli nieskończonych, którą z powodzeniem można stosować w programach.
#352
----------------------------
Listing 15.23. Przykład patii WHILE

DECLARE @crow INT
DECLARE @display VARCHAR(25)
SELECT @cro = 1
WHILE @crov < 5
BEGIN
SELECT @display = CONVERT (VARCHAR (25) , @crov )
PRINT @display
SELECT @crov = @erów + 1
END

(1 row(s) affected)

(1 row(s) affected)

1

(1 row(s) affected)

(1 row(s) affected)

2

(1 row(s) affected)

(1 row(s) affected)

3

(1 row(s) affected)

(1 row(s) affected)

4

(1 row(s) affected)
--------------------------------

Pętla jest wykonywana do momentu, kiedy zmienna @crow osiągnie wartość równą lub większą od 5. W chwili rozpoczęcia pętli, zmienna @crow jest równa l, ale przy każdym przejściu pętli wartość ta jest zwiększana o jeden. Z wyników wygenerowanych przez program wynika, że pętla została wykonana pięć razy. Kiedy zmienna osiągnęła wartość 5, warunek działania pętli przestał być prawdziwy, w związku z czym dalsze jej działanie zostało przerwane.

Pętle nieskończone

Utworzenie pętli nieskończonej nie jest niczym trudnym. Za przykład może posłużyć poniższy fragment kodu. Przerwanie jego działania wymaga akcji z zewnątrz (wymuszenia zakończenia procesu lub przyciśnięcia kombinacji klawiszy przerywającej wykonanie programu):

WHILE 1=1
PRINT "Pętla nieskończona..."

Czasem pętla nieskończona tworzona jest niechcący (wbrew intencjom programisty). Przykładem nich będzie modyfikacja programu z listingu 15.23, zawarta w listingu 15.24.

#353
--------------------------------

Listing 15.24. Nieskończona patia WHILE

DECLARE @crov INT SELECT Serów = 1
WHILE @crov <> 10 BEGIN
PRINT @crov
SELECT @crov = @crow + 2
END
--------------------------------

Pętla będzie trwać w nieskończoność, ponieważ zmienna @crow nigdy nie przyjmie wartości 10. Gdyby zmienna @crow była zwiększana, tak jak poprzednio, co jeden, pętla przerwałaby swoją pracę w chwili osiągnięcia przez zmienną wartości 10. Ponieważ jednak wartość przyrasta o 2 (poczynając od 1), liczba 10 jest pomijana. Bardzo łatwo można znaleźć się w podobnej sytuacji, dlatego trzeba zawsze dobrze przemyśleć, jaki warunek powinien znaleźć się w pętli oraz jak należy postępować z wartościami wewnątrz pętli, aby warunek ten miał szansę być spełniony.
Inny sposób przerwania pętli opiera się na użyciu wyrażenia BREAK. BREAK przerywa pętlę w miejscu swojego wystąpienia i przechodzi do wykonania dalszej części programu (pomijając pozostałe polecenia wewnątrz pętli), poczynając od pierwszego wyrażenia bezpośrednio za pętlą.
Jeżeli oprócz warunku za słowem WHILE zachodzi potrzeba określenia innych kryteriów zakończenia pętli, można to zrobić umieszczając wewnątrz pętli wyrażenia IF, które w odpowiednich warunkach uruchomią polecenie BREAK.
Załóżmy dla przykładu, że w ciele pętli znajduje się duża porcja kodu, a nam zależy na tym, aby przerwać jego wykonanie gdzieś w środku, zanim wykona się pozostała część poleceń i na nowo obliczona zostanie wartość logiczna warunku pętli. Wystarczy umieścić w tym miejscu wyrażenie IF połączone z poleceniem BREAK, a pętla zostanie natychmiast zakończona, gdy tylko spełniony zostanie zadany warunek.
Konstruowanie pętli nieskończonych, z warunkiem kończącym ich działanie w postaci wyrażenia warunkowego umieszczonego wewnątrz pętli, nie jest rozwiązaniem szczególnie polecanym. Niemniej jednak zdarzają się sytuacje, w których technika ta okazuje się przydatna - listing 15.25.
--------------------------------
Listing 15.25. Patia nieskończona przerywania poleceniem BREAK

DECLARE @total INT WHILE 1 = 1
BEGIN
SELECT @total = COUNT(*)
FROM A_Table IF Stotal > 100
BREAK
INSERT INTO A_Table
(primary_key) VALUES (@total +1)
END

(1 row(s) affected)
--------------------------------
#354
Pętla jest nieskończona (warunek działania 1=1 jest zawsze prawdziwy) - o końcu pracy pętli decyduje wyrażenie warunkowe w jej wnętrzu. Każde kolejne wykonanie pętli powoduje przypisanie liczby wierszy w tabeli A_Table do zmiennej @total. Pętla jest przerywana, gdy liczba wierszy przekracza 100, w przeciwnym wypadku do tabeli wstawiany jest nowy wiersz.

Restartowanie pętli - CONTINUE

Wyrażenie CONTINUE zawiesza wykonanie bieżącej iteracji, po czym rozpoczyna pętlę od ponownego sprawdzenia warunku jej działania. W pewnych sytuacjach zależy nam na tym, aby pętla była wykonywana w całości tylko w przypadku spełnienia określonego warunku - nie chcemy jednak opuszczać pętli całkowicie (jak ma to miejsce w przypadku BREAK). Do tego celu właśnie służy wyrażenie CONTINUE. Kiedy w trakcie wykonania pętli napotkane zostanie wyrażenie CONTINUE, dalsze polecenia są ignorowane, a punkt wykonania programu powraca do warunku sterującego pętlą.
Listing 15.26 zawiera przykład programu SQL, który korzysta z wyrażenia CONTINUE, aby ograniczyć wyprowadzane dane jedynie do filmów ze studiów, których koszt produkcji filmowych przekroczył 100 milionów dolarów.
-------------------------------
Listing 15.26. Pętla używająca wyrażenia CONTINUE

DECLARE @total_studios INT
DECLARE Scnt INT
DECLARE @rev INT SELECT Sstudios - MAX(studio_id)
FROM Movies
WHILE @cnt <= @total_studios
BEGIN
SELECT @rev = SUM(gross)
FROM Movies
WHERE studio_id = @cnt
IF @rev < 100
CONTINUE
SELECT movie_title FROM Movies
WHERE studio_id = @cnt
END

(1 row(s) affected)
---
{1 row(s) affected)

movie_title
-------------
Minerał House
Codependence Day
The Rear Windows

(3 row(s) affected)

(1 row(s) affected)

---
#355
(1 row(s) affected}

movie title
------------

(0 row(s) affected)
--------------------------------

W pętli obliczany jest sumaryczny zysk każdego studia, po czym przerywane jest dalsze działanie, jeżeli suma ta jest mniejsza od 100. Jeśli suma przekracza 100, wyświetlana jest lista filmów danego studia.

Zagnieżdżanie pętli

Pętle mogą być zagnieżdżane podobnie, jak wyrażenia warunkowe. Przy zagnieżdżaniu, pętla wewnętrzna wykonywana jest tak długo, aż spełniony zostanie odpowiedni warunek, po czym punkt wykonania powraca do pętli zewnętrznej. Kolejny
przykład (listing 15.27) składa się z dwóch pętli, które wykonują iteracje po wszystkich dniach tygodnia, a następnie po wszystkich godzinach każdego dnia.
--------------------------------
Listing 15.27. Pętle zagnieżdzone

DECLARE 0day INT
DECLARE @hour INT
DECLARE Sdisplay VARCHAR(25)
SELECT @day = 1
WHILE 8day <= 1
BEGIN
SELECT @display = CONVERT(VARCHAR(25) , @day)
PRINT Sdisplay
SELECT @hour = 1
WHILE @hour <= 24 BEGIN
SELECT @display = CONYERT(VARCHAR(25), @hour)
PRINT @display
SELECT @hour = @hour +1
END
SELECT @day = @day + 1
END
--------------------------------

Dane wyjściowe generowane przez powyższe zapytanie zostały pominięte ze względu na ich rozmiar (ponad 100 linii). Wyrażenia CONTINUE i BREAK działają w odniesieniu do najbardziej zagnieżdżonej pętli, w której się znajdują. Jeżeli w pętli liczącej godziny napotkane zostałoby wyrażenie BREAK, pętla zostałaby przerwana, a punkt wykonania powróciłby do pętli liczącej dni. Wyrażenie CONTINUE w pętli liczącej dni spowodowałoby powrót do warunku sterującego tą pętlą.

Iteracyjne przetwarzanie kursora

Jednym z najbardziej przydatnych zastosowań pętli w SQL jest pobieranie i przetwarzanie wierszy z kursora. Ponieważ kursor nie wydobywa wszystkich wierszy ze zbioru jednocześnie, lecz stopniowo, do ich przetworzenia potrzebna jest pętla -
#356
tym bardziej, że zazwyczaj nie można z góry określić, ile rekordów zwróci związane z nim zapytanie. Jeśli wiemy, że napisany przez nas kursor zwróci dokładnie 5 wierszy, możemy napisać program składający się z pięciu instrukcji FETCH. Wystarczy jednak, aby w bazie danych zaszła drobna zmiana powodująca zwrócenie o jeden rekord więcej, a napisany program stanie się bezużyteczny.
Pomijając fakt znajomości lub nieznajomości liczby rekordów zwracanych przez zapytanie, jeżeli na każdym pobranym wierszu przeprowadzana jest ta sama operacja (lub kilka operacji), najlepiej do tego celu użyć pętli. Pętle ułatwiają zarządzanie kodem, ponieważ wszelkie zmiany wprowadzane są w jednym miejscu dla wszystkich wierszy (w przeciwieństwie do poprzedniego przykładu, gdzie zmian wymagałaby każda z pięciu instrukcji FETCH).
Bazy danych obsługujące kursory posiadają specjalne zmienne informujące o stanie ostatniej operacji na kursorze. Zmienna tego typu może zostać użyta w warunku sterującym pętlą, co pozwoli pobrać wszystkie wiersze kursora. W Transact-SQL (Sybase), zmienna przechowująca status ostatniej operacji pobrania nosi nazwę @@sqlstatus, Microsoft nazywają @@fetch_status. W obu przypadkach wartość 0 informuje o pomyślnym wyniku operacji, zatem warunek sterujący pętlą mógłby wyglądać następująco:

WHILE @@sqlstatus = 0

Listing 15.28 zawiera program, który otwiera kursor założony dla tabeli Movies, przetwarza iteracyjnie wszystkie jego wiersze i wyświetla za każdym razem tytuł filmu (@title). Iteracja po wszystkich wierszach przy użyciu kursora i pętli WHILE umożliwia indywidualne traktowanie każdego z nich (tutaj wyświetlanie filmów dochodowych wielkimi literami, natomiast filmów niedochodowych małymi literami), czego nie da się zrealizować przy użyciu zwykłej instrukcji SELECT.
--------------------------------
Listing 15.28. Iteracyjne przetwarzanie danych z kursora

DECLARE get_movies CURSOR
FOR SELECT movie_title, gross, budget
FROM Movies
OPEN get_movies
-- Pobranie pierwszego wiersza z kursora
FETCH get_movies INTO @title, @gross, @budget
-- Iteracyjne przetworzenie pozostałych wierszy
WHILE @@fetch_status - 0 BEGIN IF @gross >= @budget PRINT UPPER(@title)
ELSE
PRINT LOWER(@title)
FETCH get_movies INTO etitle, @gross, @budget
END

(1 row(s) affected)

(1 row(s) affected)

MINERAŁ HOUSE

(1 row(s) affected)

(1 row(s) affected)

THE CODE WARRIOR
#357
(1 row(s) affected)

(1 row(s) affected)

BILL DURHAM

(1 rów(s) affected)

(1 row(s) affected)

CODEPENDENCE DAY

(1 row(s) affected)

(1 row(s) affected)

the linux files

(1 row(s) affected)

(1 row(s) affected)

SQL STRIKES BACK

(1 row(s) affected)

(1 rów(s) affected)

the programmer

(1 row(s) affected)

(1 row(s) affected)

hard code

(1 row(s) affected)

(1 row(s) affected)

THE REAR WINDOWS

(0 row(s) affected)
--------------------------------

Pierwszy wiersz pobierany jest z kursora przed rozpoczęciem pętli. Jeśli okaże się, że w kursorze nie ma żadnych wierszy, pętla w ogóle się nie rozpocznie (ponieważ wartość zmiennej @@fetch_status będzie różna od 0). Kolejne wiersze pobierane są na końcu pętli, chociaż nie wynika to z żadnej reguły. Równie dobrze mogłyby być one pobierane na początku pętli - reszta jej kodu i tak zostałaby wykonana. Z punktu widzenia logiki działania pętli, rozwiązanie takie jest jednak najbardziej rozsądne, ponieważ pobranie kolejnego wiersza następuje zawsze tuż przed wyliczeniem warunku działania pętli. Jak łatwo można zauważyć, zawartość zmiennych @title, @gross i @budget ulega zmianie przy każdym przejściu pętli.
Wyzwalacze


Wyzwalacze (ang. triggers) są specjalnymi procedurami składowanymi, uruchamianymi automatycznie w następstwie zaistnienia określonego zdarzenia. Ich zadanie polega na wymuszaniu integralności danych. Wyzwalacze można porównać
@358
z więzami integralności (omawianymi w rozdziale 3.), ponieważ ich wspólnym celem jest zapewnienie, aby dane przechowywane w tabelach były zgodne z regułami narzuconymi przez projektanta bazy. Wyzwalacze mogą być pisane z użyciem wszelkich wyrażeń SQL dostępnych dla procedur składowanych, z kolei więzy integralności mają za zadanie wymuszać określone warunki, takie jak związki tabel podrzędna-nadrzędna, czy unikalność danych w kolumnie.

Tworzenie wyzwalaczy

Wyzwalacz tworzony jest poleceniem CREATE TRIGGER. Istnieje kilka różnych typów wyzwalaczy, niektóre z nich są dostępne tylko w wybranych typach baz danych. Np. Oracle umożliwia tworzenie wyzwalaczy uruchamianych przed, po lub zamiast poleceń DELETE, UPDATE i INSERT. Z kolei Transact-SQL dla bazy MS SQL Server pozwala na tworzenie wyzwalaczy uruchamianych przed każdym z trzech powyższych poleceń. Więcej na temat tworzenia wyzwalaczy w języku PL/SQL przeczytać można w rozdziale 17.
Składnia umożliwiająca tworzenie wyzwalaczy w Transact-SQL (MS SQL Seryer) wygląda następująco:

CREATE TRIGGER [nazwa_właściciela].nazwa_wyzwalacza
ON [nazwa_właściciela].nazwa_tabeli
FOR (INSERT, UPDATE, DELETE)
AS
kod wyzwalacza

Wyzwalacz musi posiadać unikalną nazwę, która umożliwi odwoływanie się do niego w przyszłości. Trzeba również wskazać tablicę, na której operować będzie wyzwalacz. Klauzula FOR służy do określenia operacji powodujących uruchomienie wyzwalacza (mogą to być dowolne kombinacje INSERT, UPDATE i DELETE). Właściwy kod wyzwalacza rozpoczyna się za słowem kluczowym AS.

Usuwanie wyzwalaczy

Wyzwalacz usuwany jest poleceniem DROP TRIGGER, w sposób następujący:

DROP TRIGGER nazwa_wyzwalacza

W Transact-SQL utworzenie wyzwalacza dla danej tabeli powoduje automatyczne usunięcie wyzwalacza istniejącego, jeżeli jest on uruchamiany tymi samymi zdarzeniami, jak w przypadku nowego wyzwalacza.

Kod wyzwalacza

Zadaniem kodu umieszczonego w wyzwalaczu jest weryfikowanie wykonywanej operacji pod względem zachowania integralności bazy danych. Ze względu na możliwość stosowania warunków logicznych oraz zapytań, wyzwalacze posiadają o wiele większe możliwości w zakresie sprawowania nadzoru nad bazą danych niż
#359
zwykłe więzy integralności. Na ogól wyzwalacze stosowane są w połączeniu z poleceniami o charakterze transakcji, w celu zatwierdzenia lub odrzucenia wprowadzonych zmian.
Dla przykładu wyzwalacz z listingu 15.29 ma za zadanie nie dopuścić do wstawienia w tabelę Movies wiersza z polem budget przekraczającym wartość 100.
--------------------------------
Listing 15.29. Wyzwalacz sprawujący nadzór nad operacjami wstawienia

CREATE TRIGGER movies_insert
ON Movies
FOR INSERT
AS
BEGIN
IF budget > 100 BEGIN
ROLLBACK TRANSACTION
PRINT "Pole budget nie może przekraczać wartości 100"
END
END
--------------------------------

Próba wstawienia rekordu do tabeli Movies powoduje uruchomienie wyzwalacza movies_insert, który odmawia wykonania operacji (listing 15.30).
--------------------------------
Listing 15.30. Zablokowanie operacji wstawienia przez wyzwalacz

INSERT INTO Movies
{movie_id, movie_title, studio_id, director_id, gross, budget, release_date)
VALUES
(15, 'Test', 3, 5, 50, 101, GetDate())

Pole budget nie może przekraczać wartości 100
--------------------------------

Wyzwalacz jest uruchamiany po zakończeniu zapytania - zanim to jednak nastąpi, wcześniej sprawdzane są wszystkie więzy integralności nałożone na tabelę. Jeśli okaże się, że zapytanie działa wbrew dowolnemu z tych więzów (np. usiłuje wstawić NULL do kolumny, która takiej wartości nie akceptuje), polecenie zostanie odrzucone, a wyzwalacz nie zostanie w ogóle uruchomiony.

Wyzwalacze działające na kolumnach

Jak wiadomo, polecenia INSERT i DELETE nie odnoszą się do poszczególnych kolumn, lecz do całych wierszy. Wyjątkiem jest instrukcja UPDATE, która może modyfikować wybrane kolumny tabeli. Używając warunku UPDATE (kolumna) można zmodyfikować wyzwalacz w taki sposób, aby był on wywoływany tylko w przypadku aktualizowania określonej kolumny (lub zbioru kolumn).
Warunek UPDATE (kolumna) przyjmuje wartość prawdziwą, jeżeli polecenie UPDATE, które uruchomiło wyzwalacz, modyfikuje kolumnę wymienioną w warunku. Jeżeli modyfikowana jest inna kolumna, warunek przyjmuje wartość fałszywą. Warunki tego typu mogą występować samodzielnie lub być łączone spójnikami logicznymi
#360
z innymi warunkami. Listing 15.31, korzystający z warunku UPDATE cofa transakcję, jeżeli modyfikowana jest kolumna movie_title.
--------------------------------
Listing 15.31. Wyzwalacz korzystający z warunku UPDATE

CREATE TRIGGER movie_update
ON Movies
FOR UPDATE
AS
BEGIN
IF UPDATE(movie_title)
BEGIN
ROLLBACK TRANSACTION
PRINT "Tytuł filmu nie może być zmieniony."
END
END
--------------------------------

Poniżej (listing 15.32) znajduje się prezentacja działania wyzwalacza - wszystkie kolumny tabeli Movies, z wyjątkiem movie_title mogą być swobodnie modyfikowane.
--------------------------------
Listing 15.32. Testowanie wyzwalacza movie_update

UPDATE Movies
SET movie_title - 'Nowy Tytuł'
WHERE movie_id = 1

Tytuł filmu nie może być zmieniony.

UPDATE Movies
SET budget = 25
WHERE movie_id = 1

(1 row(s) affected)
--------------------------------

W praktyce

Za jedną z najistotniejszych cech procedur składowanych należy uznać możliwość tworzenia przy ich pomocy pewnej warstwy pośredniej (interfejsu) między bazą danych, a aplikacją. Programista nie musi budować całej serii zapytań SQL we własnych programach, wystarczy, że wywoła odpowiednie procedury bazy danych, które wykonają całą właściwą pracę na danych z bazy.
Pracując w zespole budującym komercyjną witrynę WWW, zrealizowaliśmy wszystkie odwołania do bazy danych poprzez procedury składowane. Aplikacja strony WWW nie zawierała w sobie żadnych zapytań SQL, jedynie wywołania procedur. Wszystkie reguły biznesowe zdstały osadzone w procedurach składowanych (jeżeli nie były one w stanie wykonać czegoś samodzielnie w SQL-u, wywoływały nawet programy zewnętrzne, które realizowały to zadanie dla nich).
Główną korzyścią wynikającą ze zbudowania tego, swego rodzaju, interfejsu programowania aplikacji było odizolowanie wszystkich złożonych procesów zachodzących w bazie danych od aplikacji zarządzającej witryną. W tym konkretnym
#361
przypadku, architektura bazy danych była bardzo złożona, dlatego opłacało się umieścić wszystkie zapytania wewnątrz procedur składowanych, ponieważ dzięki temu programiści bazy danych mogli udostępnić programistom aplikacji statyczny interfejs bazy, natomiast sami zajęli się jej optymalizacją i poprawą wydajności.
Często, kiedy zapytania SQL osadzane są w aplikacji, programiści bazy danych muszą przeglądać jej kod, aby pomóc programistom aplikacji w stworzeniu zapytań zoptymalizowanych pod względem wydajności. Wprowadzenie warstwy pośredniej, jaką są procedury składowane, usuwa ten problem, uniezależniając twórców aplikacji od administratorów baz danych.
Kolejna korzyść wynikająca ze stosowania proceduralnego interfejsu bazy danych, to pozostawienie furtki dla nowych rozwiązań, jakie pojawią się w przyszłości. Jak wiadomo, związki między danymi są implementowane w różny sposób, w zależności od tego, czy są to powiązania typu jeden-do-jeden, jeden-do-wielu lub wiele-do-wielu. Jeżeli zależność między dwoma danymi ulegnie zmianie, modyfikacji wymagać będą również zapytania, które na tych danych operują. Jeśli tabela zostanie podzielona na kilka mniejszych, ze względu na zmianę zależności między danymi z jeden-do-jeden na jeden-do-wielu, tam gdzie do tej pory używane było pojedyncze wyrażenie UPDATE, INSEKT lub DELETE, teraz potrzebnych będzie kilka wyrażeń tego typu. Jeżeli zapytania używane przez aplikacje zostały zaimplementowane w jej kodzie, każda zmiana organizacji bazy danych, również ta opisana powyżej, wymaga modyfikacji zapytań w całej aplikacji. Umieszczenie zapytań w procedurze składowanej oznacza, że tylko ona wymagać będzie modyfikacji, natomiast sama aplikacja pozostanie niezmieniona.
Zastosowanie procedur składowanych daje możliwość wielokrotnego wykorzystania tego samego kodu. Zamiast duplikować zapytanie w kilku miejscach aplikacji, wystarczy zastąpić je odwołaniami do procedury składowanej, zawierającej to zapytanie. Podobne rozwiązanie może zostać zrealizowane po stronie aplikacji, przez umieszczenie zapytań wewnątrz funkcji - ustępuje ono jednak pod względem korzyści, jakie daje odseparowanie kodu aplikacji od kodu bazy danych.
Tworząc aplikacje współpracujące z bazami danych, niezależnie od tego czy są one przeznaczone dla Internetu, czy też dla środowiska klient-serwer, można zyskać wiele na wydajności przez maksymalne "upakowanie" kodu bazy danych w procedurach składowanych. Ostatnia uwaga - przenoszenie aplikacji z jednego środowiska programowania do innego jest ułatwione przy zastosowaniu procedur bazy danych, ponieważ programiście pozostaje mniej kodu wymagającego przetransfor-mowania na nową platformę. Przeniesienie bazy danych do nowego środowiska jest zazwyczaj o wiele prostsze, niż przeniesienie aplikacji do nowego środowiska programowania, dlatego opłaca się budować reguły biznesowe po stronie bazy danych.