Krótko o funkcjach i procedurach w MySQL

Mam do wykonania parę zadań. Jedno z nich to napisanie funkcji/procedury w MySQL. Jak dla mnie to jest (a właściwie była) czarna magia. Troszkę czasu na to przeznaczyłem i okazało się, że takie coś wcale nie jest straszne ;-]

Podobnie jak PHP, C++, Java i masa innych języków programowania ma procedury i/lub funkcje. Jak wiadomo mogą one posłużyć do wielu różnych rzeczy. W MySQL można je wykorzystać aby zwiększyć bezpieczeństwo. Zapisana funkcja/procedura może być bez problemu wywołana z poziomu PHP, ponieważ wystarczy wysłać zapytanie do bazy będące wywołaniem funkcji/procedury. W dalszej części postaram się nieco opisać te dwie rzeczy.

Getting Started

Podczas korzystania z funkcji mogą wystąpić pewne problemy.
Jednym z ważniejszych jest znak średnika ';', który jest wpisywany podczas tworzenia funkcji/procedury. Jest prosty sposób na obejście tego. Wystarczy, że w konsoli mysql wpiszemy np "DELIMITER //", co spokojnie pozwoli nam na zapisanie kodu. Natomiast znak średnika zastąpi '//'. Tak więc warto nie zapominać o tym, a na końcu warto wpisać "DELIMITER ;" tak aby średnik miał swoje dawne "znaczenie".

Poza tym warto ustawić zmienna globalną log_bin_trust_function_creators na 1. Pozowli to nam na zapisywanie funkcji. Możemy się do tego celu posłużyć poleceniem SET GLOBAL log_bin_trust_function_creators = 1;, które wpisujemy w konsoli mysql.

Na koniec dodam, że wygodnie jest pisać wszelkie funkcje w osobnym pliku *.sql, a później korzystać z polecenia "source nazwa_pliku" w konsoli mysql. W moim wypadku taki plik wygląda, mniej więcej, tak:

delimiter //

CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
        RETURN 'jakis napis';
END

delimiter ;

Tabela

Szczerze mówiąc nie jestem tego pewien, czy funkcja/procedura musi być przypisana do określonej bazy. Na cele tego kursu zrobimy nową bazę danych, z jedną tabelą. Później nam się przyda ;]

Aby tego dokonać wystarczy, że do konsolki mysql wpiszemy:

CREATE DATABASE tmp;
create table skroty (skrot_id varchar(5),opis varchar(50));
insert into skroty values ('lol', 'lots of laught'), ('imho', 'in my humble opinion'), ('btw', 'by the way');

Funkcje

Ogólna budowa:

Ogólna budowa funkcji jest bardzo prosta. Wygląda mniej więcej tak:

CREATE FUNCTION  ( ) <nazwa> RETURNS  <typ>
BEGIN
        {kod funkcji}
END

Prosty "Hello World"

To zacznijmy od oklepany przykładu. Funkcja będzie (póki co) wypisywała napis "Hello World". Najpierw przedstawię kod, oraz sposób wywołania funkcji, a potem postaram się opisać co i jak.

delimiter //

CREATE FUNCTION HelloWorld () RETURNS VARCHAR(20)
BEGIN
        RETURN 'Hello World';
END

delimiter ;

Można ten kod zapisać do pliku, a potem wczytać poleceniem source, ale o tym pisałem już wcześniej ;-].
Wywołanie naszej funkcji polega na wpisaniu do konsoli mysql SELECT HelloWorld();. Efekt tego będzie taki:

mysql> select helloworld();
+--------------+
| helloworld() |
+--------------+
| Hello World  |
+--------------+
1 row in set (0.00 sec)

Funkcja zwraca już gotową wartość. Polecenie SELECT służy do wypisania wyników dalszych poleceń (gdy wpiszemy np. SELECT NOW() pokaże nam się aktualna data i godzina), także w tym wypadku wyświetli napis, który jest zwracany przez funkcję.

