Artykuł

microsoft.com microsoft.com
lut 10 2011
0

Tworzenie procedur składowanych w MS SQL Server

Zapytania w SQLu, można tworzyć stosunkowo prosto (oczywiście zależy to od skali złożenia), ale pisanie ładnego, funkcjonalnego i powtarzalnego kodu, to już zupełnie inna sztuka. Wyobraźmy sobie sytuację, w której chcemy stworzyć zapytanie SQL dodające użytkownika do bazy danych. Takie zapytanie, powinno utworzyć nowy wpis na bazie imienia i nazwiska, a także rozpoznać datę urodzenia oraz płeć na bazie peselu. Normalnie w programowaniu, zrobiliśmy do tego metodę. Dlaczego nie zrobić by czegoś podobnego w samym SQLu i wykorzystać twór jakim jest procedura składowana? Co nam to da? Na te i inne pytania postaram się odpowiedzieć w dzisiejszym wpisie.

Procedura składowana - co to?

Procedura składowana, to nic innego jak specjalna struktura w bazie danych, która w pewnym stopniu przybliża nasze zapytania w kierunku klasycznego programowania. Każda z tworzonych przez nas procedur, może zawierać parametry i zwracać określone wartości, może również wykonywać pewne operacje. Czyż nie brzmi to jak definicja metody, czy funkcji w programowaniu?

Nasza procedura, zawierać będzie wszystkie instrukcje przeznaczone do operacji dodania użytkownika w jednym miejscu i wykorzystywać będzie trzy parametry. Czas więc, przystąpić do dzieła:)

Testowa tabela

Przed rozpoczęciem procedury, potrzebujemy tabeli testowej. Identyfikatorem naszej tabeli, może być właśnie Pesel, skoro jest to unikalny identyfikator. Poza tym, tabela powinna zawierać:

  • Imię
  • Nazwisko
  • Płeć
  • Datę urodzenia

Jak widać, tabela nie ma przesadnie skomplikowanej struktury, a całość możemy załatwić następującym poleceniem:

CREATE DATABASE Tests
GO

USE Tests
CREATE TABLE Person
(
	PersonId char(11) NOT NULL PRIMARY KEY,
	PersonFirstName varchar(50) NOT NULL,
	PersonLastName varchar(50) NOT NULL,
	PersonSex char(1) NOT NULL,
	PersonBirthDate smalldatetime NOT NULL
)

Choć pesel jest wyrażony liczbą, to do jego zapisu wykorzystaliśmy typ char o stałej długości. W przypadku płci, wystarczy nam jedna litera (K lub M), a do daty urodzenia, wystarczy typ smalldatetime.

Zarządzanie procedurami z poziomu SQL Server Management Studio

Jeśli miałeś jakąś styczność z procedurami składowanymi, spokojnie możesz ominąć ten akapit i przejść do naszego konkretnego przypadku w następnym akapicie:)

Zarządzanie procedurami składowanymi jest bardzo proste, bo możemy to robić przy pomocy Microsoft SQL Server Management Studio. Tam w Object Explorerze znajdują się wszystkie nasze bazy danych, a jeśli rozwiniemy odpowiednio drzewo, to znajdziemy również gałąź o nazwie Programmability, a w niej Stored Procedures. W tym właśnie miejscu, trzymane są wszystkie procedury przeznaczone dla konkretnej bazy danych. Idąc głębiej, możemy uzyskać dostęp do sporej kolekcji procedur systemowych, wróćmy jednak do tematu.

Aby utworzyć nową procedurę, wystarczy kliknąć PPM na katalogu Stored procedures i z menu kontekstowego wybrać opcję New stored procedure. Wybranie tej opcji, spowoduje utworzenie pustego szablonu procedury, który powinien wyglądać mniej więcej tak (z pominięciem komentarzy):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 

	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	SET NOCOUNT ON;

	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Spróbujmy przerobić ten kod, na coś bardziej działającego, choć może niekoniecznie pożytecznego..:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE HelloWorld 
	@Name varchar(50)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 'Witaj ' + @Name + '!'
END
GO

Jeśli wywołamy ten kod, a w nowym okienku wykonamy jeszcze np. takie zapytanie:

exec HelloWorld 'Ziutek'

To otrzymamy nasz upragniony komunikat, od którego wszystko się zaczyna:) Ale teraz po kolei.

W linii 1 oraz 3, określamy ustawienia SQL Servera. Pierwszy zapis mówi o zasadach porównywania wartości NULL, natomiast drugi zapis oznacza sposób używania cudzysłowów. Wszędobylskie GO, zapewne znacie - wykonuje ono cały dotychczasowy kod, napisany przed nim powyżej.

