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 (e321355)
Albi
Świetny art, wreszcie znalazło się (samo :D specjalnie jakoś nie szukałem ^^) coś po polskiemu na ten temat ;)
radmen
No ja właśnie dzisiaj tego potrzebowałem, także pomyślałem, że od razu coś naskrobię ;p THX :)
Albi
Pytanie. Czy za każdym połączeniem do bazy muszę definiować funkcje, czy są po prostu zapamiętywane „na stałe”?
radmen
Przed chwilką sprawdziłem. Ponieważ procedura/funkcja jest zapisywana i tworzona dla określonej bazy pozostaje również zapisana.
Albi
No to pojawia się całkiem ciekawe narzędzie do zabaw z bazą ;)
radmen
Hehehe, od jutra ja już pewnie będę kombinować nad ekstremalnym wykorzystaniem tego ;]
Albi
Jak podejrzewam, tutorial był angielski? :> Podaj adres może? ^^
radmen
Na końcu notki jest link podany ;p
Albi
Jeszcze nie przeczytałem całej ;)
P.
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.
radmen
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ą ?
P.
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
liberator
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 ?
P.
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.
ja
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
radmen
@ja: Czyli odpowiadam za każdy komentarz jaki pojawi się na moim blogu tak? Będę zmuszony się upewnić tego u paru osób..
ja
>> 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;
ja
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?
No Name
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 );
n00b
http://www.eioba.pl/a73130/krotko_o_funkcjach_i_procedurach_w_mysql
kto kogo kopiuje?
radmen
n00b: proponuję przeczytać pierwsze zdanie w artykule na eioba
Lexus
Patrzecie, patrzcie :) gdzie ja dotarłem szukając info o procedurach :P do samego radmena! :D Pozdr
radmen
Miło co nie? :)
mart
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ć?
radmen
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
radmen
Shit, w razie czego, to jest na stronie 112 :)
mart
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?
mart
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ź
mart
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("SELECTout;");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/
radmen
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.
mart
OOOO ja jego ;] Ja sie mecze pół dnia, a to taki glupi błąd...
Dzięki za pomoc :)