Itek.pl – Sklepy internetowe

Lekcja 17 – Procedury składowane (Stored Procedures) i funkcje w SQL

Procedury składowane (ang. Stored Procedures) i funkcje to potężne narzędzia dostępne w SQL, które umożliwiają tworzenie złożonych operacji i logiki biznesowej bezpośrednio w bazie danych. Procedury i funkcje pozwalają na automatyzowanie procesów, usprawnienie wykonywania operacji oraz zmniejszenie liczby powtarzalnych zadań. W tej lekcji omówimy, czym są procedury składowane i funkcje, jak je tworzyć, jakie mają zastosowania oraz jakie korzyści przynoszą.

W systemach opartych na SQL, takich jak MySQL, PostgreSQL, SQL Server czy Oracle, procedury i funkcje są często wykorzystywane do zarządzania skomplikowanymi operacjami, które mogą wymagać przetwarzania wielu danych i operacji w jednym miejscu. Pozwalają one również na centralizację logiki biznesowej w bazie danych, co może poprawić wydajność i bezpieczeństwo systemu.

Procedury składowane (Stored Procedures)

Procedura składowana to zbiór poleceń SQL zapisanych w bazie danych, które mogą być wywoływane i wykonywane w jednym kroku. Procedury mogą zawierać instrukcje warunkowe, pętle, zmienne oraz inne elementy programistyczne, co sprawia, że są one potężnym narzędziem do automatyzacji zadań. Główna różnica między procedurą składowaną a zwykłym zapytaniem SQL polega na tym, że procedura jest zapisana w bazie danych i może być wielokrotnie wywoływana, co pozwala na jej ponowne wykorzystanie w różnych miejscach aplikacji.

Zastosowania procedur składowanych
  1. Automatyzacja złożonych operacji: Procedury mogą łączyć wiele zapytań SQL w jedno wywołanie, co pozwala na automatyzację skomplikowanych operacji, takich jak aktualizacje wielu tabel, przetwarzanie danych czy generowanie raportów.
  2. Centralizacja logiki biznesowej: Procedury składowane umożliwiają przeniesienie części logiki aplikacji do bazy danych, co poprawia wydajność oraz pozwala na lepszą kontrolę nad danymi.
  3. Poprawa wydajności: Zamiast wysyłać wiele zapytań SQL z poziomu aplikacji, możemy wywołać jedną procedurę składowaną, która wykona całą sekwencję operacji bez potrzeby wielokrotnych połączeń z bazą danych.
  4. Bezpieczeństwo: Procedury składowane mogą ukrywać szczegóły operacji na danych przed użytkownikami. Możemy przyznać uprawnienia tylko do wykonywania procedur, zamiast bezpośredniego dostępu do tabel.
Tworzenie procedury składowanej

Aby utworzyć procedurę składowaną, używamy polecenia CREATE PROCEDURE. Procedura może przyjmować parametry wejściowe i wyjściowe oraz zawierać blok instrukcji SQL.

Składnia:

CREATE PROCEDURE nazwa_procedury (parametry)
BEGIN
    -- Blok instrukcji SQL
END;

Przykład: Procedura składowana dodająca nowego użytkownika do tabeli Uzytkownicy.

CREATE PROCEDURE DodajUzytkownika (IN p_imie VARCHAR(100), IN p_email VARCHAR(100))
BEGIN
    INSERT INTO Uzytkownicy (Imie, Email) VALUES (p_imie, p_email);
END;

Aby wywołać procedurę, używamy polecenia CALL:

CALL DodajUzytkownika('Jan Kowalski', 'jan@example.com');
Parametry w procedurach składowanych

Procedury mogą przyjmować różne typy parametrów:

  1. IN – Parametr wejściowy, który przekazuje wartość do procedury.
  2. OUT – Parametr wyjściowy, który przekazuje wartość z procedury na zewnątrz.
  3. INOUT – Parametr wejściowo-wyściowy, który przekazuje wartość do procedury, ale może również być zmodyfikowany wewnątrz procedury i zwrócony na zewnątrz.

