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