plik

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  module  zostanie  zaprezentowany  sposób  działania  oraz  podstawy
tworzenia  procedur  składowanych.  Dowiesz  na  czym  polega  różnica
pomiędzy  zwykłym  zapytaniem  T-SQL  a  procedurą  składowaną  oraz  co  to
jest  kompilacja  i  rekompilacja  procedury.  Zostanie  wprowadzony  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 wykorzystad je w przykładowym projekcie.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:



wiedział, jak napisad własną procedurę składowaną



umiał rozbudowywad gotowe procedury składowane



umiał tworzyd proste wyzwalacze



umiał zarządzad wyzwalaczami



umiał wykorzystywad wyzwalacze do optymalizacji i automatyzacji
działania SQL Server 2008

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:



potrafid samodzielnie stworzyd bazę danych wraz z jej obiektami
(patrz moduł 4)



znad podstawową składnię języka T-SQL



umied napisad własne zapytania w języku T-SQL (patrz moduł 6 i 7)



znad zaawansowane mechanizmy języka T-SQL (patrz moduł 9)



wiedzied na czym polega bezpieczeostwo w bazach danych (patrz
moduł 11)

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 zapoznad się z materiałem zawartym
w module: 3, 5, 6, 8, 10.

Moduł 9

Dodatek

Moduł 1

Moduł 2

Moduł 3

Moduł 4

Moduł 5

Moduł 6

Moduł 7

Moduł 8

Moduł 10

Moduł 11

Moduł 12

Moduł 13

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.  To  od  niego  w  dużej  mierze  zależy  czy  wytworzone  przez
niego funkcje, procedury składowane, wyzwalacze i inne element programistyczne będą działały we
właściwy  sposób.  To  on  odpowiedzialny  jest  za  przygotowanie  odpowiednich  elementów
programowych,  które  następnie  będzie  wykorzystywał  projektant  aplikacji  i  programista  aplikacji
dostępowej.  A  zatem  również  od  niego  w  pewnym  stopniu  zależy  bezpieczeostwo  danych
gromadzonych  w  bazie.  W  SQL  Server  2008  dostępny  jest  szereg  systemowych  procedur
składowanych.  Oczywiście  programista  bazy  danych  może  korzystad  z  ich  również  pisząc  swoje
własne tzw. procedury składowane użytkownika. Kolejnym zadaniem jakie stoi przed programista i
administratorem  bazy  danych  jest  stworzenie  odpowiednich  liczby  wyzwalaczy,  które  są  pewnym
rodzajem procedur składowanych, które są wywoływane niejawnie. Należy pamiętad iż, procedury
składowane, które mogą byd wykorzystane przez programistę aplikacji dostępowej do bazy danych
powodują  kolejną  warstwę  powodującą  separację  użytkownika  koocowego  od  fizycznych  danych
oraz  powodują  maskowanie  fizycznej  struktury  bazy.  Zatem  bardzo  ważne  jest  przemyślane  i
odpowiednie stworzenie zbioru procedur składowanych.

Podstawy teoretyczne

Procedury składowane
Procedura składowana (ang. stored procedure) jest nazwanym zbiorem poleceo w języku SQL, który
jest przechowywany na serwerze w SZBD (System Zarządzania Bazą Danych) i jest kompilowany
przy pierwszym wykonaniu. Procedury wnoszą do środowiska serwera baz danych przetwarzanie
warunkowe i możliwości programistyczne.

W SZBD wykonanie dowolnego fragmentu kodu języka SQL wiąże się z pewnym ciągiem procesów –
począwszy od sprawdzenia składni aż po kompilację i wykonanie.

Proces wykonywania zapytania przez SQL Server

Poniżej zostało przedstawione, w jaki sposób odbywa się proces wykonania pojedynczego
zapytania w języku T-SQL w Microsoft SQL Server 2008:

1. Sprawdzenie i rozdzielenie kodu na fragmenty (często nazywane symbolami interpretowane

przez SZBD. Proces ten nazywamy analizą leksykalną.

2. Sprawdzenie kodu pod względem poprawności składni (kontrola poprawności semantycznej

– czyli czy kod nie odwołuje się do nieistniejących obiektów lub używa nieistniejących
poleceo oraz kontrola poprawności syntaktycznej – czy użyta składnia jest poprawna).

3. Standaryzacja wyodrębnionej części kodu, tzn. SZBD zapisuje go w jednoznacznej postaci

(usuwając niepotrzebne znaczniki).

4. Optymalizacja – każde zapytanie może posiadad wiele przygotowanych tzw. planów

wykonania  (ang.  execution  plan).  MS  SQL  Server  posiada  wewnętrzny  proces  zwany
Optymalizatorem  Zapytao,  który  wybiera  optymalny  sposób  dostępu  do  danych,  tzn.  taki
plan  wykonania  zapytania,  w  którym  serwer  będzie  skanował  (przeszukiwał)  najmniejszą
ilośd  stron  danych  (o  fizycznej  strukturze  danych  w  MS  SQL  Server  możesz  przeczytad  w
module 6). Na optymalizację szczególny wpływ mają struktura indeksów oraz sposób łączenia
tabel.

5. Kompilacja zapytania wg optymalnego planu wykonania i wykonanie skompilowanego

zapytania.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 4/21

6. Zwrócenie wyniku działania zapytania do klienta.

Proces wykonywania procedury składowanej

Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie pojedynczych zapytao
SQL. Poniżej prezentujemy schemat utworzenia i pierwszego wykonania procedury na przykładzie
MS SQL Server:

1. Tworzenie definicji procedury składowanej – tzn. wykonanie polecenie

CREATE PROCEDURE

2. Sprawdzanie kodu procedury pod względem syntaktyki.
3. Zapisanie nazwy procedury i jej kodu (tzw. ciało) do odpowiednich widoków systemowych

bazy danych (

sysobjects

oraz

syscomments

4. Wywołanie procedury składowanej przez użytkownika z odpowiednimi parametrami

używając polecenia EXEC.

5. Właściwe wykonanie procedury – optymalizacja planu wykonania i kompilacja.
6. Skompilowany optymalny plan wykonania jest zapisywany w tzw. buforze (z ang. cache'u

procedur.

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śd  skompilowanej  procedury  gwałtownie  spada  (może  tak  byd  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ą byd 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 dokonad na dwa sposoby:



W definicji procedury dodając klauzulę

WITH RECOMPILE



Używając specjalnej systemowej procedury rekompilującej (w MS SQL Server jest to
procedura

sp_recompile

Tworzenie procedur składowanych

Do tworzenia procedur składowanych używamy polecenia języka SQL CREATE PROCEDURE (lub
CREATE PROC):

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 5/21

CREATE { PROC | PROCEDURE } [nazwa_schematu.] nazwa_procedury
    [ { @parametr typ_danych }
[ WITH <opcje_procedury> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <wyrażenie_sql> [;][ ...n ] }
[;]

<opcja_procedury> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<wyrażenie_sql > ::=
{ [ BEGIN ] wyrażenie [ END ] }

W definicji procedury składowanej określamy:



nazwę procedury;



nazwy,



typy danych,



kierunek działania parametrów procedury;



ciało procedury – czyli kod wykonywany przez procedurę;



opcjonalnie deklarujemy, czy procedura ma byd 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

EXEC

[ { EXEC | EXECUTE } ]
  {
    [ @return_status = ]
    { module_name [ ;number ] | @module_name_var }
      [ [ @parameter = ] { value
        | @variable [ OUTPUT ]
        | [ DEFAULT ]
        }
      ]
    [ ,...n ]
    [ WITH RECOMPILE ]
  }
[;]

Przykładowo dla utworzonej wcześniej procedury składowanej

p_pracownicy

wywołanie

procedury będzie wyglądało w następujący sposób.

EXEC p_pracownicy
GO

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 6/21

Parametry procedur składowanych

Procedury składowane mogą przyjmowad parametry wywołania. Ilośd i typ danych, które należy
podad 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 wykonania

procedury, czy też mają byd 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śd podajemy przy wywołaniu procedury,
a procedura podczas działania może zmienid wartośd parametru i zwrócid nową wartośd.

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śd 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śd struktury dla zwykłego
użytkownika aplikacji).



Umożliwiają modyfikację danych bez bezpośredniego dostępu do tabel bazy danych.



Dostarczają mechanizmów bezpieczeostwa (można nadawad 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ływad; procedurę może pisad osoba wyspecjalizowana w bazach
danych – programista aplikacji jedynie ją wywoła).



Zapewniają szybsze wykonanie (jeśli wymagane jest wykonanie dużej liczby zapytao
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 zapytao w T-SQL na raz,
można je zastąpid wywołaniem jednej procedury składowanej).

Jeśli w ogóle można mówid o wadach procedur składowanych, to w zasadzie można wspomnied o
kilku aspektach:



Następstwem rekompilacji czasem jest zmniejszenie wydajności procedury (czyli
administrator baz danych musi wiedzied, kiedy przeprowadzid rekompilację).



W przypadku zagnieżdżania procedur składowanych należy pamiętad o tym, że zmienia się
kontekst wykonania (procedura zagnieżdżana wykonuje się z uprawnieniami innej
procedury).



Wreszcie, aby tworzyd dobre (tzn. poprawnie działające) procedury składowane niezbędne
jest poznanie zaawansowanych mechanizmów języka SZBD (np. T-SQL) takich jak:
operowanie zmiennymi, funkcje i procedury systemowe, obsługa błędów.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 7/21

Wyzwalacze
Wyzwalacz (ang. trigger) jest specjalnym rodzajem procedury składowanej. W przeciwieostwie do
zwykłej procedury składowanej wyzwalacz nie może zostad jawnie wywołany.

Wyzwalacz jest wywoływany w reakcji na określone akcje. Akcje te to wykonanie przez użytkownika
określonego polecenia SQL (I

NSERT

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. Triggery 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 } { event_type | event_group } [ ,...n ]
AS { wyrażenie_sql  [ ; ] [ ,...n ] }

<ddl_opcje_wyzwalacza> ::=
  [ ENCRYPTION ]
  [ EXECUTE AS Clause ]

3. Wyzwalacze obsługujące zdarzenie logowania (

LOGON

), który jest wywoływany, kiedy

ustalana jest sesja logującego się użytkownika.

CREATE TRIGGER nazwa_wyzwalacza
ON ALL SERVER
[ WITH <opcje_wyzwalacza_logon > [ ,...n ] ]

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 8/21

{ FOR | AFTER } LOGON
AS { wyrażenie_sql [ ; ] [ ,...n ] }

< opcje_wyzwalacza_logon > ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

Zastosowanie wyzwalaczy

Wyzwalacze znajdują wiele zastosowao w bazach danych. Przede wszystkim stosujemy wyzwalacze
wszędzie tam, gdzie w inny sposób nie da się weryfikowad 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śd już
w danym polu wystąpiła, a jeżeli użytkownik wstawia wartośd

NULL

, to wyzwalacz mu na to pozwoli

(tego efektu nie można osiągnąd innymi metodami, np. używając indeksów, ustawiając właściwośd
unikalności kolumny lub używając kryteriów sprawdzających dane wstawiane w kolumnę).

Drugie zastosowanie to wszelkiego typu automatyzacja zadao administracyjnych w bazie danych
(wszelkiego rodzaju "przypominacze", obsługa nietypowych działao 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 wybrad

New Query

. i

wpisad kod procedury. Przykładowo:

CREATE PROCEDURE dbo.getAllCustomers
AS
BEGIN

SELECT [CustomerID], [CompanyName]
FROM [Northwind].[dbo].[Customers]

END

Następnie  należy  wykonad  skrypt  klikając  F5  lub  wybierając  z  paska  narzędziowego  Execute.  Jeśli
procedura  składowana  została  utworzona  poprawnie  powinieneś  otrzymad  komunikat:
"Command(s)  completed  successfully"  oraz  zauważyd,  że  została  dodana  nowa  procedura
składowana

Object

Explorer,

gałęzi

Databases->Northwind->Programmability-

>StoredProcedures co pokazano na Rysunku 3.

Jeśli procedury nie widad w wyżej wymienionej gałęzi proszę ją odświeżyd. 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 uruchomid procedurę składowaną wystarczy użyd polecenia EXECUTE (lub krócej EXEC). Można
także kliknąd prawym klawiszem myszki na procedurze i wybrad "Execute Stored Procedure..." co
wywoła okienko, w którym można przekazad do procedury jakieś parametry. Na Rysunku 4 widad,
ż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 pokazno
na Rysunku 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 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 stworzyd 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 Explore

rozwijamy zakładkę

Menagement

a następnie

Policy Menagement

. W

pierwszym kroku należy dodad warunki jakie będą dołączone do polisy.

Jako pierwszy stwórzmy warunek, który będzie pilnował żeby nazwa procedury składowanej była
inna niż

‘sp_’

. Konfigurację tego warunku pokazano na Rysunku 6.

Następnie należy stworzyd warunek, dotyczący bazy danych na którym polisa, którą za chwilę
założymy będzie działała. Konfigurację tego warunku pokazano na Rysunku 7.

Jak już mamy stworzone warunki to w kolejnym kroku należy wystawid polisę i dodad do niej
wcześniej stworzone warunki. Konfiguracje wystawiania polisy pokazano na Rysunku 8.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 12/21

Teraz kiedy mamy wystawiona polisę chcąc ją przetestowad należy klikną prawym przyciskiem na
bazie danych

Biblioteka

i wybrad

New Query

. Następnie wpisujemy kod pustej systemowej

procedury składowanej

CREATE PROCEDURE sp_testowa
AS
GO

Zauwazmy, ze wystapił błąd. Procedura nie została utworzona gdyż zadziałała restrykcja DMF co
pokazano na Rysunku 9.

Rys. 9 Błąd procedury i zadziałanie polisy

Tworzenie wyzwalacza
Stwórzmy wyzwalacz, którego zadaniem będzie nie dopuścid do zalogowania wskazanego przez nas
użytkownika. W tym celu na początku należy dodad użytkownika

zbd_user

i kojarzymy go z bazą

danych

AdventureWorks

. Następnie tworzymy odpowiedni wyzwalacz, który nie pozwoli

użytkownikowi zalogowad się do Microsoft SQL Server Management Studio. W tym celu, klikamy
prawym przyciskiem myszy na 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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 13/21

Aby przetestowad działanie utworzonego wyzwalacza uruchamiamy drugą instancję MS SQL Server
Management Studio. W oknie

Connect to Server Authentication

wybieramy

SQL Server

Authentication

, Login:

zbd_user

, Password:

user

. Zatwierdzamy klikając

Connect

. Wybierając

Connect pokazuje się błąd logowania co pokazano na Rysunku 10.

Rys. 10 Bład logowania

Porady praktyczne

Procedury składowane



W MS 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 zaczynad 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 wynik zapamiętany w cachu procedury. Dopiero kiedy jej tam nie
znajdzie  stwierdzi  że  jest  to  procedura  użytkownika  i  zacznie  ją  kompilowad  według
poznanego schematu. Spowoduje to wydłużenie czasu wykonania procedury składowanej.



Stworzenie polisy, która nie pozwoli programiście utworzyd procedury składowanej
zaczynającej się od prefiksu

‘sp_’

spowoduje, że pierwsza próba stworzenia procedury o

takiej nazwie pociągnie za sobą koniecznośd uruchomienia sprawdzenia polisy a zatem czas
nieznacznie się wydłuży. Kolejne wykonanie próby stworzenia procedury niezgodnej z polisa
spowoduje natychmiastowe wyświetlenie komunikatu o naruszeniu restrykcji.

Wyzwalacze



Wyzwalacze  mogą  byd  tworzone  bezpośrednio  w  MS  SQL  Server  2008  Database  Engine  za
pomocą  zwykłych  zapytao  napisanych  w  języku  T-SQL  lub  poprzez  specjalne  metody  w
Microsoft  .NET  Framework  Common  Language  Runtime  (CLR),  a  następnie  po  utworzeniu
importowane  do  instancji  serwera  bazodanowego.  MS  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

RECONFGURE

. Dodatkowo powyższe instrukcje nie mogą byd użyte

wewnątrz ciała wyzwalacza DML.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 14/21

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 dodawad 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 Implemantacja i obsuga, APN Promise, Warsyawa 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  przyznawad  do  nich  uprawnienia.  Pokazano  implementacje  wyzwalaczy
(DML  i  DDL).  Książka  szczególnie  polecana  ze  względu  na dużą  zawartośd dwiczeo
laboratoryjnych.

2. Kalen Delaney, Podstawy baz danych krok po kroku, APN Promise, 2006

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
obdarzona dużą liczbą 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
należy tworzyd proste procedury składowane bez parametrów i z parametrami oraz
poznasz  jak  należy  stworzyd  wyzwalacz  i  w  jakiej  sytuacji  go  zastosowad.  Pozycja
szczególnie polecana 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 15/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  National  Insurance  zarządzająca  systemem  prac  dyplomowych  na  Twoim
wydziale  planuje  rozszerzenie  systemu  na  skalę  uczelnianą.  Pierwsze  zadanie,  jakie  przed  Tobą
analiza procedur składowanych już istniejących.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia 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 MS 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. Informacja 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. 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. 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 uruchomid zaznaczony fragment kodu.

Procedura sp_helpindex zwraca listę i opis indeksów założonych na
kolumnach w wybranej tabeli.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 16/21

Czy w tabeli dbo.Prace jest nałożony jakiś indeks?

Problem 2 (czas realizacji 10 min)

Po dokonaniu analizy procedur już istniejących, kolejne zadanie jakie przed Toba zostało
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ąd się procedurami
składowanymi bez parametrów.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia 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 MS 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. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij Open -> File.



Odszukaj plik C:\Labs\Lab10\Procedura_bez_parametrow.sql i kliknij
Open.



Zaznacz kod, który wywołuje procedurę składowaną Promotorzy (patrz
kod poniżej).

-- (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 na gałęzi "Stored Procedures" i wybranie
"New StoredProcedure..." – uzyskasz w ten sposób gotowy schemat
procedury składowanej.



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Jeśli procedura składowana została utworzona poprawnie, powinieneś
otrzymad komunikat "Command(s) completed successfully" oraz
zauważyd, że została dodana nowa procedura składowana w Object
Explorer, w gałęzi Databases -> PraceDyplomowe -> Programmability
-> StoredProcedures. Jeśli procedury nie widad w w/w gałęzi, odśwież
ją. Jeśli procedura jest już utworzona, a mimo to zażądano kolejnego

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 17/21

jej utworzenia, to otrzymamy następujący komunikat: „There is
already object name ‘Promotorzy’ in the database”.

3. Uruchomie-
nie procedury
składowanej



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
prawym  przyciskiem  myszy  na  procedurze  Promotorzy  i  wybierz
Execute  Stored  Procedure..."  co  wywoła  okienko,  w  którym  można
przekazad do procedury jakieś parametry.



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Problem 3 (czas realizacji 15 min)

Kolejne zadanie jakie powinieneś wykonad 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ązywanie
połączenia 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 MS 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. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij Open -> File.



Odszukaj plik C:\Labs\Lab10\Procedura_z_parametrami.sql i kliknij
Open.



Zaznacz kod, który wywołuje procedurę składowaną Promotorzy (patrz
kod poniżej).

-- (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 uruchomid zaznaczony fragment kodu.

Jeśli procedura składowana została utworzona poprawnie powinieneś
otrzymad komunikat "Command(s) completed successfully" oraz

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 18/21

zauważyd, że została dodana nowa procedura składowana w Object
Explorer, w gałęzi Databases->PraceDyplomowe->Programmability-
>StoredProcedures

3. Uruchomie-
nie procedury
składowanej



Zaznacz kod, który wywołuje procedurę Promotorzy. Wciśnij F5, aby
uruchomid zaznaczony fragment kodu

-- (3) Wywolujemy procedure skladowana z parametrem

EXEC Promotorzy @Nazwisko='Kowalski'

Lub  kilknij  prawym  przyciskiem  myszy  na  procedurze  Promotorzy  i
wybierz  Execute  Stored  Procedure..."  co  wywoła  okienko  w  którym
można  przekazad  do  procedury  jakieś  parametry.  Co  pokazano  na
Rysunku 11.

Rys. 11 Wywołanie procedury składowanej z parametrem

Problem 4 (czas realizacji 10 min)

Po zdefiniowaniu procedur składowanych wynikających z zmian jakie zachodzą w firmie w której
pracujesz kolejnym etapem jest stworzenie odpowiedniej liczby wyzwalaczy. Jednym z wyzwalaczy
jest zaimplementowania warunku, że każdy opiekun może mied maksymalnie 10 dyplomantów.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia 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 MS 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. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij 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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 19/21

INTO Osoba_kopi
FROM Osoba
GO



Zaznacz i uruchom (F5) fragment kodu oznaczonego w komentarzu (3).
Wynik pokazano na Rys. :

-- (3) zobaczmy, co znajduje sie w tabeli
SELECT * FROM Osoba_kopi
GO

Rys. 12



Następnie stwórz

Wyzwalacz

, którego zadaniem będzie przechwycenie

wykonywanego zapytania i wyświetlenie zdefiniowanej przez nas
informacji.



W obrębie okna Object Explorer wybieramy Osoba -> Triggers.



Wciskamy prawy przycisk myszy i wybieramy New Trigger.



Wpisujemy poniższy kod:

-- (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łujemy 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
pojawid się komunikat o tresci przedstawionej na Rysunku 13.

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  celu
spełnienia  pewnych  standardów.  W  związku  z  modernizacją  systemu  bazodanowego  oraz
spełnienia  standardów  bezpieczeostwa  dostęp  do  obiektów  bazy  danych  powinien  odbywad  się
poprzez  procedury  składowane.  W  wyniku  tego  częśd  procedur  składowanych  powinna  zostad
zmodyfikowana i przekompilowana, częśd powinna zostad napisana od początku, a częśd powinna
zostad  usunięta.  Kolejnym  pojawiającym  się  problemem  jest  kwestia  spójności  zmodyfikowanej
bazy  danych.  W  tym  celu  powinny  zostad  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 zostad utworzone

w celu poprawienia bezpieczeostwa 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.