Wstęp

Wyzwalacze, funkcje i procedury składowane to potężne narzędzia w MySQL, które pozwalają na automatyzację zadań, zwiększenie wydajności i utrzymanie integralności danych. W tej lekcji podsumujemy kluczowe koncepcje i zastosowania tych elementów.

1. Wyzwalacze (Triggers)

Oczywiście, rozbudujmy każdą sekcję o dodatkowe przykłady, które pomogą zrozumieć koncepcje, ale nie będą bezpośrednio kopiowalne do rozwiązania zadań.

1. Wyzwalacze (Triggers)

• Definicja:
  • Wyzwalacze to automatyczne reakcje bazy danych na zmiany w danych. Wyobraź sobie, że chcesz śledzić, kto i kiedy modyfikował dane w tabeli. Wyzwalacz może automatycznie zapisywać te informacje w oddzielnej tabeli logów.
  • Przykład: W systemie e-commerce, po każdej zmianie statusu zamówienia, wyzwalacz może wysyłać powiadomienie e-mail do klienta.
• Typy wyzwalaczy:
  • BEFORE:
    • Użyj, gdy chcesz sprawdzić lub zmodyfikować dane przed ich zapisaniem. Na przykład, możesz chcieć upewnić się, że wprowadzana cena produktu nie jest ujemna.
    • Przykład: Wyzwalacz, który automatycznie konwertuje wszystkie wprowadzane nazwy produktów na wielkie litery.
  • AFTER:
    • Użyj, gdy chcesz wykonać operacje po zapisaniu danych. Na przykład, po dodaniu nowego klienta, możesz chcieć automatycznie utworzyć dla niego konto w systemie lojalnościowym.
    • Przykład: Po usunięciu rekordu z tabeli zamówień, wyzwalacz aktualizuje tabele statystyk sprzedażowych.
• Zastosowania:
  • Logowanie zmian: Rejestruj, kto, co i kiedy zmienił w bazie danych.
  • Sprawdzanie poprawności: Upewnij się, że dane spełniają określone kryteria.
  • Automatyczne aktualizowanie: Aktualizuj powiązane tabele, gdy dane w jednej tabeli się zmieniają.
  • Przykład: Wyzwalacz który sprawdza czy wprowadzany numer telefonu ma poprawny format.
• Zmienne:
  • NEW:
    • Użyj, aby uzyskać dostęp do nowych wartości wstawianych lub aktualizowanych rekordów.
    • Przykład: NEW.cena w wyzwalaczu BEFORE UPDATE pozwala sprawdzić, czy nowa cena produktu jest akceptowalna.
  • OLD:
    • Użyj, aby uzyskać dostęp do starych wartości rekordów aktualizowanych lub usuwanych.
    • Przykład: OLD.status w wyzwalaczu AFTER UPDATE pozwala sprawdzić, jaki był poprzedni status zamówienia.

Zadania - Wyzwalacze

1. Zadanie 1: Utwórz wyzwalacz AFTER UPDATE na tabeli produkty, który po każdej zmianie ceny produktu zapisze informację o starej i nowej cenie w tabeli historia_cen.
2. Zadanie 2: Stwórz wyzwalacz BEFORE DELETE na tabeli zamowienia, który przed usunięciem zamówienia sprawdzi, czy zamówienie ma status "Zrealizowane", a jeśli nie, uniemożliwi usunięcie.
3. Zadanie 3: Napisz wyzwalacz AFTER INSERT na tabeli klienci, który automatycznie wygeneruje unikalny kod klienta i zapisze go w kolumnie kod_klienta.

2. Funkcje Składowane

• Definicja:
  • Funkcje składowane to bloki kodu, które wykonują obliczenia i zwracają pojedynczą wartość.
  • Przykład: Funkcja, która oblicza wiek klienta na podstawie jego daty urodzenia.
• Cechy:
  • Muszą zwracać wartość: RETURNS typ_danych.
  • Mogą być używane w zapytaniach SQL: SELECT funkcja(parametry);.
  • Zazwyczaj nie modyfikują danych: DETERMINISTIC.
  • Przykład: Funkcja która oblicza podatek od produktu na podstawie jego ceny.
• Zastosowania:
  • Obliczanie wartości: Obliczaj złożone wartości na podstawie danych w tabelach.
  • Formatowanie danych: Konwertuj dane na określony format.
  • Sprawdzanie poprawności: Sprawdzaj, czy dane spełniają określone kryteria.
  • Przykład: Funkcja która zwraca tekstowy opis poziomu zapasów produktu (niski, średni, wysoki) na podstawie liczby sztuk w magazynie.

Zadania - Funkcje Składowane

