Dzisiaj zajmiemy się tematem procedury składowane, w literaturze niekiedy nazywane osadzonymi. Najprostsza definicja jaka przychodzi mi do głowy to taka, że procedury składowane to zbiór instrukcji SQL, które zapisujemy pod jedną nazwą. Następnie możemy odwołać się do danej procedury (wywołać ją) za pomocą tej nazwy wraz z parametrami wejściowymi które wcześniej definiujemy w procedurze. Struktura procedury składowanej (najprostsza jaką znalazłem) poniżej.
Definicja procedury składowanej (najprostszej jaką znalazłem).
CREATE PROCEDURE nazwa_procedury
(
@parametr_1 typ_naszego_parametru_1,
…
@parametr_n typ_naszego_parametru_n,
)
AS
treść_zapytania
ale np. przy próbie utworzenia nowej procedury składowanej w SQL Server otrzymamy automatycznie kod (bez kometarzy).
CREATE PROCEDURE
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> =
AS
BEGIN
SET NOCOUNT ON;
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Przykład
Utwórz procedurę która będzie pobierała dwa parametry Imię , Nazwisko i w zależności od tych parametrów będzie podawała dane teleadresowa danej osoby: Adres, E-mail, Telefon, Typ Telefonu.
CREATE PROCEDURE ProcOsobyInformacje
(
@imie varchar(10)
,@nazwisko varchar(20)
)
AS
BEGIN
SELECT
osoba.BusinessEntityID AS [ID osoby]
,osoba.FirstName AS [Imie]
,CASE
WHEN osoba.MiddleName IS NULL THEN 'b.d.'
ELSE osoba.MiddleName
END AS [Drugie Imie]
,osoba.LastName AS Nazwisko
,CASE
WHEN adres.City IS NULL THEN 'b.d.'
ELSE adres.City
END AS Miasto
,CASE
WHEN adres.AddressLine1 IS NULL THEN 'b.d.'
ELSE adres.AddressLine1
END AS Adres
,CASE
WHEN email.EmailAddress IS NULL THEN 'b.d.'
ELSE email.EmailAddress
END AS [E-mail]
,CASE
WHEN telefon.PhoneNumber IS NULL THEN 'b.d.'
ELSE telefon.PhoneNumber
END AS Telefon
,CASE
WHEN telefontyp.Name IS NULL THEN 'b.d.'
ELSE telefontyp.Name
END AS [Typ Telefonu]
FROM
Person.Person osoba
LEFT JOIN Person.BusinessEntityAddress pwoa ON osoba.BusinessEntityID = pwoa.BusinessEntityID
LEFT JOIN Person.Address adres ON pwoa.AddressID = adres.AddressID
LEFT JOIN Person.EmailAddress email ON osoba.BusinessEntityID = email.BusinessEntityID
LEFT JOIN Person.PersonPhone telefon ON osoba.BusinessEntityID = telefon.BusinessEntityID
LEFT JOIN Person.PhoneNumberType telefontyp ON telefon.PhoneNumberTypeID = telefontyp.PhoneNumberTypeID
WHERE
osoba.FirstName = @imie
AND osoba.LastName = @nazwisko
END
Uruchamiamy nasz kod F5. I jeżeli wszystko jest w porządku to otrzymamy komunikat „Command(s) completed successfully.”. W tym momencie w gałęzi Databases -> AdventureWorks2008R2 -> Programmability -> Stored Procedures powinna pojawić się procedura o nazwie ProcOsobyInformacje.
Teraz możemy przetestować naszą procedurę, czy rzeczywiście zwraca poprawne wyniki. Wyszukamy informacje dotyczące osoby(lub osób) Terri Duffy. W tym celu klikamy prawym przyciskiem myszy (dalej PPM) i wybieramy opcję Execute Stored Procedure….
W oknie Execute Procedure w tabelce ze zmiennymi (parametrami) w kolumnie Value wpisujemy odpowiednie wartości, dla imienia Terri, a dla nazwiska Duffy i nasz wybór potwierdzamy przyciskiem OK.
Poniżej efekt wykonania naszej procedury.
Widzimy, że SQL Server znalazł dwie osoby o takim samym imieniu i nazwisku, różniące się drugim imieniem. Teraz możemy sprawdzić czy jest to prawda w tabeli Person.Person zapytaniem wyszukującym osoby o tym imieniu i nazwisku.
SELECT
PersonType
,FirstName
,MiddleName
,LastName
FROM Person.Person
where LastName = 'Duffy'
Wynik uruchomienia naszego zapytania.
Widzimy, że wszystko się zgadza.
Wywołać procedurę składowaną możemy także „ręcznie” wpisując w okno zapytania następujący kod.
EXEC ProcOsobyInformacje 'Terri','Duffy'
Efekt będzie identyczny
SQL Server posiada także procedury już wbudowane oto najbardziej użyteczne z nich: sys.sp_addrolemember, sys.sp_adduser, sys.sp_catalogs, sys.sp_columns, sys.sp_databases itd. ale o tym w kolejnych wpisach.
Zapraszam do lektury kolejnych wpisów na Anonco.Pl
Pamiętaj:
-
Nie możesz utworzyć dwóch obiektów w bazie o takiej samej nazwie np. widok i procedura składowa. Jeżeli zdarzy się taka sytuacja w momencie próby utworzenia drugiego obiektu o tej samej nazwie co pierwszy otrzymasz komunikat „There is already an object named ‘tutaj_nazwa_obiektu’ in the database”. Dobrą praktyką jest nazywanie obiektów np. z przedrostkami np. Proc_ dla procedur lub View_ dla widoków itd.
