Procedury składowane i wyzwalaczemath.uni.lodz.pl/~bleja/asb/ITA-101-Modul_09_v2.pdf · 3....
Transcript of Procedury składowane i wyzwalaczemath.uni.lodz.pl/~bleja/asb/ITA-101-Modul_09_v2.pdf · 3....
ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 9
Wersja 1.0
Procedury składowane i wyzwalacze
Spis treści
Procedury składowane i wyzwalacze ................................................................................................... 1
Informacje o module ............................................................................................................................ 2
Przygotowanie teoretyczne ................................................................................................................. 3
Przykładowy problem .................................................................................................................. 3
Podstawy teoretyczne.................................................................................................................. 3
Przykładowe rozwiązanie ............................................................................................................. 8
Porady praktyczne ..................................................................................................................... 13
Uwagi dla studenta .................................................................................................................... 14
Dodatkowe źródła informacji..................................................................................................... 14
Laboratorium podstawowe ................................................................................................................ 16
Problem 1 (czas realizacji 10 min) .............................................................................................. 16
Problem 2 (czas realizacji 10 min) .............................................................................................. 17
Problem 3 (czas realizacji 15 min) .............................................................................................. 18
Problem 4 (czas realizacji 10 min) .............................................................................................. 19
Laboratorium rozszerzone ................................................................................................................. 21
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 2/21
Informacje o module
Opis modułu
W tym module znajdziesz informacje na temat sposobu działania oraz
podstaw tworzenia procedur składowanych. Dowiesz się, na czym polega
różnica pomiędzy zwykłym zapytaniem T-SQL a procedurą składowaną oraz
co to jest kompilacja i rekompilacja procedury. Poznasz również specjalny
rodzaj procedury składowanej – wyzwalacz.
Cel modułu
Celem modułu jest zapoznanie czytelnika z procedurami składowanymi oraz
wyzwalaczami w środowisku bazodanowym Microsoft SQL Server 2008 oraz
zaprezentowanie, jak wykorzystać je w przykładowym projekcie.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
• wiedział, jak napisać własną procedurę składowaną
• umiał rozbudowywać gotowe procedury składowane
• umiał tworzyć proste wyzwalacze
• umiał zarządzać wyzwalaczami
• umiał wykorzystywać wyzwalacze do optymalizacji i automatyzacji
działania SQL Server 2008
Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
• potrafić samodzielnie stworzyć bazę danych wraz z jej obiektami
(patrz: moduł 4)
• znać podstawową składnię języka T-SQL
• umieć napisać własne zapytania w języku T-SQL (patrz: moduły 5 i 6)
• znać zaawansowane mechanizmy języka T-SQL (patrz: moduł 8)
• wiedzieć, na czym polega bezpieczeństwo w bazach danych (patrz:
moduł 10)
Mapa zależności modułu
Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem
do realizacji tego modułu należy zapoznać się z materiałem zawartym
w modułach 3, 5, 6, 8 i 10.
Rys. 1 Mapa zależności modułu
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 3/21
Przygotowanie teoretyczne
Przykładowy problem
Podczas wytwarzania bazy danych wiąże ze sobą grupę osób, do której należą projektant bazy
danych, administrator systemu zarządzania bazą danych, administrator bazy danych oraz
programista bazy danych. Bardzo ważne jest, żeby wszyscy biorący udział w wytwarzaniu systemu
bazodanowego współdziałali ze sobą na różnych etapach. Najwięcej wspólnego z programowaniem
w T-SQL ma programista bazy danych. Od niego w dużej mierze zależy, czy wytworzone funkcje,
procedury składowane, wyzwalacze i inne element będą działały we właściwy sposób. To on jest
odpowiedzialny za przygotowanie odpowiednich elementów programowych, które następnie
będzie wykorzystywał projektant aplikacji i programista aplikacji dostępowej.
Kolejnym zadaniem jakie stoi przed programistą i administratorem bazy danych jest stworzenie
odpowiedniej liczby wyzwalaczy, które są pewnym rodzajem procedur składowanych, z tymże
wywoływanych niejawnie. Należy pamiętać, iż użycie procedur składowanych prowadzi do
powstania kolejnej warstwy separującej użytkownika końcowego od fizycznych danych, tym samym
powodując maskowanie fizycznej struktury bazy. Z tego powodu bardzo ważne jest przemyślane i
odpowiednie przygotowanie zbioru procedur składowanych.
Podstawy teoretyczne
Procedury składowane
Procedura składowana (ang. stored procedure) jest nazwanym zbiorem poleceń w języku SQL, który
jest przechowywany na serwerze baz danych i jest kompilowany przy pierwszym wykonaniu.
Procedury wnoszą do środowiska bazodanowego przetwarzanie warunkowe i możliwości
programistyczne.
W systemie zarządzania bazami danych wykonanie dowolnego fragmentu kodu języka SQL wiąże
się z pewnym ciągiem czynności – począwszy od sprawdzenia składni, aż po kompilację i
wykonanie.
Proces wykonywania zapytania przez SQL Server
Proces wykonania pojedynczego zapytania w języku T-SQL w Microsoft SQL Server 2008 dzieli się
na następujące etapy:
1. Sprawdzenie i rozdzielenie kodu na fragmenty - dokonywany jest podział kodu na fragmenty
nazywane symbolami. Proces ten nazywamy często nazywany jest analizą leksykalną.
2. Sprawdzenie kodu pod względem poprawności semantycznej i syntaktycznej –
dokonywana jest kontrola poprawności semantycznej, czyli sprawdzenie, czy kod nie
odwołuje się do nieistniejących obiektów lub nie używa nieistniejących poleceń oraz kontrola
poprawności syntaktycznej, podczas której sprawdzana jest poprawność użytej składni.
3. Standaryzacja wyodrębnionej części kodu – silnik wykonywania zapytań zapisuje kod w
jednoznacznej postaci (np. usuwa niepotrzebne symbole).
4. Optymalizacja – Microsoft SQL Server posiada wewnętrzny proces zwany Optymalizatorem
Zapytań, który wybiera optymalny sposób dostępu do danych, tzn. taki plan wykonania
zapytania (ang. execution plan), w którym serwer będzie skanował (przeszukiwał)
najmniejszą ilość stron danych (o fizycznej strukturze danych w Microsoft SQL Server możesz
przeczytać w module 6). Na optymalizację szczególny wpływ ma struktura indeksów oraz
sposób łączenia tabel.
5. Kompilacja i wykonanie – zapytanie jest kompilowane według optymalnego planu
wykonania i w takiej postaci wykonywane.
6. Zwrócenie wyników – wyniki zapytania zwracane są do klienta.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 4/21
Proces wykonywania procedury składowanej
Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie pojedynczego
zapytania SQL. Utworzenie i pierwsze wykonanie procedury w Microsoft SQL Server można
podzielić na następujące kroki:
1. Utworzenie definicji procedury składowanej – wykonywane jest polecenie CREATE
PROCEDURE.
2. Sprawdzanie kodu procedury pod względem poprawności – dokonywana jest kontrola
poprawności syntaktycznej kodu procedury.
3. Zapamiętanie procedury w bazie danych – nazwa procedury i jej kod (tzw. ciało procedury)
są zapisywane do odpowiednich widoków systemowych bazy danych (sysobjects oraz
syscomments).
4. Wywołanie procedury – procedura składowana jest wywoływana z odpowiednimi
parametrami poleceniem EXEC.
5. Właściwe wykonanie procedury – optymalizacja planu wykonania i kompilacja.
6. Buforowanie planu wykonania – skompilowany optymalny plan wykonania jest zapisywany
w tzw. buforze procedur, skąd jest wczytywany przy następnym wywołaniu procedury.
Na rys. 2 pokazano schemat tworzenia i wykonania procedury składowanej w środowisku SQL
Server 2008.
Rys. 2 Tworzenie i wykonanie procedury składowanej w SQL Server 2008
Rekompilacja procedur składowanych
Czasami zachodzi potrzeba ponownej kompilacji procedury składowanej. Dzieje się tak, gdy
wydajność skompilowanej procedury gwałtownie spada (może tak być z wielu powodów, np.
zmiany struktury indeksów lub zapisania dużej ilości rekordów), gdy istnieje potrzeba kompilacji
procedury przy każdym jej wykonaniu (powody mogą być te same, co w pierwszej sytuacji) lub gdy
zmianie ulega kod samej procedury (gdy użyjemy polecenia ALTER PROCEDURE).
Rekompilacji, czyli ponownej kompilacji procedury, można dokonać na dwa sposoby:
• dodając w definicji procedury klauzulę WITH RECOMPILE.
• używając specjalnej systemowej procedury rekompilującej (w Microsoft SQL Server jest to
procedura sp_recompile).
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 5/21
Tworzenie procedur składowanych
Do tworzenia procedur składowanych używamy polecenia języka SQL CREATE PROCEDURE (lub w
skrócie CREATE PROC):
CREATE { PROC | PROCEDURE } [nazwa_schematu.]nazwa_procedury [ @parametr typ_danych[,…n] ] [ WITH <opcje_procedury> [,…n] ] [ FOR REPLICATION ] AS cialo_procedury <opcja_procedury> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE_AS_Clause ]
W definicji procedury składowanej określamy:
• nazwę procedury
• nazwy
• typy danych
• kierunek działania parametrów procedury
• ciało procedury (kod wykonywany przez procedurę)
Opcjonalnie możemy również zadeklarować, czy procedura ma być przy każdym wykonaniu
rekompilowana.
Poniżej podano przykład utworzenia prostej procedury składowanej nie zawierającej żadnych
parametrów:
CREATE PROCEDURE p_pracownicy AS SELECT imie, nazwisko FROM Osoby
Wywołanie procedury składowanej
Do wywołania procedury składowanej służy polecenie EXECUTE (lub w skrócie EXEC):
[ { EXEC | EXECUTE } ] [ @return_status = ] { module_name [ ;numer ] | @module_name_var } [ [ @parametr = ] { wartość | @zmienna [ OUTPUT ] | [ DEFAULT ] } ] [,…n] [ WITH RECOMPILE ]
Przykładowo dla utworzonej wcześniej procedury składowanej p_pracownicy wywołanie będzie
wyglądało w następujący sposób:
EXEC p_pracownicy GO
Parametry procedur składowanych
Procedury składowane mogą przyjmować parametry wywołania. Ilość i typ danych, które należy
podać przy wywołaniu procedury składowanej, określamy w trakcie tworzenia procedury (używając
polecenia CREATE PROCEDURE). W zależności od tego, czy parametry będą potrzebne do
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 6/21
wykonania procedury, czy też mają być one przez procedurę zwrócone, wyróżniamy dwa rodzaje
parametrów: wejściowe (INPUT) oraz wyjściowe (OUTPUT).
Możliwe jest też zdefiniowanie w procedurze parametru przejściowego (będącego jednocześnie
wejściowym i wyjściowym), czyli parametru, którego wartość podajemy przy wywołaniu, a która
może zostać zmieniona podczas działania procedury.
CREATE PROCEDURE p_pracownicy @nazwisko varchar(40)='Smith' AS SELECT imie, nazwisko FROM Osoby WHERE nazwisko=@nazwisko GO
Zalety i wady procedur składowanych
Procedury składowane dzięki temu, że są zapisane na serwerze oraz dzięki skompilowanemu
planowi wykonania przechowywanemu w buforze procedur posiadają dwie zasadnicze zalety:
• zwiększają wydajność bazy danych
• ograniczają ruch w sieci (przesyłane są tylko nazwy procedur i wartości parametrów)
Ponadto procedury składowane mają kilka zalet z punktu widzenia programistów aplikacji
bazodanowych:
• Zapewniają jedną logikę biznesową dla wszystkich aplikacji klienckich.
• Przesłaniają szczegóły tabel w bazie danych (przezroczystość struktury dla zwykłego
użytkownika aplikacji).
• Umożliwiają modyfikację danych bez bezpośredniego dostępu do tabel bazy danych.
• Dostarczają mechanizmów bezpieczeństwa (można nadawać uprawnienia do wykonywania
procedur poszczególnym użytkownikom bazy danych).
• Umożliwiają programowania modularne (procedura zostaje zapisana w bazie danych, skąd
można ją wielokrotnie wywoływać; procedurę może pisać osoba wyspecjalizowana w bazach
danych – programista aplikacji jedynie ją wywoła).
• Zapewniają szybsze wykonanie (jeśli wymagane jest wykonanie dużej liczby zapytań,
procedury składowane są szybsze, ponieważ wykonują się na serwerze, są optymalizowane i
umieszczane w pamięci przy pierwszym wykonaniu).
• Zmniejszają ruch sieciowy (jeśli zachodzi potrzeba wykonania wielu zapytań w T-SQL na raz,
można je zastąpić wywołaniem jednej procedury składowanej).
Jeśli w ogóle można mówić o wadach procedur składowanych, to w zasadzie można wspomnieć o
kilku aspektach:
• Następstwem rekompilacji czasem jest zmniejszenie wydajności procedury (czyli
administrator baz danych musi wiedzieć, kiedy przeprowadzić rekompilację).
• W przypadku zagnieżdżania procedur składowanych należy pamiętać o tym, że zmienia się
kontekst wykonania (procedura zagnieżdżana wykonuje się z uprawnieniami innej
procedury).
• Wreszcie, aby tworzyć dobre (tzn. poprawnie działające) procedury składowane niezbędne
jest poznanie zaawansowanych mechanizmów języka programowania baz danych (np. T-
SQL), takich jak zmienne, funkcje i procedury systemowe czy obsługa błędów.
Wyzwalacze
Wyzwalacz (ang. trigger) jest specjalnym rodzajem procedury składowanej. W przeciwieństwie do
zwykłej procedury składowanej wyzwalacz nie może zostać jawnie wywołany.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 7/21
Wyzwalacz jest wywoływany w reakcji na określone akcje. Akcje te to wykonanie przez użytkownika
określonego polecenia SQL (INSERT, UPDATE, DELETE) na danej tabeli, dla której został określony
wyzwalacz.
Tworzenie wyzwalaczy
Wyzwalacze tworzymy używając polecenia CREATE TRIGGER. W definicji wyzwalacza określamy:
• nazwę wyzwalacza
• dla jakiej tabeli tworzymy wyzwalacz
• na jakie akcje wyzwalacz będzie reagował
• jakiego typu wyzwalacz tworzymy
• ciało wyzwalacza (odpowiednik ciała procedury składowanej) – czyli kod wykonywany przez
wyzwalacz
W SQL Server 2008 istnieją trzy rodzaje wyzwalaczy:
1. Wyzwalacze obsługujące operacje DML (ang. Data Manipulation Language), czyli INSERT,
UPDATE oraz DELETE wykonywane na tabeli lub widoku.
CREATE TRIGGER [nazwa_schematu.]nazwa_wyzwalacza ON { table | view } [ WITH <dml_opcje_wyzwalacza> [,…n] ] { FOR | AFTER | INSTEAD OF } [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS wyrazenie_sql [ ; ] [,…n] <dml_opcje_wyzwalacza> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
2. Wyzwalacze obsługujące operacje DDL (ang. Data Definition Language), czyli CREATE,
ALTER, DROP oraz pewne procedury składowane, które wykonują operacje DDL.
CREATE TRIGGER nazwa_wyzwalacza ON { ALL SERVER | DATABASE } [ WITH <ddl_opcje_wyzwalacza> [,…n] ] { FOR | AFTER } { rodzaj_zdarzenia | event_group } [,…n] AS wyrazenie_sql [ ; ] [,…n] <ddl_opcje_wyzwalacza> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
3. Wyzwalacze obsługujące zdarzenie logowania (LOGON), które jest wywoływane, kiedy
ustalana jest sesja logującego się użytkownika.
CREATE TRIGGER nazwa_wyzwalacza ON ALL SERVER [ WITH <opcje_wyzwalacza_logon> [,…n] ] { FOR | AFTER } LOGON AS wyrazenie_sql [ ; ] [,…n]
<opcje_wyzwalacza_logon> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 8/21
Zastosowanie wyzwalaczy
Wyzwalacze znajdują wiele zastosowań w bazach danych. Przede wszystkim stosujemy wyzwalacze
wszędzie tam, gdzie w inny sposób nie da się weryfikować integralności danych, a zwłaszcza ich
zgodności z regułami logiki biznesowej. Na przykład chcemy, by w pewnej kolumnie tabeli
wstawiane były tylko wartości unikalne, ale jednocześnie zezwalamy na wstawienie wartości NULL.
Jedynym rozwiązaniem jest użycie wyzwalacza, który sprawdzi, czy wstawiana właśnie wartość już
w danym polu wystąpiła, a jeżeli użytkownik wstawia wartość NULL, to wyzwalacz mu na to
pozwoli (tego efektu nie można osiągnąć innymi metodami, np. używając indeksów, ustawiając
właściwość unikalności kolumny lub używając kryteriów sprawdzających dane wstawiane w
kolumnę).
Drugie zastosowanie to wszelkiego typu automatyzacja zadań administracyjnych w bazie danych
(wszelkiego rodzaju „przypominacze”, obsługa nietypowych działań czy chociażby wysyłanie
wiadomości przez email lub pager).
Wreszcie z uwagi na pewne cechy wyzwalacze pozwalają na określony typ przetwarzania
transakcyjnego.
Przykładowe rozwiązanie
Tworzenie i uruchomienie procedury składowanej
W cel utworzenia nowej procedury składowanej należy z paska narzędziowego wybrać New Query i
wpisać kod procedury. Przykładowo:
CREATE PROCEDURE dbo.getAllCustomers AS BEGIN SELECT [CustomerID], [CompanyName] FROM [Northwind].[dbo].[Customers] END
Następnie należy wykonać skrypt wciskając F5 lub wybierając z paska narzędziowego Execute. Jeśli
procedura składowana została utworzona poprawnie, powinieneś otrzymać komunikat
„Command(s) completed successfully” oraz zauważyć, że została dodana nowa procedura
składowana w obszarze Object Explorer, w gałęzi Databases -> Northwind -> Programmability ->
StoredProcedures, co pokazano na rys. 3.
Jeśli procedury nie widać w wyżej wymienionej gałęzi, należy ją odświeżyć. Jeśli procedura jest już
utworzona, a mimo to zażądano kolejnego jej utworzenia, to otrzymamy następujący komunikat:
„There is already an object named ‘getAllCustomers’ in the databases”.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 9/21
Rys. 3 Tworzenie nowej procedury
Aby uruchomić procedurę składowaną, wystarczy użyć polecenia EXECUTE (lub krócej EXEC).
Można także kliknąć prawym klawiszem myszki procedurę i wybrać Execute Stored Procedure, co
wywoła okienko, w którym można przekazać do procedury jakieś parametry. Na rys. 4 widać, że
rozwijane menu udostępnia także inne opcje, takie jak modyfikacja lub usunięcie.
Rys. 4 Wywołanie procedury
Inne metody tworzenia procedur składowanych
Inną metoda tworzenia procedury składowanej jest skorzystanie z gotowego schematu procedury.
W tym celu kliknij prawym przyciskiem myszy na gałęzi Stored Procedures i wybierz New
StoredProcedure – uzyskasz w ten sposób gotowy schemat procedury składowanej, co pokazano
na rys. 5.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 10/21
Rys. 5 Tworzenie nowej procedury według wzorca
Procedury składowane a polisy
W SQL Server 2008 wprowadzono politykę opartą o Management Framework dla silnika SQL, dzięki
której zarządzanie serwerem będzie bardziej zautomatyzowane w oparciu o pewne reguły, a nie
skrypty.
Zadaniem DMF (z ang. Declarative Management Framework) jest umożliwienie administratorowi
zarządzania serwerami za pomocą definiowanych przez administratora reguł. W SQL Server 2008
mamy gotowe szablony reguł oparte o najlepsze praktyki zaimplementowane w narzędziu Best
Practices Analyzer, używanym przez administratorów z poprzednimi wersjami systemu SQL Server.
Korzystając z mechanizmu polis możemy stworzyć restrykcje dla procedur składowanych o postaci:
brak możliwości definiowania przez programistę bazy danych procedur składowanych z prefiksem
„sp_”. Restrykcję tę wprowadzimy na bazie danych Biblioteka.
W ramach Object Explorer rozwijamy zakładkę Management a następnie Policy Menagement. W
pierwszym kroku należy dodać warunki, jakie będą dołączone do polisy.
Jako pierwszy stwórzmy warunek, który będzie pilnował, żeby nazwa procedury składowanej nie
posiadała prefiksu „sp_”. Konfigurację tego warunku pokazano na rys. 6.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 11/21
Rys. 6 Stworzenie warunku na procedurę składowaną
Następnie należy stworzyć warunek dotyczący bazy danych, na której polisa, którą za chwilę
założymy, będzie działała. Zostało to zilustrowane na rys. 7.
Rys. 7 Stworzenie warunku na bazę danych
W kolejnym kroku należy wystawić polisę i dodać do niej wcześniej stworzone warunki.
Konfiguracje wystawiania polisy pokazano na rys. 8.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 12/21
Rys. 8 Założenie polisy
Kiedy mamy już wystawiona polisę i chcemy ją przetestować, powinniśmy kliknąć prawym
przyciskiem myszy bazę danych Biblioteka i wybrać New Query. Następnie w edytorze możemy
wpisać następujący kod, służący do utworzenia pustej procedury składowanej:
CREATE PROCEDURE sp_testowa AS GO
Zauważmy, ze wystąpił błąd. Procedura nie została utworzona, gdyż zadziałała restrykcja DMF, co
pokazano na rys. 9.
Rys. 9 Błąd procedury i zadziałanie polisy
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 13/21
Tworzenie wyzwalacza
Stwórzmy wyzwalacz, którego zadaniem będzie nie dopuścić do zalogowania wskazanego przez nas
użytkownika. W tym celu na początku dodajemy użytkownika zbd_user i kojarzymy go z bazą
danych AdventureWorks. Następnie tworzymy odpowiedni wyzwalacz, który nie pozwoli
użytkownikowi zalogować się do Microsoft SQL Server Management Studio. W tym celu klikamy
prawym przyciskiem myszy bazę AdventureWorks i z menu kontekstowego wybieramy New
Query. W oknie edycji zapytania wpisujemy następujący kod:
USE AdventureWorks GO CREATE TRIGGER trgRestrictUser ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF (ORIGINAL_LOGIN() = 'zbd_user' AND APP_NAME() = 'Microsoft SQL Server Management Studio') ROLLBACK; END
Aby przetestować działanie utworzonego wyzwalacza, uruchamiamy drugą instancję SQL Server
Management Studio. W oknie Connect to Server Authentication wybieramy SQL Server
Authentication, w polu Login wpisujemy zbd_user, a w polu Password – user. Następnie klikamy
Connect. W efekcie powinien pokazać się błąd logowania jak na rys. 10.
Rys. 10 Bład logowania
Porady praktyczne
Procedury składowane
• W Microsoft SQL Server przy kontroli poprawności kodu procedury w trakcie jej tworzenia
serwer nie sprawdza, czy istnieją obiekty (tabele, widoki), do których procedura się
odwołuje. Sprawdzenie to następuje dopiero przy wykonaniu procedury (w przypadku
odwołania do nieistniejącego obiektu procedura zgłosi błąd).
• Rekompilacja procedury składowanej nie oznacza utworzenia procedury na nowo. Oznacza
utworzenie nowego planu wykonania i zapisanie go do bufora procedury na miejsce
poprzednio skompilowanego planu tej samej procedury.
• Systemowe procedury składowane w systemie Microsoft SQL Server 2008 są
przechowywane w bazie master, zaś w ich nazwach pojawia się prefiks „sp_”.
• Zgodnie z dobra praktyka programowania baz danych, procedury składowane użytkownika w
SQL Server 2008 nie powinny zaczynać się od prefiksu „sp_”. Prefiksem tym obarczone są
systemowe procedury składowane. W sytuacji kiedy kompilator zobaczy procedurę
składowaną o takiej nazwie, będzie jej szukał w procedurach systemowych jako tych, które
już są skompilowane, a ich plan wykonania jest zapamiętany w buforze procedur. Dopiero
kiedy jej tam nie znajdzie zacznie ją kompilować według poznanego schematu. Spowoduje to
wydłużenie czasu wykonania procedury składowanej.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 14/21
• Stworzenie polisy, która nie pozwoli programiście utworzyć procedury składowanej
zaczynającej się od prefiksu „sp_” spowoduje, że pierwsza próba stworzenia procedury o
takiej nazwie pociągnie za sobą konieczność uruchomienia sprawdzenia polisy, a zatem czas
wykonania polecenia nieznacznie się wydłuży. Kolejna próba stworzenia procedury
niezgodnej z polisą spowoduje natychmiastowe wyświetlenie komunikatu o naruszeniu
restrykcji.
• Z punktu widzenia bezpieczeństwa fizycznej struktury bazy danych procedury składowane
stanowią dodatkowy poziom izolacji pomiędzy warstwą aplikacji dostępowej do bazy danych
a warstwą fizyczna bazy danych.
• Poprzez zastosowanie procedur składowanych możemy uniknąć jednego z
najpopularniejszych ataków na bazę danych, tzw. Wstrzykiwania kodu SQL z racji tego iż w
sieci pomiędzy bazą danych a aplikacją dostępową nie jest przesyłany kod SQL tylko nazwa
procedury składowanej wraz z jej parametrami.
Wyzwalacze
• Wyzwalacze mogą być tworzone bezpośrednio w Microsoft SQL Server 2008 Database
Engine za pomocą zwykłych zapytań napisanych w języku T-SQL lub poprzez specjalne
metody w Microsoft .NET Framework Common Language Runtime (CLR), które po
utworzeniu importowane są do instancji serwera bazodanowego.
• Microsoft SQL Server 2008 pozwala na tworzenie wielu wyzwalaczy dla specyficznego
zdarzenia.
• Do tworzenia wyzwalaczy potrzebne są specjalne uprawnienia w bazie danych.
• Następujące instrukcje języka T-SQL nie są dozwolone w wyzwalaczach DML: ALTER
DATABASE, LOAD DATABASE, RESTORE DATABASE, CREATE DATABASE, LOAD LOG,
RESTORE LOG, DROP DATABASE i RECONFGURE. Dodatkowo powyższe instrukcje nie mogą
być użyte wewnątrz ciała wyzwalacza DML.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
• rozumiesz, co to jest procedura składowana oraz wyzwalacz
• rozumiesz zasadę działania procedur składowanych i wyzwalaczy
• znasz składnię zaawansowanego języka Transact-SQL
• umiesz dodawać użytkowników do SQL Server
• rozumiesz różnicę pomiędzy różnymi rodzajami wyzwalaczy
Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że
rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego
w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.
Dodatkowe źródła informacji
1. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo
Wiernik, SQL Server 2005 Implementacja i obsługa, APN Promise, 2006
W książce obszernie przedstawiono zagadnienia związane z programowaniem baz
danych. Szczegółowo omówiono zagadnienia dotyczące procedur składowanych i
wyzwalaczy. Omówiono w niej implementacje procedur składowanych oraz w jaki
sposób przyznawać do nich uprawnienia. Pokazano implementacje wyzwalaczy
(DML i DDL). Książka szczególnie polecana ze względu na dużą zawartość ćwiczeń
laboratoryjnych.
2. Kalen Delaney, Podstawy baz danych krok po kroku, APN Promise, 2006
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 15/21
Bardzo dobra książka dla osób początkujących. Pokazano w niej praktyczne
zastosowanie wyzwalaczy i procedur składowanych. Książka oprócz teorii zawiera
wiele przykładów.
3. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008
Pozycja napisana w sposób prosty. Wprowadza w SQL Server 2008 w sposób szybki
i łatwy. Osoba początkująca w SQL Server 2008 znajdzie w niej podstawy z każdego
tematu dotyczącego serwera bazodanowego. W prosty sposób dowiesz się, jak
tworzyć proste procedury składowane bez parametrów i z parametrami oraz jak
posługiwać się wyzwalaczami. Pozycja polecana zarówno dla osób początkujących,
jak i zaawansowanych.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 16/21
Laboratorium podstawowe
Problem 1 (czas realizacji 10 min)
Jesteś programistą bazodanowym w firmie National Insurance. Właśnie dowiedziałeś się od
swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych,
którym zarządzała jak dotąd na Twoim wydziale. Pierwsze zadanie, jakie przed Tobą stoi to analiza
już istniejących procedur składowanych.
Zadanie Tok postępowania
1. Nawiąż
połączenie z SQL
Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Mirosoft Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Uzyskaj
informacje o bazie
danych
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab09\ProcedurySystemowe.sql i kliknij Open.
• Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje
procedurę systemową sp_helpdb:
-- (1) Ustawiamy sie na baze danych PraceDyplomowe
USE PraceDyplomowe
GO
-- (2) Wywolanie procedury systemowej sp_helpdb
EXEC sp_helpdb PraceDyplomowe
Procedura sp_helpdb zwraca informacje o wybranej bazie danych
(rozmiar, listę plików i informacje o nich).
3. Uzyskaj
informacje o
obiektach bazy
danych
• Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje
procedurę systemową sp_help:
-- (3) Wywolanie procedury systemowej sp_help
EXEC sp_help dbo.Prace
Procedura sp_help zwraca informacje o wybranym obiekcie bazy
danych.
Czy w tabeli dbo.Prace jest jakakolwiek kolumna typu bit?
4. Uzyskaj
informacje o
indeksach tabeli
• Zaznacz kod, który wywołuje procedurę systemową sp_helpindex.
-- (4) Wywolanie procedury systemowej sp_helpindex
EXEC sp_helpindex dbo.Prace
• Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
Procedura sp_helpindex zwraca listę i opis indeksów założonych na
kolumnach w wybranej tabeli.
Czy w tabeli dbo.Prace jest nałożony jakiś indeks?
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 17/21
Problem 2 (czas realizacji 10 min)
Po dokonaniu analizy procedur już istniejących, kolejne zadanie, jakie zostało przed Tobą
postawione, to zdefiniowanie procedur składowanych dla nowych użytkowników oraz modyfikacja
już istniejących procedur składowanych. W pierwszej kolejności powinieneś zająć się procedurami
składowanymi bez parametrów.
Zadanie Tok postępowania
1. Nawiąż
połączenie z SQL
Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual
PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz
procedurę
składowaną
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab10\Procedura_bez_parametrow.sql i kliknij
Open.
• Zaznacz kod, który wywołuje procedurę składowaną Promotorzy:
-- (1) Ustawiamy sie na baze danych PraceDyplomowe
USE PraceDyplomowe
GO
-- (2) Tworzymy procedure skladowana uzytkownika
CREATE PROCEDURE Promotorzy
AS
BEGIN
SELECT [Nazwisko], [Imie] FROM Promotor
END
Innym sposobem stworzenia procedury składowanej jest kliknięcie
prawym przyciskiem myszy gałęzi Stored Procedures i wybranie New
StoredProcedure – uzyskasz w ten sposób gotowy schemat procedury
składowanej.
• Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
Jeśli procedura składowana została utworzona poprawnie, powinieneś
otrzymać komunikat „Command(s) completed successfully” oraz
zauważyć, że została dodana nowa procedura składowana w Object
Explorer, w gałęzi Databases -> PraceDyplomowe -> Programmability
-> StoredProcedures. Jeśli procedury nie widać w w/w gałęzi, odśwież
ją. Jeśli procedura jest już utworzona, a mimo to zażądano kolejnego
jej utworzenia, to powinieneś otrzymać komunikat „There is already
object name ‘Promotorzy’ in the database”.
3. Uruchom
procedurę
składowaną
• Zaznacz kod, który wywołuje procedurę Promotorzy:
-- (3) Wywolujemy procedure skladowana Promotorzy
EXEC Promotorzy
GO
Innym sposobem na wywołanie procedury składowanej jest kliknięcie
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 18/21
prawym przyciskiem myszy na procedurze Promotorzy i wybranie
Execute Stored Procedure, co wywoła okienko, w którym można
przekazać do procedury jakieś parametry, jak na rys. 11.
Rys. 11 Wywołanie procedury składowanej z parametrem
• Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
Problem 3 (czas realizacji 15 min)
Kolejne zadanie, jakie powinieneś wykonać, to zdefiniowanie procedur składowanych dla nowych
użytkowników oraz modyfikacja już istniejących procedur składowanych z parametrami.
Zadanie Tok postępowania
1. Nawiąż
połączenie z SQL
Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual
PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz
procedurę
składowaną
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab10\Procedura_z_parametrami.sql i kliknij
Open.
• Zaznacz kod, który wywołuje procedurę składowaną Promotorzy:
-- (1) Ustawiamy sie na baze danych PraceDyplomowe
USE PraceDyplomowe GO
-- (2) Tworzymy procedure skladowana użytkownika z parametrem
CREATE PROCEDURE dbo.Promotorzy
@Nazwisko VARCHAR(30)
AS
BEGIN SELECT *
FROM [PraceDyplomowe].[dbo].[Osoba]
WHERE [PraceDyplomowe].[dbo].[Osoba].[nazwisko] = @Nazwisko
END
• Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
3. Uruchom
procedurę
składowaną
• Zaznacz kod, który wywołuje procedurę Promotorzy. Wciśnij F5, aby
uruchomić zaznaczony fragment kodu:
-- (3) Wywolujemy procedure skladowana z parametrem
EXEC Promotorzy @Nazwisko='Kowalski'
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 19/21
Problem 4 (czas realizacji 10 min)
Po zdefiniowaniu procedur składowanych wynikających ze zmian, jakie zachodzą w firmie, w której
pracujesz, kolejnym etapem jest stworzenie odpowiedniej liczby wyzwalaczy. Jeden z wyzwalaczy
powinien obsługiwać następujący warunek: każdy opiekun może mieć maksymalnie 10
dyplomantów.
Zadanie Tok postępowania
1. Nawiąż
połączenie z SQL
Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual
PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz
wyzwalacz
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab10\Wyzwalacz_1.sql i kliknij Open.
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (1) Ustawiamy się na baze danych PraceDyplomowe
USE PraceDyplomowe
GO
-- (2) przygotujmy tabele do testowania wyzwalaczy
SELECT ID_Osoby, Nazwisko, Imie, Nr_Indeksu, ID_Stopnian
INTO Osoba_kopi
FROM Osoba
GO
• Zaznacz i uruchom (F5) fragment kodu oznaczonego w komentarzu (3).
Wynik pokazano na rys. 12:
-- (3) zobaczmy, co znajduje sie w tabeli
SELECT * FROM Osoba_kopi
GO
Rys. 12 Sprawdzenie zawartości tabeli
• W obrębie okna Object Explorer wybierz Osoba -> Triggers.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 20/21
• Wciśnij prawy przycisk myszy i wybierz New Trigger.
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (4) Tworzymy nowy wyzwalacz
CREATE TRIGGER Ocenay
ON Osoba
AFTER INSERT, UPDATE
AS
DECLARE @username as varchar(30) SELECT @username = SYSTEM_USER
PRINT 'Uzytkownik '+ @username + ' zmienil zawartosc tabeli
Osoba!!!'
GO
• W celu zobaczenia działania wyzwalacza wywołaj następujące
zapytanie:
-- (5) Sprawdzamy dzialanie wyzwalacza
UPDATE Osoba
SET [Nazwisko] = 'Nowak'
WHERE ID_Osoby=1
GO
Jeśli wyzwalacz zadziałał poprawnie, w oknie Messages powinien
pojawić się komunikat o treści przedstawionej na rys. 13.
Rys. 13 Sprawdzanie efektu działania wyzwalacza
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski Moduł 9
ITA-101 Bazy danych Procedury składowane i wyzwalacze
Strona 21/21
Laboratorium rozszerzone
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że
firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizacje systemu. W
związku z modernizacją systemu bazodanowego oraz w celu spełnienia standardów
bezpieczeństwa, dostęp do obiektów bazy danych powinien odbywać się poprzez procedury
składowane. W wyniku tego część procedur składowanych powinna zostać zmodyfikowana i
przekompilowana, część powinna zostać napisana od początku, a część powinna zostać usunięta.
Kolejnym pojawiającym się problemem jest kwestia spójności zmodyfikowanej bazy danych. W tym
celu powinny zostać zmodyfikowane, utworzone lub usunięte wyzwalacze służące do
zaimplementowania pewnych warunków.
Zadanie, jakie przed Tobą stoi, to:
1. Podjęcie decyzji, jakie nowe procedury składowane i wyzwalacze powinny zostać utworzone
w celu poprawienia bezpieczeństwa bazy danych.
2. Podjęcie decyzji, które procedury składowane w bazie danych pozostaną bez zmian, a które
zostaną zmodyfikowane lub usunięte.
3. Podjęcie decyzji, które wyzwalacze w bazie danych pozostaną bez zmian, a które zostaną
zmodyfikowane lub usunięte.