Zajęcia -Tomasz Sadowski

Lekcja: Procedury Składowane w MySQL (Rozszerzona)

1. Wstęp

Procedury składowane (Stored Procedures) w MySQL pozwalają na przechowywanie i wykonywanie złożonych operacji SQL w bazie danych. Mogą zawierać logikę warunkową, pętle i instrukcje sterujące przepływem. Są szczególnie przydatne do automatyzacji powtarzalnych operacji.

2. Tworzenie Procedury Składowanej

Procedura składowana jest tworzona za pomocą CREATE PROCEDURE. Podstawowa składnia:

DELIMITER //

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

DELIMITER ;

DELIMITER // – zmienia domyślny znak końca zapytania (;) na //, aby poprawnie obsłużyć kod wewnątrz procedury.
CREATE PROCEDURE – tworzy nową procedurę.
BEGIN ... END – blok kodu procedury.

3. Rodzaje parametrów w procedurach

IN – wartość przekazywana do procedury, ale nie może być modyfikowana.
OUT – procedura zwraca wartość poprzez ten parametr.
INOUT – wartość jest przekazywana, może zostać zmieniona i zwrócona.

3. Rodzaje parametrów w procedurach składowanych

Procedury składowane mogą przyjmować trzy typy parametrów:

IN – wartość jest przekazywana do procedury, ale nie może zostać zmodyfikowana.
OUT – procedura może zwrócić wartość poprzez ten parametr.
INOUT – wartość jest przekazywana do procedury, może być zmieniona i zwrócona.

Przykład procedury z parametrem IN

Procedura zwracająca liczbę zamówień dla danego klienta:

DELIMITER //

CREATE PROCEDURE liczba_zamowien(IN klient_id INT, OUT liczba INT)
BEGIN
    SELECT COUNT(*) INTO liczba FROM zamowienia WHERE zamowienia.klient_id = klient_id;
END //

DELIMITER ;

Wywołanie procedury:

CALL liczba_zamowien(1, @liczba);
SELECT @liczba;

4. Przykład: Procedura z warunkiem IF NOT EXISTS

Przykład 1: Dodanie klienta tylko jeśli nie istnieje

DELIMITER //

CREATE PROCEDURE dodaj_klienta(IN imie VARCHAR(50), IN nazwisko VARCHAR(50), IN wiek INT)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM klienci WHERE klienci.imie = imie AND klienci.nazwisko = nazwisko) THEN
        INSERT INTO klienci (imie, nazwisko, wiek) VALUES (imie, nazwisko, wiek);
    END IF;
END //

DELIMITER ;

Wywołanie procedury:

CALL dodaj_klienta('Tomasz', 'Lis', 45);

Działanie:

Jeśli klient o podanym imieniu i nazwisku nie istnieje w tabeli klienci, to zostanie dodany.
Jeśli istnieje, procedura nie doda nowego wpisu.

5. Instrukcje sterujące w procedurach składowanych

Procedury mogą wykorzystywać struktury sterujące:

IF ... THEN ... END IF – warunki
CASE ... WHEN ... THEN ... END CASE – wybór warunków
LOOP, WHILE, REPEAT – pętle

Przykład 2: Procedura przypisująca rabat do klienta

DELIMITER //

CREATE PROCEDURE ustaw_rabat(IN klient_id INT)
BEGIN
    DECLARE wiek_klienta INT;
    
    -- Pobranie wieku klienta
    SELECT wiek INTO wiek_klienta FROM klienci WHERE id = klient_id;
    
    -- Ustawienie rabatu
    IF wiek_klienta > 60 THEN
        UPDATE klienci SET rabat = 10 WHERE id = klient_id;
    ELSE
        UPDATE klienci SET rabat = 0 WHERE id = klient_id;
    END IF;
END //

DELIMITER ;

Ćwiczenia do wykonania

Ćwiczenie 1: Procedura dodająca nowego klienta

Zadanie:
Napisz procedurę dodaj_klienta, która przyjmuje imie, nazwisko i wiek, a następnie dodaje nowego klienta do tabeli klienci, ale tylko jeśli taki klient jeszcze nie istnieje.

Podpowiedź:
Użyj IF NOT EXISTS, aby sprawdzić, czy dany klient jest już w bazie.

Ćwiczenie 2: Procedura dodająca nowe zamówienie dla klienta

Zadanie:
Napisz procedurę dodaj_zamowienie, która przyjmuje klient_id, kwota oraz data_zlozenia, a następnie dodaje nowe zamówienie do tabeli zamowienia, ale tylko jeśli dany klient istnieje w tabeli klienci.

Podpowiedź:
Użyj IF EXISTS w zapytaniu SQL.

Ćwiczenie 3: Procedura sprawdzająca ilość produktu w magazynie

Zadanie:
Stwórz procedurę sprawdz_magazyn, która przyjmuje nazwa_produktu i zwraca ilość dostępnych sztuk w magazynie.

Podpowiedź:
Użyj SELECT ilosc INTO zmienna FROM magazyn WHERE nazwa = ...

To podsumowanie procedur składowanych w MySQL. Wykonaj ćwiczenia, aby lepiej zrozumieć ich działanie! 🚀