Oto rozbudowana kontynuacja lekcji o funkcjach składowanych i wyzwalaczach w MySQL, wzbogacona o więcej przykładów oraz 6 ćwiczeń.

Funkcje składowane i wyzwalacze w MySQL – przykłady i ćwiczenia

1. Funkcje składowane w MySQL

Funkcje składowane (stored functions) pozwalają na wykonywanie określonych operacji na danych i zwracanie pojedynczej wartości. Można je wykorzystywać w zapytaniach SELECT i zwiększać w ten sposób czytelność oraz reużywalność kodu.

1.1 Tworzenie funkcji składowanej – podstawowy przykład

Poniżej przykład funkcji składowanej, która oblicza podatek VAT dla podanej kwoty:

DELIMITER $$

CREATE FUNCTION oblicz_vat(cena DECIMAL(10,2)) 
RETURNS DECIMAL(10,2) 
DETERMINISTIC
BEGIN
    RETURN cena * 1.23;
END $$

DELIMITER ;

Wywołanie funkcji w zapytaniu:

SELECT oblicz_vat(100) AS cena_z_vat;

Wynik:

| cena_z_vat | |------------| | 123.00 |

1.2 Funkcja składowana operująca na danych z tabeli

Załóżmy, że mamy tabelę zamowienia i chcemy obliczyć łączną wartość zamówienia dla danego klienta.

DELIMITER $$

CREATE FUNCTION suma_zamowien(id_klienta INT) 
RETURNS DECIMAL(10,2) 
DETERMINISTIC
BEGIN
    DECLARE suma DECIMAL(10,2);
    SELECT SUM(kwota) INTO suma FROM zamowienia WHERE klient_id = id_klienta;
    RETURN COALESCE(suma, 0);
END $$

DELIMITER ;

Wywołanie funkcji:

SELECT suma_zamowien(5) AS laczna_wartosc_zamowien;

1.3 Modyfikowanie funkcji składowanej

Jeśli chcemy zmienić istniejącą funkcję, musimy ją najpierw usunąć (DROP FUNCTION), a następnie utworzyć na nowo.

DROP FUNCTION IF EXISTS oblicz_vat;

2. Wyzwalacze w MySQL

Wyzwalacze (triggers) pozwalają na automatyczne wykonywanie operacji przy dodawaniu, edytowaniu lub usuwaniu rekordów. Mogą być stosowane przed (BEFORE) lub po (AFTER) wykonaniu danej operacji.

2.1 Tworzenie wyzwalacza – automatyczne uzupełnianie daty

Jeśli mamy tabelę zamowienia i chcemy, by każde nowe zamówienie automatycznie otrzymywało aktualną datę, możemy utworzyć wyzwalacz:

DELIMITER $$

CREATE TRIGGER ustaw_date_wstawienia
BEFORE INSERT ON zamowienia
FOR EACH ROW
BEGIN
    SET NEW.data_zlozenia = NOW();
END $$

DELIMITER ;

2.2 Tworzenie wyzwalacza – logowanie zmian

Załóżmy, że mamy tabelę klienci i chcemy zapisywać zmiany nazwisk klientów w osobnej tabeli log_zmian.

Tworzenie tabeli logów:

CREATE TABLE log_zmian (
    id INT AUTO_INCREMENT PRIMARY KEY,
    klient_id INT,
    stare_nazwisko VARCHAR(100),
    nowe_nazwisko VARCHAR(100),
    data_zmiany TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Tworzenie wyzwalacza:

DELIMITER $$

CREATE TRIGGER loguj_zmiane_nazwiska
BEFORE UPDATE ON klienci
FOR EACH ROW
BEGIN
    IF OLD.nazwisko <> NEW.nazwisko THEN
        INSERT INTO log_zmian (klient_id, stare_nazwisko, nowe_nazwisko)
        VALUES (OLD.id, OLD.nazwisko, NEW.nazwisko);
    END IF;
END $$

DELIMITER ;

Teraz każda zmiana nazwiska klienta będzie zapisywana w tabeli log_zmian.

2.3 Usuwanie wyzwalacza

Jeśli chcemy usunąć wyzwalacz, używamy polecenia:

DROP TRIGGER IF EXISTS loguj_zmiane_nazwiska;

CREATE TABLE klienci (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(100),
    wiek INT
);

CREATE TABLE zamowienia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    klient_id INT,
    kwota DECIMAL(10,2),
    data_zlozenia DATETIME,
    FOREIGN KEY (klient_id) REFERENCES klienci(id) ON DELETE CASCADE
);

CREATE TABLE log_zmian (
    id INT AUTO_INCREMENT PRIMARY KEY,
    klient_id INT,
    stare_nazwisko VARCHAR(100),
    nowe_nazwisko VARCHAR(100),
    data_zmiany TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE log_usuniec (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zamowienie_id INT,
    klient_id INT,
    data_usuniecia TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE magazyn (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100),
    ilosc INT
);

INSERT INTO klienci (imie, nazwisko, wiek) VALUES
('Jan', 'Kowalski', 35),
('Anna', 'Nowak', 28),
('Piotr', 'Wisniewski', 42),
('Maria', 'Dabrowska', 19),
('Kamil', 'Lewandowski', 16);

INSERT INTO zamowienia (klient_id, kwota, data_zlozenia) VALUES
(1, 150.50, '2024-05-10 12:30:00'),
(2, 200.00, '2024-05-15 14:15:00'),
(3, 300.75, '2024-06-01 09:00:00'),
(4, 99.99, '2024-06-02 16:45:00');

INSERT INTO magazyn (nazwa, ilosc) VALUES
('Laptop', 10),
('Telefon', 25),
('Tablet', 15);

Ćwiczenia

Ćwiczenia – funkcje składowane (3 zadania)

Napisz funkcję składowaną rabat_procentowy, która dla podanej ceny zwróci cenę po zastosowaniu 10% rabatu.
Utwórz funkcję liczba_zamowien, która zwróci liczbę zamówień dla podanego ID klienta.
Zmień funkcję suma_zamowien, aby zwracała wartość łącznych zamówień tylko z ostatnich 30 dni.

Ćwiczenia – wyzwalacze (3 zadania)

Utwórz wyzwalacz log_usuniecia, który po usunięciu zamówienia doda odpowiedni wpis do tabeli log_usuniec.
Napisz wyzwalacz sprawdz_wiek, który przed dodaniem nowego klienta sprawdzi, czy ma on więcej niż 18 lat. Jeśli nie – operacja ma być anulowana.
Dodaj wyzwalacz aktualizuj_stan_magazynu, który po dodaniu zamówienia automatycznie zmniejszy liczbę dostępnego produktu w tabeli magazyn.

Podsumowanie

Funkcje składowane pozwalają na wykonywanie obliczeń i zwracanie wartości,
Wyzwalacze automatyzują operacje w odpowiedzi na zmiany w tabelach,
Oba mechanizmy usprawniają działanie bazy danych i eliminują powtarzalność kodu.

Dzięki powyższym ćwiczeniom uczniowie nauczą się stosowania funkcji i wyzwalaczy w praktycznych scenariuszach.