Lekcja 27 – Skrypty SQL i automatyzacja

📄

1. Skrypty SQL

Skrypt SQL to plik tekstowy (rozszerzenie .sql) zawierający sekwencję poleceń SQL. Pozwala na automatyzację tworzenia baz, tabel i wstawiania danych.

Prawidłowa kolejność w skrypcie tworzącym bazę:

-- 1. Tworzenie bazy danych
CREATE DATABASE IF NOT EXISTS sklep
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sklep;

-- 2. Tabele bez kluczy obcych (tabele nadrzędne)
CREATE TABLE kategorie ( ... );
CREATE TABLE klienci ( ... );

-- 3. Tabele z kluczami obcymi (tabele podrzędne)
CREATE TABLE produkty (
  id_produktu INT AUTO_INCREMENT PRIMARY KEY,
  id_kategorii INT,
  FOREIGN KEY (id_kategorii) REFERENCES kategorie(id_kategorii)
);

-- 4. Dane testowe (INSERT)
INSERT INTO kategorie ... ;
INSERT INTO produkty ... ;

🔄

2. Transakcje

-- Podstawowa transakcja
START TRANSACTION;  -- lub: BEGIN;

UPDATE konta SET saldo = saldo - 1000 WHERE id = 1;
UPDATE konta SET saldo = saldo + 1000 WHERE id = 2;

COMMIT;  -- zatwierdź zmiany
-- lub: ROLLBACK; -- cofnij wszystkie zmiany od START TRANSACTION

-- SAVEPOINT — punkt pośredni
START TRANSACTION;
INSERT INTO zamowienia ... ;
SAVEPOINT po_zamowieniu;

INSERT INTO pozycje_zamowien ... ;

ROLLBACK TO SAVEPOINT po_zamowieniu;  -- cofa tylko do SAVEPOINT
COMMIT;  -- zatwierdź resztę

Transakcje gwarantują właściwości ACID: Atomicity (wszystko albo nic), Consistency (spójność), Isolation (izolacja), Durability (trwałość). Bez transakcji operacje są auto-commitowane.

⚙️

3. Procedury składowane (Stored Procedures)

-- Tworzenie procedury składowanej
DELIMITER //
CREATE PROCEDURE dodaj_klienta(
  IN p_imie    VARCHAR(50),
  IN p_email   VARCHAR(120)
)
BEGIN
  INSERT INTO klienci (imie, email) VALUES (p_imie, p_email);
  SELECT LAST_INSERT_ID() AS nowe_id;
END //
DELIMITER ;

-- Wywołanie procedury
CALL dodaj_klienta('Anna', 'anna@mail.com');

-- Procedura z OUT — zwraca wartość
DELIMITER //
CREATE PROCEDURE srednia_cena_kat(
  IN  p_kat    VARCHAR(100),
  OUT p_wynik  DECIMAL(10,2)
)
BEGIN
  SELECT AVG(cena) INTO p_wynik
  FROM produkty WHERE kategoria = p_kat;
END //
DELIMITER ;

CALL srednia_cena_kat('Elektronika', @wynik);
SELECT @wynik;

⚡

4. Wyzwalacze (TRIGGERS)

-- Trigger: automatycznie ustaw datę modyfikacji
DELIMITER //
CREATE TRIGGER przed_update_produktu
BEFORE UPDATE ON produkty
FOR EACH ROW
BEGIN
  SET NEW.zmodyfikowano = NOW();
END //
DELIMITER ;

-- Trigger: loguj usunięcia do tabeli archiwum
DELIMITER //
CREATE TRIGGER po_delete_klienta
AFTER DELETE ON klienci
FOR EACH ROW
BEGIN
  INSERT INTO archiwum_klientow (id, imie, email, usuniety_kiedy)
  VALUES (OLD.id_klienta, OLD.imie, OLD.email, NOW());
END //
DELIMITER ;

W triggerach: NEW — nowe wartości (INSERT, UPDATE); OLD — stare wartości (UPDATE, DELETE). Typy: BEFORE/AFTER × INSERT/UPDATE/DELETE = 6 kombinacji.

✏️

Zadania interaktywne

Zadanie 1Quiz: ROLLBACK cofa zmiany do...

Po wykonaniu START TRANSACTION, kilku operacjach DML i SAVEPOINT pkt1, kolejnych operacjach DML — do czego cofnie ROLLBACK TO SAVEPOINT pkt1?

Do początku sesji (wszystkie zmiany od zalogowania)
Do momentu SAVEPOINT pkt1 (cofa tylko zmiany po tym punkcie)
Do ostatniego COMMIT (cofa całą transakcję)
Nie cofa niczego — ROLLBACK TO SAVEPOINT tylko zatwierdza

Zadanie 2Uzupełnij brakującą część procedury

Uzupełnij brakujące słowo kluczowe w tej procedurze składowanej:

DELIMITER //
CREATE PROCEDURE usun_stare_logi(IN p_dni INT)
___
  DELETE FROM logi
  WHERE data_log < DATE_SUB(NOW(), INTERVAL p_dni DAY);
END //
DELIMITER ;

START
BEGIN
EXECUTE
OPEN

Zadanie 3Popraw błędny DELIMITER

W poniższym kodzie DELIMITER jest użyty niepoprawnie. Wskaż błąd:

DELIMITER //
CREATE PROCEDURE test()
BEGIN
  SELECT 1;
END;   -- ← tutaj jest błąd!
DELIMITER ;

Brakuje // po END — powinno być END // a nie END;
DELIMITER // powinno być na końcu, nie na początku
CREATE PROCEDURE nie może mieć BEGIN...END
DELIMITER powinien być złożony z liter, nie symboli

Skrypty SQL — transakcje, procedury, wyzwalacze

🎯 Cele lekcji