1. Zadanie 1: Utwórz funkcję liczba_produktow_klienta, która przyjmuje klient_id i zwraca liczbę produktów zakupionych przez tego klienta.
2. Zadanie 2: Napisz funkcję formatuj_date, która przyjmuje datę i zwraca ją w formacie "DD-MM-RRRR".
3. Zadanie 3: Stwórz funkcję czy_klient_vip, która przyjmuje klient_id i zwraca TRUE, jeśli klient ma więcej niż 10 zamówień, w przeciwnym razie FALSE.

3. Procedury Składowane

• Definicja:
  • Procedury składowane to bloki kodu, które wykonują sekwencję instrukcji SQL.
  • Przykład: Procedura, która dodaje nowego klienta, tworzy dla niego konto i wysyła powitalny e-mail.
• Cechy:
  • Mogą przyjmować parametry: IN, OUT, INOUT.
  • Mogą modyfikować dane: INSERT, UPDATE, DELETE.
  • Nie muszą zwracać wartości: CALL procedura(parametry);.
  • Przykład: Procedura która generuje raport sprzedażowy dla danego okresu czasu.
• Zastosowania:
  • Automatyzacja: Wykonuj złożone operacje za pomocą jednego wywołania.
  • Transakcje: Zapewnij spójność danych, grupując operacje w transakcje.
  • Raportowanie: Generuj niestandardowe raporty.
  • Przykład: Procedura która przenosi dane archiwalne do oddzielnej bazy danych.
• Elementy:
  • Instrukcje warunkowe: IF, CASE.
    • Przykład: Użyj IF, aby sprawdzić, czy klient ma wystarczająco środków na koncie, zanim wykonasz transakcję.
    • Przykład: Użyj CASE, aby przypisać klientowi status na podstawie liczby zakupów.
  • Pętle: WHILE, LOOP.
    • Przykład: Użyj WHILE, aby przetworzyć wszystkie zamówienia w danym okresie.
    • Przykład: Użyj LOOP, aby wykonać operację określoną liczbę razy.

Zadania - Procedury Składowane

1. Zadanie 1: Utwórz procedurę dodaj_zamowienie, która przyjmuje klient_id, kwota i status, a następnie dodaje nowe zamówienie do tabeli zamowienia.
2. Zadanie 2: Napisz procedurę aktualizuj_statusy_zamowien, która aktualizuje statusy wszystkich zamówień o wartości powyżej 500 na "Wysłane".
3. Zadanie 3: Stwórz procedurę generuj_raport_klienta, która przyjmuje klient_id i wyświetla listę wszystkich zamówień klienta wraz z ich kwotami i statusami.

Kod Testowej Bazy Danych

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

CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(50),
    cena DECIMAL(10, 2)
);

CREATE TABLE zamowienia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    klient_id INT,
    produkt_id int,
    kwota DECIMAL(10, 2),
    status VARCHAR(20),
    data_zamowienia datetime
);

CREATE TABLE historia_cen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    produkt_id INT,
    stara_cena DECIMAL(10, 2),
    nowa_cena DECIMAL(10, 2),
    data_zmiany DATETIME
);

INSERT INTO klienci (imie, nazwisko) VALUES
('Jan', 'Kowalski'),
('Anna', 'Nowak'),
('Piotr', 'Wisniewski');

INSERT INTO produkty (nazwa, cena) VALUES
('Laptop', 1200.00),
('Mysz', 25.00),
('Klawiatura', 50.00);

INSERT INTO zamowienia (klient_id, produkt_id, kwota, status, data_zamowienia) VALUES
(1, 1, 1200.00, 'Nowe', '2023-01-01 10:00:00'),
(1, 2, 25.00, 'Zrealizowane', '2023-01-02 12:00:00'),
(2, 3, 50.00, 'Wyslane', '2023-01-03 14:00:00'),
(3, 1, 1200.00, 'Nowe', '2023-01-03 15:00:00'),
(3, 2, 50.00, 'Nowe', '2023-01-05 15:00:00'),
(3, 3, 25.00, 'Zrealizowane', '2023-01-06 15:00:00'),
(3, 1, 1200.00, 'Zrealizowane', '2023-01-07 15:00:00'),
(3, 2, 50.00, 'Wyslane', '2023-01-08 15:00:00'),
(3, 3, 25.00, 'Wyslane', '2023-01-09 15:00:00'),
(3, 1, 1200.00, 'Nowe', '2023-01-10 15:00:00'),
(3, 2, 50.00, 'Nowe', '2023-01-11 15:00:00');

1. Wyzwalacze (Triggers)

• Przykład 1: Logowanie zmian w tabeli

DELIMITER //
CREATE TRIGGER log_zmiany_ceny
AFTER UPDATE ON produkty
FOR EACH ROW
BEGIN
    IF NEW.cena <> OLD.cena THEN
        INSERT INTO historia_cen (produkt_id, stara_cena, nowa_cena, data_zmiany)
        VALUES (OLD.id, OLD.cena, NEW.cena, NOW());
    END IF;
END //
DELIMITER ;

Ten przykład pokazuje, jak utworzyć wyzwalacz, który rejestruje zmiany cen produktów w oddzielnej tabeli. U