Dopiero w linii 5, zaczynam właściwy kod procedury. Aby stworzyć procedurę, wykorzystujemy standardowe polecenie CREATE. Następnie, mówimy jaki obiekt chcemy utworzyć i jaką nazwę ma przyjąć. Kolejnym krokiem, będzie określenie parametrów. Choć są one opcjonalne, jednak mogą okazać się bardzo przydatne. Np. w tym przypadku określamy imię użytkownika. Każdy parametr musi zaczynać się od znaku małpki. Musimy podać także jego typ oraz ewentualnie długość. Parametry (jeśli jest ich więcej niż jeden), rozdzielamy przecinkiem.

Po utworzeniu nagłówka procedury, w linii 7, stosujemy słowo kluczowe AS, które pozwala nam na wprowadzenie właściwego ciała procedury. Treść procedury, umieszczamy pomiędzy kolejnymi słowami kluczowymi - BEGIN oraz END.

W linii 9, wyłączamy licznik wierszy w tabeli bazy danych, których dotknęło to zapytanie. Tak na dobrą sprawę, polecenie to jest zbędne w tym przypadku, ale wygenerował je SQL Server, tak więc już je zostawiłem. Kluczową linią naszej procedury, jest linia 10, która wykonuje SELECT z naszym imieniem. Co ciekawe, procedura ta nie odwołuje się do żadnej tabeli z bazy danych, ale po jej wywołaniu poleceniem z kolejnego listingu, powinniśmy ujrzeć coś na kształt:

Witaj Ziutek!

Gratulacje! Utworzyłeś oraz wywowałałeś być może swoją pierwszą w życiu procedurę składowaną;) W tej chwili, powinna się ona pojawić w katalogu procedur składowanych (być może będziesz musiał go odświeżyć). Teraz możesz ją zmodyfikować (opcja Modify w menu kontekstowym), albo też np. usunąć (opcja Delete w menu kontekstowym).

Procedura dodająca użytkowników

Czas wrócić do tematu przewodniego, czyli do procedury, która pozwala na dodawanie użytkowników. Spełniać będzie ona następujące założenia:

  • Dodawanie użytkowników następuje na podstawie numeru PESEL (unikalny id tabeli), imienia oraz nazwiska
  • Z numeru pesel uzyskujemy datę urodzenia oraz płeć
  • Numer pesel, zawsze liczy 11 znaków
  • Do procedury można docelowo dołożyć weryfikację sumy kontrolnej oraz poszczególnych składników peselu

Powyższe założenia, spełnić może taki właśnie kod:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE AddPerson 
	-- Parametry
	@PersonId char(11),
	@PersonFirstName varchar(50),
	@PersonLastName varchar(50)
AS
BEGIN
	-- Zmienne procedury
	DECLARE @PersonSex char(1);
	DECLARE @PersonBirthDate smalldatetime;
	DECLARE @Year char(2);
	DECLARE @Month varchar(2);
	DECLARE @TempMonth smallint;
	
	-- Wyciąganie prawdziwej daty
	SET @TempMonth = SUBSTRING(@PersonId, 3, 2)
	SELECT @Year = 
		CASE
			WHEN @TempMonth < 20 THEN '19'
			WHEN @TempMonth < 40 THEN '20'
			WHEN @TempMonth < 60 THEN '21'
			WHEN @TempMonth < 80 THEN '22'
		END
	SELECT @Month = 	
		CASE
			WHEN @TempMonth < 20 THEN CONVERT(varchar, @TempMonth)
			WHEN @TempMonth < 40 THEN CONVERT(varchar, @TempMonth - 20)
			WHEN @TempMonth < 60 THEN CONVERT(varchar, @TempMonth - 40)
			WHEN @TempMonth < 80 THEN CONVERT(varchar, @TempMonth - 60)
		END
	IF LEN(@Month) < 2
		SET @Month = '0' + @Month
	SET @PersonBirthDate = CONVERT(smalldatetime, @Year + SUBSTRING(@PersonId, 1, 2) +  
		@Month + SUBSTRING(@PersonId, 5, 2), 112) 
		
	-- Ustawianie płci	
	IF CONVERT(int, SUBSTRING(@PersonId, 10, 1)) % 2 = 0
		 SET @PersonSex = 'K'
	ELSE
		SET @PersonSex = 'M'
		
	INSERT INTO Person(PersonId, PersonFirstName, PersonLastName,
		PersonSex, PersonBirthDate)
	VALUES (@PersonId, @PersonFirstName, @PersonLastName,
		@PersonSex, @PersonBirthDate)
