T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 30 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 31
Programowanie w systemie SQL Server 6.3.2. Użycie kursorów zagnieżdżonych do tworzenia raportu......................68
6.4. Zmienne kursorowe ...................................................................................70
6.5. Operacje związane z kursorami..................................................................71
OPEN ...........................................................................................................71
1. Wprowadzenie..................................................................................................32 FETCH .........................................................................................................71
1.1. Paczki (Batches) ........................................................................................32 CLOSE .........................................................................................................71
1.2. Procedury pamiętane i procedury wyzwalane ............................................33 DEALLOCATE............................................................................................71
1.3. Skrypty ......................................................................................................33 6.6. Zmienne globalne związane z kursorami ...................................................73
2. Zmienne i parametry.........................................................................................34 7. Procedury pamiętane ........................................................................................74
2.1. Przekazywanie danych...............................................................................34 7.1. Wyrażenie CREATE PROCEDURE .........................................................74
2.2. Zmienne i wiązanie zmiennych w programach aplikacyjnych....................34
2.3. Deklarowanie zmiennych...........................................................................35
2.4. Parametry i kody powrotu z procedur ........................................................38
3. Przykłady skryptów ..........................................................................................39
4. Instrukcje sterujące języka Transact-SQL.........................................................41
BEGIN ... END ................................................................................................41
GOTO ..............................................................................................................42
IF...ELSE..........................................................................................................43
RETURN..........................................................................................................44
WAITFOR........................................................................................................45
WHILE.............................................................................................................46
CASE ...............................................................................................................48
PRINT ..............................................................................................................50
Komentarze ......................................................................................................51
SET NOCOUNT ..............................................................................................51
5. Procedury wyzwalane.......................................................................................53
5.1. Wyrażenie CREATE TRIGGER................................................................53
5.2. Ograniczenia na procedury wyzwalane......................................................55
5.3. Rekurencyjne procedury wyzwalane:.........................................................57
5.4. Zagnieżdżanie procedur wyzwalanych.......................................................57
5.5. Przykłady procedur wyzwalanych .............................................................58
5.5.1. Użycie procedury wyzwalanej do przekazywania komunikatu............58
5.5.2. Użycie procedury wyzwalanej z komunikatem w postaci e-maila.......58
5.5.3. Sprawdzanie reguł biznesowych obejmujące kilka tabel .....................58
5.5.4. Użycie COLUMNS_UPDATED.........................................................60
6. Kursory i operacje na kursorach .......................................................................62
6.1. Wyrażenie DECLARE CURSOR ..............................................................62
6.2. Charakterystyki kursora.............................................................................66
6.3. Przykłady kursorów...................................................................................67
6.3.1. Kursor prosty ......................................................................................67
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 32 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 33
1. Wprowadzenie
" wyrażenia CREATE DEFAULT, CREATE PROCEDURE, CREATE
RULE, CREATE TRIGGER, CREATE VIEW nie nogą występować w
Dla wykonywania obliczeń, których nie można wyrazić za pomocą
paczce z innymi wyrażeniami (wyrażenia te muszą tworzyć odrębne paczki);
pojedynczego wyrażenia w języku Transact-SQL (T-SQL), SQL Server dopuszcza
" przy zmianie lub dodaniu kolumny do tabeli (ALTER TABLE) w obrębie
kilka sposobów grupowania tych wyrażeń. Są to:
paczki, do kolumny tej nie można odwoływać się w tej samej paczce;
" paczki lub wsady (batches),
" jawne podanie słowa kluczowego EXECUTE jest wymagane przy
" procedury pamiętane (stored procedures,
wszystkich wyrażeniach paczki z wyjątkiem pierwszego.
" procedury wyzwalane (triggers),
Komenda GO jest znacznikiem końca paczki  przekazuje informacje, że
" skrypty (scripts).
paczka ma zostać przesłana do SQL Servera.
Do tworzenie tych złożonych wyrażeń można wykorzystywać następujące
Przykłady
elementy:
" wyrażenia sterujące,
" zmienne,
" obsługa błędów - można określać sposób reakcji systemu na błędy poprzez
specyfikację akcji, która ma być podjęta w przypadku wystąpienia błędu lub
poprzez generowanie komunikatu o błędzie określonym przez użytkownika
zamiast standardowych komunikatów systemowych.
1.2. Procedury pamiętane i procedury wyzwalane
1.1. Paczki (Batches)
Procedura pamiętana jest grupą wyrażeń T-SQLa, która została raz
skompilowana a następnie może być wielokrotnie wykonywana.
Paczka stanowi grupę złożoną z jednej lub z wielu wyrażeń Transact-SQLa
W procedurach pamiętanych, procedurach wyzwalanych i w paczkach możliwe
przysłaną jaką pojedynczą jednostkę z aplikacji do SQL Servera w celu jej
jest odwoływanie się do tabel, które jeszcze nie istnieją  możliwe jest to dzięki
wykonania. SQL Server kompiluje te wyrażenia w pojedynczą jednostkę
mechanizmowi zwanemu opóz
nione rozwiązywanie nazw.
wykonywalną zwaną planem wykonania (execution plan). Wyrażenia tworzące
plan wykonania wykonywane są jednorazowo w jednym czasie. W przypadku
wystąpienia błędu kompilacji, żadne z wyrażeń w paczce nie zostanie wykonane.
Błąd wykonanie (run-time error), taki jak przepełnienie numeryczne lub
1.3. Skrypty
naruszenie warunku spójności, ma jeden z następujących dwóch efektów:
" większość błędów przerywa wykonywanie bieżącego wyrażenia i tym
Skrypt jest ciągiem wyrażeń T-SQLa zapamiętanym w pliku. Plik ten może być
samym wyrażeń następujących po nim;
parametrem wejściowym dla programów osql, isql oraz dla analizatora zapytań
" niektóre błędy wykonania, na przykład naruszenie warunków spójności, SQL Server Query Analyzer.
przerywają jedynie wykonywanie bieżącego wyrażenia - pozostałe W skrypcie może być zawartych jedna lub wiele paczek. Komenda GO
wyrażenia są wykonywane. sygnalizuje koniec paczki. Jeśli w skrypcie nie ma żadnej komendy GO, to skrypt
Wyniki przetworzenia wyrażeń wykonanych przed wystąpieniem błędu traktowany jest jako pojedyncza paczka.
pozostają bez zmian. Wyjątkiem jest sytuacja, gdy paczka należy do transakcji i Skrypty są najczęściej używane do:
błąd powoduje odrzucenie transakcji. W tym przypadku wszystkie niezatwierdzone
" utrzymywania stałej kopii kroków wykonywanych w celu utworzenia i
modyfikacje danych wykonane przed wystąpieniem błędu zostaną wycofane.
wypełnienia bazy danych (na przykład jako mechanizm archiwowania),
Przy tworzeniu paczek obowiązują następujące ograniczenia:
" przenoszenia zbioru instrukcji między serwerami;
" w celach edukacyjnych.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 34 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 35
2. Zmienne i parametry Języki programów aplikacyjnych takie jak C, C++, Basic, Java, mają własne
zmienne do przechowywania danych. Aplikacja używająca interfejsu (API)
do bazy danych musi przemieścić dane zwrócone przez wyrażenie T-SQL do
zmiennych aplikacyjnych. Wykonuje się to za pomocą procesu wiązania
2.1. Przekazywanie danych
zmiennych (variable binding). Aplikacja wykorzystuje funkcje interfejsu do
Przekazywanie danych między wyrażeniami T-SQLodbywa się za pomocą: wiązania kolumn zbioru wynikowego ze zmiennymi programowymi. Gdy
zostanie pobrany wiersz, to sterownik przesyła dane z kolumn do wiązanych
" zmiennych lokalnych T-SQLa,
zmiennych programowych.
" parametrów T-SQLa, wejściowych i wyjściowych.
" Znaczniki parametrów.
Przykład: Znaczniki parametrowe są udostępniane przez interfejsy baz danych oparte
na ADO, OLE DB i ODBC. Znacznik parametru jest znakiem zapytania (?)
Niech tabela Pracownik ma defiuicję: umieszczonym w miejscu wyrażenia wejściowego w wyrażeniu T-SQLa.
Znacznik ten jest następnie wiązany ze zmienną aplikacyjną. Pozwala to na
wykorzystanie wartości zmiennej aplikacyjnej jako wejścia do wyrażenia T-
SQLa. Znaczniki parametrów umożliwiają także wiązanie parametrów
wyjściowych i kodów powrotu ze zmiennymi aplikacyjnymi. Dane
Wykorzystanie zmiennej lokalnej do przekazywania danych: wyjściowe podczas wykonywania procedury są wówczas zwracane do
zmiennych związanych . Mechanizm ten udostępnia także interfejs DB-
Library API.
2.3. Deklarowanie zmiennych
Zmienne deklarowane są w ciele paczki lub procedury za pomocą wyrażenia
Wykorzystanie parametru wejściowego do przekazywania danych:
DECLARE. Przypisywanie wartości zmiennym odbywa się za pomocą wyrażenia
SET lub SELECT. Wśród zmiennych wyróżniamy zmienne kursorowe. Po
zadeklarowaniu zmienne inicjowane są wartością NULL.
Składnia
DECLARE { {@zmienna_lokalna typ_danych}
| {@zmienna_kursorowa CURSOR}
} [,...n]
Zmienna zaczynają się znakiem @. Typem danych nie może być text, ntext ani
image.
2.2. Zmienne i wiązanie zmiennych w programach aplikacyjnych
Aplikacje mogą wykorzustywać zmienne aplikacyjne i znaczniki parametrów
(parameter markers) do przetwarzania danych z wyrażeń T-SQLa.
" Zmienne aplikacyjne.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 36 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 37
Przykład 4. Przykład skryptu, który tworzy tabelę o dwóch kolumnach colA i colB, a
1. Wykorzystanie zmiennej @szukaj do znalezienia autorów, których nazwiska następnie dołącza do niej 26 wierszy  w kolumnie colA umieszczane są liczby
zaczynają się na 'Kow'. od 0 do 25, a w colB umieszczane są znaki: dla 0  'a', dla 1  'b', itd.
2. Deklarowanie trzech zmiennych lokalnych:
Zakresem zmiennej jest zbiór wyrażeń T-SQLa, w których można odwoływać
się do tej zmiennej. Zakres rozciąga się od miejsce deklaracji do końca paczki lub
procedury pamiętanej, w których została zadeklarowana.
3. Błędne odwołanie do zmiennej zadeklarowanej w innej paczce:
Zmiennej można przypisać wartość w liście SELECT. Jeśli SELECT zwraca
większą liczbę elementów niż jeden, to zmiennej przypisywana jest ostatnia
zwracana wartość.
5. Pod zmienną @MaxPracID umieszczony zostanie nojwiększy numer
identyfikacyjny pracownika:
6. Efekt ten sam, co w przykładzie 5:
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 38 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 39
2.4. Parametry i kody powrotu z procedur
Parametry używane są do wymiany danych między procedurą pamiętaną, a
Przykład wykonania procedury:
wyrażeniami, z których procedura została wywołana, przy czym:
" parametry wyjściowe umożliwiają przekazywanie danych do procedur
pamiętanych,
" parametry wyjściowe pozwalają na przekazywanie wartości zmiennych na
zewnątrz procedur,
" każda procedura pamiętana zwraca kod powrotu, jeśli kod ten nie jest jawnie
ustalony, to zwracany kod ma wartość 0.
Poniższa procedura ilustruje wykorzystanie parametrów wejściowych,
parametrów wyjściowych i kodu powrotu:
Najpierw modyfikujemy tabelę Pracownik:
3. Przykłady skryptów
1. Przykład skryptu:
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 40 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 41
2. Kilka paczek tworzy jedną transakcję. BEGIN TRANSACTION i COMMIT
4. Instrukcje sterujące języka Transact-SQL
oznaczają początek i koniec transakcji. Wyrażenia BEGIN TRANSACTION,
USE, CREATE TABLE, SELECT i COMMIT stanowią samodzielne paczki.
Wszystkie wyrażenia INSERT włączono do jednej paczki:
BEGIN ... END
Definiuje blok wyrażeń. Bloki wyrażeń mogą być zagnieżdżane
Przykład
1. Wykorzystanie bloku wyrażeń w procedurze wyzwalanej:
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 42 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 43
Przykład:
2. Przykład tworzy wykaz książek o biznesie kosztujących mniej niż $20, jeśli
istnieje co najmniej jedna książka tego rodzju. W przeciwnym razie udzielana
jest informacja o braku takich książek i wypisywane są wszystkie książki o
cenie niższej niż $20.
IF...ELSE
Składnia
IF wyrażenie_logiczne
{wyrażenie_sql | blok_wyrażeń}
[ELSE
{wyrażenie_sql | blok_wyrażeń}]
Jeśli wyrażenie_logiczne zawiera wyrażenie SELECT, to musi być ujęte w
nawiasy.
Przykłady:
1. Jeśli średnia cena tytułów jest większa niż $15, to pisany jest tekst "Średnia
cena jest większa niż $15"
GOTO
Przekazuje sterowanie do etykiety.
Składnia:
Definicja etykiety:
etykieta:
Instrukcja:
GOTO etykieta
etykieta - identyfikator
Uwagi
GOTO nie może wyprowadzać poza paczkę.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 44 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 45
RETURN WAITFOR
Powoduje bezwarunkowe wyjście z zapytania lub procedury. Określa czas, przedział czasu lub zdarzeniu, które wyzwalają wykonanie bloku
Składnia wyrażeń, procedurę pamiętaną lub transakcję.
RETURN [
wyrażenie_całkowite ]
Składnia
wyrażenie_całkowite  kod powrotu zwracany przez procedurę (procedury
WAITFOR {DELAY 'czas' | TIME 'czas'}
domyślnie zwracają 0).
DELAY poleca oczekiwać określony przedział czasu,
Przykład
TIME poleca oczekiwanie do określonej chwili czasu.
'czas' Jest określony w jednym z dopuszczolnych formatów datetime. Nie
1. Przykład pokazuje, że jeśli nie podano wartości dla parametru nm, to
może być to data.
przetwarzanie procedury jest kończone.
Po wykonaniy WAITFOR traci się możliwość łączenia z SQL Serverem do
momentu zakończenia oczekiwania. Aby uzyskać informacje o procesach
aktywnych i oczekujących można wykorzystać procedurę pamiętaną sp_who.
Przykłady
A. WAITFOR TIME
Polecenie wykonania procedury update_all_stats o 20:20.
Sprawdzenie poprawnej alokacji stron nastąpi o 22:00:
2. Kodem powrotu jest 1, gdy stanem jest Kalifornia (CA), w przeciwnym razie 2.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 46 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 47
B. WAITFOR DELAY Przykłady
Oczekiwanie przez określony czas:
A. BREAK i CONTINUE w WHILE
Jeśli średnia płaca pracowników w tabeli Pracownik jest mniejsza niż 3000, to
podnieś płacę każdego pracownika o 10%. Powtarzaj tę operację aż do
przekroczenia przez ogólny funduszu płac kwoty 10000 zł.
B. WHILE w połączeniu z kursorem
Inną (lepszą) metodą kolejkowania zadań jest użycie SQL Server Agenta lub SQL-
Zliczanie liczby wierszy w tabeli z wykorzystaniem kursora. Konstrukcja
DMO.
WHILE testuje wartość zmienne @@FETCH_STATUS, która może przyjmować
wartości -2, -1 lub 0. Jeśli wiersz został usunięty od czasu uruchomienia procedury
(-2), wiersz jest pomijany.
WHILE
Określa warunek powtarzania wykonywania wyrażenia SQL-owego. Na
wykonywanie wyrażenia można wpływać za pomocą słów kluczowych BREAK i
CONTINUE.
Składnia
WHILE wyrażenie_logiczne
{wyrażenie_sql | blok_wyrażeń}
[BREAK]
{wyrażenie_sql | blok_wyrażeń}
[CONTINUE]
BREAK  powoduje wyjście z pętki WHILE.
CONTINUE  powoduje wznowieni pętli WHILE.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 48 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 49
CASE
A. Proste wyrażenie CASE
Oblicza wykaz warunków i zwraca jedo z możliwych wyrażeń. CASE ma dwa
W wyrażeniu SELECT prosty CASE dopuszcza jedynie sprawdzanie warunków
formaty:
równościowych. Zmodyfikujmy najpierw naszą bazę danych:
" proste CASE porównuje wyrażenie ze zbiorem prostych wyrażeń i określa
wynik,
" obliczane CASE oblicza zbiór wyrażeń logicznych i określa wynik.
W obydwu formatach może wystąpić ELSE.
Składnia
Proste wyrażenie CASE:
CASE wyrażenie_wejściowe
WHENwyrażenie_when THEN wyrażenie_wynikowe [...n]
[ELSE wyrażenie_wynikowe_else] Postać tabeli:
END
Obliczane wyrażenie CASE:
CASE
WHENwyrażenie_logiczne THEN wyrażenie_wynikowe [...n]
[ELSE wyrażenie_wynikowe_else]
END
Wypiszmy informacje o stanowiskach poszczególnych pracowników w bardziej
czytelnej postaci:
Wartość wynikowa:
Proste wyrażenie CASE:
" Obliczana jest wartość wyrażenie_wejściowe, a następnie kolejno wartości
warunków wyrażenie_wejściowe = wyrażenie_when.
" Zwracana jest wartość wyrażenie_wynikowe dla pierwszego warunku
(wyrażenie_wejściowe = wyrażenie_when) dającego wartość TRUE.
" Jeśli żaden warunek wyrażenie_wejściowe = wyrażenie_when nie jest
prawdziwy, to w przypadku określenia ELSE zwracana jest wartość
wyrażenie_wynikowe_else, a jeśli brak ELSE to zwracana jest wartość
NULL.
Wynik wyrażenia:
Obliczane wyrażenie CASE:
" Dla każdego WHEN obliczane jest wyrażenie_logiczne.
" Zwracana jest wartość wyrażenie_wynikowe dla pierwszego prawdziwego
wyrażenia_logicznego.
" Jeśli żaden warunek wyrażenie_logiczne nie ma wartości TRUE, to
wartością jest wyrażenie_logiczne_else, jeśli występuje fraza ELSE, lub
NULL w przeciwnym razie.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 50 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 51
B. Obliczane wyrażenie CASE Komentarze
Składnia
W wyrażeniu SELECT obliczany CASE umożliwia zastępowanie wartości w
  tekst_komentarza (zgodnie ze standardem SQL-92)
