Funkcje i Procedury Składowane w MySQL

Lekcja: Funkcje i Procedury Składowane w MySQL

1. Wstęp

Funkcje i procedury składowane w MySQL pozwalają na automatyzację operacji wykonywanych w bazie danych. Różnią się sposobem użycia:

  • Procedury są wykonywane jako osobne instrukcje (CALL), mogą modyfikować dane i nie muszą zwracać wartości.
  • Funkcje mogą być używane w zapytaniach SQL (SELECT), muszą zwracać wartość i nie mogą zmieniać danych w tabelach.

2. Tworzenie Procedur Składowanych

2.1. Składnia tworzenia procedury

DELIMITER //

CREATE PROCEDURE nazwa_procedury(parametry)
BEGIN
    -- instrukcje SQL
END //

DELIMITER ;
  • DELIMITER // – zmienia separator poleceń, aby obsłużyć wieloliniowy kod procedury.
  • CREATE PROCEDURE – tworzy nową procedurę.
  • BEGIN ... END – definiuje blok kodu procedury.

2.2. Przykład procedury z warunkiem IF

DELIMITER //

CREATE PROCEDURE sprawdz_rabat(IN klient_id INT, OUT rabat INT)
BEGIN
    DECLARE wiek INT;
    SELECT wiek INTO wiek FROM klienci WHERE id = klient_id;

    IF wiek > 60 THEN
        SET rabat = 10;
    ELSE
        SET rabat = 0;
    END IF;
END //

DELIMITER ;
  • Pobiera wiek klienta.
  • Jeśli wiek przekracza 60 lat, zwraca rabat 10%.

3. Tworzenie Funkcji Składowanych

3.1. Składnia tworzenia funkcji

DELIMITER //

CREATE FUNCTION nazwa_funkcji(parametry) RETURNS typ_danych
DETERMINISTIC
BEGIN
    -- instrukcje SQL
    RETURN wartość;
END //

DELIMITER ;
  • RETURNS typ_danych – określa typ zwracanej wartości.
  • DETERMINISTIC – oznacza, że funkcja zawsze zwróci tę samą wartość dla tych samych danych wejściowych.

3.2. Przykład funkcji obliczającej średnią wartość zamówień klienta

DELIMITER //