END
GO

Przeanalizujmy kod krok po kroku.

W liniach 8-10, definiujemy parametry procedury. PersonId, to nic innego jak pesel danej osoby.

W liniach pomiędzy 12, a 51, umieszczamy właściwie ciało procedury. Na początek, umieszczamy zmienne, używane wewnątrz procedury do przetworzenia danych - głównie do przetworzenia daty.

W liniach 21-39, zajmujemy się przetworzeniem daty urodzenia. Choć z pozoru jej forma wydaje się być prosta - YYMMDD, to w istocie, jest ona dużo bardziej skomplikowana, ponieważ liczba miesiąca oznacza konkretne stulecie (czytaj więcej na temat PESEL). Wygląda to mniej więcej tak:

  • dla lat 1800-1899 - 81-92
  • dla lat 1900-1999 - normalnie
  • dla lat 2000-2099 - 21-32
  • dla lat 2100-2199 - 41-52
  • dla lat 2200-2299 - 61-72

Czyli przykładowo osoba urodzona w lutym w tym stuleciu, nie ma miesiąca 02, tylko 22. Instrukcje niniejszej procedury nie obsługują lat 1800-1899 (dopisanie tej funkcjonalności, możecie potraktować jako zadanie domowe:).

W liniach 22-28, wykorzystujemy instrukcję CASE, która działa na zasadzie podobnej do instrukcji IF/Switch (sprawdzamy warunki). Za jej pomocą, określamy stulecie osobnika, którego chcemy dodać PESEL.

W liniach 29-37, zajmujemy się przetworzeniem miesiąca, niestety w tym przypadku trzeba się trochę więcej napocić. W zależności od stulecia, należy wykonać odpowiednie odejmowanie, dodatkowo, wynik tej operacji konwertujemy do zmiennej typu varchar o długości 2 znaków. Następnie sprawdzamy długość tej zmiennej i jeśli wynosi ona jeden, to znaczy, że brakuje zera z przodu i musimy je dopisać.

Efektem naszych powyższych bojów, w walce z datą, będzie złożenie daty w całość. Mamy już stulecie, właściwy rok, odpowiedni miesiąc oraz dzień. Wystarczy skonkatenować wszystkie elementy i wykonać konwersję na typ DATETIME (dlatego ważna była np. poprawna forma miesiąca, zapisanego z zerem z przodu, dla wartości mniejszych niż 10).

10 cyfrą każdego peselu, jest oznaczenie płci. Liczba parzysta oznacza kobietę, nieparzysta mężczyznę. Łatwo to sprawdzić, sprawdzając resztę z dzielenia. Kobiety oznaczamy literką K, a mężczyzn literką M.

Ostatnim krokiem naszej procedury, jest już tylko wykonanie INSERTA do bazy danych, który zawierać będzie wartości z naszych zmiennych.

Testy, testy, testy...

Aby przetestować naszą procedurę, spróbujemy dodać Jana Kowalskiego, który rodził się czterokrotnie na przestrzeni wieków ;)

Procedurę wywołujemy poleceniem exec, następnie przekazując nazwę samej procedury oraz kolejne wartości parametrów:

exec AddPerson '44051401458', 'Jan', 'Kowalski'
exec AddPerson '44251401458', 'Jan', 'Kowalski'
exec AddPerson '44451401458', 'Jan', 'Kowalski'
exec AddPerson '44651401458', 'Jan', 'Kowalski'

Jeśli wykonacie ten kod, to zobaczycie, że nie uda się dodać 3 i 4 Jana Kowalskiego. Ograniczenie takie, zostało narzucone przez typ daty w SQL Serverze - po prostu, przekroczyliśmy jego dopuszczalną logiczną na dzień dzisiejszy wartość.

Czego tu jeszcze brakuje?

Choć powyższa procedura działa, to brakuje jej kilku rzeczy:

  • Wspomnianej wcześniej obsługi lat 1800-1899
  • Obsługi 11 cyfry, będącej sumą kontrolną
  • Obsługi potencjalnych błędów
  • Lepszej optymalizacji

Ale to wszystko, to tak właściwie temat na osobny wpis;)

Data ostatniej modyfikacji: 24.06.2013, 12:30.

Podoba Ci się ten wpis? Powiedz o tym innym!

Send to Kindle

Komentarze

blog comments powered by Disqus