zbiorze wynikowym. Nstępujący przykad wypisuje płacę w postaci komentarza na
lub
podstawie przedziału, do którego płaca należy:
/* tekst_komentarza */ (konwencja z języka C).
Komentarzem jest więc dowolny tekst poprzedzony dwoma bezpośrednio po
sobie następującymi myślnikami. Jeśli komentarz obejmuje kilka linii tekstu, to
dwa myślniki muszą wystąpić na początku każdej linii. Natomiast za pomocą
symboli /* (otwarcie komentarza) i */ (zamknięcie komentarza) można oznaczać
dowolnie długi komentarz  również rozciągający się na kilka linii.
SET NOCOUNT
Wynik wyrażenia:
Wyłącza (ON) i włącza (OFF) komunikat informujący o liczbie wierszy, których
dotyczyło wyrażenie języka Transact-SQL. Komunikat ten stanowi część wyniku
zwracanego przez wyrażenie.
Składnia
SET NOCOUNT {ON | OFF}
Uwaga:
PRINT
Wartość funkcji @@ROWCOUNT jest aktualizowana również przy SET
NOCOUNT w stanie ON.
Wypisuje komunikat
Składnia Przykłady
PRINT 'tekst_ASCII' | @zmienna_lokalna | @@FUNKCJA | wyrażenie Przykład ilustruje blokowanie komunikatu podczas wykonywaniu programu
osql lub pracy w środowisku SQL Server Query Analyzer.
Uwaga
Aby wypisać zdefiniowany przez użytkownika komunikat o błędzie zwracany
pod zmienną globalną @@ERROR, lepiej stosować RAISERROR zamiast
PRINT.
Wynik wykonania:
Przykłady
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 52 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 53
Ustawienie na ON:
5. Procedury wyzwalane
5.1. Wyrażenie CREATE TRIGGER
daje wynik bez informacji o liczbie wierszy w tabeli, których dotyczyła operacja:
Tworzy procedurę wyzwalaną (specjalny przypadek procedury pamiętanej),
która jest automatycznie wykonywana podczas próby wykonania określonego
wyrażenia modyfikacji danych na określonej tabeli. Możliwe jest tworzenie wielu
procedur wyzwalanych dla wyrażeń: INSERT, UPDATE i DELETE.
Składnia
CREATE TRIGGER nazwa_procedury_wyzwalanej
ON tabela
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
wyrażenie_sql [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (kolumna)
[{AND | OR} UPDATE (kolumna)]
[...n]
| IF (COLUMNS_UPDATED(){operacja_bitowe} maska_modyfikacji)
{ operator_porównania} maska_kolumn [...n]
}
wyrażenie_sql [...n]
}
}
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 54 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 55
Argumenty zwraca wzorzec bitowy typu varbinary wskazujący, które z kolumn tabeli były
modyfikowane.
WITH APPEND
operacja_bitowa
Wskazuje, że można dodać dodatkową procedurę wyzwalaną tego samego typu
(dla zachowania kompatybilności z poziomem 6.5).
Jedna z następujących operacji: & - bitowe AND, | - bitowe OR, ^ - bitowe OR
wyłączające, ~ - bitowe NOT.
NOT FOR REPLICATION
maska_modyfikacji
Wskazuje, że procedura nie jest wyzwalana, gdy tabela jest modyfikowana
przez proces replikacji.
Jest maską bitową przedstawioną jako liczba całkowita. Niech na przykład
tabela t1 zawiera kolumny C1, C2, C3, C4, C5. Aby sprawdzić, czy
AS
modyfikowano kolumny C2, C3 i C4 należy określić maskę jako liczbę 14 (o
Określa akcje procedury wyzwalanej.
binarnej reprezentacji 01110). Liczba 2 oznacza modyfikowanie jedynie
kolumny C2, itd. Kolumna pierwsza odpowiada najmniej znaczącej pozycji.
wyrażenie_sql
operator_porównanie
Określa warunki i akcje procedury. Warunki sprawdzają, czy wyrażenie
DELETE, INSERT lub UPDATE ma być wykonane. Akcje specyfikują
Znak równości (=) sprawdza, czy wszystkie kolumny określone w
wyrażenia języka Transact-SQL. Mogą to być dowolne wyrażenia z wyjątkiem
masce_modyfikacji były rzeczywiście modyfikowane. Symbole większości (>) i
SELECT. Procedurę wyzwalaną projektuje się w taki sposób, aby sprawdzać i
mniejszości (<) sprawdza czy wszystkie lub niektóre z z kolumn były
zmieniać dane w bazie danych bez ich zwracania użytkownikowi.
modyfikowane.
W CREATE TRIGGER można odwoływać się do specjalnych tabel:
maska_kolumn
" deleted i inserted mają strukturę identyczną ze strukturą tabeli dla której Jest liczbą całkowitą określającą maskę kolumn, w stosunku do których
definiowana jest procedura wyzwalana i przechowuje starą lub nową sprawdzamy modyfikację.
wartość wierszy, które mają być zmieniane za pomocą akcji
Uwaga:
użytkownika. Aby na przykład odzyskać wartości z tabeli deleted,
Procedury wyzwalane są często używane do wymuszania przestrzegania reguł
piszemy:
biznesowych i warunków spójności. Warunki określane w ALTER TABLE i
CREATE TABLE, takie jak PRIMARY KEY i FOREIGN KEY, mają
pierwszeństwo przed procedurami wyzwalanymi.
n
5.2. Ograniczenia na procedury wyzwalane
Wskazuje możliwość użycia wielu wyrażeń języka Transact-SQL.
Obowiązują następujące ograniczenia przy korzystaniu z procedur
IF UPDATE (kolumna)
wyzwalanych:
Testuje wykonanie akcji INSERT lub UPDATE na podanej kolumnie.
1. CREATE TRIGGER musi być pierwszym wyrażeniem w paczce i może być
stosowane tylko do jednej tabeli.
IF (COLUMNS_UPDATED())
2. Może być tworzona tylko w bieżącej bazie danych, ale może odwoływać się do
obiektów spoza tej bazy.
Testuje dla akcji INSERT lub UPDATE, czy podane kolumny były
modyfikowane (przez aktualizację lub dołączanie). COLUMNS_UPDATED
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 56 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 57
3. Ta sama akcja wyzwalana może być zdefiniowana dla więcej niż jednej akcji
użytkownika (np. dla INSERT i dla UPDATE) w tym samym wyrażeniu
5.3. Rekurencyjne procedury wyzwalane:
CREATE TRIGGER.
4. Wyniki z procedury wyzwalanej są zwracane do aplikacji w ten sam sposób jak
Jeśli w procedurze pamiętanej sp_dboption ustawiona zostanie opcja recursive
w przypadku procedur pamiętanych. Aby wyeliminować zwracanie wyników
triggers, to możliwe są rekurencyjne procedury wyzwalane. Możliwe są dwa
spowodowane odpaleniem procedury wyzwalanej nie należy włączać do niej
rodzaje rekursji:
ani wyrażeń SELECT zwracających wartości, ani wyrażeń przypisujących
" rekursja pośrednia,
wartości zmiennym. Wymaga to specjalnej obsługi, w szczególności należy
" rekursja bezpośrednia.
wówczas używać SET NOCOUNT.
Z przykładem rekursji pośredniej mamy do czynienia wtedy, gdy aktualizacja
5. Procedury wyzwalane nie mogą być definiowane na widokach
tebeli T1 odpala procedurę PW1 aktualizującą tabelę T2. Aktualizacja tabeli T2
(perspektywach).
wyzwala procedurę PW2 modyfikującą tabelę T1 itd.
6. Wyrażenie TRUNCATE TABLE (usuwa wszystkie wiersze) nie wyzwala akcji
Z rekursją bezpośrednią mamy do czynienia wtedy, gdy aktualizacja tabeli T1
DELETE.
wyzwala procedurę PW1 aktualizującą tabelę T1, co ponownie wyzwala PW1 itd.
7. Następujące wyrażenia języka Transact-SQL są niedopuszczalne w procedurach
Możemy mieć jednocześnie do czynienia z rekursją pośrednią i bezpośrednią.
wyzwalanych:
Przypuśćmy, że dla zdarzenia UPDATE na tabeli T zdefiniowano dwie procedury
ALTER DATABASE ALTER PROCEDURE ALTER TABLE
wyzwalane PW1 i PW2. Niech PW1 aktualizuje rekurencyjnie tabelę T.
ALTER TRIGGER ALTER VIEW CREATE DATABASE
Wyrażenie UPDATE wyzwala PW1 i PW2. Dodatkowo, wykonanie procedury
CREATE DEFAULT CREATE INDEX CREATE PROCEDURE
PW1 wyzwala rekurencyjne wykonywanie PW1 i wykonanie PW2. Tabele
CREATE RULE CREATE SCHEMA CREATE TABLE
inserted i deleted zawierają wiersze tylko odpowiadające temu wyrażeniu
CREATE TRIGGER CREATE VIEW DENY
UPDATE, które wyzwoliło procedurę.
DISK INIT DISK RESIZE DROP DATABASE
Nie ma reguł określających kolejność wykonywania procedur wyzwalanych.
DROP DEFAULT DROP INDEX DROP PROCEDURE
Jeśli dowolna procedura spowoduje ROLLBACK TRANSACTION, to już żadna
DROP RULE DROP TABLE DROP TRIGGER
inna procedura wyzwalana nie jest wykonywana (niezależnie od poziomu
DROP VIEW GRANT LOAD DATABASE
zagnieżdżenia)
LOAD LOG RESTORE DATABASE RESTORE LOG
REVOKE RECONFIGURE
5.4. Zagnieżdżanie procedur wyzwalanych
TRUNCATE TABLE UPDATE STATISTICS
Procedury wyzwalane można zagnieżdżać do 32 poziomów. Jeśli procedura
Dla każdego zdarzenia modyfikacji danych (DELETE, INSERT, DELETE)
zmienia tabelę, dla której zdefiniowano inną procedurę wyzwalaną, to jest ona
można zdefiniować wiele procedur wyzwalanych. Można na przykład wykonać
wykonywana. To wykonanie może wyzwolić trzecią procedurę itd. Jeśli jakaś
CREATE TRIGGER FOR UPDATE dla tabeli, dla której została już zdefiniowana
procedura w łańcuchu wywołań doprowadzi do nieskończonej pętli, to jest
procedura wyzwalana dla UPDATE. (We wczaśniejszych wersjach można było
przerywana. W celu wyłączenia zagnieżdżania procedur wyzwalanych, należy w
definiować tylko jedną procedurę wyzwalaną dla każdego zdarzenia modyfikacji
procedurze sp_configure opcję nested triggers ustawić na 0 (off). Domyślnie
tabeli).
zagnieżdżanie jest możliwe. Jeśli zagnieżdżanie zostanie wyłączone, to wyłączana
jest również rekurencyjna możliwość wykonywania procedur wyzwalanych,
niezależnie od ustawienia opcji recursive triggers w procedurze sp_dboption.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 58 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 59
5.5. Przykłady procedur wyzwalanych
5.5.1. Użycie procedury wyzwalanej do przekazywania komunikatu
Za pomocą systemowej procedury pamiętanej definiujemy komunikat
który ma być wyprowadzany przy dołączaniu nowych pracowników. Akcję tę
Zdefiniujmy procedurę wyzwalaną:
definiuje następująca procedura wyzwalana:
5.5.2. Użycie procedury wyzwalanej z komunikatem w postaci e-maila
Modyfikację tabeli Pracownik można związać z wysyłaniem e-maila:
5.5.3. Sprawdzanie reguł biznesowych obejmujące kilka tabel
Ponieważ warunek CHECK może odwoływać się tylko do kolumn w jednej
tabeli, zatem warunki spójności odnoszące się do wielu tabel (jako reguły
biznesowe, organizacyjne) muszą być zdefiniowane za pomocą procedur
wyzwalanych.
Poniższy przykład definiuje procedurę wyzwalaną, która w przypadku
dołączenia lub zmiany stanowiska pracownika sprawdza, czy jego płaca mieści się
w odpowiednim przedziale zdefiniowanym w tabeli Stanowiska. Przyjmijmy, że
postać tabeli Stanowiska jest następująca:
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 60 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 61
Wydanie polecenia:
powoduje wyprowadzenie następujących komunikatów:
Transakcja zostanie odrzucona.
Wykonanie komend:
5.5.4. Użycie COLUMNS_UPDATED
W przykładzie tworzona jest tabela HistPracownik związana z tabelą
Pracownik. Jeśli zmiena się płaca lub stanowisko pracownika, to tworzony jest
odpowiedni rekord i dołączany jest do tabeli HistPracownik.
Wykorzystanie funkcji COLUMNS_UPDATED() umożliwia szybkie testowanie
zmian żądanych kolumn kolumn.
powoduje dołączanie wierszy do tabeli HistPracownik. Cztery pierwsze wiersze
po wykonaniu powyższych komend przedstawione są w poniższej tabeli:
HistPracownik
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 62 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 63
(UPDATE) wykonane na tabelach bazowych, są odzwierdziedlane przy
następnych odwołaniach do kursora (FETCH).
6. Kursory i operacje na kursorach
SCROLL
Określa, że wszystkie opcje popierania danych (operacji FETCH) są dostępne.
6.1. Wyrażenie DECLARE CURSOR
Opcjami tymi są: FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE. Brak
specyfikacji SCROLL oznacza, że realizowana jest tylko opcja NEXT. SCROLL
nie może wystąpić z opcją FAST_FORWARD.
Definiuje atrybuty kursora, takie jak sposób skrolowania oraz zapytanie
tworzące zbiór wunikowy, na którym kursor operuje. DECLARE CURSOR
wyrażenie_select
akceptuje dwie składnie:
Jest to standardowe wyrażenie SELECT definiujące zbiór wynikowy kursora.
" składnię zgodną ze standardem SQL-92,
Słowa kluczowe COMPUTE, COMPUTE BY, FOR BROWSE i INTO nie są w
" składnię Transact-SQL będącą rozszerzeniem SQL-92.
tym przypadku dopuszczalne.
Składnia SQL-92:
READ ONLY
Zabrania wykonywania aktualizacji. Kursor nie może być zatem powoływany
DECLARE nazwa_kursora [INSENSITIVE] [SCROLL] CURSOR
we frazie WHERE CURRENT OF wyrażeń UPDATE lub DELETE. Opcja ta ma
FOR wyrażenie_select
pierwszeństwo przed właściwością aktualizacji kursora.
[FOR {READ ONLY | UPDATE [OF nazwa_kolumny [,...n]]}]
Składnia Transact-SQL:
UPDATE [OF nazwa_kolumny [,...n]]
Definiuje aktualizowlne kolumny kursora. Użycie UPDATE bez podania
DECLARE nazwa_kursora CURSOR
kolumn oznacza modyfikowalność wszystkich kolumn.
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
Argumenty w składni Transact-SQL:
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
LOCAL
[TYPE_WARNING]
Określa, że kursor jest lokalny w paczce, procedurze pamiętanej lub
FOR wyrażenie_select
wyzwalanej, w której został zdefiniowany kursor. Do kursora można się
[FOR UPDATE [OF nazwa_kolumny [,...n]]]
odwoływać przez lokalne zmienne kursorowe w tych jednostkach programowych
lub poprzez parametr OUTPUT procedury pamiętanej. Kursor jest dealokowany po
Argumentu w składni SQL-92:
zakończeniu paczki, procedury pamiętanej lub wyzwalanej z wyjątkiem sytuacji,
gdy jest zwracany w parametrze OUTPUT. W tym ostatnim przypadku dealokacja
INSENSITIVE
następuje, gdy dealokowana jest ostatnia zmienna odwołująca się do kursora, lub
Definiuje kursor tworzący tymczasową kopię danych używanych przez kursor.
gdy nastąpi wyjście poza zakres tej zmiennej.
Wszystkie zapytanie do kursora są realizowane z tej tymczasowej tabeli
utrzymywanej w tempdb. Zatem modyfikacje wykonywane w tabelach bazowych
GLOBAL
nie są uwzględniane w operacjach związanych z kursorem. Kursor nie dopuszcza
Oznacza, że zakres kursora jest globalny względem połączenia. Kursor jest
modyfikacji. Jeżeli w składni SAL-92 pominięte jest słowo kluczowe
jedynie niejawnie dealokowany przy rozłączeniu. Można się do niego odwoływać
INSENSITIVE, to zatwierdzone operacje usuwania (DELETE) i modyfikacji
we wszystkich paczkach i procedurach w ramach połączenia.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 64 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 65
zmieniać się przy każdej operacji FETCH. Opcja dostępu ABSOLUTE nie jest
FORWARD_ONLY więc dostępna dla kursorów dynamicznych.
Określa, że kursor może być skrolowany jedynie od pierwszego do ostatniego
wiersza. Można więc tylko używać opcji FETCH NEXT. Jeśli FAST_FORWARD
FORWARD_ONLY użyte jest bez słów kluczowych STATIC, KEYSET, Definiuje kursor FORWARD_ONLY i READ_ONLY z optymalizacją
DYNAMIC, to kursor działa jako kursor dynamiczny (DYNAMIC). Jeśli nie użyto działania. FAST_FORWARD nie może wystąpić ze SCROLL ani z
ani FORWARD_ONLY, ani SCROLL, to domyślnie przyjmowane jest FOR_UPDATE. FAST_FORWARD i FORWARD_ONLY są wzajemnue
FORWARD_ONLY, jeśli nie użyto STATIC, KEYSET lub DYNAMIC. STATIC, rozłączne.
KEYSET i DYNAMIC sprawiają, że kursor jest domyślnie kursorem skrolującym
(SCROLL). W przeciwieństwie do interfejsów API, takich jak ODBC i ADO, READ_ONLY
FORWARD_ONLY jest udostępniany z wariantami STATIC, KEYSET i Jak w SQL-92. Zabrania wykonywania aktualizacji. Kursor nie może być zatem
DYNAMIC. FAST_FORWARD i FORWARD_ONLY są wzajemnie rozłączne - powoływany we frazie WHERE CURRENT OF wyrażeń UPDATE lub DELETE.
jeśli określono jedno z nich, to drugie nie może wystąpić. Opcja ta ma pierwszeństwo przed właściwością aktualizacji kursora.
STATIC SCROLL_LOCKS
Podobnie jak INSENSITIVE w składni SQL-92. Definiuje kursor tworzący Określa, że zagwarantowane jest pomyślne wykonanie pozycyjnego usuwania i
tymczasową kopię danych używanych przez kursor. Wszystkie zapytanie do aktualizacji za pomocą kursora. Następuje zablokowanie dostępu do wierszy w
kursora są realizowane z tej tymczasowej tabeli utrzymywanej w tempdb. Zatem chwili wczytania ich do kursora w celu zapewnienia ich dostępności w czasie
modyfikacje wykonywane w tabelach bazowych nie są uwzględniane w operacjach póz
niejszej aktualizacji. Nie można używać SCROLL_LOCKS, jeśli użyto
związanych z kursorem. Kursor nie dopuszcza modyfikacji. FAST_FORWARD.
KEYSET OPTIMISTIC
Określa, że przynależność i porządek wierszy w kursorze są ustalone od chwili Określa, że nie powiedzie się pozycyjne usuwania ani pozycyjna aktualizacja,
otwarcia kursora. Zbiór kluczy jednoznacznie identyfikujących wiersze jest jeśli wiersz był aktualizowany od chwili wczytania go do kursora. Serwer nie
wbudowany w tabelę w tempdb zwaną keyset-em. Zmiany wartości blokuje tych wierszy. Zamiast tego, serwer porównuje znaczniki czasowe wartości
niekluczowych w tabelach bazowych, wykonane przez waściciela kursora lub kolumna, lub sumy kontrolne, jeśli kolumna nie ma znaczników kontrolnych, aby
innych użytkowników, są widoczne podczas skrolowania kursora. Dałączenia określić, czy wiersz był modyfikowany od chwili wczytania go do kursora. Jeśli
wykonane przez innych użytkowników nie są widoczne (dołączać nie można wiersz był modyfikowany, to pozycyjna operacja aktualizacja lub usuwanie są
poprzez kursor). Jeśli wiersz został usunięty, to próba dostępu do niego ustawia odrzucane. OPTIMISTIC nie może wystąpić razem z FAST_FORWARD.
zmienną globalną @@FETCH_STATUS na -2. Aktualizacja wartości kluczowej
spoza kursora traktowana jest jak usunięcie starego i dołączanie nowego wiersza. TYPE_WARNING
Wiewrsz z nową wartością nie jest więc widoczny, a próba dostępu do wiersza ze Określa, że ostrzeżenie jest wysyłane do klienta w przypadku niejawnej
starą wartością ustawia zmienną globalną @@FETCH_STATUS na -2. Nowe konwersji kursora z żądanego typu na inny
wartości są widoczne, jeśli aktualizacja jest wykonywana poprzez kursor za
pomocą WHERE CURRENT OF. wyrażenie_select
Jest to standardowe wyrażenie SELECT definiujące zbiór wynikowy kursora.
DYNAMIC Słowa kluczowe COMPUTE, COMPUTE BY, FOR BROWSE i INTO nie są w
Definiuje kursor, w którym odzwierciedlane są wszystkie zmiany. Wartości tym przypadku dopuszczalne. SQL Server dokonuje niejawnej konwersji kursora
danych, uporządkowanie i przynależność wierszy do zbioru wynikowego mogą na inny typ, jeśli frazy w wyrażeniu_select pozostają w konflikcie z
funkcjonalnością żądanego typu kursora (patrz Implicit Cursor Conversions).
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 66 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 67
Oposuje atrybuty kursore, takie jak czy jest on korsorem "tylko w przód"
UPDATE [OF nazwa_kolumny [,...n]] (forward-only), czy skrolującym (scrolling).
Definiuje aktualizowlne kolumny kursora. Użycie UPDATE bez podania
kolumn oznacza modyfikowalność wszystkich kolumn, jeśli nie podano sp_describe_cursor_columns
READ_ONLY. Podaje atrybuty kolumn w bieżącym zbiorze wynikowym kursora.
Uwagi: sp_describe_cursor_tables
Podaje tabele bazowe, do których odwołuje się kursor.
1. DECLARE CURSOR definiuje atrybuty kursora, takie jak działanie podczas
skrolowania oraz zapytanie używane do utworzenia zbioru wynikowego, na
6.3. Przykłady kursorów
którym operuje kursor.
2. OPEN tworzy zbiór wynikowy.
3. FETCH zwraca wiersz ze zbioru wynikowego. 6.3.1. Kursor prosty
4. CLOSE zwalnia (releases) bieżący zbiór wynikowy związany z kursorem.
Zbiór wynikowy generowany przy otwieraniu tego kursora obejmuje wszystkie
5. DEALLOCATE zwalnia zasoby używane przez kursor.
wiersze i kolumny tabeli Pracownik z bazy danych pubs. Kursor może być
modyfikowany (aktualizacja i usuwanie) i wszystkie modyfikacje są widoczne
Jeśli DECLARE CURSOR (w Transact-SQL) nie podaje READ_ONLY,
przy dostępie do kursora (za pomocą FETCH). Brak opcji SCROLL oznacza, że
OPTIMISTIC ani SCROLL_LOCKS, domyślnie przyjmuje się:
jedyną metodą dostępu do kursora jest FETCH NEXT. Następujący fragment
" jeśli wyrażenie SELECT nie zezwala na aktualizację (niewystarczające
programu wypisuje wszystkie wiersze z tabeli Pracownik:
uprawnienia, dostęp do odległych tabel bez możliwości aktualizacji itp.), to
kursor jest READ_ONLY,
" kursor STATIC i FAST_FORWARD jest domyślnie READ_ONLY,
" kursor DYNAMIC i KEYSET jest domyślnie OPTIMISTIC.
Nazwy kursorów mogą być powoływane tylko przez wyrażenie Transact-SQLa.
Nie można się do nich odwoływać z poziomu funkcji interfejsu API. Na przykład,
po zadeklarowaniu kursora jego nazwa nie może być powoływana z poziomu
funkcji i metod OLE DB, ODBC, ADO ani DB-Library. Do wierszy kursora nie
ma dostępu za pomocą funkcji interfejsu API. Są one dostępne tylko poprzez
FETCH języka Transact-SQL.
6.2. Charakterystyki kursora
Po zadeklarowaniu kursora następujące procedury pamiętane pozwalają
określić charakterystyki kursora:
sp_cursor_list
Zwraca wykaz kursorów aktualnie widzianych w połączeniu oraz ich atrybuty.
sp_describe_cursor
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 68 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 69
6.3.2. Użycie kursorów zagnieżdżonych do tworzenia raportu
Dla każdego stanowiska z tabeli Stanowiska wypisz pracowników zajmujących
Postać wyniku:
to stanowisko.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 70 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 71
6.5. Operacje związane z kursorami
6.4. Zmienne kursorowe
OPEN
Zmienna kursorowa może być wiązana z kursorem na dwa sposoby:
Otwiera kursor i wypełnia go danymi poprzez wykonanie wyrażenia Transact-
SQL określonego w DECLARE CURSOR lub w wyrażeniu SET
" Przez nazwę z użyciem SET, który podstawia kursor pod zmienną
zmienna_kursorowa.
kursorową:
Składnia:
OPEN { { [GLOBAL] nazwa_kursora } | @nazwa_zmiennej kursorowej}
FETCH
Przykład
Pobiera określony wiersz z kursora
Składnia:
FETCH
[ [NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}
]
FROM
]
{ { [GLOBAL] nazwa_kursora } | @nazwa_zmiennej_kursorowej}
[INTO @
nazwa_zmiennej [,...n] ]
" Korsor może być także tworzony i wiązany ze zmienną kursorową bez
definiowania nazwy kursora, jak w przykładzie:
CLOSE
Zamyka otwarty kursor przez zwolnienie bieżącego zbioru wynikowego i
zwolnienie wszystkich blokad na wierszach, na których kursor wskazuje. CLOSE
zachowuje struktury danych potrzebne do ponownego otwarcia, ale pobieranie
danych i pozycyjna aktualizacja nie są możliwe przed ponownym otwarciem.
Składnia:
CLOSE { { [GLOBAL] nazwa_kursora } | @nazwa_zmiennej_kursorowej }
DEALLOCATE
Usuwa wszystkie odwołania kursora. Po dealokacji ostatniego odwołania
kursora struktury danych zawierające kursor są usuwane. Wyrażenia operujące na
kursorze używają nazwa kursora lub zmiennej kursorowej w celu odwoływania się
do tego kursora. DEALLOCATE usuwa powiązania między kursorem a nazwą
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 72 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 73
kursora lub zmienną kursorową. Jeśli nazwa lub zmienna jest ostatnią z
odwołujących się do kursora, to kursor jest dealokowany i wszystkie zasoby
używane przez kursor są zwalniane. Zwalniane są też blokady skrolowania
używane do ochrony izolacji przy pobieraniu danych. Blokady transakcji używane
do realizacji aktualizacji, włącznie z aktualizacją pozycyjną poprzez kursor, są
trzymane do końca transakcji.
Składnia:
DEALLOCATE {{[GLOBAL] nazwa_kursora} | @nazwa_zmiennej_kursorowej}
6.6. Zmienne globalne związane z kursorami
Przykład
@@CURSOR_ROWS - liczba wierszy w kursorze,
Skrypt pokazuje jak kursor trwa do czasu aż ostatnia nazwa lub zmienna
@@FETCH_STATUS - stan operacji FETCH (0, -1, -2)
odwołująca się do niego nie zostanie dealokowana:
0 wyrażenie FETCH zakończyło się sukcesem
 1 wyrażenie FETCH zakończyło się niepowodzeniem, lub wiersz był
