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 <procedure_name, sysname=""> <@Param1, sysname, @p1> <datatype_for_param1,> = <default_value_for_param1,>, <@Param2, sysname, @p2> <datatype_for_param2,> = <default_value_for_param2,> AS BEGIN SET NOCOUNT ON; SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END </default_value_for_param2,></datatype_for_param2,></default_value_for_param1,></datatype_for_param1,></procedure_name,> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
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.
