#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 wartoci 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ł powię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 wyjciowe 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 wartoci 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łasnociš bazy danych, nazywanych wspólnie zmiennymi globalnymi. W niektórych przypadkach użytkownik ma prawo modyfikować wartoci zmiennych globalnych, ale nie może ich tworzyć. Wartoci 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ę pomylnie, zmienna przyjmuje wartoć 0. @@fetch_status; MS; Wynik ostatniej operacji FETCH wykonanej na kursorze. Wartoć O wskazuje pomylnš 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. Domylnie 28 #365 @@nestievel; MS, Sybase; Wartoć okrelajš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, jeli procedura wywoła innš procedurę, która z kolei wywoła kolejnš, zmienna przyjmie wartoć 2. Maksymalna wartoć zmiennej wynosi 16. Po przekroczeniu tej wartoci 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ć okrelajšca status bieżšcego kursora. 0 - ostatnia operacja pobrania zakończyła się pomylnie, 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 wywietlenie 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š wartoci specyficzne dla każdej sesji, podczas gdy inne przechowujš wartoci 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 jeli 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 wartoci 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 programicie. Trzeba okrelić, 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 okrelonych 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 wywietleniu 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: RAISERROR ( { numer_błedu | 'komunikat błędu' }, moc_błedu, stan [, lista_argumentów] ) [NOWAIT] TwiTH LOG] [SETERROR] Natomiast składnia polecenia RAISERROR według Sybase: RAISERROR numer_błędu [ { 'komunikat błędu' | @8nazwa_zmiennej } ] [, argumenty ] [ WITH { data_błędu | rozszerzona_lista_wyboru } ] 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 okrela się numer błędu lub komunikat błędu. Wartociš 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 okrelony jawnie, system nada mu wartoć domylnš 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 okrela stopień powagi błędu. Wartoć tego parametru okrela 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...
sliwak