Mała dygresja

Jeżeli kod funkcji zapisujesz do pliku, a potem go ładujesz go, to dopisz na początku coś takiego: "DROP FUNCTION IF EXISTS HelloWorld;". Służy to do kasowania funkcji HelloWorld() jeżeli istnieje. Ponieważ później w pliku jest ponowna deklaracja funkcji, to stara zostanie skasowana i od razy zastąpiona nową.

W innym wypadku, trzeba będzie ręcznie kasować funkcję zanim zadeklarujemy ją na nowo (to samo polecenie co wyżej było wymienione). Jeśli tego nie zrobimy otrzymamy komunikat: "ERROR 1304 (42000): FUNCTION helloworld already exists"

Zmienne

W naszej funkcji możemy deklarować zmienne. Ich deklaracja wygląda tak: "DECLARE nazwa_zmiennej typ_zmiennej";

W naszym przykładzie możemy się posłużyć zmienną (np. jakis_napis), która będzie przechowywała wartość, która później będzie zwracana. Kod po zmianach wygląda tak:

DROP FUNCTION IF EXISTS HelloWorld;

delimiter //

CREATE FUNCTION HelloWorld () RETURNS VARCHAR(20)
BEGIN
        DECLARE jakis_napis VARCHAR(20);
        SET jakis_napis = 'Hello World';
        RETURN jakis_napis;
END

delimiter ;

Oczywiście zmiennych może być więcej. Możemy również takiej zmiennej przypisać wartość domyślną. Aby to uczynić podczas deklaracji należy dopisać "DEFAULT 'jakas wartosc' ". Także można powyższy kod nieco zmodyfikować, tak aby zmiennej od razu była przypisana wartość, która będzie zwracana. W obu przypadkach wynik działania funkcji możemy sprawdzić w ten sam sposób, który został opisany wcześniej.

DROP FUNCTION IF EXISTS HelloWorld;

delimiter //

CREATE FUNCTION HelloWorld () RETURNS VARCHAR(20)
BEGIN
        DECLARE jakis_napis VARCHAR(20) DEFAULT = 'Hello World';
        RETURN jakis_napis;
END

delimiter ;

Parametry

Do funkcji również można wprowadzić parametr. Deklaracja tego parametru jest podczas tworenia funkcji (podobnie robi się podczas tworzenia funkcji w innych językach programowania).

Deklaracja parametru następuje zaraz po podaniu nazwy funkcji (w nawiasach), czyli np: CREATE FUNCTION ( ) RETURNS

Poniższy przykład pokaże stworzenie funkcji, która odczyta zmienna paramter podawaną przy woływaniu funkcji, oraz zwróci jej wartość.

DROP FUNCTION IF EXISTS HelloWorld;

delimiter //

