#363 Rozdział 16. Procedury składowane w języku Transact-SQL
W poprzednim rozdziale opisane zostały podstawowe techniki programowania procedur składowanych, w tym użycie wyrażeń warunkowych IF oraz pętli WHILE. Przedstawione zostały również zasady programowania procedur składowanych, deklarowania zmiennych oraz przekazywania wartości do procedur poprzez parametry. Celem tego rozdziału jest przedstawienie kilku zaawansowanych technik tworzenia procedur składowanych (związanych głównie z Transact-SQL). Następny rozdział poświęcony jest procedurom składowanym tworzonym przy użyciu PL/SQL - języka proceduralnego wbudowanego w bazę danych Oracle. Na kolejnych stronach omówimy obiekty tymczasowe, przechowujące dane używane przez procedury składowane, metody tworzenia kodu obsługującego błędy w procedurach Transact-SQL, a także kilka bardziej zaawansowanych technik programowania wyzwalaczy. Ogólne informacje na temat programowania w Transact-SQL Aby obejrzeć efekt działania napisanego samodzielnie bloku kodu w Transact-SQL, trzeba uruchomić go poleceniem GO. Procedury składowane również składają się z bloków kodu, ale nie korzystają w nich z polecenia GO, ponieważ wszelkie dane wyjściowe generowane przez procedurę są w niej przechowywane aż do momentu jej zakończenia.
Zmienne globalne
Zmienne tworzone są przy użyciu wyrażenia DECLARE, natomiast do zmiany ich wartości służy instrukcja SELECT (zmienna umieszczana jest w miejscu zajmowanym normalnie przez literał lub nazwę kolumny). Oprócz zmiennych tworzonych #364 przez użytkownika, istnieje również zbiór zmiennych będących własnością bazy danych, nazywanych wspólnie zmiennymi globalnymi. W niektórych przypadkach użytkownik ma prawo modyfikować wartości zmiennych globalnych, ale nie może ich tworzyć. Wartości przechowywane w zmiennych globalnych są dostępne dla wszystkich użytkowników, natomiast same zmienne globalne są łatwe w identyfikacji, ponieważ poprzedza je niejeden, lecz dwa znaki @. =================== Uwaga Użytkownicy mogą tworzyć zmienne rozpoczynające się od dwóch znaków @@, np. @@zmienna, co jednak wcale nie czyni ich zmiennymi globalnymi. Są to zwykłe zmienne nazwane w mylący sposób. =====================
Tabela 16.1 zawiera listę niektórych bardziej istotnych zmiennych globalnych. Druga kolumna ("Baza danych") wskazuje, które z baz danych korzystają z danej zmiennej. Pełnej listy zmiennych globalnych należy szukać w dokumentacji konkretnej bazy danych. -------------------------------- Tabela 16.1. Zmienne globalne w bazach danych Transact-SQL
Zmienna Baza danych Zawartość
@@connections; MS, Sybase; Liczba prób połączeń od momentu ostatniego uruchomienia bazy danych
@@cursor_rows; MS; Liczba wierszy zwróconych przez kursor otwarty ostatnio przez użytkownika (wartość specyficzna dla każdego użytkownika). Jeżeli nie otwarto żadnego kursora lub kursor otwarty jako ostatni został usunięty, zmienna przyjmuje wartość 0
@@error; MS, Sybase; Kod błędu ostatniego wyrażenia wykonanego przez użytkownika. Wartość zmiennej jest specyficzna dla każdego użytkownika. Jeżeli ostatnie polecenie zakończyło się pomyślnie, zmienna przyjmuje wartość 0.
@@fetch_status; MS; Wynik ostatniej operacji FETCH wykonanej na kursorze. Wartość O wskazuje pomyślną operację,-1 oznacza błąd lub wyczerpanie się wierszy w kursorze, -2 - wiersz nie został znaleziony
@@identity; MS, Sybase; Specyficzna dla każdego użytkownika wartość, wstawiona jako ostatnia do kolumny identyfikacyjnej. Jeżeli ostatnia operacja INSEKT została wykonana na tabeli nie posiadającej kolumny identyfikacyjnej, wartość zmiennej wynosi NOLL. Zmienna jest przydatna, jeżeli zachodzi potrzeba wydobycia klucza głównego ostatniego wiersza wstawionego do tabeli zawierającej kolumnę identyfikacyjną
@@max_connections; MS, Sybase; Liczba jednoczesnych połączeń, jaką dopuszcza komputer z serwerem bazy danych
@@max_precision; MS; Liczba cyfr na prawo od przecinka w typach danych DECIMAL i NDMERIC. Domyślnie 28 #365 @@nestievel; MS, Sybase; Wartość określająca poziom zagnieżdżenia. Początkowo równa 0. Zwiększana o jeden za każdym razem, kiedy procedura zostanie wywołana z innej procedury. Zatem, jeśli procedura wywoła inną procedurę, która z kolei wywoła kolejną, zmienna przyjmie wartość 2. Maksymalna wartość zmiennej wynosi 16. Po przekroczeniu tej wartości transakcja jest przerywana, aby uniknąć rekursji w nieskończoność
@@options; MS; Wartość przechowująca opcje przetwarzania zapytań (usta- wione poleceniem SET) dla bieżącej sesji.
@@rowcount; MS, Sybase; Liczba wierszy, na które wpłynęło ostatnie zapytanie. Jeżeli wyrażenie użyte w zapytaniu nie miało wpływu na żaden wiersz (jak np. CREATE TABLE), wartość zmiennej wynosi 0
@@servername; MS, Sybase; Nazwa serwera, na którym zainstalowana jest baza danych.
@@spid; MS, Sybase; Identyfikator procesu serwera bazy danych.
@@sqlstatus; Sybase; Wartość określająca status bieżącego kursora. 0 - ostatnia operacja pobrania zakończyła się pomyślnie, 1 - wystąpił błąd, 2 - kursor wyczerpał wszystkie wiersze
@@version; MS,Sybase; Data, numer wersji bazy danych oraz typ procesora, dla którego jest ona przeznaczona. -----------------------------
Zmienne globalne mogą być używane wszędzie tam, gdzie dopuszcza się stosowanie zmiennych lokalnych. Przykładem niech będzie wyświetlenie wersji bazy danych, zawartej w zmiennej @@version, przy pomocy zwykłego polecenia PRINT -listing 16.1. ----------------------------- Listing 16.1. Zastosowanie zmiennej globalnej
PRINT @@version
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright 1988-1998 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 4) -----------------------------
Najistotniejszą różnicą występującą pomiędzy zmiennymi globalnymi jest to, że niektóre z nich zawierają wartości specyficzne dla każdej sesji, podczas gdy inne przechowują wartości globalne względem wszystkich użytkowników. Na przykład zmienna @@error reprezentuje kod błędu ostatniego wyrażenia wykonanego przez bieżącego użytkownika. Za każdym razem, kiedy użytkownik pobiera wartość tej zmiennej, wskazuje ona kod błędu wykonanej przez niego operacji, nawet jeśli w międzyczasie inni użytkownicy wykonali swoje własne zapytania. Dla odmiany, są również zmienne, między innymi @@version i @@servername, których wartości mają faktycznie charakter globalny, tzn. zwracają zawsze tę samą wartość, niezależnie od tego w której sesji są wykorzystywane. #366 Przerywanie pracy procedur - RETURN
Polecenie RETURN służy do natychmiastowego wstrzymania przetwarzania bloku programu lub procedury składowanej w języku PL/SQL. Kiedy wyrażenie to napotkane zostanie wewnątrz procedury, jest ona przerywana i następuje powrót do miejsca jej wywołania.
Obsługa błędów
Problem odpowiedniego przetwarzania błędów spędza sen z powiek niejednemu programiście. Trzeba określić, kiedy program powinien spodziewać się wystąpienia nieprawidłowych sytuacji oraz jak powinien się zachować w każdej z nich. Większość programów obsługuje błędy poprzez sprawdzanie określonych warunków w czasie wykonania programu i przerywanie go, jeżeli okaże się, że któryś z tych warunków wykracza poza przyjęte normy. Najprostszy sposób informowania użytkownika o zaistniałym błędzie polega na wyświetleniu odpowiedniego komunikatu poleceniem PRINT. Rozwiązanie to jest jednak mało użyteczne, ponieważ nie przerywa wykonania bloku kodu i nie informuje samej bazy danych o zaistniałym błędzie. Wykorzystanie polecenia PRINT sprowadza się na ogół do generowania komunikatów diagnostycznych, dzięki którym użytkownik wie co się dzieje w danej chwili z wykonywanym programem.
RAISERROR
Polecenie RAISERROR kończy działanie programu i informuje o powstałym błędzie. Jest bardzo elastyczne - przy jego pomocy można generować błędy definiowane przez użytkownika z własnymi komunikatami lub błędy odpowiadające komunikatom systemowym. Istnieją pewne różnice w implementacji polecenia RAISERROR na platformach Microsoft i Sybase. Dotyczą one przede wszystkim składni i zakresu kodów błędów przeznaczonych dla systemu i użytkownika. Składnia polecenia RAISERROR w wersji Microsoft wygląda następująco:
Składnia w obu przypadkach wydaje się być skomplikowana - na szczęście większość elementów jest opcjonalna. Jeżeli chcemy, aby napisane przez nas polecenie RAISERROR działało w obu systemach, Sybase i Microsoft, jego składnia powinna wyglądać następująco:
RAISERROR numer błędu 'komunikat błędu' #367 Wartość kodu błędu, którą można bezpiecznie użyć na obu platformach, wynosi 50000. W dalszej części rozdziału omówione zostaną kolejno parametry RAISERROR specyficzne dla Microsoft i Sybase.
RAISERROR w Microsoft SQL Sewer
Wywołując polecenie RAISERROR w MS SQL Sewer zazwyczaj określa się numer błędu lub komunikat błędu. Wartością graniczną między kodami błędów systemowych, a kodami błędów użytkownika jest liczba 50000. Jeżeli numer błędu nie zostanie określony jawnie, system nada mu wartość domyślną równą 50000. (Błędy zawierające komunikat, zamiast numeru błędu nazywane są błędami ad hoc.) Drugi argument polecenia w MS SQL Server określa stopień powagi błędu. Wartość tego parametru określa typ błędu, jaki wystąpił oraz rodzaj akcji, jaką powinien podjąć program. Stopnie powagi błędów używane w SQL Server zestawione zostały w tabeli 16.2. -----------------------------
Tabela 16.2. Stopnie powagi błędów w Transact-SQL
Kod; Opis producenta bazy; Znaczenie
00-01; Miscellaneous, System Information; Ogólny komunikat informujący o wystąpieniu pewnego zdarzenia (ale nie błędu) 02-06; Reyersed; Kody nie używane w obecnej wersji. 07; Notification: Status Information; Komunikat informujący o statusie zdarzenia 08; Notification: User Intervention Required; Informacja o wystąpieniu zdarzenia, które wymaga podjęcia akcji przez użytkownika O9; User Defined ; Poziom zarezerwowany dla komunikatów użytkownika umieszczonych w tabeli sysmessages lub stworzonych na zasadach "ad hoc." Kiedy tworzysz własne komunikaty błędów powinieneś nadawać im ten stopień powagi 10; Information; Komunikaty związane z działalnością użytkowników DBA (administratorów) 11; Specified Database, Object Not Found; Element wyspecyfikowany w zapytaniu nie został znaleziony 12; Unused; Nieużywany 13; User Transaction, Syntax Errors; Nieprawidłowe użycie kodu transakcji. Wartość ta pojawia się, gdy wyrażenia zamykające transakcję (COMMIT i ROLLBACK) nie pasują do wyrażeń BEGIN TRAN 14; Insufficient Permissions; Użytkownik nie posiada uprawnień do wykonywania operacji, którą, zamierzał przeprowadzić. Powodem takiej sytuacji może być brak dostępu do określonego obiektu lub próba naruszenia więzów integralności 15; Syntax Error in SQL, Statement; Baza danych nie jest w stanie przeanalizować wyrażenia SQL wprowadzonego przez użytkownika (autor wyraża głęboką nadzieję, iż po przeczytaniu tej książki użytkownik nigdy nie spotka się z tym typem błędu) #368 16; Miscellaneous User Error; Stopień powagi często związany z błędami programistów. Jego obecność może mieć również związek z niepoprawną konwersją typów lub nieprawidłowym użyciem obiektu 17; Insufficient Resources; Stopień powagi towarzyszący błędom generowanym w wyniku wyczerpania się pewnego zasobu bazy danych. Może to być np. wyczerpanie blokad, wolnego miejsca w bazie tempdb lub ogólny brak miejsca w pamięci. Kiedy zajdzie taka sytuacja generowany jest błąd ze stopniem powagi 17., natomiast proces, który wyczerpał zasoby jest przerywany 18; Nonfatal Internal Error; Kolejny stopień powagi mający związek z błędami programistów. Błędy tego typu są zazwyczaj rezultatem problemów zdalnych połączeń z bazą danych 19; SQL Server Fatal Error in Resource; Błąd generowany po osiągnięciu przez SQL Server pewnego limitu zasobów 20; SQL Server Fatal Error in Current Process; Wyrażenie napotkało błąd, który nie dotyczy bazy danych lub serwera w ogólności 21; SQL Server Fatal Error in Database (dbid) Processes; Wystąpił błąd mający wpływ na inne procesy korzystające z bazy danych, ale nie na samą bazę danych 22; SQL Server Fatal Error Table Integrity Suspect; Tabela lub indeks wymieniony w komunikacie jest uszkodzony i prawdopodobnie wymaga usunięcia, a następnie ponownego odtworzenia 23; SQL Server Fatal Error: Database Integrity Suspect; Podejrzenie uszkodzenia całej zawartości bazy danych. Problem można próbować rozwiązać przez restart bazy danych lub uruchomienie narzędzi diagnostycznych DBCC 24; Hardware Error; Problem z serwerem dotyczący sprzętu - zazwyczaj ma związek z nośnikiem na którym przechowywana jest baza danych 25; Internal System Error; Wewnętrzny błąd Servera SQL -----------------------------------
Określając stopień powagi błędu można zadecydować o tym, czy po jego wystąpieniu procedura (lub inny blok kodu) będzie dalej wykonywana, a także czy błąd jest na tyle poważny, że należy zakończyć sesję użytkownika. Od stopnia powagi błędu zależy również, czy zostanie on wpisany do dziennika błędów Windows NT. Argumentowi określającemu stan nadaje się na ogół wartość 1 -jest ona zmieniana tylko wtedy, gdy określony komunikat błędu wymaga wyspecyfikowania stanu. Argument ten może przyjmować wartości z zakresu od 1 do 127. Lista_argumentów zawiera zmienne, których wartości są podstawiane w miejsce znaczników użytych w treści komunikatu błędu. Znaczniki mogą być umieszczane w komunikatach błędów ad hoc tworzonych przez użytkowników. Również niektóre komunikaty systemowe zawierają znaczniki zastępowane wartościami z listy argumentów. #369 Znaczniki umieszczane w treści komunikatów reprezentują określone typy danych, zgodnie z formą % typ_danych. Przykład użycia znaczników i zmiennych przedstawia listing 16.2 - znacznik % s (reprezentujący łańcuch) jest zastępowany zmienną @ lan. -------------------------------- listing 16.2. Polecenie RAISERROR korzystające z dodatkowych argumentów
Server: Msg 50000 Level 18 State 1, Linę 3 Ten komunikat zawiera znacznik: łańcuch
Tabela 16.3. zawiera kompletną listę znaczników reprezentujących różne typy danych. --------------------------------
Tabela 16.3. Znaczniki używane w poleceniu RAISERROR
Znacznik Typ danych d lub i Liczba całkowita ze znakiem O Liczba ósemkowa bez znaku P Wskaźnik S Łańcuch U Liczba całkowita bez znaku x lub x Liczba szesnastkowa bez znaku --------------------------------
SQL Server umożliwia uruchomienie polecenia RAISERROR z trzema dodatkowymi znacznikami logicznymi. Pierwszy, WITH LOG, określa czy wywołaniu polecenia powinien towarzyszyć wpis do dziennika zdarzeń (ang. event log) Windows NT. Ze znacznika tego mogą korzystać jedynie użytkownicy o uprawnieniach administratora systemu (WITH LOG jest obowiązkowy przy stopniu powagi błędu na poziomie 19. lub wyższym). Znacznik NOWAIT nakazuje natychmiastowe przesłanie komunikatu błędu do użytkownika. Trzeci znacznik, SETERROR, powoduje przypisanie bieżącego numeru błędu do zmiennej @@error (50000 w przypadku błędów ad hoc). W normalnych warunkach do zmiennej @@error wstawiane są jedynie numery błędów o stopniu powagi 19. i wyższych.
RAISERROR w Sybase
Składnia RAISERROR w Sybase jest podobna do składni używanej w MS SQL Se-rver, z pewnymi istotnymi różnicami. W przeciwieństwie do SQL Server, Sybase wymaga wyspecyfikowania zarówno numeru błędu, jak i samego komunikatu. Liczby od 17000 do 19999 są zarezerwowane dla błędów systemowych, których komunikaty można znaleźć w tabeli master. . sysmessages. Komunikatom błędów użytkownika powinny być przydzielane wartości od 20000 wzwyż. Jeżeli wyrażenie RAISERROR zostało użyte z kodem błędu powyżej 20000, ale bez komunikatu, następuje próba jego odszukania w tabeli sysmessages lokalnej bazy danych. #370 Komunikat może być zwykłym łańcuchem, łańcuchem zawierającym znaczniki argumentów lub zmienną. Rozwiązaniem możliwym do zastosowania jest zbudowanie treści komunikatu w zmiennej, np. @error_msg, a następnie użycie jej w poleceniu RAISERROR:
RAISERROR 20001 @error_msg
Sybase, podobnie jak SQL Server, pozwala na stosowanie znaczników wewnątrz komunikatów błędów. Argumentami wstawianymi w miejsce znaczników mogą być zmienne lub literały. Oczywiście tworząc własne komunikaty błędów raczej rzadko korzysta się ze znaczników, ich użycie ma jednak sens w przypadku komunikatów systemowych lub zdefiniowanych przez innych użytkowników. Oto przykład użycia literału do budowy komunikatu:
Wiemy już, jak zgłaszać błędy w Transact-SQL, teraz musimy nauczyć się je wyłapywać w trakcie wykonania programu. Najpopularniejsza metoda opiera się na wykorzystaniu zmiennej @@error. Jak już wcześniej wspominaliśmy, prawidłowe wykonanie wyrażenia kwitowane jest wartością O w zmiennej @@error. W przeciwnym przypadku w zmiennej umieszczany jest kod błędu. Używając wyrażeń warunkowych można testować zmienną @@error i odpowiednio reagować na wszelkie nieoczekiwane wartości. Druga opcja rozpoznawania błędów bazuje na zmiennej @@rowcount. Jeżeli mamy pewność, że zapytanie wysłane do bazy danych wpłynie na określoną liczbę wierszy, po jego wykonaniu możemy sprawdzić wartość @@rowcount, aby przekonać się, czy zapytanie faktycznie zrealizowało swoje zadanie. W przypadku, gdy tak się nie stało można użyć polecenia RAISERROR do zgłoszenia zaistniałej usterki. Przyjrzyjmy się kilku przykładom. Pierwszy z nich (listing 16.3) zawiera fragment kodu, który próbuje uaktualnić pewien zbiór wierszy w tabeli Movies. Po wykonaniu wyrażenia UPDATE, testowana jest wartość zmiennej @@error. W ten sposób dowiadujemy się, czy zapytanie wykonało się pomyślnie. Jeśli nie, generowany jest błąd. Dodatkowo, zapytanie i kod testujący wystąpienie błędu zamknięte zostały we wspólnej transakcji, aby uniknąć zatwierdzenia częściowej aktualizacji tabeli. -------------------------- Listing 16.3. Fragment kodu używający zmiennej @@error do wykrywania błędów
BEGIN TRAN 1
UPDATE Movies SET movie_title = NULL WHERE movie_id > 5 IF @@error <> 0 BEGIN ROLLBACK TRAN RAISERROR('Uaktualnienie tabeli Movies nie powiodło się.', 18, 1) END ELSE COMMIT Transact-SQL #371 Server: Msg 515, Level 16, State 2, Linę 0 Cannont insert the value NULL into column 'movie_title', table 1 rafeco.dbo.Movies'; column does not allow nulls. UPDATE fails. The statement has been terminated. Server: Msg 50000, Level 18, State 1. Linę 10 Uaktualnienie tabeli Movies nie powiodło się. --------------------------
Używając zmiennej @@rowcount można wykrywać sytuacje, w których wykonywane wyrażenia nie przynoszą oczekiwanego efektu. Na przykład, jeśli spodziewamy się, że wyrażenie DELETE usunie jakieś wiersze z tabeli, możemy to potwierdzić sprawdzając wartość zmiennej @@rowcount po wykonaniu zapytania. W tym przypadku (listing 16.4) nie ma potrzeby stosowania transakcji, ponieważ ewentualny błąd będzie oznaczał brak usuniętych wierszy. -------------------------- Listing 16.4. Kod sprawdzający wystąpienie błędu w oparciu o zmienną @@rowcount
DELETE FROM Movies WHERE movie_id > 10 IF SSrowcount = 0 RAISERROR ('Nie usunięto żadnych wierszy', 18, 7) Server: Msg 50000, Level 18, State 7, Linę 5 Nie usunięto żadnych wierszy --------------------------
Wyrażenie RETURN
Wyrażenie RETURN jest kolejnym elementem umożliwiającym obsługę błędów wewnątrz procedur. Napotkanie polecenia RETURN w programie Transact-SQL powoduje wstrzymanie jego działania i powrót do miejsca, z którego program ten został wywołany (może to być klient bazy danych lub inna aplikacja odwołująca się do bazy poprzez procedury składowane). Polecenie RETURN wywołane wewnątrz procedury składowanej może zawierać kod powrotu, na podstawie którego program nadrzędny będzie w stanie określić wynik jej działania. Składnia polecenia RETURN wygląda następująco:
RETURN [(kod powrotu)]
Kod powrotu jest zwykłą liczbą całkowitą. Celem wyrażenia RETURN jest przerwanie przetwarzania bloku kodu lub procedury bez generowania błędu. RETURN jest zazwyczaj stosowany w blokach kodu zawierających więcej niż jedno zapytanie. Jeżeli wynik pierwszego zapytania wskaże, że następne z nich nie powinny być już wykonywane, można użyć polecenia RETURN, aby opuścić blok kodu i przejść do wykonania dalszej części programu. Oczywiście, wystarczyłoby umieścić pozostałe zapytania wewnątrz wyrażenia warunkowego, które zostałoby wykonane tylko przy spełnieniu odpowiednich kryteriów, niemniej jednak użycie polecenia RETURN jest lepszym rozwiązaniem z dwóch powodów. Po pierwsze, jeżeli sytuacja rozgrywa się wewnątrz procedury składowanej, RETURN może zwrócić odpowiedni kod informujący program nadrzędny o tym, co zaszło w procedurze. Po drugie, jeżeli program jest skomplikowany, o wiele łatwiej jest napisać wyrażenie warunkowe, które uruchomi RETURN, niż obejmować blokiem całą pozostałą część programu, aby uzależnić jej wykonanie od spełnienia określonego warunku. #372 Przyjrzyjmy się przykładowi użycia wyrażenia RETURN w procedurze składowanej. Jak już wcześniej wspomniano, zastosowanie tego polecenia ma sens jeżeli procedura składowana wykonuje więcej niż jedno zapytanie. W tym przypadku procedura zwiększa dochód każdego filmu o 25%, gdy kwota ta znajduje się poniżej określonej wartości. Jeżeli zapytanie zmodyfikuje jakiekolwiek wiersze, zostaną one wydobyte przez następne zapytanie, w przeciwnym wypadku procedura zakończy się zwróceniem liczby 1. Omawiany przykład znajduje się w listingu 16.5. -------------------------- Listing 16.5. Procedura napisana z użyciem polecenia RETURN
CREATE PROCEDURĘ update_movie_gross @gross_limit DEC = NULL AS UPDATE Movies SET gross - gross * 1.25 WHERE gross < @gross_limit
IF @@rowcount = 0 RETURN 1
SELECT movie_title, gross FROM Movies WHERE gross < @gross_limit * 1.25
The command(s) completed successfully. --------------------------
Definiowanie własnych komunikatów błędów
Użytkownicy mogą tworzyć własne komunikaty błędów w tablicy master.. sysmessages, używając do tego celu procedury systemowej sp_addmessage. Jej składnia wygląda następująco:
Do stworzenia prostego komunikatu błędu, o stopniu powagi równym 7. (ostrzeżenie), należy użyć następującego polecenia:
sp_addmessage 50010, 7, 'To jest komunikat błędu.'
Jeżeli komunikat o tym numerze już istnieje, wywołaniu funkcji sp_addmessage towarzyszyć musi słowo REPLACE. Długość komunikatów jest ograniczona do 255 znaków. Do wywołania stworzonego powyżej komunikatu można użyć polecenia RAISERROR:
RAISERROR 50010
Obiekty tymczasowe
Niektórych zadań nie da się zrealizować poprzez pojedyncze zapytanie. Trzeba wtedy umieścić gdzieś dane, aby procedura mogła przeprowadzić na nich dalsze operacje. Ponieważ nie chcemy zmieniać danych bezpośrednio w tabelach, trzeba znaleźć dla nich inne, tymczasowe miejsce, w którym będzie można je dowolnie #373 modyfikować. Niektóre systemy relacyjnych baz danych oferują do tego celu specjalne struktury danych, takie jak tablice, czy tablice rekordów. Transact-SQL nie posiada takich możliwości - jest jednak w stanie przydzielać miejsce do przechowywania tymczasowych danych w inny sposób. Do tej pory przedstawione zostały dwie metody pozwalające zapamiętywać informacje w trakcie wykonywania programu języka Transact-SQL. Dane można zapisać w zmiennej, która będzie je pamiętać przez cały czas trwania programu lub w jednej z tabel bazy danych, gdzie pozostaną aż do ich usunięcia. Innym miejscem, w którym można tymczasowo składować dane w celu ich obróbki lub późniejszego wydobycia jest tempdb. Bazy danych oferują kilka typów obiektów tymczasowych - różnice między nimi polegają na czasie życia oraz dostępności dla użytkowników. Najpowszechniejszą strukturą tymczasową (o której była mowa w rozdziale poprzednim) jest zmienna. Zmienne nie korzystają z tempdb (ich zawartość przechowywana jest w pamięci).
tempdb - zasada działania
tempdb jest specjalną bazą danych, tworzoną w ramach każdej instalacji SQL Sewera. To, co odróżnia tempdb od innych baz danych, to możliwość tworzenia obiektów przez użytkowników oraz kasowanie całej zawartości bazy przy każdym restarcie serwera. Żaden obiekt umieszczony w bazie tempdb nie przetrwa restartu serwera, a wiele z nich przestanie istnieć w jeszcze krótszym czasie. W zależności od sposobu utworzenia elementu, jego żywotność może zostać ograniczona do czasu trwania sesji, w której element ten został zdefiniowany lub nawet do czasu wykonania procedury, w której dany obiekt powołany został do życia. tempdb służy ponadto jako miejsce przechowywania tymczasowych tabel tworzonych przez bazę w czasie wykonywania bardziej skomplikowanych zapytań. Wiąże się z tym pewne zagrożenie - kiedy zajęty zostanie cały obszar pamięci przydzielony bazie tempdb, zapytania wymagające dostępu do tego obszaru będą wstrzymywały swoje działanie do chwili, kiedy otrzymają dostatecznie dużo miejsca, aby móc wznowić wykonanie.
Tworzenie tymczasowej tabeli
Sposób tworzenia tabeli tymczasowej w bazie tempdb zależy od tego, komu mają być przydzielone prawa jej przeglądania i modyfikowania, a także jak długo ma istnieć sama tabela. Jedna z metod tworzenia tabeli tymczasowej polega na poprzedzeniu jej nazwy znakiem hash (#). Utworzona w ten sposób tabela jest przechowywana w bazie tempdb i automatycznie usuwana w chwili zakończenia bieżącej sesji lub, jeśli została utworzona wewnątrz procedury składowanej, w chwili kiedy zakończone zostanie wykonanie tej procedury. Przykład polecenia tworzącego tabelę tymczasową znajduje się w listingu 16.6. #374 -------------------------- Listing 16.6. Utworzenie kopii tabeli Movies w bazie tempdb
The command(s) completed successfully. --------------------------
Po utworzeniu tabeli tymczasowej, można przeprowadzać na niej operacje typu IN-SERT, UPDATE, DELETE i SELECT, w taki sam sposób jak na każdej innej tabeli bazy danych. Tabel tymczasowych można również używać w złączeniach z innymi tabelami tymczasowymi lub zwykłymi.
Klasy obiektów tymczasowych
Obiekty tymczasowe klasyfikowane są według praw dostępu do każdego z nich. Rozróżnia się dwie klasy: lokalną i globalną. Dostęp do obiektów lokalnych ma jedynie użytkownik, który je utworzył, natomiast obiekty globalne są dostępne dla wszystkich użytkowników bazy danych. Obiekty lokalne istnieją do momentu zakończenia sesji lub procedury, w trakcie której zostały utworzone, z kolei czas życia obiektów globalnych mija dopiero w chwili restartu bazy danych. Tworzenie obiektów globalnych o zasięgu sesji czy procedury mija się z celem, ponieważ czas ich istnienia jest zbyt krótki, aby inni użytkownicy byli w stanie z nich skorzystać. Opisana wcześniej technika tworzenia obiektów tymczasowych przy użyciu znaku # dotyczy obiektów lokalnych. Tabela 16.4. zawiera listę klas obiektów tymczasowych dostępnych w Transact-SQL. --------------------------
Tabela 16.4. Klasy obiektów tymczasowych
Klasa Platforma Przedrostek Dostęp Czas życia
Tabele lokalne obie # Lokalny Bieżąca sesja lub procedura składowana Procedury lokalne MS # Lokalny Bieżąca sesja lub procedura składowana Globalne tabele, widoki, procedury MS # Globalny Bieżąca sesja Trwałe obiekty tymczasowe obie brak Globalny Restart bazy danych Modele obiektów obie brak Globalny Tworzone w chwili startu bazy danych, usuwane w chwili jej zamknięcia Tabele robocze obie wewnętrzny Tworzone przez samą bazę, istnieją do momentu zakończenia zapytania, które je utworzyło ---------------------------------- #375 Trwałe obiekty tymczasowe
Trwałe obiekty tymczasowe są standardowymi obiektami bazy danych tworzonymi zamiast tabeli tymczasowych w tempdb. Po utworzeniu standardowego obiektu w tempdb, istnieje on do momentu zamknięcia bazy danych lub do chwili jego jawnego usunięcia. W przeciwieństwie do obiektów tymczasowych, obiekty trwałe utworzone w tempdb są dostępne dla wszystkich użytkowników bazy danych. W przypadku informacji szczególnie cennych, lepiej jest zapamiętać je w zakątku innej bazy danych, a następnie usunąć, gdy zajdzie taka potrzeba, niż ryzykować udostępnienie innym użytkownikom przez zapisanie w bazie tempdb.
WAITFOR
Wyrażenie WAITFOR wprowadza zwłokę czasową do bloku poleceń Transact-SQL. Istnieją dwie formy tego wyrażenia: WAITFOR DELAY odczekuje określoną ilość czasu, po czym wznawia wykonanie kodu, WAITFOR TIME oczekuje nadejścia odpowiedniego czasu i dopiero wtedy przechodzi do dalszej części programu. Druga forma polecenia (WAITFOR TIME) umożliwia tworzenie zadań harmonogramowych. Załóżmy, że przygotowaliśmy dla bazy danych skomplikowane zadanie do wykonania, które wymagać będzie dużej mocy obliczeniowej. Zależy nam więc na tym, aby zadanie to zostało wykonane przez bazę w chwili, kiedy korzysta z niej minimalna liczba użytkowników. W takim przypadku używamy wyrażenia WAITFOR TIME i ustawiamy czas wykonania zadania np. na północ. Jeśli dla odmiany chcemy, żeby zadanie rozpoczęło się np. 30 minut po naszym wyjściu z biura, używamy polecenia WAITFOR DELAY. Polecenie WAITFOR DELAY przyjmuje argument będący czasem wyrażonym w godzinach, minutach i sekundach:
WAITFOR DELAY godziny:minuty:sekundy
Poniższe polecenie zatrzymuje wykonanie programu na pięć minut:
WAITFOR DELAY 00:05:00
Jeżeli okres oczekiwania ma trwać do momentu spełnienia określonego warunku, można to zrobić łącząc WAITFOR DELAY z pętlą WHILE (i odpowiednim warunkiem):
WHILE (SELECT COUNT(*) FROM Movies = 10) WAITFOR DELAY 00:10:00
Składnia polecenia WAITFOR TIME jest bardzo podobna do WAITFOR DELAY:
WAITFOR TIME godziny:minuty:sekundy:milisekundy
==================== Rada Elementy łańcucha czasu czytane są od największego do najmniejszego, zatem pomijając dowolny element usuwamy najmniejszą jednostkę specyfikacji czasu. Na przykład 10:05 oznacza 10 godzin i 5 minut, a nie 10 sekund i 5 milisekund. ===================== #376 Zaawansowane techniki tworzenia wyzwalaczy
Rola wyzwalaczy oraz metody ich tworzenia przedstawione zostały w poprzednim rozdziale. Teraz zajmiemy się kilkoma zaawansowanymi technikami ich wykorzystania.
ROLLBACK TRIGGER
Jak już wcześniej wyjaśnialiśmy (w rozdziale 13.) polecenie ROLLBACK anuluje wszelkie zmiany wprowadzone do bazy danych od momentu rozpoczęcia ostatniej transakcji. W języku Transact-SQL baz danych Sybase przewidziano specjalne polecenie cofania transakcji przeznaczone dla wyzwalaczy - ROLLBACK TRIGGER. Polecenie to anuluje wszelkie zmiany wprowadzone do bazy przez wyzwalacz, a także przez zapytanie, które ten wyzwalacz uruchomiło. Z oczywistych względów ROLLBACK TRIGGER przerywa pracę wyzwalacza w miejscu swojego wystąpienia. Jeśli przyczyna anulowania pracy wykonanej przez wyzwalacz jest na tyle poważna, że wymaga wygenerowania błędu, do wyrażenia ROLLBACK TRIGGER można dołączyć klauzulę WITH RAISERROR. Do sprawdzenia poprawności działania wyzwalacza można wykorzystać zmienną OSrowcount. Np. potrzebny jest nam wyzwalacz, który cofa wszelkie wyrażenia mające wpływ na więcej niż pięć wierszy bazy danych. Jedną z możliwych realizacji takiego wyzwalacza przedstawia listing 16.7. Zapytanie uruchamiające wyzwalacz znajduje się w listingu 16.8. ---------------------------------- Listing 16.7. Wyzwalacz cofający wyrażenie, jeżeli ma ono wpływ na zbyt dużą liczbę wierszy
CREATE TRIGGER movies_modify_trigger ON Movies FOR INSERT, UPDATE, DELETE AS IF @@rowcount > 5 ROLLBACK TRIGGER WITH RAISERROR 'Zmodyfikowano zbyt dużą liczbę wierszy.' PRINT 'Wyzwalacz nie wykrył błędu.' ---------------------------------- ---------------------------------- Listing 16.8. Wyrażenie uruchamiające wyzwalacz movies_modijy_trigger
UPDATE Movies SET movie_title - 'Modified title' ----------------------------------
Zmodyfikowano zbyt dużą liczbę wierszy.
Uruchomienie zapytania z listingu 16.8 powoduje "odpalenie" wyzwalacza movies_modify_trigger. Wyrażenie warunkowe wewnątrz wyzwalacza przyjmuje wartość prawdziwą, ponieważ zmienna @@rowcount jest większa od 5 (zmodyfikowane zostały wszystkie wiersze tabeli). Wykonane zostaje wyrażenie ROLLBACK TRIGGER, które przerywa dalsze działanie wyzwalacza i cofa wszelkie #377 zmiany, po czym generowany jest błąd (RAISERROR). Ostatnie polecenie PRINT nie jest uruchamiane, ponieważ wykonanie wyzwalacza kończy się na wyrażeniu ROLLBACK TRIGGER.
Tabele INSERTED i DELETED
Pisząc wyzwalacze dla poleceń INSERT i DELETE zachodzi czasem potrzeba odwołania się do danych, które zostały przed chwilą wstawione do tabeli lub z niej usunięte. W Transact-SQL dostęp do tych danych jest możliwy poprzez dwie pseudotabele. Kiedy wyzwalacz wywołany zostanie poleceniem INSERT, wstawiane dane są przechowywane przez cały czas jego działania w tabeli INSERTED. Podobnie, dane usunięte poleceniem DELETE są dostępne w wyzwalaczu poprzez tabelę DELETED. Informacje zawarte w obu wymienionych tabelach można wykorzystać do sprawdzenia poprawności operacji, która uruchomiła wyzwalacz. Na ogół tabele te używane są podczas transakcji. Przed uruchomieniem wyrażenia INSERT lub DELETE można rozpocząć transakcję, a następnie sprawdzić w pseudotabelach, czy jej wynik jest zgodny z oczekiwaniami. Jeżeli okaże się, że coś jest nie w porządku z danymi w jednej z tych tabel, wystarczy cofnąć całą transakcję i powrócić do stanu pierwotnego. Tabela DELETED może zostać wykorzystana w wyzwalaczu, który automatycznie cofa bieżącą transakcję, jeżeli w wyniku jej działania usuniętych zostało więcej niż pięć wierszy bazy danych. Przykład takiego wyzwalacza znajduje się w listingu 16.9, natomiast uruchamiające go zapytanie zawiera listing 16.10. ---------------------------------- Listing 16.9. Wyzwalacz cofający transakcję, która usuwa więcej niż pięć wierszy
CREATE TRIGGER movies_delete_trigger ON Movies FOR DELETE AS IF (SELECT COUNT(*) FROM DELETED) > 5 ROLLBACK TRIGGER ---------------------------------- ---------------------------------- Listing 16.10. Zapytanie, które uruchamia wyzwalacz z listingu 16.9
DELETE FROM Movies
No records deleted ----------------------------------
Wyzwalacz z listingu 16.9 jest bardzo podobny do wyzwalacza z listingu 16.7. Pomijając fakt, iż powyższy wyzwalacz jest uruchamiany tylko w przypadku operacji usuwania (FOR DELETE), należy zwrócić uwagę na inną różnicę, a mianowicie wykorzystanie przez wyzwalacz movie_delete_trigger pseudotabeli DELETED. Pseudotabele mogą zostać również użyte do sprawdzania poprawności danych tuż przed ich wstawieniem lub usunięciem z tabeli. Dzięki temu będziemy bardziej pewni, że nic niepożądanego nie trafi do bazy danych za naszymi plecami. #378 Przykładem niech będzie wyzwalacz, który uniemożliwia wstawianie do bazy danych filmów z datą dystrybucji wcześniejszą niż data bieżąca - listing 16.11. ---------------------------------- Listing 16.11. Wyzwalacz stojący dbający o poprawność danych wstawianych do tabeli
CREATE TRIGGER movies_test_trigger ON Movies FOR INSERT AS IF EXISTS (SELECT * FROM INSERTED WHERE release_date < GETDATE()) ROLLBACK TRIGGER WITH RAISERROR 'Data dystrybucji musi dotyczyć przyszłości.' ----------------------------------
Wartością słowa kluczowego EXISTS w wyrażeniu IF jest prawda, jeżeli zapytanie użyte w warunku zwróci jakiekolwiek wiersze.
W praktyce
Omawiając procedury języka Transact-SQL nie powiedzieliśmy nic na temat możliwości korzystania z programów zewnętrznych oraz pisania procedur w innych językach programowania. Zarówno Sybase Adaptive Sewer, jak i Microsoft SQL Server umożliwiają programistom tworzenie programów w innych językach programowania, które następnie mogą być wywoływane z wnętrza procedur i zwracać do nich dane. Transact-SQL pozwala na tworzenie zadań, które wykonują swoją pracę poprzez wywoływanie aplikacji zewnętrznych względem bazy danych. Np., jeżeli napisaliśmy kiedyś program w Perlu, który pobiera listę adresów e-mail z bazy danych i wysyła określoną treść pod każdy z nich, możemy wywołać ten program z wnętrza procedury składowanej, mimo że jest on zupełnie niezależny od bazy danych. Microsoft SQL Server wyszedł z podobną ideą. Jeżeli procesy tworzone przez programistę są na tyle złożone, że wymagają wysyłania zapytań do bazy danych oraz jednoczesnego wykonywania programów zewnętrznych, opłaca się umieścić wszystkie te polecenia wewnątrz procedur i odwoływać się do nich w miarę potrzeby. Często jest to rozwiązanie znacznie lepsze, niż pisanie programu zewnętrznego, który połączy wszystkie polecenia składowe w jedno spójne zadanie. Fakt, iż Transact-SQL jest w stanie współdziałać z rozszerzeniami pisanymi w innych językach oraz odwoływać się do zewnętrznych aplikacji, czyni go bardzo uniwersalnym środowiskiem programowania. Tego typu możliwości pozwalają użytkownikom robić znacznie więcej, niż tylko wykonywać zapytania i manipulować danymi wewnątrz bazy danych.
Istnieją dwie metody wykorzystania oprogramowania zewnętrznego względem bazy danych. Pierwsza z nich opiera się na użyciu procedur rozszerzonych, przechowywanych w postaci bibliotek dołączanych dynamicznie (DLL). Procedury tego #379 typu muszą zostać zarejestrowane w serwerze, po czym mogą być wywoływane poleceniem EXEC, tak jak każda inna procedura bazy danych. Procedury rozszerzone mogą przyjmować parametry i zwracać wyniki swojego działania. Microsoft SQL Server oraz Sybase Adaptive Server wyposażone są w pewien standardowy zbiór procedur rozszerzonych, przeznaczonych dla użytkowników. Procedury rozszerzone umożliwiają firmom niezależnym tworzenie aplikacji rozbudowujących możliwości baz danych Transact-SQL i sprzedawanie ich na rynku oprogramowania. Korzystanie przez twórców oprogramowania z bibliotek DLL, zamiast ze zwykłych procedur składowanych ma również swoje uzasadnienie ekonomiczne (procedury składowane pisane są w języku Transact-SQL, co oznacza, że ich kod źródłowy jest ogólnodostępny). Większość procedur rozszerzonych współdziała z aplikacjami zewnętrznymi poprzez wspólny interfejs, jakim jest język programowania. Załóżmy dla przykładu, że mamy program do wysyłania faksów napisany w C++, natomiast informacje o klientach przechowywane są w bazie danych Sybase. Przy takich warunkach można napisać procedurę rozszerzoną, która pobierze numery faksów z bazy danych, a następnie udostępni je poprzez interfejs języka C++programowi wysyłającemu faksy. Microsoft umożliwia tworzenie procedur rozszerzonych przy użyciu interfejsu ODS (ang. Open Data Services) przeznaczonego dla programów w języku C. Przygotowany kod jest kompilowany do postaci biblioteki DLL, a następnie po zarejestrowaniu w serwerze bazy danych wykonuje się w ramach procesu serwera. Rozwiązanie takie jest niezwykle wydajne, ponieważ serwer i kod biblioteki dzielą wspólny obszar pamięci. Wiąże się z tym jednak pewne poważne zagrożenie - niestabilny kod procedury rozszerzonej może doprowadzić do zawieszenia serwera. Sybase Adaptive Server zarządza procedurami rozszerzonymi przy użyciu oddzielnego serwera o nazwie XP Server. XP Server oraz serwer bazy danych komunikują się ze sobą poprzez zdalne wywołania procedur (RPC - ang. remote procedurę calls). Zaletą tego rozwiązania jest to, że żadna wadliwie działająca procedura nie jest w stanie zaszkodzić serwerowi. Wadą jest konieczność komunikowania się dwóch serwerów przez wywołania RPC, które nie są tak szybkie, jak bezpośrednie załadownie kodu procedury do serwera bazy danych (rozwiązanie zastosowane przez Microsoft). Procedury rozszerzone rejestrowane są w serwerze bazy danych przy użyciu specjalnej procedury o nazwie sp_addextendedproc. Procedura przyjmuje dwa argumenty: nazwę procedury rozszerzonej oraz nazwę przechowującego ją pliku DLL. Składnia sp_addextendedproc wygląda następująco: sp_addextendedproc nazwa_procedury, nazwa_pliku_DLL Druga technika korzystania z zewnętrznego oprogramowania opiera się na wywoływaniu aplikacji nie związanych z bazą danych. Do tego celu służy procedura rozszerzona xp_cmdshell, wymagająca dwóch argumentów: polecenia do wykonania #380 i opcjonalnego znacznika no_output, który blokuje dane wyjściowe generowane przez wykonywany program. Składnia procedury xp_cmdshell wygląda następująco:
xp_cmdshell 'polecenie' [, no_output]
Poniższy przykład uruchamia interpreter języka Perl umieszczony w katalogu C:\perl\bin, z parametrem w postaci nazwy pliku zawierającego program do wykonania:
Procedura xp_cmdshell może zostać użyta wewnątrz procedury bazy danych, kiedy zachodzi potrzeba odwołania się do programu zewnętrznego. Na przykład, jeżeli procedura przetwarza zamówienia, z jej wnętrza można wywołać program, który weryfikuje dane karty kredytowej, po czym zwraca wynik swojego działania z powrotem do bazy danych.