Przykład: Procedura z parametrami IN i OUT.

CREATE PROCEDURE ObliczPodatek (IN p_kwota DECIMAL(10,2), OUT p_podatek DECIMAL(10,2))
BEGIN
    SET p_podatek = p_kwota * 0.23;
END;

Wywołanie procedury z parametrem wyjściowym:

CALL ObliczPodatek(1000, @podatek);
SELECT @podatek;  -- Zwraca wartość podatku
Instrukcje warunkowe i pętle w procedurach

Procedury składowane obsługują instrukcje warunkowe oraz pętle, co pozwala na tworzenie bardziej zaawansowanej logiki.

Przykład: Procedura z instrukcją warunkową IF.

CREATE PROCEDURE SprawdzSaldo (IN p_klient_id INT, OUT p_status VARCHAR(20))
BEGIN
    DECLARE saldo DECIMAL(10,2);
    
    SELECT Saldo INTO saldo FROM Konta WHERE Klient_ID = p_klient_id;
    
    IF saldo > 0 THEN
        SET p_status = 'Aktywne';
    ELSE
        SET p_status = 'Zablokowane';
    END IF;
END;

W tej procedurze sprawdzamy saldo konta klienta i w zależności od wyniku ustawiamy odpowiedni status.

Funkcje w SQL

Funkcje w SQL są podobne do procedur składowanych, ale mają kilka istotnych różnic. Funkcje zawsze zwracają wartość i są używane głównie do przetwarzania danych, które mogą być wykorzystywane bezpośrednio w zapytaniach SQL. Funkcje są bardziej ograniczone niż procedury – mogą mieć tylko parametry wejściowe i nie mogą zmieniać danych w bazie (czyli wykonywać operacji takich jak INSERT, UPDATE czy DELETE).

Tworzenie funkcji

Funkcje tworzy się za pomocą polecenia CREATE FUNCTION, a składnia wygląda następująco:

CREATE FUNCTION nazwa_funkcji (parametry)
RETURNS typ_wartosci
BEGIN
    -- Blok instrukcji
    RETURN wartość;
END;

Przykład: Funkcja obliczająca podatek od kwoty.

