SQL jak prawie każdy język programowania pozwala na tworzenie funkcji, a co za tym idzie również zmiennych przechowujących dane.
Zmienne lokalne
Implementacja zmiennych w kwerendzie SQL’a nie jest zbyt trudna, ale należy pamiętać o kilku zasadach:
- Długość nazwy zmiennej nie może przekraczać
128 znaków, - Nie jest dopuszczalne wykorzystywanie znaków specjalnych np. polskich liter,
- Zmienne lokalne oznaczamy znakiem
@poprzedzając nim nazwę nowo utworzonego obiektu (przykład:@jestemZmiennaLokalna), - Zmienne lokalne deklarujemy słowem kluczowym
DECLARE, a po nazwie podajemy typ danych (przykład:DECLARE @zadeklarowanaZmienna VARCHAR(50)), - Wartości przypisujemy poprzedzając zmienną słowem kluczowym
SET(przykład:SET @zadeklarowanaZmienna = 'Cześć'), - Możliwa jest deklaracja i inicjalizacja zmiennej równocześnie.
UWAGA: Omawiany materiał działa na bazach danych firmy Microsoft.
Typy danych
Kilka najważniejszych typów danych jakie mogą przyjmować zmienne:
| Typ | Opis |
| VARCHAR(rozmiar) | Przyjmuje tekst, w nawiasach podaje się liczbę znaków |
| CHAR | Znaki tekstowe |
| SYSNAME | Przyjmuje nazwę np. kolumny |
| INT | Liczby całkowite |
| FLOAT | Liczby rzeczywiste |
Tabela klienci na której będziemy uruchamiać przykładowe kwerendy:
| ID | Imie | Nazwisko | Miasto |
| 1 | Jan | Kowalski | Warszawa |
| 2 | Michał | Łagoda | Kraków |
| 3 | Marcin | Kulczyk | Kraków |
| 4 | Radosław | Sikorski | Kraków |
| 5 | Monika | Kopacz | Gdańsk |
| 6 | Piotr | Grabiec | Toruń |
| 7 | Ewa | Pogodna | Bydgoszcz |
| 8 | Sebastian | Szybki | Warszawa |
Przykład
Weźmy proste zapytanie typu:
SELECT imie, nazwisko FROM klienci WHERE imie = 'Sebastian';Otrzymamy jeden rekord:
| imie | nazwisko |
| Sebastian | Szybki |
Teraz do powyższego przykładu dodajmy zmienną lokalną o nazwie imie:
DECLARE @imie VARCHAR(200);
SET @imie = 'Sebastian';
Select imie, nazwisko FROM klienci WHERE imie = @imie;I tym razem otrzymamy identyczny wynik.
Podsumowanie
Zmiennych w SQL można używać w instrukcji warunkowej, pętli, instrukcji CASE itd.
Funkcje skalarne: CREATE FUNCTION
Jak wspomniałem na początku tego artykułu, SQL poza zwykłymi kwerendami jak prawie każdy język programowania umożliwia również tworzenie funkcji. Przyjrzyjmy się więc jak je napisać…
Funkcje skalarne, którymi zajmiemy się w tej części tego wpisu zazwyczaj są tak zbudowane, że zwracają pojedynczą wartość. Ważne jest jednak to, że konstrukcji takiej można używać we wszystkich zapytaniach SQL, w których dopuszczalne jest użycie wyrażeń skalarnych.
Przy budowie funkcji skalarnych należy zwrócić szczególną uwagę na:
- nazwę (nie może ona zawierać znaków specjalnych, polskich liter oraz spacji czy tak zwanych znaków białych),
- blok
BEGIN ... END, który musi zostać umieszczony w ciele funkcji, - parametry, przy wywoływaniu funkcji muszą zostać podane wszystkie jej parametry (argumenty) nawet te domyślne.
Po zapoznaniu się z teorią, najlepiej prześledzić wszystko w praktyce. Napiszmy więc funkcję skalarną w SQL.
Uwaga: Omawiany materiał działa na bazach danych firmy Microsoft.
Tabela pracownicy, na której uruchomimy przykładowe zapytanie:
| ID | Imie | Nazwisko | Data_urodzenia | Stanowisko | Data_zatrudnienia |
| 1 | Jan | Kowalski | 1984-12-23 | Programista | 1999-12-09 |
| 2 | Adam | Adamiak | 1991-08-08 | Menadżer | 2011-01-31 |
| 3 | Jakub | Kowalik | 1990-03-16 | Programista | 2010-01-04 |
| 4 | Michał | Jakubiak | 1981-05-02 | Projektant | 1998-09-22 |
Przykład
GO
CREATE FUNCTION nazwaFunkcji
(@zmienna AS VARCHAR(200)) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @imie AS VARCHAR(200)
SET @imie = ''
SELECT @imie = @imie + Imie + ', ' FROM dbo.pracownicy WHERE Stanowisko = @zmienna;
RETURN @imie;
END
GOPowyższą funkcję możemy uruchomić takim kodem:
SELECT dbo.nazwaFunkcji(Stanowisko) FROM pracownicy;Rezultat:
| Jan, Jakub |
| Adam |
| Jan, Jakub |
| Michał |
Jak widać, funkcja dla każdego wiersza, zwróciła nam informacje o imionach pracowników, zajmujących dane stanowisko.
Jak to działa?
Uruchamiając zapytanie:
SELECT dbo.nazwaFunkcji(Stanowisko) FROM pracownicy;„Przechodziliśmy” po wszystkich wierszach z tabeli pracownicy, podając do funkcji nazwaFunkcji() dane z kolumny Stanowisko. W funkcji tej znowu przechodziliśmy, tym razem po całej tabeli pracownicy tak aby odczytać imiona pracowników zajmujących dane stanowisko. Informacje te były zapisywane do zmiennej @imie, którą na końcu zwróciliśmy.
Jak widać została zwrócona jedna wartość (zmienna @imie) czyli zgodnie z tym co napisałem na początku nasza funkcja skalarna zwróciła pojedynczą daną.
Usuwanie funkcji
Funkcje możemy skasować za pomocą takiego kodu:
IF OBJECT_ID('dbo.nazwaFunkcji') IS NOT NULL --jeżeli funkcja istnieje
DROP FUNCTION dbo.nazwaFunkcji; --kasowanieFunkcje tablicowe
Funkcje tablicowe definiowane przez programistę charakteryzują się między innymi tym, że zwracają one określone dane (na przykład tabelę) i głównie wykorzystywane są w zwykłych kwerendach po klauzuli FROM. Można więc z nich korzystać bardzo podobnie jak z widoków. Funkcje tablicowe to pewnego rodzaju procedury składowane, które działają mniej więcej na tych samych zasadach.
Pisząc funkcje tablicowe w SQL nie definiujemy bloku BEGIN - END, a jedyne co musimy zrobić to napisać zapytanie i zwrócić odpowiednie dane. Oczywiście mogą to być informacje odpowiednio spersonalizowane na podstawie odczytanych jako argumenty pewnych danych.
UWAGA: Omawiany materiał działa na bazach danych firmy Microsoft.
Tabela pracownicy, na której uruchomimy przykładowe zapytanie:
| ID | Imie | Nazwisko | Data_urodzenia | Stanowisko | Data_zatrudnienia |
| 1 | Jan | Kowalski | 1984-12-23 | Programista | 1999-12-09 |
| 2 | Adam | Adamiak | 1991-08-08 | Menadżer | 2011-01-31 |
| 3 | Jakub | Kowalik | 1990-03-16 | Programista | 2010-01-04 |
| 4 | Michał | Jakubiak | 1981-05-02 | Projektant | 1998-09-22 |
Przykład
No to tyle z teorii, czas zabrać się za praktykę. Napiszmy więc funkcję tablicową, która na podstawie przekazanej jako argument nazwy stanowiska zwróci nam tabelę z pracownikami zajmującymi dane stanowisko.
GO
CREATE FUNCTION FN_pracownicy (@stanowisko AS VARCHAR(200))
RETURNS TABLE
AS
RETURN
SELECT imie, nazwisko FROM pracownicy WHERE stanowisko = @stanowisko;
GOPo uruchomieniu tego zapytania dostaniemy informację, że funkcja została zapisana na serwerze. Aby z niej skorzystać należy uruchomić poniższy kod:
SELECT * FROM FN_pracownicy('Programista');Rezultat:
| imie | nazwisko |
| Jan | Kowalski |
| Jakub | Kowalik |
O tym jak skasować stworzoną wyżej funkcję, możecie przeczytać w sekcji Usuwanie funkcji.
Modyfikacja funkcji
Przypuśćmy, że nasza funkcja zapisana na serwerze SQL zawiera błąd. Co więc w takiej sytuacji zrobić? Możemy ją oczywiście skasować i napisać od nowa, ale jeśli nie mamy jej kodu? W Microsoft SQL Management Studio aby dostać się do zapisanych funkcji wystarczy wykonać następujące kroki:
- Przechodzimy do bocznego okna o nazwie „Object Explorer” następnie z rozwijanej listy wybieramy „Databases”, odszukujemy nazwę naszej bazy danych w moim przypadku jest to „kurs”, następnie folder „Programmability”, folder „Functions”, folder „Table-valued Functions” (tutaj przechowywane są właśnie funkcje tablicowe) oraz odnajdujemy na liście naszą funkcję.
- Klikamy prawym przyciskiem myszy na nazwie funkcji, którą chcemy zmodyfikować i z menu kontekstowego wybieramy opcję „Modify”:

- Po wykonaniu powyższych czynności otworzy nam się kod funkcji, który możemy dowolnie zmodyfikować, a po zakończeniu prac wystarczy po prostu wcisnąć przycisk „Execute”:

- Funkcja została zmodyfikowana.
Procedury składowane
Procedury składowane to pewnego rodzaju funkcje tablicowe z tym wyjątkiem, że przy procedurach składowanych mamy większe możliwości operacji na danych. Oczywiście wszystko tak jak zwykłe funkcje zapisujemy bezpośrednio na serwerze bazodanowym. A następnie możemy ich użyć na przykład z poziomu jakiejś aplikacji korzystającej z bazy danych.
Procedury składowane mogą między innymi modyfikować dane w tabelach i co najważniejsze sprawdzać poprawność przesyłanych wartości. Oznacza to, że na przykład procedura składowana dodająca nowy wiersz w jakiejś tabeli automatycznie po stronie serwera SQL sprawdzi czy dane, które chcemy zapisać są poprawne czyli czy spełniają wcześniej ustalone kryteria.
Stosowanie procedur składowanych zwiększa również wydajność aplikacji. Należy tutaj zwrócić uwagę, iż po stronie klienta, przesyłana jest tylko nazwa danej procedury wraz z danymi, a nie na przykład całe zapytanie SQL. Ważne jest również to, że wszystko wykonywane jest bezpośrednio na serwerze bazodanowym, a więc nie musimy po kilka razy przesyłać jakichś informacji.
Tabela pracownicy, na której uruchomimy przykładowe zapytanie:
| ID | Imie | Nazwisko | Data_urodzenia | Stanowisko | Data_zatrudnienia |
| 1 | Jan | Kowalski | 1984-12-23 | Programista | 1999-12-09 |
| 2 | Adam | Adamiak | 1991-08-08 | Menadżer | 2011-01-31 |
| 3 | Jakub | Kowalik | 1990-03-16 | Programista | 2010-01-04 |
| 4 | Michał | Jakubiak | 1981-05-02 | Projektant | 1998-09-22 |
Procedury składowane tworzymy bardzo podobnie jak zwykłe funkcje, które zostały opisane w wcześniejszych częściach tego artykułu.
Napiszmy przykładową procedurę składowaną, która będzie dodawała nowy wiersz do tabeli pracownicy. Musimy tutaj zwrócić uwagę między innymi na to, żeby żadne z pól nie było puste:
GO
CREATE PROC dbo.dodaj_dane
@imie VARCHAR(200) = NULL,
@nazwisko VARCHAR(200) = NULL,
@dataUrodzenia DATE = NULL,
@stanowisko VARCHAR(200) = NULL,
@dataZatrudnienia DATE = NULL
AS
DECLARE @blad AS NVARCHAR(500);
IF @imie IS NULL OR @nazwisko IS NULL OR @dataUrodzenia IS NULL OR
@stanowisko IS NULL OR @dataZatrudnienia IS NULL
BEGIN
SET @blad = 'Błędne dane!';
RAISERROR(@blad, 16,1);
RETURN;
END
INSERT INTO pracownicy(Imie, Nazwisko, Data_urodzenia, Stanowisko, Data_zatrudnienia)
VALUES (@imie, @nazwisko, @dataUrodzenia, @stanowisko, @dataZatrudnienia);
GOKod ten podobnie jak zwykłe funkcje uruchamiamy w taki sposób (możemy to również zrobić z poziomu aplikacji klienckiej):
EXEC dbo.dodaj_dane @imie = 'Jan', @nazwisko = 'Kowalski',
@dataUrodzenia = '1991-08-08', @stanowisko = 'Programista',
@dataZatrudnienia = '2001-11-11'
GOPo uruchomieniu powyższego kodu otrzymamy informację, że 1 wiersz został dodany.
Tabela pracownicy wygląda więc tak:
| ID | Imie | Nazwisko | Data_urodzenia | Stanowisko | Data_zatrudnienia |
| 1 | Jan | Kowalski | 1984-12-23 | Programista | 1999-12-09 |
| 2 | Adam | Adamiak | 1991-08-08 | Menadżer | 2011-01-31 |
| 3 | Jakub | Kowalik | 1990-03-16 | Programista | 2010-01-04 |
| 4 | Michał | Jakubiak | 1981-05-02 | Projektant | 1998-09-22 |
| 5 | Jan | Kowalski | 1991-08-08 | Programista | 2001-11-11 |
Jak widać na końcu pojawił się nowy wiersz.
Oczywiście w procedurze składowanej możemy umieścić również zapytania typu SELECT oraz DELETE.
Ogólna struktura procedury składowanej w SQL
GO
CREATE PROC nazwaProcedury
--//Argumenty które przyjmuje np.:
@imie VARCHAR(200) = NULL,
@nazwisko VARCHAR(200) = NULL,
--//@nazwaZmiennej typDanych = NULL - Opcjonalnie możemy od razu zainicjować zmienne
AS
--//Deklarowane jakieś inne zmienne (wewnętrzne)
DECLARE @blad AS NVARCHAR(500);
--//@nazwaZmiennej AS typDanych
--//Sprawdzamy poprawność wprowadzonych danych np.:
IF @imie IS NULL OR @nazwisko IS NULL
BEGIN
--//Jeżeli dane są niepoprawne, wyświetlamy komunikat o błędzie
SET @blad = 'Błędne dane!'; --//Ustawiamy komunikat
RAISERROR(@blad, 16,1);
RETURN; --//Kończymy działanie procedury
END
--//Dowolne zapytanie SQL, operujące na danych przekazanych jako argumenty
INSERT INTO pracownicy(Imie, Nazwisko)
VALUES (@imie, @nazwisko);
GO


Dzięki za czujność ;) Poprawione.
Prosze
Podoba mi się że teksty są lakoniczne. Bez zbędnych opisów i wodotrysków jedna też mam uwagę tym razem fldo fragmentu który jest niespójny „nie jest dopuszczalne jest wykorzystywanie znaków specjalnych, polskich liter itp.,”
Proszę wybaczyć moje literówki.
Cześć!
Dzięki za komentarz i informację. Artykuł zaktualizowany :)
Pozdrawiam serdecznie,
Łukasz Dudziński, autor bloga StrefaKodera.pl
Dzień dobry, chciałbym zgłosić literówkę w miejscu „jakie mogĘ przyjmować zmienne”.
Pozdrawiam i życzę miłego dnia