poza zbiorem wynikowym (zakresem kursora)
 2 brak wiersza, do którego odnosi się FETCH (został na przykład w
międzyczasie usunięty).
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 74 T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 75
;liczba
Można tworzyć procedury, które mają tę samą nazwę, po której występuje
7. Procedury pamiętane
średnik i liczba całkowita. Na przykład można utworzyć grupę procedur
odnoszących się do obsługi sprzedaży: SprzedażProc;1, SprzedażProc;2 itd.
Wówczas komenda DROP PROCEDURE SprzedażProc spowoduje usunięcie
7.1. Wyrażenie CREATE PROCEDURE
całej grupy procedur.
Wyrażenie CREATE PROCEDURE tworzy procedurę pamiętaną (zbiór
@parameter
wyrażeń w języku Transact-SQL), do której można przekazywać wartości
Jest parametrem procedury.
parametrów i która może zwracać zarówno wartości parametrów, jak i kody
powrotu.
data_type
Procedury mogą być tworzone jako procedury trwałe lub tymczasowe 
Jest typem danych parametru. Parametr typu CURSOR moży wystąpić tylko
tymczasowe w obrębie sesji (lokalne procedury tymczasowe) lub do
jako parametr wyjściowy (OUTPUT) i musi być kwalifikowany słowem
tymczasowego użycia we wszystkich sesjach (globalne procedury tymczasowe).
kluczowym VARYING.
Procedury pamiętane mogą być tworzone do automatycznego wykonywania w
momencie startowania SQL Servera.
wart_domyślna
Jest domyślną wartością parametru (może być NULL). Wartość ta jest
Składnia:
przyjmowana, jeśli przy wywoływaniu procedury nie zostanie określona wartość
parametru.
CREATE PROC[EDURE] nazwa_procedury [;liczba]
[
OUTPUT
{@parameter typ_danych} [VARYING] [= wart_domyślna] [OUTPUT]
Oznacza, że parametr zwraca wartość.
]
[,...n]
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
[WITH
RECOMPILE oznacza, że SQL Server nie ma przechowywać planu wykonania
{
procedury, ale ma być ona rekompilowana przy każdym wykonaniu.
RECOMPILE
ENCRYPTION poleca szyfrowanie treści procedury w tabeli syscomments  nie
| ENCRYPTION
można jej odczytać za pomocą sp_helptext.
| RECOMPILE, ENCRYPTION
}
AS
]
Określa akcje wykonywane przez procedurę.
[FOR REPLICATION]
AS
wyrażenie_sql
wyrażenie_sql [...n]
Wyrażenie języka Transact-SQL.
Argumenty:
Uwagi:
nazwa_procedury
1. Maksymalny rozmiar procedury pamiętanej jest 128 MB.
#nazwa_procedury oznacza, że procedura jest lokalną procedurą tymczasową, a
2. Procedura pamiętana jest tworzona zawsze w bieżącej bazie danych.
##nazwa_procedury oznacza, że procedura jest globalną procedurą tymczasową.
3. Domyślnie parametry mają wartość NULL.
T. Pankowski, Bazy danych. Cz.V MS SQL-7 Programowanie serwera bazy danych str. V - 76
4. Za pomocą sp_depends można uzyskać informację o obiektach, do których
odwołuje się procedura.
5. Zmanę nazwy procedury można wykonać za pomocą sp_rename.
6. Aby procedura była procedurą startową, to należy ją utworzyć w bazie
danych master, a następnie użyć sp_procoption.