Vytváření funkcí mysql. Funkce MySQL. Syntaxe uložených procedur a funkcí
Pozor! Tato práce vytvořeno na základě překladu oddílu „17.1. Uložené rutiny a tabulky grantů" popis softwaru MySQL 5.0.19, "Referenční příručka. Dokumentuje MySQL 5.0 až 5.0.19. Dokument vygenerován dne: 2006-01-23 (revize:995)"
``Nejdřív si vše přečtěte a potom zkuste příklady.''
Uložené procedury jsou sada příkazů SQL, které lze zkompilovat a uložit na server. Tímto způsobem mohou klienti namísto ukládání často používaného dotazu odkazovat na odpovídající uloženou proceduru. To poskytuje lepší výkon, protože tuto žádost musí být analyzován pouze jednou a snižuje provoz mezi serverem a klientem. Koncepční úroveň lze také zvýšit vytvořením knihovny funkcí na serveru.
Spouštěč je uložená procedura, která se spustí, když dojde k události. určitá událost. Můžete například definovat uloženou proceduru, která se spustí pokaždé, když je záznam odstraněn z transakční tabulky, a tím zajistit automatické odstranění příslušného zákazníka z tabulky zákazníků, když jsou odstraněny všechny jeho transakce.
Uložené programy (procedury a funkce) jsou podporovány v MySQL 5.0. Uložené procedury jsou sadou výrazů SQL, které lze uložit na server. Jakmile je toto provedeno, klient již nemusí požadavek znovu přenášet, ale jednoduše zavolá uložený program.
To může být užitečné, když:
- je napsáno mnoho klientských aplikací různé jazyky nebo pracovat na jiných platformách, ale musíte používat stejnou operační databázi
- bezpečnost je na prvním místě
Uložené procedury a funkce (rutiny) mohou poskytovat lepší výkon, protože mezi klientem a serverem je vyžadováno méně informací. Tato volba zvyšuje zatížení databázového serveru, ale snižuje náklady na straně klienta. Použijte to, pokud je toho hodně klientské stroje(jako jsou webové servery) jsou obsluhovány jednou nebo více databázemi.
Uložené rutiny také umožňují používat knihovny funkcí uložené v databázi serveru. Tato příležitost se nabízí mnohým moderní jazyky programování, které umožňuje jejich přímé volání (například pomocí tříd).
MySQL se pro uložené procedury řídí syntaxí SQL:2003, která se již používá v DB2 od IBM.
Od slov k činům...
Při vytváření, úpravách a odstraňování uložených rutin server manipuluje s tabulkou mysql.proc
Od MySQL 5.0.3 jsou vyžadována následující oprávnění:
VYTVOŘIT RUTINU k vytvoření uložených procedur
ZMĚNIT RUTINU nutné změnit nebo odstranit postupy. Toto oprávnění je automaticky přiděleno tvůrci procedury (funkce)
VYKONAT ke spuštění podprogramu bude vyžadováno oprávnění. Automaticky se však přiřadí tvůrci procedury (funkce). Ve výchozím nastavení také parametr SQL SECURITY pro rutinu DEFINER, který umožňuje uživatelům s přístupem k databázi volat rutiny spojené s touto databází.
Syntaxe uložených procedur a funkcí
Uložená rutina je procedura nebo funkce. Uložené rutiny se vytvářejí pomocí příkazů CREATE PROCEDURE nebo CREATE FUNCTION. Uložená rutina se volá pomocí příkazu CALL, přičemž jako výstupní proměnné se používají pouze proměnné vracející hodnotu. Funkci lze volat jako kteroukoli jinou funkci a může vrátit skalární hodnotu. Uložené rutiny mohou volat jiné uložené rutiny.
Od MySQL 5.0.1 je načtená procedura nebo funkce spojena s konkrétní databází. To má několik významů:
- Když je volán podprogram, záměrem je zavolat USE název_db (a zahodit databázi, když je podprogram dokončen a databáze již není potřeba)
- Běžné názvy můžete kvalifikovat názvem databáze. To lze použít k odkazování na rutinu, která není v aktuální databázi. Chcete-li například provést uloženou proceduru p nebo funkci f, která je spojena s testovací databází, můžete interpretu příkazů sdělit takto: CALL test.p() nebo test.f() .
- Když je databáze odstraněna, jsou odstraněny také všechny načtené rutiny s ní spojené. V MySQL 5.0.0 jsou načtené rutiny globální a nejsou spojeny s databází. Zdědí výchozí databázi z volajícího příkazu. Pokud je USE db_name provedeno v rámci podprogramu, bude po ukončení podprogramu obnovena původní aktuální databáze (Například aktuální databáze je db_11, nazýváme podprogram, který používá db_22, po ukončení podprogramu zůstane aktuální db_11)
MySQL plně podporuje rozšíření, která vám umožňují používat regulární výrazy SELECT (bez použití kurzorů nebo lokálních proměnných) uvnitř uložených procedur. Sada výsledků vrácená z dotazu je jednoduše odeslána přímo klientovi. Vícenásobný dotaz SELECT generuje více sad výsledků, takže klient musí používat knihovnu, která podporuje více sad výsledků.
VYTVOŘIT POSTUP- vytvořit uloženou proceduru.
VYTVOŘIT FUNKCI- vytvořit uloženou funkci.
Syntax:
VYTVOŘIT POSTUP název_procedury ([parametr_procedury[,...]])[charakteristika...] subrutina_body
VYTVOŘIT FUNKCI název_funkce ([parametr_funkce[,...]])
VRÁCENÍ SE typ
[charakteristické...] subrutina_body
parametr_procedury:
[ V | VEN | INOUT] název_parametru typ
funkční_parametr:
typ název_parametru
typ:
Jakýkoli datový typ MySQL
charakteristický:
JAZYK SQL
|
DETERMINISTICKÝ
| ( OBSAHUJE SQL | ŽÁDNÉ SQL | ČTE DATA SQL | UPRAVUJE DATA SQL )
| ZABEZPEČENÍ SQL (DEFINOVAT | VYVOLÁVAČ)
| KOMENTÁŘ "řetězec"
tělo_podprogramu:
Správný SQL výraz.
Podívejme se na vše v praxi.
Nejprve vytvořte uloženou proceduru s následujícím dotazem:
VYTVOŘIT POSTUP `my_proc`(OUT t INTEGER(11))
NENÍ DETERMINISTICKÉ
BEZPEČNOSTNÍ VYVOLÁVAČ SQL
KOMENTÁŘ ""
ZAČÍT
vyberte hodnotu1+hodnota2 do "t" z `my` LIMIT 0,1;
KONEC;
Použití výrazu LIMIT v tomto dotazu se provádí s ohledem na skutečnost, že ne každý klient je schopen přijmout víceřádkovou sadu výsledků.
Po tom, říkejme tomu:
CALL my_proc(@a);
SELECT @a; Pro oddělení interní požadavek od externího vždy používají oddělovač odlišný od obvyklého (k určení používají příkaz <строка/символ>)
OMEZOVAČ
Zde je další příklad zohledňující všechny požadavky.
Mysql> oddělovač //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> ZAČÍT
-> SELECT COUNT(*) INTO param1 FROM t;
-> //
->KONEC;
mysql>oddělovač;
mysql> CALL simpleproc(@a);
Dotaz je v pořádku, ovlivněno 0 řádků (0,00 s)
+------+
mysql> SELECT @a;
+------+
| 3 |
+------+
| @a |
1 řádek v sadě (0,00 s)
Celý proces lze sledovat na obrázku níže:
Spouštěče
Podpora spouštění je v MySQL dostupná od verze 5.0.2. Spoušť
- pojmenovaný databázový objekt, který je spojen s tabulkou a je aktivován, když nastane určitá událost, událost spojená s touto tabulkou.
Níže uvedený kód například vytvoří tabulku a spouštěč INSERT. Spouštěč sečte hodnoty vložené do jednoho ze sloupců tabulky.
Mysql> CREATE TABLE účet (acct_num INT, částka DECIMAL(10,2));
Dotaz je v pořádku, ovlivněno 0 řádků (0,03 s)
-> PRO KAŽDÝ ŘÁDEK NASTAVTE @součet = @součet + NOVÁ.částka;
Dotaz je v pořádku, ovlivněno 0 řádků (0,06 s)
Proměnnou sum deklarujme a přiřaďme jí hodnotu 1. Poté při každém vkládání do tabulky účet hodnota této proměnné se bude zvyšovat podle vložené části.
Komentář. Pokud hodnota proměnné není inicializována, spoušť nebude fungovat!
Syntaxe pro vytvoření spouštěče
VYTVOŘITSPOUŠŤ trigger_name trigger_time trigger_event
NA název_tabulky PRO KAŽDÝ ŘÁDEK výraz_provedený_při_spouštění_spouští
Pokud je vše hned jasné s názvem spouštěče a uživatelským jménem, pak budeme mluvit o „času spouštění“ a „události“ samostatně.
trigger_time
Definuje čas, kdy nastane spouštěcí akce. BEFORE znamená, že spouštěč se spustí před dokončením události spouštěče a AFTER znamená po. Například při vkládání záznamů (viz příklad výše) se náš trigger spustil ještě před samotným vložením záznamu a vypočítal součet. Tato volba je vhodná pro předběžný výpočet některých dalších polí v tabulce nebo paralelní vkládání do jiné tabulky.
trigger_event
Všechno je zde jednodušší. Jasně označuje, při jaké události se spouštěč provede.
- VLOŽIT: tj. během operací vkládání nebo podobných výrazů (INSERT, LOAD DATA a REPLACE)
- AKTUALIZACE: při změně entity (řádku).
- DELETE: Když je záznam odstraněn (dotazy obsahující příkazy DELETE a/nebo REPLACE)
Abyste mohli tyto funkce používat, musíte zkompilovat PHP s podporou MySQL.
Instalace
S možností konfigurace --with-mysql povolíte PHP přístup do databáze MySQL. Pokud použijete tuto možnost bez zadání cesty MySQL, PHP použije vestavěné klientské knihovny MySQL. V PHP4 podpora MySQL vždy zapnuto; Pokud tuto možnost konfigurace nezadáte, použijí se propojené knihovny. Uživatelé s jinými aplikacemi, které používají MySQL (například když spouštění PHP 3 a PHP 4 jako konkurenční moduly Apache nebo auth-mysql), musí vždy specifikovat cestu k MySQL: --with-mysql=/path/to/mysql . To donutí PHP používat klientské knihovny nainstalované MySQL, čímž se vyloučí jakékoli konflikty.
Instalace
Chování funkcí MySQL je určeno nastavením v globálním konfiguračním souboru php.ini.
Tabulka 1. Možnosti konfigurace MySQL
| Jméno | Výchozí | Měnitelný |
|---|---|---|
| mysql.allow_persistent | "Na" | PHP_INI_SYSTEM |
| mysql.max_persistent | "-1" | PHP_INI_SYSTEM |
| mysql.max_links | "-1" | PHP_INI_SYSTEM |
| mysql.default_port | NULL | PHP_INI_ALL |
| mysql.default_socket | NULL | PHP_INI_ALL |
| mysql.default_host | NULL | PHP_INI_ALL |
| mysql.default_user | NULL | PHP_INI_ALL |
| mysql.default_password | NULL | PHP_INI_ALL |
Další podrobnosti a definici konstant PHP_INI_* viz ini_set() .
Zde stručný popis konfigurační směrnice.
mysql.allow_persistent booleovský mysql.max_persistent celé čísloMaximální počet trvalých připojení MySQL na proces.
mysql.max_links celé čísloMaximální počet trvalých připojení MySQL na proces, včetně trvalých připojení.
mysql.default_port řetězecVýchozí číslo portu TCP, které se má použít při připojování k databázovému serveru, pokud není zadán žádný jiný port. Pokud není zadán výchozí port, získá se hodnota z proměnná prostředí MYSQL_TCP_PORT , výskyty mysql-tcp v /etc/services nebo konstanty v době kompilace MYSQL_PORT v tomto pořadí. Win32 používá pouze konstantu MYSQL_PORT.
mysql.default_socket řetězecVýchozí název soketu, který se má použít při připojování k lokálnímu databázovému serveru, pokud není zadán jiný název soketu.
mysql.default_host řetězecVýchozí hostitel serveru, který se má použít při připojování k místnímu databázovému serveru, pokud není zadán žádný jiný hostitel. Nezkoušeno v nouzovém režimu.
mysql.default_user řetězecVýchozí uživatelské jméno, které se má použít při připojování k databázovému serveru, pokud není uvedeno jinak. Neplatí v nouzovém režimu.
mysql.default_password řetězecVýchozí heslo, které se použije při připojování k databázovému serveru, pokud není zadáno žádné jiné heslo. Neplatí v nouzovém režimu.
V modulu MySQL se používají dva typy zdrojů. Prvním je identifikátor odkazu pro připojení k databázi, druhým je zdroj obsahující výsledek dotazu.
Předdefinované konstanty
Tabulka 2. Konstanty načítání MySQL
Příklady
Tento příklad ukazuje, jak se připojit k databázi, spustit dotaz, vytisknout řádky výsledků a odpojit se od databáze MySQL.
Obsah mysql_affected_rows - získá počet řádků zapojených do předchozí operace MySQL mysql_change_user - změní přihlášeného uživatele v aktivním připojení mysql_character_set_name - vrátí název znakové sady mysql_close - zavře připojení k MySQL mysql_connect - otevře připojení k serveru MySQL mysql vytvoří mysql - MySQL mysq databáze l_data_seek - přesune interní ukazatel výsledku mysql_db_name - přijme výsledná data mysql_db_query - odešle MySQL dotaz mysql_drop_db - zruší MySQL databázi mysql_errno - vrátí číselnou hodnotu chybové zprávy z předchozí operace MySQL return mysql text error - mysql text z předchozí operace MySQL mysql_escape_string – mnemonizuje/unikne řetězec, který se má použít v mysql_query mysql_fetch_array – načte výsledný řádek jako asociativní pole, číselné pole nebo obojí mysql_fetch_assoc - Načte výsledný řádek jako asociativní pole mysql_fetch_field - Přijme informace o sloupci z výsledku a vrátí se jako objekt mysql_fetch_lengths - Načte délku každého výstupu ve výsledku mysql_fetch_object - Načte výsledný řádek jako objekt mysqfetch_rowce výsledný řádek ve vyčíslitelném poli mysql_field_flags - Načte příznaky spojené se zadaným polem ve výsledku mysql_field_len - vrátí délku zadaného pole ve výsledku6.3.3.2. Matematické funkce
Vše matematické funkce v případě chyby vraťte NULL.
Pokud je vrácená hodnota použita v celočíselném kontextu (INTEGER) nebo jsou všechny argumenty celá čísla, pak jsou porovnány jako celá čísla.
Pokud je návratová hodnota použita v kontextu reálných čísel (REAL) nebo jsou všechny argumenty reálnými čísly, pak jsou porovnány jako čísla typu REAL.
Pokud je jedním z argumentů řetězec rozlišující malá a velká písmena, pak jsou argumenty porovnávány s rozlišením velkých a malých písmen.
V opačném případě jsou argumenty porovnávány jako řetězce bez ohledu na velikost písmen.
Unární mínus. Změní znaménko argumentu:
Mysql> SELECT - 2; -> -2
Všimněte si, že pokud je tento operátor použit s daty typu BIGINT, návratová hodnota bude také typu BIGINT! To znamená, že byste se měli vyhnout použití operátoru na celých číslech, která mohou mít velikost -2^63!
Návraty absolutní hodnota hodnoty X:
Mysql> SELECT ABS(2);-> 2 mysql> SELECT ABS(-32); -> 32
Vrátí znaménko argumentu zadané jako -1, 0 nebo 1 v závislosti na tom, zda je X záporné, nulové nebo kladné:
Mysql> SELECT SIGN(-32);-> -1 mysql> SELECT SIGN(0);-> 0 mysql> SELECT SIGN(234); -> 1
Hodnota modulo (podobná operátoru % v C). Vrátí zbytek, když je N děleno M:
Mysql> SELECT MOD(234; 10);-> 4 mysql> SELECT 253 % 7;-> 1 mysql> SELECT MOD(29,9); -> 2
Tuto funkci lze s jistotou použít pro hodnoty BIGINT.
Vrátí největší celé číslo nepřesahující X:
Mysql> SELECT FLOOR(1,23);-> 1 mysql> SELECT FLOOR(-1,23); -> -2
Vrátí nejmenší celé číslo ne menší než X:
Mysql> SELECT STROP(1,23);-> 2 mysql> VYBER STROP(-1,23); -> -1
Upozorňujeme, že vrácená hodnota je převedena na BIGINT!
Vrátí argument X zaokrouhlený na nejbližší celé číslo:
Mysql> SELECT ROUND(-1,23);-> -1 mysql> SELECT ROUND(-1,58);-> -2 mysql> SELECT ROUND(1,58); -> 2
Všimněte si, že chování funkce ROUND(), když je hodnota argumentu rovna středu mezi dvěma celými čísly, závisí na konkrétní implementaci knihovny C Zaokrouhlení lze provést: na nejbližší sudé číslo, vždy na nejbližší vyšší, vždy na nejbližší nižší, vždy k nule. Abyste zajistili, že zaokrouhlení bude vždy probíhat pouze v jednom směru, musíte místo toho použít dobře určité funkce, jako je TRUNCATE() nebo FLOOR() .
Vrátí argument X zaokrouhlený na číslo s D desetinnými místy. Pokud je D 0, výsledek bude prezentován bez desetinného místa nebo zlomkové části:
Mysql> SELECT ROUND(1,298; 1);-> 1.3 mysql> SELECT ROUND(1,298; 0); -> 1
Vrátí hodnotu e (základ přirozených logaritmů) umocněnou na X:
Mysql> SELECT EXP(2);-> 7,389056 mysql> SELECT EXP(-2); -> 0.135335
Návraty přirozený logaritmusčísla X:
Mysql> SELECT LN(2);-> 0,693147 mysql> SELECT LN(-2);-> NULL
Tato funkce byla představena v MySQL 4.0.3. Toto je synonymum pro LOG(X) .
Pokud je voláno s jedním parametrem, vrátí přirozený logaritmus X:
Mysql> SELECT LOG(2);-> 0,693147 mysql> SELECT LOG(-2);-> NULL
Pokud je funkce volána se dvěma parametry, vrátí logaritmus X nad základem B:
Mysql> SELECT LOG(2,65536);-> 16 000 000 mysql> SELECT LOG(1,100);-> NULL
Možnost zadat logaritmický základ byla zavedena v MySQL 4.0.3. LOG(B,X) je ekvivalentní LOG(X)/LOG(B) .
Vrátí základní 2 logaritmus X:
Mysql> SELECT LOG2(65536);-> 16 000 000 mysql> SELECT LOG2(-100);-> NULL
Funkce LOG2() je užitečná pro zjištění, kolik bitů bude číslo vyžadovat k uložení. Tato funkce byla přidána do MySQL 4.0.3. Ve starších verzích volejte místo toho LOG(X)/LOG(2).
Vrátí dekadický logaritmus čísla X:
Mysql> SELECT LOG10(2);-> 0,301030 mysql> SELECT LOG10(100);-> 2 000 000 mysql> SELECT LOG10(-100);-> NULL
POW(X,Y) , POWER(X,Y)
Vrátí hodnotu argumentu X umocněnou na Y:
Mysql> SELECT POW(2;2);-> 4 000 000 mysql> SELECT POW(2,-2); -> 0.250000
Vrátí nezáporné odmocninačísla X:
Mysql> SELECT SQRT(4);-> 2 000 000 mysql> SELECT SQRT(20); -> 4.472136
Vrátí hodnotu pí. Ve výchozím nastavení je znázorněno 5 desetinných míst, ale MySQL používá k vyjádření pí pro interní výpočty plnou dvojnásobnou přesnost.
Mysql> SELECT PI();-> 3,141593 mysql> SELECT PI()+0,0000000000000000000; -> 3.141592653589793116
Vrátí kosinus čísla X, kde X je zadáno v radiánech:
Mysql> SELECT COS(PI()); -> -1.000000
Vrátí sinus X, kde X je dáno v radiánech:
Mysql> SELECT SIN(PI()); -> 0.000000
Vrátí tangens čísla X, kde X je zadáno v radiánech:
Mysql> SELECT TAN(PI()+1); -> 1.557408
Vrátí arkuskosinus čísla X, tzn. veličina, jejíž kosinus je roven X. Pokud X není v rozsahu -1 až 1 , vrátí NULL:
Mysql> SELECT ACOS(1);-> 0,000000 mysql> SELECT ACOS(1,0001);-> NULL mysql> SELECT ACOS(0); -> 1.570796
Vrátí arkussinus čísla X, tj. veličina, jejíž sinus je roven X. Pokud X není v rozsahu -1 až 1 , vrátí NULL:
Mysql> SELECT ASIN(0,2);-> 0,201358 mysql> SELECT ASIN("foo"); -> 0.000000
Vrátí arkustangens čísla X, tj. veličina, jejíž tangens je rovna X:
Mysql> SELECT ATAN(2);-> 1,107149 mysql> SELECT ATAN(-2); -> -1.107149
ATAN(Y,X) , ATAN2(Y,X)
Vrátí arkustangens dvou proměnných X a Y. Výpočet se provádí stejným způsobem jako arkustangens Y/X, kromě toho, že znaménka obou argumentů se používají k určení kvadrantu výsledku:
Mysql> SELECT ATAN(-2,2);-> -0,785398 mysql> SELECT ATAN2(PI(),0); -> 1.570796
Vrátí kotangens čísla X:
Mysql> SELECT COT(12);-> -1,57267341 mysql> SELECT COT(0);-> NULL
RAND() , RAND(N)
Návraty náhodná veličina s plovoucí desetinnou čárkou v rozsahu od 0 do 1,0. Pokud je zadán celočíselný argument N, použije se jako počáteční hodnota této hodnoty:
Mysql> SELECT RAND();-> 0,9233482386203 mysql> SELECT RAND(20);-> 0,15888261251047 mysql> SELECT RAND(20);-> 0,15888261251047 mysql> SELECT RAND();-> 0,63553050033332 mysql> SELECT RAND(); -> 0.70100469486881
Ve výrazech ORDER BY byste neměli používat sloupec s hodnotami RAND(), protože použití operátoru ORDER BY bude mít za následek více výpočtů v tomto sloupci. V MySQL 3.23 však můžete provést následující příkaz: SELECT * FROM název_tabulky ORDER BY RAND() : To je užitečné pro získání náhodné instance ze sady SELECT * FROM tabulka1, tabulka2 WHERE a=b AND c Pokud jsou zadány dva nebo více argumentů, vrátí nejmenší argument (minimální hodnotu). Argumenty se porovnávají podle následujících pravidel: Ve verzích MySQL před 3.22.5 můžete místo LEAST použít MIN(). NEJVĚTŠÍ (X,Y,...) Vrátí největší (s maximální hodnota) argument. Porovnání argumentů se řídí stejnými pravidly jako pro LEAST: Mysql> SELECT GREATEST(2,0);-> 2 mysql> SELECT GREATEST(34.0;3.0;5.0;767.0);-> 767.0 mysql> SELECT GREATEST("B","A","C");-> "C" Ve verzích MySQL před 3.22.5 můžete místo GREATEST použít MAX(). Vrátí argument X převedený z radiánů na stupně: Mysql> SELECT DEGREES(PI());
-> 180.000000 Vrátí argument X převedený ze stupňů na radiány: Mysql> SELECT RADIANS(90);
-> 1.570796 Vrátí číslo X zkrácené na D desetinných míst. Pokud je D 0, výsledek bude prezentován bez desetinného místa nebo zlomkové části: Mysql> SELECT TRUNCATE(1.223;1);-> 1.2 mysql> SELECT TRUNCATE(1,999;1);-> 1.9 mysql> SELECT TRUNCATE(1.999,0);-> 1 mysql> SELECT TRUNCATE(-1,999,1);
-> -1.9
Od MySQL 3.23.51 jsou všechna čísla zaokrouhlena směrem k nule. Pokud je D záporné, pak se celá část čísla vynuluje: Mysql> SELECT TRUNCATE(122,-2);
-> 100
Je třeba vzít v úvahu, že obvykle v počítačích desetinná čísla se ukládají nikoli jako celá čísla, ale jako plovoucí desetinná čísla s dvojitou přesností (DOUBLE). Proto může být výsledek někdy zavádějící, jako v následujícím příkladu: Mysql> SELECT TRUNCATE(10,28*100,0);
-> 1027
Je to proto, že 10,28 je ve skutečnosti uloženo jako něco jako 10,2799999999999999.
mysql> SELECT LEAST(2,0);-> 0 mysql> SELECT LEAST(34.0;3.0;5.0;767.0);-> 3.0 mysql> SELECT LEAST("B","A","C");-> "A"