CREATE FUNCTION srednia_kwota_zamowien(klient_id INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE wynik DECIMAL(10,2);
    
    SELECT AVG(kwota) INTO wynik FROM zamowienia WHERE klient_id = klient_id;
    
    RETURN IFNULL(wynik, 0);
END //

DELIMITER ;
  • Oblicza średnią kwotę zamówień danego klienta.
  • Zwraca 0, jeśli klient nie ma zamówień.

4. Pętle w Procedurach i Funkcjach

4.1. Pętla WHILE

DELIMITER //

CREATE PROCEDURE wyswietl_liczby(IN liczba INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= liczba DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;
  • Wyświetla liczby od 1 do liczba.

4.2. Pętla LOOP

DELIMITER //

CREATE PROCEDURE wyswietl_liczby_loop(IN liczba INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    nazwa_petli: LOOP
        SELECT i;
        SET i = i + 1;
        
        IF i > liczba THEN
            LEAVE nazwa_petli;
        END IF;
    END LOOP;
END //

DELIMITER ;
  • Wykorzystuje pętlę LOOP do wyświetlania liczb.
  • LEAVE kończy pętlę po osiągnięciu wartości liczba.

5. Instrukcje Warunkowe

5.1. Instrukcja CASE

DELIMITER //

CREATE PROCEDURE okresl_status_zamowienia(IN zamowienie_id INT, OUT status_zamowienia VARCHAR(20))
BEGIN
    DECLARE kwota_zamowienia DECIMAL(10,2);
    
    SELECT kwota INTO kwota_zamowienia FROM zamowienia WHERE id = zamowienie_id;
    
    CASE 
        WHEN kwota_zamowienia < 100 THEN SET status_zamowienia = 'Niskie';
        WHEN kwota_zamowienia BETWEEN 100 AND 300 THEN SET status_zamowienia = 'Srednie';
        ELSE SET status_zamowienia = 'Wysokie';
    END CASE;
END //

DELIMITER ;
  • Określa status zamówienia na podstawie jego kwoty.

5.2. IF ELSE w Procedurze

Poniższa procedura sprawdza, czy dany produkt w magazynie jest dostępny.

DELIMITER //

CREATE PROCEDURE sprawdz_dostepnosc(IN produkt_nazwa VARCHAR(50), OUT status VARCHAR(50))
BEGIN
    DECLARE ilosc INT;
    
    SELECT ilosc INTO ilosc FROM magazyn WHERE nazwa = produkt_nazwa;
    
    IF ilosc > 0 THEN
        SET status = 'Produkt jest dostępny';
    ELSE
        SET status = 'Produkt niedostępny';
    END IF;
END //

DELIMITER ;
  • Pobiera ilość danego produktu w magazynie.
  • Jeśli ilość jest większa niż 0, zwraca "Produkt jest dostępny".
  • W przeciwnym razie zwraca "Produkt niedostępny".

5.3. IF ELSEIF ELSE w Procedurze

Ta procedura określa priorytet dostawy na podstawie wartości zamówienia.

DELIMITER //

CREATE PROCEDURE okresl_priorytet_dostawy(IN kwota DECIMAL(10,2), OUT priorytet VARCHAR(20))
BEGIN    
    IF kwota < 100 THEN
        SET priorytet = 'Niski priorytet';
    ELSEIF kwota BETWEEN 100 AND 500 THEN
        SET priorytet = 'Sredni priorytet';
    ELSEIF kwota BETWEEN 501 AND 1000 THEN
        SET priorytet = 'Wysoki priorytet';
    ELSE
        SET priorytet = 'Ekspresowa dostawa';
    END IF;
END //

DELIMITER ;
  • Na podstawie wartości zamówienia określa priorytet dostawy:
    • Niski priorytet (mniej niż 100)
    • Średni priorytet (100-500)
    • Wysoki priorytet (501-1000)
    • Ekspresowa dostawa (powyżej 1000)

Dzięki temu przykłady nie pokrywają się z zadaniami w lekcji, ale nadal dobrze ilustrują użycie instrukcji warunkowych w procedurach składowanych MySQL.

Ćwiczenia

Ćwiczenie 1: Procedura sprawdzająca dostępność produktu w magazynie

Napisz procedurę sprawdz_magazyn, która przyjmuje nazwa_produktu i zwraca ilość dostępnych sztuk.

Ćwiczenie 2: Procedura obliczająca liczbę zamówień danego klienta

Stwórz procedurę ilosc_zamowien, która przyjmuje klient_id i zwraca liczbę zamówień klienta.

Ćwiczenie 3: Funkcja obliczająca całkowitą wartość zamówień klienta

Utwórz funkcję calkowita_kwota_zamowien, która przyjmuje klient_id i zwraca sumę wszystkich jego zamówień.

Ćwiczenie 4: Funkcja zwracająca najwyższą kwotę zamówienia klienta

Napisz funkcję najwyzsza_kwota_zamowienia, która zwraca najwyższą wartość zamówienia danego klienta.

Ćwiczenie 5: Procedura wyświetlająca listę zamówień klienta za pomocą pętli

Napisz procedurę lista_zamowien, która dla danego klient_id wyświetla ID wszystkich jego zamówień.

Ćwiczenie 6: Procedura przypisująca klientom status na podstawie liczby zamówień

Napisz procedurę status_klienta, która nadaje klientowi status:

  • Nowy, jeśli ma 1 zamówienie,
  • Regularny, jeśli ma 2-5 zamówień,
  • VIP, jeśli ma więcej niż 5 zamówień.

To podsumowanie funkcji i procedur składowanych w MySQL. Przykłady powinny pomóc w rozwiązaniu ćwiczeń, ale wymagają modyfikacji, aby uniknąć bezpośredniego kopiowania.