CREATE FUNCTION ObliczPodatek (p_kwota DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    RETURN p_kwota * 0.23;
END;

Funkcję można wywołać bezpośrednio w zapytaniu SELECT:

SELECT ObliczPodatek(1000);

Funkcje mogą być używane w zapytaniach do przetwarzania danych w sposób bardziej elastyczny niż standardowe operatory SQL.

Zastosowanie funkcji

Funkcje są użyteczne w wielu scenariuszach:

  1. Przetwarzanie danych: Funkcje mogą być używane do obliczania wartości na podstawie kolumn w tabelach, np. obliczania podatku, rabatów czy sumy.
  2. Zastosowanie w zapytaniach: Funkcje mogą być bezpośrednio używane w zapytaniach SELECT, WHERE, ORDER BY, co pozwala na dynamiczne przetwarzanie danych.
  3. Operacje na ciągach znaków: Funkcje mogą być używane do operacji na ciągach znaków, np. do formatowania danych lub wyszukiwania fragmentów tekstu.
Funkcje złożone

Funkcje mogą również zawierać bardziej złożoną logikę, taką jak instrukcje warunkowe i pętle, podobnie jak procedury. Funkcje złożone mogą przetwarzać dane w bardziej zaawansowany sposób.

Przykład: Funkcja sprawdzająca, czy użytkownik ma wystarczające saldo na koncie.

CREATE FUNCTION SprawdzSaldo (p_klient_id INT, p_kwota DECIMAL(10,2))
RETURNS BOOLEAN
BEGIN
    DECLARE saldo DECIMAL(10,2);
    
    SELECT Saldo INTO saldo FROM Konta WHERE Klient_ID = p_klient_id;
    
    RETURN saldo >= p_kwota;
END;

Funkcję tę możemy wykorzystać do sprawdzenia, czy klient ma wystarczające saldo przed wykonaniem transakcji:

SELECT SprawdzSaldo(1, 500);

Różnice między procedurami składowanymi a funkcjami

  1. Zwracanie wartości: Procedury składowane mogą, ale nie muszą zwracać wartości, podczas gdy funkcje zawsze zwracają jedną wartość.
  2. Zmiana danych: Procedury składowane mogą wykonywać operacje modyfikujące dane, takie jak INSERT, UPDATE, DELETE, natomiast funkcje są ograniczone do operacji przetwarzania danych bez ich zmieniania.
  3. Użycie w zapytaniach: Funkcje mogą być używane bezpośrednio w zapytaniach SQL, natomiast procedury składowane są wywoływane za pomocą polecenia CALL i nie mogą być częścią zwykłego zapytania SELECT.

Zalety procedur składowanych i funkcji

  1. Reużywalność kodu: Procedury i funkcje mogą być używane wielokrotnie w różnych miejscach aplikacji, co zmniejsza konieczność powtarzania kodu SQL.
  2. Poprawa wydajności: Wykonanie skomplikowanych operacji w jednej procedurze lub funkcji może być bardziej efektywne niż wysyłanie wielu zapytań z aplikacji do bazy danych.
  3. Bezpieczeństwo: Zamiast udostępniać użytkownikom dostęp do tabel, możemy dać im uprawnienia do wykonywania procedur, co zwiększa kontrolę nad danymi.
  4. Centralizacja logiki biznesowej: Procedury i funkcje pozwalają na przeniesienie części logiki aplikacji do bazy danych, co ułatwia zarządzanie nią i utrzymanie jej w jednym miejscu.

Podsumowanie

Procedury składowane i funkcje to nieocenione narzędzia w SQL, które pozwalają na tworzenie złożonych operacji i logiki biznesowej bezpośrednio w bazie danych. Procedury są idealne do automatyzacji operacji na danych, takich jak aktualizacje, dodawanie lub usuwanie rekordów, natomiast funkcje sprawdzają się najlepiej w przetwarzaniu danych i zwracaniu wartości, które mogą być używane w zapytaniach SQL. Oba te mechanizmy pozwalają na centralizację logiki w bazie danych, co może znacząco poprawić wydajność i bezpieczeństwo systemu.

W następnej lekcji zajmiemy się wyzwalaczami (Triggers), które umożliwiają automatyczne wywoływanie operacji na danych w odpowiedzi na określone zdarzenia w bazie danych.

Gratulacje! Ukończyłeś lekcję 17.
Przejdź teraz do lekcji 18 >> Wyzwalacze (Triggers) w bazach danych


Spis Treści - darmowy kurs SQL


Wprowadzenie: Czym jest baza danych?
Lekcja 1: Instalacja dedykowanego środowiska dla SQL
Lekcja 2: Tworzenie bazy danych
Lekcja 3: Tworzenie tabel w SQL
Lekcja 4: Wstawianie danych do bazy danych SQL
Lekcja 5: Pobieranie danych z bazy danych SQL
Lekcja 6: Aktualizacja i usuwanie danych SQL
Lekcja 7: Operacje na danych (JOIN)
Lekcja 8: Funkcje agregujące i grupowanie danych (GROUP BY)
Lekcja 9: Podzapytania (Subqueries)
Lekcja 10: Indeksy w bazach danych
Lekcja 11: Optymalizacja zapytań SQL
Lekcja 12: Transakcje w bazach danych
Lekcja 13: Zarządzanie blokadami (Locks) i dostępem do danych
Lekcja 14: Replikacja danych w bazach danych
Lekcja 15: Partycjonowanie danych SQL
Lekcja 16: Widoki (Views) w bazach danych
Lekcja 17: Procedury składowane (Stored Procedures) i funkcje w SQL
Lekcja 18: Wyzwalacze (Triggers) w bazach danych
Lekcja 19: Zaawansowane indeksowanie w bazach danych
Lekcja 20: Narzędzia do zarządzania dużymi danymi (Big Data) w SQL
Lekcja 21: Bezpieczeństwo baz danych


Dodatki: 
- Spis najważniejszych funkcji SQL