CREATE FUNCTION HelloWorld (parametr VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
        RETURN parametr;
END

delimiter ;

Przykład działania:

mysql> select HelloWorld('zlo');
+-------------------+
| HelloWorld('zlo') |
+-------------------+
| zlo               |
+-------------------+

1 row in set (0.00 sec)

Procedury

Zastosowanie procedur jest nieco większe. Aktualnie funkcje nie mają prawa wykonywać poleceń SQL. Autorzy serwera przyznają, że takie ograniczenia powinny wkrótce zniknąć, ale do tej pory jesteśmy zmuszeni korzystać z procedur, które mogą wykorzystywać/wykonywać polecenia SQL.

Ogólna budowa

Wybaczcie takie dość lakoniczne przedstawienie tego. Niestety sam mam jeszcze pewne problemy z "ogarnięciem" procedur.

CREATE PROCEDURE sp_name ([parameter[,.
[characteristic ...] routine_body

Nowy HelloWorld

No to od razu przedstawię jak wygląda nasza poprzednia funkcja przepisana na procedurę. Warto zauważyć, że nie będzie zwracana żadna wartość. Wywołanie tej procedury również będzie inne. Należy również wspomnieć, że procedura nie musi zwracać wartości, ale może (nawet kilka na raz).

DROP PROCEDURE IF EXISTS HelloWorld;

delimiter //
//
CREATE procedure HelloWorld(out jakis_tekst VARCHAR(30))
BEGIN
  
  set jakis_tekst = 'Hello World';

END
//

delimiter ;

Jak wspominałem wywołanie takiej procedury jest już inne. Ponieważ MySQL nie wie czy wywołana procedura zwróci jakąś wartość . Do procedury można przekazać jakąś wartość, lub też ją "wyciągnąć" (in, out). Także aby zobaczyć wynik działania procedury trzeba w konsoli wpisać coś takiego:

mysql> CALL HelloWorld(@out);
mysql> Select @out;
+-------------+
| @out        |
+-------------+
| Hello World |
+-------------+

1 row in set (0.00 sec)

Select INTO

Możliwe, że źle zrozumiałem tekst z kursu, który przeczytałem także wybaczcie jeśli się pomylę. SELECT INTO służy do tego aby zwrócić wynik zapytania SQL do zmiennej. W tym przykładzie wykorzystamy również tabelę, którą stworzyliśmy w tym celu.

W przykładzie pokażę procedurę, która pobierze jeden rekord z tabeli skroty i zapisze go do zmiennej.

DROP PROCEDURE IF EXISTS pobierz;

delimiter //

CREATE PROCEDURE pobierz (out nazwa VARCHAR(50))
BEGIN
        SELECT opis INTO nazwa FROM skroty LIMIT 1;
END
//

delimiter ;

Oraz oczywiście sposób wywołania i zwracany wynik:

mysql> CALL pobierz (@out);
Query OK, 0 rows affected (0.05 sec)

mysql> select @out;
+----------------+
| @out           |
+----------------+
| lots of laught |
+----------------+

1 row in set (0.01 sec)

Na koniec troszkę zmodyfikujemy tą procedurę. Nowa procedura będzie pobierała jeden parametr (który będzie skrótem), natomiast do drugiej zmiennej zostanie zapisany wynik zapytania SQL.

Poza zmienną out (która będzie zwracać wynik), dopiszemy zmienną in, która będzie potrzebna do wykonania zapytania SQL. Cały kod nowej procedury wygląda tak:

DROP PROCEDURE IF EXISTS pobierz;

delimiter //

CREATE PROCEDURE pobierz (out nazwa VARCHAR(50), in skrot VARCHAR(5))
BEGIN
        SELECT opis INTO nazwa FROM skroty WHERE skrot_id=skrot LIMIT 1;
END
//

delimiter ;

Oczywiście teraz wywołujemy procedurę:

mysql> CALL pobierz(@out, 'imho');
Query OK, 0 rows affected (0.00 sec)

mysql> select @out;
+----------------------+
| @out                 |
+----------------------+
| in my humble opinion |
+----------------------+

1 row in set (0.00 sec)

Rekursywne procedury

Tak to jest możliwe, ale trzeba dodatkowo zmienić zimenną max_sp_recursion_depth. Standardowo jest ona ustawiona na 0, co oznacza, że procedura nie może zagłebić się dalej (czyli na chłopski rozum, nie bedzie mogła ani razu wykonać samej siebie ;-]). Wartość możemy przypisać dowolną, ale jest to pewnego rodzaju ograniczenie, ponieważ nie do końca będziemy wiedzieć ile razy procedura będzie miała się powtórzyć...

Zakończenie

Nie jestem za bardzo utalentowany w pisaniu tego typu kursów, także wybaczcie i poprawiajcie wszelkie błędy. W poznaniu funkcji i procedur bardzo pomógł mi artykuł znaleziony na Revealnet. Gdyby były jakieś wątpliwośc to warto zajrzeć właśnie tam ;-]

P.S. Mam nadzieję, że komuś to się przyda ;-]

Komentarze

Disclaimer

Dodaj komentarz