Tworzenie skryptów w SQL (batch, procedury, funkcje)

Łączenie poleceń, automatyczne budowanie bazy, procedury składowane — INF.03

Teoria Skrypty batch, procedury, funkcje w SQL

  • Skrypt SQL (batch) to plik, który łączy wiele poleceń: tworzenie bazy, tabel, wstawianie danych, nadawanie uprawnień.
  • Procedury składowane — blok poleceń wykonywany na serwerze, wywoływany przez CALL, mogą mieć parametry.
  • Funkcje składowane — przyjmują argumenty i zwracają wartość (np. obliczenia, formatowanie tekstu).
  • DELIMITER w MySQL pozwala zdefiniować procedury/funkcje mimo użycia średników w ich wnętrzu.

Skrypty, procedury i funkcje automatyzują powtarzalne zadania administratora i programisty baz danych.

Ćwiczenie 1 Batch: budowa bazy w jednym skrypcie

Zadanie: Napisz skrypt, który tworzy bazę szkolna, tabelę uczniowie, wstawia 2 rekordy i nadaje uprawnienia do odczytu użytkownikowi praktykant.
  • Utwórz bazę danych i ją wybierz (CREATE DATABASE, USE).
  • Stwórz tabelę z kluczem głównym i ograniczeniem CHECK.
  • Wstaw przykładowe dane oraz nadaj uprawnienia GRANT SELECT.
CREATE DATABASE szkolna;
USE szkolna;

CREATE TABLE uczniowie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30) NOT NULL,
  nazwisko VARCHAR(50) NOT NULL,
  rocznik INT CHECK (rocznik >= 2000)
);

INSERT INTO uczniowie (imie, nazwisko, rocznik) VALUES
('Klaudia', 'Balcerek', 2008),
('Adam', 'Borek', 2009);

CREATE USER 'praktykant'@'localhost' IDENTIFIED BY 'Szkolne123!';
GRANT SELECT ON szkolna.* TO 'praktykant'@'localhost';
FLUSH PRIVILEGES;

Ćwiczenie 2 Procedura składowana — automatyczna promocja

Zadanie: Stwórz procedurę, która podnosi rocznik wszystkich uczniów o 1 i dopisuje wpis do tabeli logów.
  • Utwórz (jeśli trzeba) tabelę logi.
  • Zdefiniuj procedurę podnoszącą rocznik.
  • Po aktualizacji dodaj wpis do logów.
CREATE TABLE IF NOT EXISTS logi (
  id INT PRIMARY KEY AUTO_INCREMENT,
  opis VARCHAR(100),
  data_zdarzenia DATETIME DEFAULT NOW()
);

DELIMITER $$

CREATE PROCEDURE PromoteRocznik()
BEGIN
  UPDATE uczniowie
  SET rocznik = rocznik + 1;

  INSERT INTO logi (opis)
  VALUES ('Podniesiono rocznik wszystkim uczniom');
END $$

DELIMITER ;

-- Wywołanie:
CALL PromoteRocznik();

Ćwiczenie 3 Funkcja składowana — wyliczanie wieku

Zadanie: Utwórz funkcję, która wylicza wiek ucznia na podstawie rocznika, i użyj jej w zapytaniu SELECT.
  • Zdefiniuj funkcję z parametrem rok.
  • Niech zwraca różnicę między bieżącym rokiem a rocznikiem.
  • W SELECT pokaż imię, nazwisko i wyliczony wiek.
DELIMITER $$

CREATE FUNCTION WiekUcznia(rok INT) RETURNS INT
BEGIN
  RETURN YEAR(CURDATE()) - rok;
END $$

DELIMITER ;

SELECT imie,
       nazwisko,
       WiekUcznia(rocznik) AS wiek
FROM uczniowie;

Ćwiczenie 4 Batch: tabela logów + procedura

Zadanie: Skrypt tworzy tabelę logi i procedurę DodajLog, która dopisuje wpis o dowolnej treści.
  • Stwórz tabelę z autoinkrementacją i domyślną datą.
  • Zdefiniuj procedurę przyjmującą parametr opis_txt.
  • Wywołaj procedurę testowo kilka razy.
CREATE TABLE IF NOT EXISTS logi (
  id INT PRIMARY KEY AUTO_INCREMENT,
  opis VARCHAR(100),
  data_zdarzenia DATETIME DEFAULT NOW()
);

DELIMITER $$
CREATE PROCEDURE DodajLog(IN opis_txt VARCHAR(100))
BEGIN
  INSERT INTO logi (opis) VALUES (opis_txt);
END $$
DELIMITER ;

CALL DodajLog('Utworzenie nowego konta');
CALL DodajLog('Zmiana hasła użytkownika');

Ćwiczenie 5 Funkcja: pełna nazwa ucznia

