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.
Podczas korzystania z funkcji mogą wystąpić pewne problemy. 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: 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; Ogólna budowa funkcji jest bardzo prosta. Wygląda mniej więcej tak: 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. Można ten kod zapisać do pliku, a potem wczytać poleceniem source, ale o tym pisałem już wcześniej ;-]. 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ę. 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” 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: 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. 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ść. Przykład działania: 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. Wybaczcie takie dość lakoniczne przedstawienie tego. Niestety sam mam jeszcze pewne problemy z “ogarnięciem” procedur. 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). 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: 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. Oraz oczywiście sposób wywołania i zwracany wynik: 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: Oczywiście teraz wywołujemy procedurę: 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ć… 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 ;-]
Getting Started
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”.delimiter //
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN 'jakis napis';
END
delimiter ;
Tabela
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:
CREATE FUNCTION ( ) <nazwa> RETURNS <typ>
BEGIN
{kod funkcji}
END
Prosty “Hello World”
delimiter //
CREATE FUNCTION HelloWorld () RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello World';
END
delimiter ;
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)
Mała dygresja
Zmienne
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 ;
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
DROP FUNCTION IF EXISTS HelloWorld;
delimiter //
CREATE FUNCTION HelloWorld (parametr VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
RETURN parametr;
END
delimiter ;
mysql> select HelloWorld('zlo');
+-------------------+
| HelloWorld('zlo') |
+-------------------+
| zlo |
+-------------------+
1 row in set (0.00 sec)
Procedury
Ogólna budowa
CREATE PROCEDURE sp_name ([parameter[,.
[characteristic ...] routine_body
Nowy HelloWorld
DROP PROCEDURE IF EXISTS HelloWorld;
delimiter //
//
CREATE procedure HelloWorld(out jakis_tekst VARCHAR(30))
BEGIN
set jakis_tekst = 'Hello World';
END
//
delimiter ;
mysql> CALL HelloWorld(@out);
mysql> Select @out;
+-------------+
| @out |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)
Select INTO
DROP PROCEDURE IF EXISTS pobierz;
delimiter //
CREATE PROCEDURE pobierz (out nazwa VARCHAR(50))
BEGIN
SELECT opis INTO nazwa FROM skroty LIMIT 1;
END
//
delimiter ;
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)
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 ;
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
Zakończenie
Świetny art, wreszcie znalazło się (samo
specjalnie jakoś nie szukałem ^^) coś po polskiemu na ten temat
No ja właśnie dzisiaj tego potrzebowałem, także pomyślałem, że od razu coś naskrobię ;p THX
Pytanie. Czy za każdym połączeniem do bazy muszę definiować funkcje, czy są po prostu zapamiętywane „na stałe”?
Przed chwilką sprawdziłem. Ponieważ procedura/funkcja jest zapisywana i tworzona dla określonej bazy pozostaje również zapisana.
No to pojawia się całkiem ciekawe narzędzie do zabaw z bazą
Hehehe, od jutra ja już pewnie będę kombinować nad ekstremalnym wykorzystaniem tego ;]
Jak podejrzewam, tutorial był angielski? :> Podaj adres może? ^^
Na końcu notki jest link podany ;p
Jeszcze nie przeczytałem całej
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.
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ą ?
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
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 ?
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.
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
@ja: Czyli odpowiadam za każdy komentarz jaki pojawi się na moim blogu tak? Będę zmuszony się upewnić tego u paru osób..
>> 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;
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?
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 );
http://www.eioba.pl/a73130/krotko_o_funkcjach_i_procedurach_w_mysql
kto kogo kopiuje?
n00b: proponuję przeczytać pierwsze zdanie w artykule na eioba
Patrzecie, patrzcie
gdzie ja dotarłem szukając info o procedurach
do samego radmena!
Pozdr
Miło co nie?
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ć?
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
Shit, w razie czego, to jest na stronie 112
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?
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ź
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');");
out;”);2. $call = mysql_query("SELECT
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/
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.
OOOO ja jego ;] Ja sie mecze pół dnia, a to taki glupi błąd…
Dzięki za pomoc
Polecam zestaw pomocnych procedur i funkcji dla mysql: http://code.google.com/p/neo-mysql/downloads/list
Dzięki, może się przydać!