• Przykład 2: Sprawdzanie poprawności danych

DELIMITER //
CREATE TRIGGER sprawdz_ilosc_magazyn
BEFORE INSERT ON zamowienia
FOR EACH ROW
BEGIN
    DECLARE dostepna_ilosc INT;
    SELECT ilosc INTO dostepna_ilosc FROM magazyn WHERE produkt_id = NEW.produkt_id;
    IF dostepna_ilosc < NEW.ilosc THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Niewystarczająca ilość produktu w magazynie.';
    END IF;
END //
DELIMITER ;

Ten przykład pokazuje, jak użyć wyzwalacza BEFORE INSERT, aby sprawdzić, czy w magazynie jest wystarczająca ilość zamawianego produktu.

• Przykład 3: Automatyczne aktualizowanie tabeli powiązanej

DELIMITER //
CREATE TRIGGER aktualizuj_statystyki_klienta
AFTER INSERT ON zamowienia
FOR EACH ROW
BEGIN
    UPDATE statystyki_klientow
    SET liczba_zamowien = liczba_zamowien + 1
    WHERE klient_id = NEW.klient_id;
END //
DELIMITER ;

Ten przykład pokazuje, jak zaktualizować statystyki klienta po dodaniu nowego zamówienia.

2. Funkcje Składowane

• Przykład 1: Obliczanie wartości

DELIMITER //
CREATE FUNCTION oblicz_rabat(kwota DECIMAL(10, 2)) RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE rabat DECIMAL(10, 2);
    IF kwota > 1000 THEN
        SET rabat = kwota * 0.1;
    ELSE
        SET rabat = 0;
    END IF;
    RETURN rabat;
END //
DELIMITER ;

Ten przykład pokazuje, jak utworzyć funkcję, która oblicza rabat na podstawie kwoty zamówienia.

• Przykład 2: Formatowanie danych

DELIMITER //
CREATE FUNCTION formatuj_numer_telefonu(numer VARCHAR(20)) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE sformatowany_numer VARCHAR(20);
    SET sformatowany_numer = CONCAT('(+48) ', SUBSTRING(numer, 1, 3), '-', SUBSTRING(numer, 4, 3), '-', SUBSTRING(numer, 7, 3));
    RETURN sformatowany_numer;
END //
DELIMITER ;

Ten przykład pokazuje, jak sformatować numer telefonu.

• Przykład 3: Sprawdzanie poprawności danych

DELIMITER //
CREATE FUNCTION czy_email_poprawny(email VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE wynik BOOLEAN;
    IF email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$' THEN
        SET wynik = TRUE;
    ELSE
        SET wynik = FALSE;
    END IF;
    RETURN wynik;
END //
DELIMITER ;

Ten przykład pokazuje, jak sprawdzić, czy adres e-mail jest poprawny.

3. Procedury Składowane

• Przykład 1: Automatyzacja operacji

DELIMITER //
CREATE PROCEDURE dodaj_klienta_i_zamowienie(IN imie VARCHAR(50), IN nazwisko VARCHAR(50), IN produkt_id INT, IN ilosc INT)
BEGIN
    DECLARE klient_id INT;
    INSERT INTO klienci (imie, nazwisko) VALUES (imie, nazwisko);
    SET klient_id = LAST_INSERT_ID();
    INSERT INTO zamowienia (klient_id, produkt_id, ilosc) VALUES (klient_id, produkt_id, ilosc);
END //
DELIMITER ;

Ten przykład pokazuje, jak utworzyć procedurę, która dodaje nowego klienta i tworzy dla niego zamówienie.

• Przykład 2: Transakcje

DELIMITER //
CREATE PROCEDURE przenies_srodki(IN z_konta INT, IN na_konto INT, IN kwota DECIMAL(10, 2))
BEGIN
    DECLARE dostepne_srodki DECIMAL(10, 2);
    START TRANSACTION;
    SELECT srodki INTO dostepne_srodki FROM konta WHERE id = z_konta;
    IF dostepne_srodki >= kwota THEN
        UPDATE konta SET srodki = srodki - kwota WHERE id = z_konta;
        UPDATE konta SET srodki = srodki + kwota WHERE id = na_konto;
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END //
DELIMITER ;

Ten przykład pokazuje, jak użyć transakcji, aby zapewnić spójność danych podczas przenoszenia środków między kontami.

• Przykład 3: Generowanie raportów

DELIMITER //
CREATE PROCEDURE raport_sprzedazy_produktu(IN produkt_id INT)
BEGIN
    SELECT k.imie, k.nazwisko, z.data_zamowienia, z.ilosc
    FROM klienci k
    JOIN zamowienia z ON k.id = z.klient_id
    WHERE z.produkt_id = produkt_id;
END //
DELIMITER ;

Ten przykład pokazuje, jak utworzyć procedurę, która generuje raport sprzedaży dla danego produktu.