Zadanie: Dodaj funkcję, która zwraca pełne imię i nazwisko ucznia jako jeden tekst, i użyj jej w SELECT.
  • Napisz funkcję z dwoma parametrami: imię i nazwisko.
  • Zwróć złączony tekst z odstępem.
  • Wyświetl listę uczniów w postaci „Imię Nazwisko”.
DELIMITER $$

CREATE FUNCTION PelnyTekst(imie_txt VARCHAR(30),
                           nazwisko_txt VARCHAR(50))
RETURNS VARCHAR(100)
BEGIN
  RETURN CONCAT(imie_txt, ' ', nazwisko_txt);
END $$

DELIMITER ;

SELECT PelnyTekst(imie, nazwisko) AS uczen
FROM uczniowie;

Zadanie INF.03 Skrypt „System rekrutacji”

Opis: Przygotuj skrypt SQL, który:
  • tworzy bazę rekrutacja,
  • tworzy tabele: kandydaci, kierunki, podania,
  • definiuje procedurę dodającą nowe podanie,
  • definiuje funkcję zwracającą pełny opis podania.
  1. Stwórz strukturę tabel z kluczami głównymi i obcymi.
  2. Dodaj kilku przykładowych kandydatów i kierunki.
  3. Utwórz procedurę DodajPodanie(kandydat_id, kierunek_id).
  4. Utwórz funkcję OpisPodania(id_podania), która zwraca tekst „Imię Nazwisko — Kierunek”.
CREATE DATABASE rekrutacja;
USE rekrutacja;

CREATE TABLE kandydaci (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30) NOT NULL,
  nazwisko VARCHAR(50) NOT NULL
);

CREATE TABLE kierunki (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(100) NOT NULL
);

CREATE TABLE podania (
  id INT PRIMARY KEY AUTO_INCREMENT,
  kandydat_id INT NOT NULL,
  kierunek_id INT NOT NULL,
  data_zlozenia DATE DEFAULT (CURRENT_DATE),
  FOREIGN KEY (kandydat_id) REFERENCES kandydaci(id),
  FOREIGN KEY (kierunek_id) REFERENCES kierunki(id)
);

INSERT INTO kandydaci (imie, nazwisko) VALUES
('Jan', 'Nowak'),
('Anna', 'Kowalska');

INSERT INTO kierunki (nazwa) VALUES
('Informatyka'),
('Teleinformatyka');

DELIMITER $$

CREATE PROCEDURE DodajPodanie(
  IN p_kandydat_id INT,
  IN p_kierunek_id INT
)
BEGIN
  INSERT INTO podania (kandydat_id, kierunek_id)
  VALUES (p_kandydat_id, p_kierunek_id);
END $$

CREATE FUNCTION OpisPodania(p_id INT) RETURNS VARCHAR(200)
BEGIN
  DECLARE wynik VARCHAR(200);

  SELECT CONCAT(k.imie, ' ', k.nazwisko, ' — ', ki.nazwa)
  INTO wynik
  FROM podania p
  JOIN kandydaci k ON p.kandydat_id = k.id
  JOIN kierunki ki ON p.kierunek_id = ki.id
  WHERE p.id = p_id;

  RETURN wynik;
END $$

DELIMITER ;

-- Przykładowe użycie:
CALL DodajPodanie(1, 1);
SELECT OpisPodania(1) AS opis;

Quiz Sprawdź wiedzę!

  1. Co to jest skrypt batch w SQL i kiedy go używamy?
  2. Jak zdefiniować procedurę składowaną w MySQL?
  3. Czym różni się procedura od funkcji składowanej?
  4. Po co zmienia się DELIMITER przy tworzeniu procedur/funkcji?
  5. Jak wywołać procedurę oraz jak użyć funkcji w zapytaniu SELECT?
  • To plik z wieloma poleceniami SQL wykonywanymi „hurtem”, np. do utworzenia całej bazy z danymi.
  • Przez CREATE PROCEDURE ... BEGIN ... END z użyciem zmienionego delimitera, np. DELIMITER $$.
  • Procedura nie musi zwracać wartości i wywołuje się ją CALL, funkcja zawsze zwraca wartość i można jej użyć w SELECT.
  • Aby całe ciało procedury/funkcji zostało wysłane do serwera jako jedno polecenie mimo średników w środku.
  • Procedurę: CALL NazwaProcedury(...);, funkcję: np. SELECT Funkcja(...); lub w liście kolumn.

Checklista Sprawdź umiejętności

  • Tworzę skrypty batch do budowy i inicjalizacji bazy danych.
  • Definiuję procedury składowane z parametrami i potrafię je wywołać.
  • Tworzę funkcje składowane i wykorzystuję je w zapytaniach SELECT.
  • Potrafię zamienić opis słowny zadania na strukturę bazy i skrypty SQL.
Materiały: INF.03 — Skrypty, procedury, funkcje SQL · Autor: Tomasz Puchała © 2025