May 18, 2007 

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

More from this category

33 Responses to “Krótko o funkcjach i procedurach w MySQL”

  1. Albi May 18, 2007 at 3:14 pm #

    Świetny art, wreszcie znalazło się (samo :D specjalnie jakoś nie szukałem ^^) coś po polskiemu na ten temat ;)

  2. radmen May 18, 2007 at 3:14 pm #

    No ja właśnie dzisiaj tego potrzebowałem, także pomyślałem, że od razu coś naskrobię ;p THX :)

  3. Albi May 18, 2007 at 3:14 pm #

    Pytanie. Czy za każdym połączeniem do bazy muszę definiować funkcje, czy są po prostu zapamiętywane „na stałe”?

  4. radmen May 18, 2007 at 3:14 pm #

    Przed chwilką sprawdziłem. Ponieważ procedura/funkcja jest zapisywana i tworzona dla określonej bazy pozostaje również zapisana.

  5. Albi May 18, 2007 at 3:14 pm #

    No to pojawia się całkiem ciekawe narzędzie do zabaw z bazą ;)

  6. radmen May 18, 2007 at 3:14 pm #

    Hehehe, od jutra ja już pewnie będę kombinować nad ekstremalnym wykorzystaniem tego ;]

  7. Albi May 18, 2007 at 3:14 pm #

    Jak podejrzewam, tutorial był angielski? :> Podaj adres może? ^^

  8. radmen May 18, 2007 at 3:14 pm #

    Na końcu notki jest link podany ;p

  9. Albi May 18, 2007 at 3:14 pm #

    Jeszcze nie przeczytałem całej ;)

  10. P. May 18, 2007 at 3:14 pm #

    Procedury i funkcje MySQL faktycznie są potężnym narzędziem, ale… no właśnie, niestety mają kilka poważnych ograniczeń. Na przykład dynamiczny SQL dopuszczalny jest w procedurach, ale w funkcjach już nie, a nieraz by się przydał. Ponadto zmiana procedury/funkcji (przynajmniej poprzez mojego ulubionego klienta, który robi drop/create) powoduje wykasowanie zdefiniowanych uprawnień do procedury składowanej (uprawnienia trzymane są w bazie mysql).
    Nie zmienia to jednak faktu, że stored routines w MySQL „wymiatają”, zwłaszcza w porównaniu z mocno ograniczonym w tym miejscu Postgresem (a może ja po prostu go nie lubię ;-) )

    EDIT: Polecam także zapoznanie się z kursorami – w połączeniu z procedurami dają niesamowite możliwości.

  11. radmen May 18, 2007 at 3:14 pm #

    P. A powiedz mi, czy miałbyś pojęcie jak za pomocą procedur MySQL wykonać coś takiego co by przenosiło dane z jednej tabeli do drugiej, biorąc pod uwagę fakt, że nazwy pewnych rekordów się nieco różnią ?

  12. P. May 18, 2007 at 3:14 pm #

    Zapodaj jakieś przykładowe tabelki – źródłową i docelową, to sprawdzimy. i zdefiniuj dokładniej zadanie – tabele mają taką samą strukturę, tylko różne nazwy kolumn, czy struktury są różne ? Chodzi zawsze o te 2 tabele, czy ma to być parametr ?
    Chrome niestety leży, ale awaryjnie mam konto potfur(at)maszyna.pl

  13. liberator May 18, 2007 at 3:14 pm #

    Czy jest możliwość zdefiniowania procedury w ten sposób- żeby zwracała wartość podobnie jak RETURN w funkcji – tj bez konieczności wywoływania SELECT @param ?

  14. P. May 18, 2007 at 3:14 pm #

    AFIK nie. Procedura pozwala po prostu wykonać zapytania SQL + pętelki itp. a wyjściem z procedury jest resultset. Przez klientów call obsługiwany jest jak select/insert/update.

  15. ja May 18, 2007 at 3:14 pm #

    uwaga ogólna: opis na dole „Jakkolwiek jestem właścicielem tego bloga, nie ponoszę odpowiedzialności za kometarze napisane przez innych obywateli tego wolnego kraju.” jest nieaktualny. Od niedawna w majestacie prawa odpowiadasz za wpisy dokonane na Twoim (sensownym skądinąd) blogu

  16. radmen May 18, 2007 at 3:14 pm #

    @ja: Czyli odpowiadam za każdy komentarz jaki pojawi się na moim blogu tak? Będę zmuszony się upewnić tego u paru osób..

  17. ja May 18, 2007 at 3:14 pm #

    >> Zapodaj jakieś przykładowe tabelki – źródłową i docelową, to sprawdzimy. i zdefiniuj dokładniej zadanie

    — przyklad ciala procedury
    — wybiera z jednej do drugiej tabeli o innej strukturze
    INSERT INTO opis
    (
    isbn,
    opis,
    site_id
    )
    select
    isbn,
    nota_wydawcy,
    site_id
    from dane
    where nota_wydawcy IS NOT NULL;

  18. ja May 18, 2007 at 3:14 pm #

    może ktoś podpowie, jak procedurze składowanej wykonać kod znajdujący się jako tekst w tabeli. selectem wybieram go do zmiennej, ale co dalej?

  19. No Name May 18, 2007 at 3:14 pm #

    W funkcjach można wykonać kod SQL, należy tylko zadeklarować taką właściowość przy tworzeniu funkcji:

    CREATE FUNCTION logowanie( username varchar(50), password varchar(50) ) RETURNS tinyint READS SQL DATA
    RETURN ( SELECT COUNT FROM users AS usr WHERE usr.username = username AND usr.password = password );

  20. n00b May 18, 2007 at 3:14 pm #

    http://www.eioba.pl/a73130/krotko_o_funkcjach_i_procedurach_w_mysql

    kto kogo kopiuje?

  21. radmen May 18, 2007 at 3:14 pm #

    n00b: proponuję przeczytać pierwsze zdanie w artykule na eioba

    Odnośnik do oryginalnej publikacji: http://blog.radmen.info/2007/05/18/krotko-o-funkcjach-i-procedurach-w-mysql/

  22. Lexus May 18, 2007 at 3:14 pm #

    Patrzecie, patrzcie :) gdzie ja dotarłem szukając info o procedurach :P do samego radmena! :D Pozdr

  23. radmen May 18, 2007 at 3:14 pm #

    Miło co nie? :)

  24. mart May 18, 2007 at 3:14 pm #

    Witam,
    “Należy również wspomnieć, że procedura nie musi zwracać wartości, ale może (nawet kilka na raz).”
    mógłby ktoś napisać JAK PROCEDURA MOŻE ZWRACAĆ KILKA WARTOŚCI NA RAZ?
    Chcę, aby przeszukała tabelę według np. pola Name i zwrócł wszystkie ID gdzie Name=Jan, ale wyskakuje błąd “Result consisted of more than one row”
    PS. wiem co oznacza błąd, ale nie znam rozwiązania, czy w ogole można to rozwiązać?

  25. radmen May 18, 2007 at 3:14 pm #

    Mart: a w czym konkretnie masz problem ? Przed chwilą wyczytałem, że można poprzez CALL procedura() można pobrać więcej danych.

    Może to Ci się przyda (przykład w PHP) http://tinyurl.com/myy5er

  26. radmen May 18, 2007 at 3:14 pm #

    Shit, w razie czego, to jest na stronie 112 :)

  27. mart May 18, 2007 at 3:14 pm #

    Opiszę problem dokładniej:
    w MySQL mam tabelę `imie` (id, name)
    tworze procedurę z parametrami: (out idu INT, in n INT)
    i chcę, aby zwróciła mi wszystkie id dla name=n

    w php wyglądałoby to mniej wiecej tak:
    1. $n = ‘Jan’;
    2. $select = (“SELECT `id` FROM `imie` WHERE `name`=’”.$n.”‘;”)
    tylko, że powyższe zapytanie jak przepisze do procedury, wprowadzajac n jako parametr to zwróci błąd: “Result consisted of more than one row”

    Chciałbym, aby procedura zwracała np. text w postaci:
    id1|id2|id3|id4…

    Jest to w ogole możliwe?

  28. mart May 18, 2007 at 3:14 pm #

    Ok, udało się rozwiązać problem :)
    Dla zainteresowanych:

    SELECT GROUP_CONCAT(id ORDER BY name SEPARATOR ‘|’) INTO `result` FROM `imie` WHERE `name` = “Jan” GROUP BY `name`;

    Pozdro i dzieki za podpowiedź

  29. mart May 18, 2007 at 3:14 pm #

    Witam, natrafiłem na kolejny, chyba banalny problem, ale nie mogę sobie poradzić…

    Chcę wywołać procedurę i zwrócić jej wynik w php:
    [...]
    1. $call = mysql_query(“CALL serwer_register_user(out,'parametr1');");
    2. $call = mysql_query("SELECT     out;”);
    3. return $call;
    [...]

    Ale jedyne co funkcja php zwraca to: “Resource id #11″

    Czy poprawnie wywołuje procedure w php? Wszystko robię wg tej strony: http://preetul.wordpress.com/2009/06/26/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

  30. radmen May 18, 2007 at 3:14 pm #

    mart: mysql_query() zwraca tylko zasób. Jak chcesz pobrać dane to musisz to przepuścić dodatkowo przez mysql_fetch_array()/mysql_fetch_object() – odyłam do manuala PHP.

  31. mart May 18, 2007 at 3:14 pm #

    OOOO ja jego ;] Ja sie mecze pół dnia, a to taki glupi błąd…

    Dzięki za pomoc :)

  32. grek May 18, 2007 at 3:14 pm #

    Polecam zestaw pomocnych procedur i funkcji dla mysql: http://code.google.com/p/neo-mysql/downloads/list

  33. radmen May 18, 2007 at 3:14 pm #

    Dzięki, może się przydać!