Sieci rozległe

Ćwiczenie 5
Tworzenie procedur przechowywanych oraz
kursorów w MS SQL Server
1
1.
Część teoretyczna.
1.1.
Wstęp.
Każdy szanujący się serwer baz danych zapewnia obsługę procedur przechowywanych (ang.
Stored Procedure). Procedura składowana jest obiektem bazy BD, który zawiera zbiór
określonych instrukcji języka SQL, a w zasadzie jego proceduralnej odmiany. Procedura służy
do wykonywania często powtarzanych czynności. Zamiast pisać wielokrotnie ten sam,
składający się z kilkudziesięciu linii kod SQL, możemy go umieścić w procedurze, którą
następnie możemy bardzo łatwo wywoływać. Aby można było wpływać na działanie
procedury z zewnątrz, można je sparametryzować. Dzięki temu możemy przekazać jej pewne
wartości, które będą w niej wykorzystane. Zalety procedur składowanych to:
1.2.

dzielenia kodu logiki biznesowej pomiędzy różne aplikacje,

implementacja ochrony dostępu do tabel systemowych,

zapewnienie mechanizmów bezpieczeństwa,

zwiększenie wydajności,

redukcja ruchu sieciowego
Polecenia SQL.
Do tworzenia procedur służy następujące polecenie SQL:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Następujące opcje oznaczają:
procedure_name
@parameter
data_type
VARYING
OUTPUT
RECOMPILE
ENCRYPTION
FOR
REPLICATION
nazwa procedury, może być poprzedzona znakiem # co oznacza lokalną
procedurę tymczasową, lub znakiami ## co oznacza globalną procedurę
tymczasową
nazwa parametru poprzedzona małpą @
typ parametru
oznacza iż procedura zwraca w wyniku kursor
oznacza parametr wyjściowy
powoduje iż SQL Server nie będzie przechowywał skompilowanej wersji
procedury w Cachu, tylko kompilował ją przy każdym uruchomieniu
treść (kod) zwykłej procedury znajduje się w polu text, w tabeli
syscomments; każdy użytkownik może ją wydobyć i odczytać; czasem
jednak chcemy aby nikt się nie dowiedział jak procedura jest
implementowana – wtedy używamy tej opcji, co spowoduje iż kod będzie
przechowywany w wersji zaszyfrowanej
procedura nie może być uruchamiana na komputerze Subskrybenta; jest
ona używana jako filtr, który umożliwia jej wykonywanie jedynie
podczas replikacji; ta opcja nie może być używana z RECOMPILE.
Analogiczną składnię posiada polecenie służące do zmiany procedur:
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }
]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
2
Do usuwania procedur służy natomiast proste polecenie:
DROP PROCEDURE { procedure } [ ,...n ]
za pomocą którego możemy usunąć jedną lub kilka procedur na raz.
1.3.
Procedura wybierająca.
Oto prosta procedura wybierająca wiersze z tabeli (będziemy się opierać o bazę Northwind):
CREATE PROCEDURE PokazProdukty
AS
SELECT *
FROM products
GO
Do uruchamiania procedur służy polecenie „Execute”:
exec PokazProdukty;
1.4.
Procedura z parametrem wejściowym.
Żeby działanie procedury było elastyczne musimy dodać do niej parametr:
CREATE PROCEDURE PokazProdukt
@id integer
AS
SELECT *
FROM products
WHERE ProductId = @id
GO
Aby uruchomić ww procedurę należy przekazać parametr – identyfikator produktu:
exec PokazProdukt 12;
1.5.
Procedura z parametrem wyjściowym.
Teraz mamy 2 parametry : wejściowy i wyjściowy:
CREATE PROCEDURE PokazPracownika
@id integer,
@pelna_nazwa varchar(40) OUTPUT
AS
SELECT @Pelna_nazwa = (TitleOfCourtesy+' '+FirstName+' '+LastName)
FROM Employees
WHERE EmployeeID = @id
Aby uruchomić ww procedurę należy przekazać parametr wejściowy oraz pobrać parametr
wyjściowy. Aby to zrobić musimy utworzyć najpierw zmienną lokalną typu tekstowego na
przechowywanie parametru wyjściowego. Dalej wywołujemy procedurę, która wizualnie nie
zwraca żadnego wyniku. Aby pokazać rezultat użyjemy funkcji PRINT:
DECLARE @nazwa varchar(80);
exec PokazPracownika 5, @nazwa output;
PRINT 'NAZWA PRACOWNIKA : '+@nazwa;
1.6.
Deklaracja zmiennych lokalnych.
Czasem musimy gdzieś przechować jakąś wartość. Do tego celu służą zmienne, których już
użyliśmy powyżej. Do deklaracji zmiennych służy instrukcja:
DECLARE
{{ @local_variable data_type }
Nazwa zmiennej musi być poprzedzona małpką @. Przykład:
DECLARE @numer integer;
Zmienne lokalne muszą się być wykorzystane w tym samym pakiecie. To oznaczy, że dla linii
kodu rozdzielonych instrukcją GO zmienne lokalne będą niewidoczne.
Zmienne globalne są poprzedzone podwójną małpką @@.
3
1.7.
Przypisywanie wartości zmiennych lokalnych.
Do przypisania wartości zmiennej służy instrukcja:
SET { @local_variable = expression }
Przykład użycia:
SET @tekst = ‘Hello World’;
1.8.
Zwracanie wartości jako rekord.
Niekiedy chcemy zwrócić z procedury jakąś pojedynczą wartość lub zbiór wartości, ale bez
użycia parametrów, a jako rekord. Można to zrealizować za pomocą polecenia SELECT bez
frazy FROM:
SELECT ‘Hello world!’;
Gdy zwracamy kilka wartości, oddzielamy je przecinkami:
SELECT 1, 2, 3;
1.9.
Instrukcja warunkowa IF ... ELSE.
Wewnątrz procedury możemy korzystać z różnych instrukcji kontrolujących bieg programu.
Jedną z powszechniejszych jest instrukcja warunkowa IF. Jej składnia jest bardzo prosta:
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Przykład wykorzystania:
IF (@zmienna is null) THEN
SET @zmienna = 0
ELSE
SET @zmienna = @zmienna + 1;
1.10. Bloki instrukcji.
Niekiedy zachodzi potrzeba umieszczenia wielu instrukcji, tam gdzie wymagana jest
pojedyncza instrukcja. Często ma to miejsce w instrukcji warunkowej. Do tego celu używa się
instrukcji złożonej:
BEGIN
{
sql_statement
| statement_block
}
END
Przykład użycia:
IF (@zmienna is null) THEN
BEGIN
SET @zmienna = 0;
SET @ok = 1;
END
1.11. Generowanie błędów wewnątrz procedury.
Czasem zaistnieje taka sytuacja iż wewnątrz procedury chcemy zgłosić błąd. Zazwyczaj ma to
miejsce podczas walidacji parametrów – kiedy mają one nieprawidłowe wartości. Do tego celu
służy instrukcja RAISEERROR:
RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Najczęściej używamy jej z 3 parametrami (pełny opis w dokumentacji Transact-SQL):
msg_str
severity
treść komunikatu błędu
określa na ile dany błąd jest krytyczny, im wyższa tym bardziej poważny
4
state
błąd; wartości od 0 do 18 mogą być wywoływane przez dowolnego
użytkownika, poziom od 18 do 25 jest zarezerwowany dla członków roli
sysadmin
liczba całkowita z przedziału od 1 do 127 określająca stan wywołania
błądu
Przykład wykorzystania:
IF @kwota < 0
BEGIN
RAISERROR('Kwota musi być dodatnia!', 14, 1)
RETURN
END
1.12. Pętla WHILE.
Język Transact-SQL posiada nawet tak ciekawe mechanizmy jak pętle. Ich składnia jest
następująca:
WHILE Boolean_expression
{ sql_statement | statement_block }
[ BREAK ]
{ sql_statement | statement_block }
[ CONTINUE ]
Jak widać w bloku pętli mogą wystąpić instrukcje BREAK oraz CONTINUE, których
działanie jest oczywiste. Przykład wykorzystania:
WHILE @licznik > 0
exec procedura;
1.13. Uruchamianie SQL wygenerowanego w locie.
Nieraz nie możemy określić kodu SQL na etapie jego tworzenia. Jesteśmy zmuszeni najpierw
go wygenerować dynamicznie w procedurze i potem uruchomić. Do tego celu służy znana
nam już instrukcja EXEC:
EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
Przykład użycia:
EXEC(‘select * from customers’);
1.14. Kursory.
Kursor to jest kolekcja elementów danych w buforze, która odwzorowuje stan aktualny tabel
bazy danych. W tej kolekcji w każdy moment może być aktywnym tylko jeden rekord. Kursor
zawiera mechanizmy przesuwania pomiędzy rekordami oraz wymiany danymi pomiędzy bazą
a kursorem. Kursorów używamy podobnie jak zwykłego polecenia wybierającego SELECT, z
tą różnicą że polecenie SELECT przerabia i zwraca cały wynik w jednej instrukcji, a kursor
umożliwia nam iterację rekord po rekordzie. Deklarowanie kursorów jest opisane w HELPu.
Użycie kursora w języku Transact-SQL jest następujące (przykład):
/* deklaracja zmiennych do przechowywania imienia i nazwiska
pracownika */
DECLARE @lname varchar(40), @fname varchar(20)
/* deklaracja kursora */
DECLARE employees_cursor CURSOR FOR
/* polecenie SELECT przypisane do kursora */
SELECT lastname, firstname FROM employees
ORDER BY 1,2
/* otwarcie kursora */
OPEN employees_cursor
/* Pobranie rekordu z kursora, do odpowiednich zmiennych.
Kolejność zmiennych odpowiada kolejności pól w poleceniu SELECT.
Zawsze pierwszy rekord należy pobrać przed pętlą WHILE */
FETCH NEXT FROM employees_cursor
INTO @lname, @fname
5
/* Pętla WHILE sprawdza zmienną globalną @@FETCH_STATUS,
która określa czy jest następny rekord do pobrania */
WHILE @@FETCH_STATUS = 0
BEGIN
/* wypisanie pracownika na ekranie */
PRINT 'Pracownik: ' + @fname + ' ' + @lname;
/* pobranie kolejnego rekordu */
FETCH NEXT FROM employees_cursor
INTO @lname, @fname
END
/* zamknięcie kursora */
CLOSE employees_cursor
/* zwolnienie zasobów */
DEALLOCATE employees_cursor
GO
Szczegóły są opisane w komentarzach.
Kursory mogą być także zwracane jako rezultat procedury, przykłady są opisane w HELPu
(patrz hasło „CREATE PROCEDURE”).
1.15. Funkcje wbudowane w serwer.
SQL Server zapewnia nam bardzo bogate zaplecze jeśli chodzi o różnego rodzaju funkcje,
które możemy wykorzystać w naszych procedurach. Ich lista jest bardzo długa, i nie sposób
wymienić ich wszystkich. Najczęściej używane kategorie funkcji to tekstowe, matematyczne,
daty i czasu, statystyczne czy systemowe. Poniżej znajduje się lista często używanych funkcji,
których nazwy dość dobrze opisują ich działanie:
SUBSTRING, REPLACE, UPPER, LOWER, LTRIM, RTRIM
ABS, FLOOR, ROUND, SQRT, SIGN, RAND, POWER, EXP, SIN, COS
GETDATE, YEAR, MONTH, DAY
NULLIF, CURRENT_USER, CASE
HAS_DBACCESS, IS_MEMBER, IS_SRVROLEMEMBER
Po dokładny opis odsyłam do help-a Transact-SQL, hasło functions.
2.
Zadania do wykonania (w oparciu o bazę Pubs).
2.1.Utwórz procedurę „Title_authors”, zwracającą tytuły książek i nazwiśka ich autorów. Dla formowania
zapytań SQL do bazy danych wykorzystaj tabeli: Titles, Authors oraz Titleauthor. Za dopomogą
procedury systemowej sp_helptext sprawdź kod źródłowy
stworzonej procedury. Napisz kod dla
uruchomienia procedury.
2.2.Utwórz procedurę „MyProc” wybierającą wszystkich autorów mieszkających w wyznaczonym stanu
oraz listę napisanych przez tych autorów książek. Dla formowania zapytań SQL do bazy danych
wykorzystaj następne tabeli bazy danych: authors, titleautor, titles. Parametrem wejściowym procedury
musi być stan zamieszkania autorów. Rezultat procedury musi zawierać trzy kolumny: Imię, Nazwisko
autora oraz Tytuł książki. Napisz kod dla uruchomienia procedury. Za dopomogą procedury systemowej
sp_helptext sprawdź kod źródłowy
stworzonej procedury.
2.3.Do poprzedniej procedury dodaj nową procedurę grupy. Procedura musi realizować poszukiwanie
książek , cena których jest w wyznaczonym zasięgu. Parametrami wejściowymi procedury są: typ książki,
nakład, cena dolna, cena górna. Dla formowania zapytań SQL do bazy danych wykorzystaj tabeli titles
oraz publishers. Rezultat procedury musi zawierać dwie kolumny: tytuł książki oraz wydawnictwo.
Napisz kod dla uruchomienia procedury.
2.4.Utwórz procedurę „get_sum” , zwracającą sumę cen wszystkich sprzedanych książek napisanych
przez autorzy, mieszkające w wyznaczonym stanu. Procedura musi mieć dwa parametry wejściowe oraz
jeden wyjściowy. Parametrami wejściowymi są stan i typy książek(odpowiedni pola „state” i „type” w
tabelach). Parametr wyjściowy musi zawierać rezultat – sumę cen sprzedanych książek. Dla formowania
zapytań SQL wykorzystaj tabeli authors, titleauthor, titles. Zapytanie SQL musi ewentualne zawierać
funkcję agregowania SUM. Ciało procedury musi sprawdzać czy nie są puste parametry wejściowe.
6
Kiedy te parametry są puste suma musi być wyznaczona na wszystkich sprzedanych książkach. Napisz
kod dla uruchomienia procedury.
2.5. Za dopomogą procedury sp_depends wyznacz wszystkie obiekty bazy danych którzy są skojarzone ze
stworzonymi procedurami.
2.6. Utwórz kursor „aut_curs” który musi odwzorować z tabeli authors dani autorów zamieszkanych w
wyznaczonym stanie. Stan zamieszkania(wartość kolumny state) może być nadany przez zmienną
lokalną. Kursor musi być dwukierunkowym (typu „Scroll”) oraz odwzorować dla każdego rekordu
następne elementy: stan zmiennej lokalnej w moment aktywacji kursora, identyfikator autora(„au_id”)
,nazwisko autora („au_fname”), oraz stan zamieszkania(„state”). Napisz kod dla uaktualnienia,
zamknięcia i usunięcia kursora, linie kodu do ustalenia wskaźnika na pierwszą pozycję, do przesunięcia
wskaźnika do kolejnego wierszu. Sprawdź działanie kodu przy przesuwaniu wskaźnika po wierszach
kursora.
2.7. Do kodu stworzonego w p. 2.6 dodaj linię kodu, który modyfikuje zmienną lokalną po odtworzeniu
kursora. Popatrz czy zostali wprowadzone te zmiany do otwartego już kursora? Objaśnij ten rezultat.
2.8. Utwórz procedurę do stworzenia i odtwarzania kursora. Zanalizuj HELP z hasłem „CREATE
PROCEDURE” oraz napisz kod do stworzenia i odtwarzania kursora. Procedura musi zawierać parametr
wyjściowy typu kursor oraz uruchamiać sam kursor w tej zmiennej. Kursor musi zawierać kolumny:
title_id, title i price z tabeli titles. Napisz kod do uruchomienia procedury, aktualizacji kursora, nawigacji
po wierszach kursora, zamknięcia i usunięcia kursora. Sprawdź działanie kodu .
2.9. Uruchom procedurę systemową sp_cursor_list dla odczytania informacji o aktualnych kursorach. Dla
sprawdzenia tej procedury uruchom bez zamknięcia kursor w p.2.6. Zanalizuj rezultaty.
3.
Pytania kontrolne.
3.1.
Jak zadeklarować zmienne lokalne, a jak globalne.
3.2.
Co to jest blok instrukcji. Do czego służy.
3.3.
Do czego służą instrukcje BREAK i CONTINUE w pętli WHILE.
3.4.
Czy gdzieś w bazie danych jest przechowywany kod procedur
składowanych. Jeśli tak to gdzie.
3.5.
Co to jest i do czego służy kursor. Jakich instrukcji używa się do
ich obsługi.
3.6.
Jakiej funkcji można użyć do generowania błędów, jakie są jej
parametry.
3.7.
Co oznaczają opcje RECOMPILE i ENCRYPTION w definicji
procedury.
7