#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żnoci 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 wczeniej implementowane były wyłšcznie po stronie aplikacji. Użycie procedur składowanych wišże się z kilkoma korzyciami. 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. Jeli jest to jedyna aplikacja korzystajšca z bazy danych, można w całoci 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ć bezporednio 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żliwoci 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 kolejnoci zajmiemy się procedurami w języku Transact-SQL (temat ten będzie kontynuowany w rozdziale 16.), natomiast póniej, w rozdziale 17., wyjanimy 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 okrelonego 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 integralnoci 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 korzyci. 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 wartoci 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, wyjanienia wymaga pewna ogólna cecha procedur -brak możliwoci 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 czynnoci 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 bezporedniego 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 oczywicie 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š wartociš, która użyta zostaje w klauzuli WHERE. Znak @ wskazuje, iż okrelony parametr jest zmiennš, a nie nazwš kolumny tabeli lub innego typu parametru. Do uruchomienia procedury mo...
sliwak