Język SQL: procedury składowane

Procedura składowana to element języka SQL, pozwalający na wielokrotne wykorzystywanie tych samych kwerend, jednak ze zmienionymi argumentami. Dzięki temu możemy uzyskać lepszą spójność danych, oraz przyjemniej wykonywać powtarzalne działania.

W celu prezentacji będę pracował na bazie danych z egzaminu maturalnego z 2016r. Biblioteka Podręczników. Składa się ona z trzech tabel:

studenci

meldunek

wypozyczenia_p

Procedura bez argumentów

Najprostszą odmianą procedury składowanej jest ta nie korzystająca z argumentów. Z racji tego, że nie możemy przekazać jej żadnych danych, nie mamy możliwości sterowania nią. Niemniej możemy ją wykorzystać, do automatyzacji powtarzalnych lub monotonnych czynności. Załóżmy że otrzymamy takie polecenie: Napiszmy procedurę która sprawdzi ile kobiet, a ile mężczyzn wypożycza książki z biblioteki.

CREATE PROCEDURE `Zlicz_plcie`() 
    SELECT 'Kobiety' AS `plec`, COUNT(`pesel`) AS `ilosc` FROM `wypozyczenia_p`
	 WHERE RIGHT(`pesel`, 2) % 2 = 0 
    UNION ALL 
    SELECT 'Mężczyźni' AS `plec`, COUNT(`pesel`) AS `ilosc` FROM `wypozyczenia_p` 
	 WHERE RIGHT(`pesel`, 2) % 2 = 1; 

Teraz możemy taką procedurę wywołać:

CALL `Zlicz_plcie`();

Procedura z argumentami wejścia

Procedury składowane pozwalają nam na przesyłanie do nich argumentów. W związku z tym ilość możliwości ich zastosowania jest ogromna. W programowaniu stosuje się pewne wzorce, ułatwiające rozwiązanie problemu (wzorce projektowe), jednym z nich jest tzw. factory. Fabryka to podejście, w którym zamiast tworzyć obiekt własnoręcznie, tworzymy funkcję która zrobi to za nas na podstawie argumentów. Zróbmy coś podobnego w SQL’u. Napiszę kwerendę zamelduj, która przyjmie dwa argument – id pokoju i pesel.

DELIMITER //
CREATE PROCEDURE zamelduj(IN pesel int ,IN id_pok int)
BEGIN
    INSERT INTO meldunek (`id_pok`, `pesel`) VALUES (id_pok, pesel);
END //
DELIMITER ;

Teraz pora na krótkie wyjaśnienie. Polecenie DELIMITER zmienia symbol końca polecenia. Używamy go, aby uniknąć konfliktu przy słowie kluczowym END. Gdy piszemy procedurę z argumentami wejścia, musimy zaznaczyć, że będą one podane. W tym celu w nawiasie obok nazwy procedury umieszczamy najpierw słowo IN, następnie nazwę argumentu i na końcu jego typ.

Procedura z argumentami wyjścia

Możemy również dodać do procedury argumenty wyjścia. Może to nam np. ułatwić diagnozowanie błędów.

DELIMITER //
CREATE PROCEDURE zamelduj_z_informacja(IN pesel int ,IN id_pok int,OUT rezultat varchar(32))
BEGIN
    INSERT INTO meldunek (`id_pok`, `pesel`) VALUES (id_pok, pesel);
    SET rezultat = "Wykonano";
END //
DELIMITER ;

Wywołanie wygląda tak:

CALL zamelduj_z_informacja(12345678901,32,@output);
SELECT @output as `wyjscie`;