Język SQL: procedury składowane

article-thumbnail

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`;