18. Procedury składowalne i funkcje bazodanowe

18. Procedury składowalne i funkcje bazodanowe

 Grzegorz Chuchra
Grzegorz Chuchra
00:00
07.06.2006
63251 wyświetlenie

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:

  1. sprawdzenie poprawności wpisanego skryptu
  2. sprawdzenie statystyk
  3. 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.


Spodobał Ci się ten artykuł? Podziel się z innymi!

Źródło:

Polecamy również w kategorii Kurs SQL