Procedury składowane
Wewnątrz procedury możemy robić
wszystko to co w ramach każdego skryptu SQL. Możemy więc używać poleceń
zarówno z języka DML jak i DCL. Dzięki temu możemy
sobie utworzyć standardową procedurą nadającą uprawnienia użytkownikowi do
odpowiednich obiektów bazodanowych, jak i skrypt usuwający użytkownika, a co za
tym idzie wszystkie powiązane z nim dane z innych tabel.
Takie grupowanie operacji sprawi, iż nie będziesz musiał za każdym razem głowić
się gdzie znajdują się dane związane z użytkownikiem. Nie będzie też
konieczności trzymania wszystkich raz stworzonych skryptów w jakimś odpowiednim
katalogu. Dzięki procedurą składowanym wystarczy utworzyć procedurę sp_DeleteUser
która będzie pobierała jak parametr klucz główny użytkownika i sam zadba o to
aby usunąć dane ze wszystkich tabel.
Aby utworzyć procedurę składowaną wystarczy wstawić nasze skrypt w szablon tworzący procedurę składowaną
CREATE PROCEDURE
Oprócz nazwy procedury i parametrów procedura nie posiada żadnych dodatkowych opcji o których należy pamiętać podczas jej budowania.
Aby zademonstrować jak łatwo można utworzyć procedurę napiszemy sobie skrypt który wyświetla informacje o użytkownikach wybranych po nazwiskach.
SELECT [ContactID]
,[FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
WHERE [LastName] LIKE 'Adams'
ORDER BY
FirstName
Teraz wystarczy tylko wpisać nasz skrypt w szablon tworzący procedurę. Sam obiekt nazwiemy uspGetContactsByLastName. Prefix usp mówi nam o tym, że procedura jest stworzona przez użytkownika bazy danych UserStorageProcedure. Teraz należy jeszcze zdefiniować parametr po jakim procedura ma wyszukiwać użytkowników. Ponieważ my chcemy wyszukiwać kontakty po nazwisku to należy jeszcze podać tą informacje przy tworzenie procedury.
CREATE PROCEDURE uspGetContactsByLastName
@LastName NVARCHAR(32)
AS
SELECT [ContactID]
,[FirstName]
,[LastName]
,[EmailAddress]
,[Phone]
FROM [AdventureWorks].[Person].[Contact]
WHERE [LastName] LIKE @LastName + '%'
ORDER BY
FirstName
Teraz już tylko wystarczy uruchomić skrypt i procedura już jest dostępna na naszym serwerze.
Aby sprawdzić jak działa wystarczy odświeżyć widok obiektów bazodanowych w okienku ObjectExplorer. Rozwinąć folder Programmability ->Storaged Procedures nacisnąć prawym guzikiem myszy na naszą procedurę i wybrać z menu podręcznego Execute Stored Procedure…
Efektem działania powinno być okno dialogowe w którym możemy wpisać nazwisko osób które chcemy odnaleźć. Następnie naciskamy OK. co w rezultacie prowadzi do wygenerowania nam skryptu który służy do uruchomienia procedury.
Tak wygląda utworzony przez SQL Server 2005 skrypt:
USE [AdventureWorks]
GO
DECLARE @return_value int
EXEC @return_value =
[dbo].[uspGetContactsByLastName]
@LastName =
N'Ada'
SELECT 'Return Value' =
@return_value
GO
W rzeczywistości wystarczyło by nam zaledwie tyle kodu aby uzyskać taki sam efekt.
EXEC [dbo].[uspGetContactsByLastName]
'Ada'
lub
EXEC [dbo].[uspGetContactsByLastName] @LastName = 'Ada'
Jak widać możemy przekazywać parametry do procedury anonimowo nie przypisując jawnie któremu z parametrów chcemy przypisać wartość. To rozwiązanie jest dobre jeśli nasza procedura ma tylko jeden parametr wejściowy. Jeśli jednak nasza procedura oprócz wyszukiwania po nazwisku wyszukiwała by dodatkowo po imieniu. To lepszym rozwiązaniem było by jawne podanie parametrów wyglądało by to wtedy mniej więcej tak:
EXEC [dbo].[uspGetContacts] @LastName = 'Ada’, @FirstName = ‘John’
Jeśli w naszym skrypcie procedura jest wywoływana jako pierwszy element słowo EXEC jest opcjonalne i procedura również się wykona.
@STRONA@Własności procedur
Procedury składowane działają szybciej niż taki sam skrypt. Dzieje się to z kilku prostych przyczyn. Ponieważ SQL Server przy każdym uruchamianiu skryptu musi poczynić kilka ważnych kroków takich jak:
- sprawdzenie poprawności wpisanego skryptu
- sprawdzenie statystyk
- wykonanie planu zapytania
Jeśli wykonywana jest skrypt SQL wszystkie te operacje robione są podczas jego uruchomienia. Procedura składowana jest z definicji sprawdzana przez kompilator podczas jej tworzenia, a plan zapytania jest tworzony automatycznie przy jej pierwszym uruchomieniu. Tak więc przy dodatkowym atutem procedur jest szybkość ich działania.
Funkcje składowane
Funkcje składowane mogą
realizować identyczną funkcjonalność jak procedury składowane mają jednak
jeszcze parę dodatkowych atutów.
Przypuśćmy, że chcemy sobie zrobić funkcje która przerabia datę z
nieprzyjemnego formatu 2005-09-11 23:44:44.327 do formy troszeczkę bardziej
czytelnej 11-9-2005. Do takiego celu świetnie nada się funkcja bazodanowa.
CREATE FUNCTION funDateFormater
(
@myDate datetime
, @separator varchar(8)
)
RETURNS nvarchar(32)
AS
BEGIN
RETURN
CONVERT(nvarchar(32),
DATEPART(dd, @myDate))
+ @separator
+ CONVERT(nvarchar(32),
DATEPART(mm, @myDate))
+ @separator
+ CONVERT(nvarchar(32),
DATEPART(yy, @myDate))
END
A teraz zobaczmy jak wywołać funkcje i prezentuje się sam wynik.
SELECT
dbo.funDateFormater(GETDATE(), '-') 11-9-2005
Jako parametr pobiera datę i rodzaj seperatora pomiędzy dniem, miesiącem i rokiem. Jak widać sam szablon funkcji nie różni się niczym oprócz słowa kluczowego FUNCTION i wartości zwracanej RETURNS niczym od deklaracji procedury. Jednak powyższa funkcja ma jedną cechę której nie posiada procedura. Mianowicie można wywołać ją bezpośrednio na operacji SELECT.
SELECT Person.Contact.FirstName,
Person.Contact.LastName, dbo.funDateFormater(HumanResources.Employee.BirthDate,
'-') AS BirthDate
FROM Person.Contact INNER
JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
Dzięki funkcją możemy stworzyć
własną bibliotekę z niezbędnymi skryptami których zapewnie będziemy używać w
wielu projektach. Daje to nam możliwość wielokrotnego wykorzystywania bloków
raz napisanego kodu. Przykład funkcji formatującej datę jest przykładem jak
jedna prosta funkcja może zostać wykorzystana w wielu projektach wszędzie tam
gdzie korzystamy z daty.
W rozdziale poświęconym itegracji SQL Server 2005
z Frameworke
rozwiniemy temat i napiszemy troszeczkę zgrabniejszą funkcję formatującą datę.
Podsumowanie
Procedury składowane są podstawą przy tworzeniu warstwy bazodanowej skomplikowanych systemów informatycznych. Oprócz wszystkich pozytywnych aspektów które zostały poruszone w artykule istnieje jeszcze problem bezpieczeństwa. W portalach internetowych gdzie dostęp do Internetu jest kluczową sprawą ważne jest aby użytkownicy mieli jak najmniejszy dostęp do obiektów bazodanowych. Tu świetnie sprawdzają się procedury. Stanowią zamkniętą w paczkę funkcjonalności którą można udostępnić bez potrzeby nadawania użytkownikom dostępu do tabeli.
