7,8 tis

Jednoduše řečeno, uložené procedury (“SP”) jsou procedury uložené v databázi (napsané pomocí SQL a dalších příkazů), které lze nazvat databázovým strojem nebo přidruženým programovacím jazykem.

V tomto článku vám řeknu, jak vytvořit HP pomocí MySQL a spustit jej na serveru MySQL a prostřednictvím PHP.

Poznámka: Nebudeme se zde zabývat všemi aspekty HP. Chcete-li získat pomoc v otázkách, které nejsou uvedeny v článku, můžete vždy použít úředníka Dokumentace MySQL.

HP je podporováno i na jiných běžných databázových serverech (například Postgre), takže to, co si dnes probereme, platí i pro ně.

Většina z nás dobře zná obvyklá nastavení, která nám umožňují vytvořit databázovou aplikaci: vytvoření databáze, vytvoření tabulky, vytvoření indexu, dat CRUD, generování dotazů na straně klienta a v případě potřeby další zpracování.

Tento proces funguje ve většině případů skvěle, ale existuje jeden důležitý aspekt, který programování databáze nepokrývá: uložená procedura.

Napadají mě minimálně čtyři dobré důvody, proč používat HP v databázových aplikacích. Za prvé, snižuje provoz v síti a zatížení serveru.

Standardní webová databázová aplikace PHP má čtyři součásti:

• Úroveň klienta, který je obvykle reprezentován webovým prohlížečem. Poskytuje interakci s uživatelem a umožňuje zadávání dat prostřednictvím uživatelského rozhraní;
• Úroveň webového serveru, kde se zpracovávají požadavky uživatelů a odpovědi se zasílají zpět na úroveň klienta;
• PHP vrstva, která zpracovává všechny PHP komponenty, vytváří aplikační logiku a generuje PHP část odpovědi;
• Úroveň databáze, který zpracovává všechny databázové dotazy, včetně (mimo jiné) dotazů SELECT, příkazů INSERT atd.

Typicky, když jde o velká aplikace, tyto prvky s největší pravděpodobností nejsou na stejném počítači, je dokonce možné, že nejsou ve stejné síti.

Přestože se rychlost sítě v posledních několika letech výrazně zvýšila, stále je to nejpomalejší a nejspolehlivější (ve srovnání s ostatními) kanál pro přenos dat (CPU cache, paměť, pevný disk atd.)

Aby se tedy zvýšila rychlost aplikace a zlepšila spolehlivost, některé případy se uchýlí k tomu, že na straně serveru (konkrétně serveru MySQL) bude prováděno více zpracování dat a logiky, a méně dat se bude přenášet po síti.

Za druhé, zvyšuje produktivitu. HP jsou uloženy a spouštěny přímo na serveru MySQL. Lze je předkompilovat a analyzovat na databázovém serveru.

To je zcela odlišné od zpracování stejného dotazu na straně klienta, kde je dotaz analyzován databázovými ovladači, analyzován a optimalizován (pokud je to možné) při každém volání příkazu dotazu.

Je to podobné jako spouštění interpretovaného jazyka (strana klienta) a kompilovaného jazyka (strana databázového serveru). A víme, že zkompilovaný program poběží rychleji.

Za třetí, jednou napsaný HP může být spuštěn kdekoli. SQL je standardní a 100% nezávislý na platformě. Spoléhá pouze na databázový server. Přemýšlejte o tom, kolik různých jazyků/knihoven se používá pro práci s databází.

Zaměřují se na zlepšení efektivity získávání a zpracování dat na straně serveru, místo toho, aby specifikovali stejnou logiku zpracování dat, syntakticky popisovanou všemi těmito jazyky/knihovnami odlišně. Protože tato logika zpracování dat je tak široce používána.

V neposlední řadě je HP základním aspektem zabezpečení databáze.

uvažujme lehká instalace pro databázi. Řekněme dovnitř informační systém Systém řízení lidských zdrojů (HRIS) má tabulku obsahující informace o mzdě každého zaměstnance. Zaměstnanec HR by měl být schopen získat některá data z této tabulky: celkové částky platů, průměrný plat atd.

Tento zaměstnanec by však neměl mít přístup detailní informace o platu každého zaměstnance, protože tyto informace jsou důvěrné a mohou být dostupné pouze některým manažerům.

Víme, že MySQL má úplný systém správy oprávnění. Je zřejmé, že v tomto případě nemůžeme tomuto HR zaměstnanci udělit ani privilegium SELECT (protože pokud to uděláme, bude to znamenat, že uvidí podrobný plat každého zaměstnance).

Pokud však nemá přístup k platové tabulce, jak bude tento zaměstnanec moci získat přehled mezd? Jak můžeme zaměstnanci HR poskytnout přístup k těmto informacím, aniž bychom ohrozili personální politiku společnosti?

Řešením tohoto problému je použití uložené procedury, která vydá požadované informace a udělí je zaměstnanci, který má oprávnění EXECUTE. (Podrobný seznam a popis oprávnění MySQL naleznete v oficiální dokumentaci.

HP je v tomto případě mostem mezi uživatelem (náš HR pracovník) a tabulkou (plat), ke kterému uživatel nemá přímý přístup:

To je vše! S pomocí HP můžeme uživateli poskytnout možnost dokončit úkol bez ohrožení bezpečnosti databáze (a personální politiky)!

Nevýhody používání uložených procedur

Po výčtu všech výhod používání HP bychom měli jasně porozumět některým nevýhodám a zjistit, zda existují způsoby, jak situaci zlepšit:

• Nedostatek kontroly verzí samotného HP. Když se změní verze HP, změní se bez uložení historie akcí pro předchozí verze na straně serveru. To může způsobit určité nepohodlí, když by uživatel chtěl vrátit změny.

  V takových případech doporučuji napsat HP na straně klienta a zde poskytnout správu verzí. Když je HP připraveno, kód lze snadno zkopírovat například do MySQL Workbench a vytvořit proceduru na straně serveru. Tímto způsobem můžeme získat určitý stupeň kontroly verzí.

• Absence jednoduchý způsob « synchronizovat» implementace změn a vynucení použití všech subjektů Nejnovější verze, zejména když má každý člen týmu svou vlastní lokální databázi pro vývoj a testování.

  Řešením může být kontrola verzí, ale stále je nutný ruční zásah aktualizací místní kopie HP na lokální server DB. Dalším způsobem je použití " podmíněný objekt" Členové týmu mohou být rozděleni tak, aby alespoň jedna osoba byla zodpovědná za údržbu HP a řešení pomocí jejího volání prostřednictvím kódu.

  Zbytek skupiny, který potřebuje výsledky provádění HP, může vyvinout a otestovat svou část pomocí podmíněného objektu za předpokladu, že „podmíněná“ volání do HP přinesou požadovaný výsledek. V pozdější fázi můžete kombinovat různé části odstraněním podmíněného kódu.

• Potíže s vytvářením záloh/exportováním. HP je na straně serveru. Vývojáři budou mít pouze základní oprávnění (SELECT, EXECUTE atd.) a žádná administrátorská práva pro zálohování a export. V jistém smyslu to není vůbec nevýhoda, ale spíše jeden ze základních aspektů zabezpečení databáze.

  A toto omezení nemůžete obejít a nedoporučuje se. Očekává se, že tým bude mít pro tuto práci přiděleného DBA. Pravidelný záloha Databáze může sloužit i pro účely exportu (a importu).

Vytvoření uložené procedury v MySQL

Podívejme se, jak vytvořit HP na serveru MySQL, vytvořit uživatele, přidělit mu oprávnění a spustit (pod tímto přihlášením) HP pro kontrolu, zda jsou data zpracována správně. Ve svém pracovním prostředí používám MySQL Workbench.

Existují i ​​další nástroje (například PHPMyAdmin), takže si můžete vybrat, co vám nejlépe vyhovuje.

Řekněme, že máme takovou tabulku:

CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Nejprve vytvoříme uživatele ‚tr‘ pro HR zaměstnance, který potřebuje získat souhrnné informace o mzdě z této tabulky (průměrná mzda pro firmu, maximální, minimální atd.):

VYTVOŘIT UŽIVATELE "tr"@"localhost" IDENTIFIKOVANÉ OD "mypass";

Tomuto uživateli přidělujeme pouze oprávnění EXECUTE pro tabulku, která obsahuje mzdové údaje:

povolit provedení na hris.* na tr@`%`

Potřebu poskytnout určitá oprávnění můžeme objasnit přečtením části „ Uživatelé a oprávnění» Dokumentace MySQL:

Nyní vytvoříme HP následovně:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN vybrat avg(sal) do avg_sal z platu; KONEC

POZNÁMKA: Všechny výše uvedené operace vyžadují administrátorská práva na serveru MySQL.

Po provedení příkazu v MySQL Workbench bude avg_sal HP vytvořen a připraven k volání. Vypíše průměrnou mzdu z platové tabulky.

Abychom otestovali, zda uživatel tr může spustit HP bez přístupu k tabulce platů, můžeme změnit naši roli přihlášením k serveru MySQL jako uživatel tr. To lze provést vytvořením nového připojení v MySQL Workbench s jiným uživatelským jménem a heslem.

Po přihlášení jako uživatel tr první věc, kterou uvidíme, je, že nevidíme tabulky, je nám k dispozici pouze HP:

Je zřejmé, že uživatel tr nebude moci získat žádná data z žádné tabulky (tj. nebude moci vidět konkrétní částky platů z tabulky platů), ale bude moci spustit HP, které jsme právě vytvořili, abychom určili průměrnou mzdu za společnost :

volání avg_sal(@out); vyberte @out;

Výsledkem je průměrná mzda.

V tomto okamžiku jsme dokončili všechny přípravné práce: vytvořili jsme uživatele, přidělili mu oprávnění, vytvořili HP a zkontrolovali jeho provedení. Dále si ukážeme, jak toto HP nazvat z PHP.

Volání uložené procedury z PHP

Pomocí PDO je volání HP docela jednoduché. PHP kód vypadá takto:

$dbms = "mysql"; //Nahraďte níže uvedené parametry připojení tak, aby odpovídaly vašemu prostředí $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$hostitel;dbname=$db"; $cn=nové PDO($dsn, $uživatel, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

$res bude obsahovat hodnotu průměrné mzdy z tabulky platů. Poté bude uživatel moci dále zpracovávat odchozí data.

Závěr

V tomto článku jsme se podívali na nedílnou součást databáze MySQL: uložené procedury.

Výhody používání HP jsou zřejmé, ale dovolte mi je znovu zdůraznit: Uložené procedury umožňují poskytovat přísné řízení přístupu k určitým datům v databázi, aby byly splněny obchodní požadavky.

Ukázali jsme si také základní kroky vytváření uložených procedur, vytvoření uživatele a přidělení oprávnění a jak volat HP přes PHP.

Tento článek nepokrývá všechny aspekty, které se týkají uložených procedur. Nějaký důležité body, jako jsou I/O parametry, řídicí příkazy, kurzory, úplná syntaxe atd. Tím jsme se v tomto krátkém článku nezabývali.

Pokud vás tento článek zaujal, zanechte prosím komentář a my rádi zveřejníme podrobnější materiál o tomto užitečném a výkonném prvku MySQL.

MySQL 5 má mnoho nových funkcí, z nichž jednou z nejvýznamnějších je vytváření uložených procedur. V tomto tutoriálu budu mluvit o tom, které to jsou a jak vám mohou usnadnit život.

Úvod

Uložená procedura je způsob, jak zapouzdřit opakující se akce. Uložené procedury mohou deklarovat proměnné, manipulovat s datovým tokem a používat další programovací techniky.

Důvod jejich vzniku je jasný a je potvrzen častým používáním. Na druhou stranu, pokud budete mluvit s těmi, kteří s nimi pracují nepravidelně, názory se rozdělí na dvě zcela opačné strany. Nezapomeň na to.

Za

• Sdílení logiky s jinými aplikacemi. Uložené procedury zapouzdřují funkčnost; to poskytuje konektivitu pro přístup k datům a jejich správu napříč různými aplikacemi.
• Izolace uživatelů z databázových tabulek. To vám umožní poskytnout přístup k uloženým procedurám, ale ne k samotným datům tabulky.
• Poskytuje ochranný mechanismus. Podle předchozího bodu, pokud máte přístup k datům pouze prostřednictvím uložených procedur, nikdo jiný nemůže vymazat vaše data pomocí příkazu SQL DELETE.
• Zlepšený výkon v důsledku snížení síťový provoz. Pomocí uložených procedur lze kombinovat více dotazů.

Proti

• Zvýšené zatížení databázového serveru kvůli skutečnosti, že většina práce se provádí na straně serveru a méně na straně klienta.
• Budete se muset hodně učit. Abyste mohli psát uložené procedury, budete se muset naučit syntaxi výrazů MySQL.
• Duplikujete svou aplikační logiku na dvou místech: kód serveru a kód uložených procedur, čímž komplikujete proces manipulace s daty.
• Migrace z jednoho DBMS na jiný (DB2, SQL Server atd.) může vést k problémům.

Nástroj, se kterým pracuji, se nazývá MySQL Query Browser, což je docela standardní pro interakci s databázemi. Nástroj příkazový řádek MySQL je další skvělá volba. Důvod, proč vám to říkám, je ten, že oblíbený phpMyAdmin každého nepodporuje spouštění uložených procedur.

Mimochodem, používám základní strukturu tabulek, abych vám usnadnil pochopení tohoto tématu. Mluvím o uložených procedurách a jsou dostatečně složité, aby vyžadovaly ponoření se do těžkopádné struktury tabulek.

Krok 1: Umístěte omezovač

Oddělovač je znak nebo řetězec znaků, který se používá k označení klienta MySQL, že jste dokončili psaní výrazu SQL. Po věky byl středníkem oddělovačem. Mohou však nastat problémy, protože v uložené proceduře může být více výrazů, z nichž každý musí končit středníkem. V tomto tutoriálu používám jako oddělovač řetězec „//“.

Krok 2: Jak pracovat s uloženými procedurami

Vytvoření uložené procedury

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "Procedura" BEGIN SELECT "Ahoj světe!"; KONEC//

První část kódu vytvoří uloženou proceduru. Další obsahuje volitelné parametry. Poté přichází na řadu název a nakonec samotné tělo procedury.

V názvech uložených procedur se rozlišují velká a malá písmena. Také nelze vytvořit více procedur se stejným názvem. Uvnitř uložené procedury nemohou být výrazy, které upravují samotnou databázi.

4 vlastnosti uložené procedury:

• Jazyk: Pro účely přenositelnosti je výchozí SQL.
• Deterministický: pokud procedura vždy vrací stejný výsledek a bere stejné vstupní parametry. Toto je pro proces replikace a registrace. Výchozí hodnota je NOT DETERMINISTIC.
• Zabezpečení SQL: uživatelská práva se kontrolují během hovoru. INVOKER je uživatel, který volá uloženou proceduru. DEFINER je „tvůrcem“ procedury. Výchozí hodnota je DEFINER.
• Komentář: Pro účely dokumentace je výchozí hodnota ""

Volání uložené procedury

Chcete-li volat uloženou proceduru, musíte zadat klíčové slovo CALL, za kterým následuje název procedury a za ním parametry (proměnné nebo hodnoty) v závorkách. Závorky jsou povinné.

CALL název_uložené_procedury (param1, param2, ....) CALL procedura1(10 , "parametr řetězce" , @parametr_var);

Úprava uložené procedury

MySQL má příkaz ALTER PROCEDURE pro změnu procedur, ale je vhodný pouze pro změnu určitých charakteristik. Pokud potřebujete změnit parametry nebo tělo procedury, měli byste ji odstranit a znovu vytvořit.

Odebrání uložené procedury

POSTUP VYPNĚNÍ, POKUD EXISTUJE p2;

Toto je jednoduchý příkaz. Příkaz IF EXISTS zachytí chybu, pokud takový postup neexistuje.

Krok 3: Možnosti

Podívejme se, jak můžeme předat parametry uložené proceduře.

• CREATE PROCEDURE proc1(): prázdný seznam parametrů
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): jeden vstupní parametr. Slovo IN je volitelné, protože výchozí parametry jsou IN (in).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): vrácen jeden parametr.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): jeden parametr, vstupní i návratový.

Samozřejmě můžete zadat několik parametrů různých typů.

Příklad parametru IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS výsledek; KONEC//

Příklad parametru OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Toto je test"; KONEC //

Příklad parametru INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; KONEC //

Krok 4: Proměnné

Nyní vás naučím, jak vytvářet proměnné a ukládat je do procedur. Musíte je explicitně deklarovat na začátku bloku BEGIN/END spolu s jejich datovými typy. Jakmile proměnnou deklarujete, můžete ji použít stejným způsobem jako proměnné relace, literály nebo názvy sloupců.

Syntaxe deklarace proměnné vypadá takto:

DECLARE název_varianta DATA-TYPE DEFAULT defaultvalue;

Pojďme deklarovat některé proměnné:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE dnes TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Práce s proměnnými

Jakmile deklarujete proměnnou, můžete nastavit její hodnotu pomocí příkazů SET nebo SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE dnes TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "Jsem řetězec"; SELECT CONCAT(str,paramstr), dnes FROM tabulka2 WHERE b >=5; KONEC //

Krok 5: Struktury řízení závitů

MySQL podporuje konstrukce IF, CASE, ITERATE, LEAVE LOOP, WHILE a REPEAT pro řízení vláken v rámci uložené procedury. Podíváme se na to, jak používat IF, CASE a WHILE, protože jsou nejběžněji používané.

IF design

Pomocí konstrukce IF můžeme provádět úkoly obsahující podmínky:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET proměnná1 = param1 + 1; POKUD proměnná1 = 0 THEN SELECT proměnná1; ENDIF; POKUD param1 = 0 THEN SELECT "Hodnota parametru = 0"; ELSE SELECT "Hodnota parametru<>0"; END IF; END //

CASE design

CASE je další metodou testování podmínek a výběru vhodného řešení. Tento skvělá cesta nahrazení více IF konstruktů. Konstrukt lze popsat dvěma způsoby, což poskytuje flexibilitu při správě více podmíněných výrazů.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET proměnná1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (proměnná1); ELSE INSERT INTO table1 VALUES (99); KONCOVÝ PŘÍPAD; KONEC //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET proměnná1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); KDYŽ proměnná1 = 1 THEN INSERT INTO table1 VALUES (proměnná1); ELSE INSERT INTO table1 VALUES (99); KONCOVÝ PŘÍPAD; KONEC //

WHILE design

Technicky existují tři typy smyček: smyčka WHILE, smyčka LOOP a smyčka REPEAT. Můžete také zacyklit pomocí programovací techniky Darth Vadera: příkazy GOTO. Zde je příklad smyčky:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET proměnná1 = 0; WHILE proměnná1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Krok 6: Kurzory

Kurzory se používají k procházení sady řádků vrácených dotazem a ke zpracování každého řádku.

MySQL podporuje kurzory v uložených procedurách. Zde je krátká syntaxe pro vytvoření a použití kurzoru.

DECLARE jméno-kurzoru CURSOR FOR SELECT ...; /*Deklarování kurzoru a jeho vyplnění */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Co dělat, když nejsou žádné další záznamy*/ OPENkurzor-název; /*Otevřít kurzor*/ FETCH název-kurzoru INTO proměnná [, proměnná]; /*Přiřadí hodnotu proměnné, která se rovná aktuální hodnotě sloupce*/ CLOSE kurzor-název; /*Zavřít kurzor*/

V tomto příkladu provedeme několik jednoduchých operací pomocí kurzoru:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE POKRAČOVAT OBSLUHU PRO NENALEZENO SET b = 1; OPEN cur1; SET b = 0; SET c = 0; KDYŽ b = 0 UDĚLEJTE NAČÍST cur1 DO a; POKUD b = 0 POTOM SET c = c + a; ENDIF; END WHILE; CLOSE cur1; SET param1 = c; KONEC //

Kurzory mají tři vlastnosti, kterým musíte porozumět, abyste se vyhnuli neočekávaným výsledkům:

• Není citlivý: kurzor, který se otevře jednou, nebude odrážet změny v tabulce, ke kterým dojde později. Ve skutečnosti MySQL nezaručuje, že kurzor bude aktualizován, takže na to nespoléhejte.
• Jen pro čtení: Kurzory nelze upravovat.
• Bez přetáčení: kurzor se může pohybovat pouze jedním směrem - dopředu, bez jejich výběru nebudete moci přeskakovat řádky.

Závěr

V tomto tutoriálu jsem vás seznámil se základy práce s uloženými procedurami a některými specifickými vlastnostmi, které jsou s tím spojené. Samozřejmě budete muset prohloubit své znalosti v oblastech, jako je bezpečnost, SQL výrazy a optimalizace, než se stanete skutečným guru procedur MySQL.

Měli byste vypočítat výhody použití uložených procedur ve vaší konkrétní aplikaci a poté vytvořit pouze nezbytné procedury. Obecně používám postupy; Dle mého názoru se vyplatí implementovat do projektů kvůli jejich zabezpečení, údržbě kódu a celkovému designu. Také mějte na paměti, že procedury MySQL jsou stále ve vývoji. Očekávejte vylepšení týkající se funkčnosti a vylepšení. Neváhejte se podělit o své názory.

Jednoduše řečeno, uložené procedury (“SP”) jsou procedury uložené v databázi (napsané pomocí SQL a dalších řídicích příkazů), které lze spustit databázovým strojem a volat z programového kódu, který běží s tímto enginem. """ Přečtěte si úplně

Uložené procedury v MySQL a PHP. Část 2

Taylor Wren (Taylor Ren), 03.01.2014

Vytvoření uložené procedury v MySQL

Vzhledem k tomu, že HP jsou uloženy na serveru, je doporučeno je vytvářet přímo na serveru, tzn. Neměli byste používat PHP nebo jiné programovací jazyky ke spouštění příkazů SQL k vytváření uložených procedur.

Podívejme se, jak vytvořit HP na serveru MySQL, jak pro něj vytvořit uživatele a jak mu přidělit oprávnění ke spuštění našeho HP. Poté zkontrolujeme správnost výsledku. K tomu použiji MySQL Workbench. Můžete použít i jiné programy (například PHPMyAdmin). Můžete si vybrat sadu nástrojů, která vám nejlépe vyhovuje.

Řekněme, že naše tabulka vypadá takto:

CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Pro našeho zaměstnance, který potřebuje statistické informace o platech (průměrné, maximální, minimální atd.) z této tabulky, vytvoříme uživatele „tr“ následovně:

VYTVOŘIT UŽIVATELE "tr"@"localhost" IDENTIFIKOVANÉ OD "mypass";

Nyní přidělme tomuto uživateli jediné oprávnění EXECUTE ve schématu, kde se nachází tabulka platů:

Udělte spuštění na hris.* na tr@`%`

Můžeme ověřit, že jsme přidělili správná oprávnění otevřením "Uživatelé a oprávnění" v MySQL Bench:

Nyní vytvoříme samotný HP následovně:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN vybrat avg(sal) do avg_sal z platu; KONEC

Po provedení tohoto příkazu v MySQL Workbench bude vytvořen avg_sal HP připravený k použití. Vrací průměrnou mzdu z platové tabulky.

Abychom ověřili, zda uživatel tr může skutečně provozovat HP a nemá přístup k tabulce platů, musíme se znovu připojit k serveru MySQL a přihlásit se jako tr. V MySQL Workbench to lze provést vytvořením dalšího připojení a zadáním požadovaného uživatele a jeho hesla.

Po připojení z pod tr si jako první všimneme, že uživatel nevidí vůbec žádné tabulky, vidí pouze HP:

Je zřejmé, že uživatel tr nemá přístup k žádné z tabulek (a tudíž nemůže vidět podrobné informace o platech z tabulky platů), ale může spustit námi vytvořený HP, který mu vrátí průměrnou mzdu pro společnost:

Volání avg_sal(@out); vyberte @out;

Zobrazí se průměrná mzda.

Takže jsme udělali všechny přípravné práce: vytvořili uživatele, přidělili mu oprávnění, vytvořili HP a otestovali jej. Nyní se podívejme, jak toto HP nazvat z PHP.

Volání uložené procedury z PHP

Při použití PDO je volání HP docela jednoduché. Zde je odpovídající kód PHP:

$dbms = "mysql"; // Nahraďte následující parametry připojení parametry vhodnými pro vaše prostředí: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$hostitel;dbname=$db"; $cn=nové PDO($dsn, $uživatel, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

Proměnná $res obsahuje průměrnou mzdu z platové tabulky. Nyní může uživatel provádět další zpracování výstupu pomocí PHP.

závěry

V tomto článku jsme se podívali na dávno zapomenutou komponentu databází MySQL: uložené procedury. Výhody používání HP jsou zřejmé, ale dovolte mi připomenout: Uložené procedury nám umožňují použít přísnější řízení přístupu k určitým datům, když to obchodní logika vyžaduje.

Kromě toho jsme si ukázali základní kroky při vytváření uložených procedur, uživatelů a přidělování příslušných oprávnění a ukázali, jak se z PHP volá HP.

Tento článek nepokrývá celé téma uložené procedury. Některé důležité aspekty, jako jsou I/O parametry, řídicí příkazy, kurzory, úplná syntaxe atd., nebyly v tomto krátkém článku popsány.

Pokud máte zájem, zanechte zde prosím komentář. V případě potřeby rádi nabídneme podrobnější články o užitečném a výkonném aspektu MySQL, uložených procedurách.

Taylor Wren

Taylor je nezávislý vývojář webových a desktopových aplikací se sídlem v Suzhou ve východní Číně. Začal s vývojovými nástroji Borland (C++Builder, Delphi), vydal knihu na InterBase. Od roku 2003 je certifikovaným expertem Borland. Poté jsem přešel na vývoj webu v typické konfiguraci LAMP. Později jsem začal pracovat s jQuery, Symfony, Bootstrap, Dart atd.

Předchozí publikace:

Od autora: Proč spíte v práci! Jste vzhůru a čekáte, až DBMS provede dotaz? Je tedy potřeba to urychlit. Použili jste uložené procedury MySQL? Nevíte jak? Tak se probuďte, protože teď budeme uvažovat přesně o tomto tématu.

Jaké další postupy existují?

Pokud máte fobii z lékařských postupů, pak tyto struktury „nejsou tím správným tématem“. Nemusíte se tedy bát. Ale vážně, uložené procedury jsou pohodlnou a užitečnou věcí pro „zdraví“ DBMS. Říká se jim také „uložené funkce MySQL“, ale to není úplně přesná definice. I když pojďme se vypořádat se vším v pořádku.

Uložené procedury mohou výrazně optimalizovat výkon serveru a zvýšit jeho rychlost, protože jejich kód se po prvním spuštění uloží do mezipaměti RAM. Pro všechna následující volání bude procedura načtena z mezipaměti, nikoli znovu odeslána k provedení.

V MySQL volání uložené procedury znamená, že dotazy zapsané do jejího kódu budou zpracovány pouze napůl. A to pouze v případě, že se změní hodnoty jejich parametrů. Ale ne všechno je tak dokonalé. Nejprve si popišme pozitivní aspekty používání postupů:

Zapouzdření funkčnosti – veškerý kód je uložen na jednom místě, což usnadňuje přístup dalším aplikacím. Tímto způsobem jsou uložené procedury podobné funkcím programu.

Izolace přístupu k datům – všichni uživatelé nemají přístup k řádkům tabulky, ale pouze k uloženým procedurám. Což zase zvyšuje úroveň zabezpečení všech dat.

Zvýšení rychlosti serveru ukládáním do mezipaměti a slučováním požadavků.

V MySQL jsou uložené procedury teoreticky struktury související s „vyššími záležitostmi“ – programováním DBMS. Takže vy a já (jako profesionálové) alespoň pomalu, ale... Vraťme se ale k postupům a popišme si negativní aspekty jejich použití:

Zvyšuje se zatížení databázového serveru - většina kódu procedury se provádí na straně serveru. Tento DBMS je postaven na modelu klient-server, který zahrnuje několik zařízení.

Manipulace s daty se stává složitější – při vývoji aplikací budete muset část kódu psát na straně klienta.

Proces přenosu databází na jiné koleje (DBMS) je stále složitější.

Postupy v phpMyAdmin

Nejprve se podívejme na použití uložených procedur v MySQL na příkladu phpMyAdmin. Tímto způsobem bude pro nás snazší porozumět tomuto typu struktury. Začněme!

Spustíme softwarový shell, vpravo vybereme testovací databázi. Moje databáze je světová. Poté v hlavní nabídce nahoře přejděte na kartu „Postupy“. Zde klikněte na „Přidat postup“.

Poté se objeví dialogové okno „Přidat postup“. Vyplňujeme všechna pole uvedená na obrázku. Zadejte název a typ procedury. Ve sloupci „Definice“ zadejte účet uživatele a v komentářích (nepovinné) si naznačíme, že se jedná pouze o příklad uložené procedury.

Již v této fázi se seznamujeme se zvláštnostmi syntaxe pro vytváření uložených procedur MySQL. Do pole „Definice“ zapíšeme tělo struktury. Všimněte si, že prováděný dotaz je mezi klíčová slova ZAČÁTEK a KONEC:

BEGIN SELECT "AHOJ, SLOVO!"; KONEC

ZAČÍT VYBERTE "AHOJ, SLOVO!" ;

Tento požadavek neprovádí žádné akce s databází, ale pouze zobrazuje nápis. Uvedli jsme to v poli „Přístup k datům SQL“.

Chcete-li dokončit vytváření našeho prvního postupu, klikněte na „OK“ v dolní části. Poté program zobrazí „zelenou“ zprávu indikující, že požadavek byl úspěšně dokončen. Jeho kód je uveden níže. V MySQL se uložené procedury a funkce vytvářejí pomocí speciálního příkazu CREATE PROCEDURE. Ale o tom později.

Nyní spusťte vytvořenou strukturu k provedení. Chcete-li to provést, v části „Postupy“ klikněte na odkaz „Spustit“. Ale jaká je to ostuda! Kam zmizela naše oblíbená „zelená“? Proč program „nadává“ a „křičí“, že nemá dostatek přidělené paměti?

Kam hledal autor této publikace...! Pardon, trochu zmatený. Ostatně autorem jsem já. Uklidněte se, hned vše napravíme! K této chybě dochází, protože hodnota parametru thread_stack v hlavním konfiguračním souboru zůstává nezměněna. Ve výchozím nastavení je pro každý stream přiděleno 128 Kb. Přidělený limit RAM je dostačující pro provádění jednoduchých dotazů, ale nestačí pro procedury.

To opět dokazuje, že na spouštění triggerů a uložených procedur v MySQL je vynaloženo více prostředků.

Přejděte do konfiguračního souboru my.ini a zvyšte limit RAM nastavený pro každé vlákno na 256 kb. Nyní znovu spusťte vytvořenou proceduru. Tentokrát vše proběhlo podle očekávání a program vrátil výsledek bez chyby.

Upozorňujeme, že volání se provádí pomocí příkazu CALL s uvedením názvu procedury a přijatých parametrů (v závorkách).

Složitější příklad

Ale přesto jsou možnosti phpMyAdmin vhodnější pro rychlé vytváření procedur. A k vývoji uložené procedury v MySQL s dynamickým počtem argumentů (například) budete potřebovat pohodlnější software. Proč:

phpMyAdmin nechce správně „rozumět“ procedurám, které nebyly vytvořeny pomocí speciálního konstruktoru.

Program nevykonává struktury spuštěné pod rootem a prázdným heslem a v Denveru je vytvoření nového uživatele a přihlášení do phpMyAdmin pod ním skutečný problém.

Pokud budete pečlivě sledovat mé publikace a splníte všechna „přání“ v nich uvedená, pak byste již měli mít nainstalovaného MySQL Administrator. V souvislosti s tím si stačí stáhnout MySQL Query Browser z tohoto odkazu. Je lepší používat tyto dva programy společně: v prvním vytvořte procedury a ve druhém je otestujte. Jít:

Vlevo nahoře přejděte na kartu „Katalog“.

Vyberte požadovanou databázi a v horním menu klikněte na „Uložené procedury“ a dole na „Vytvořit uložený proces“

V okně editoru, které se objeví, zadejte kód procedury a klikněte na „Execute SQL“.

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`() BEGIN deklarovat int; set a="SELECT COUNT(*) FROM města as"; if(a > 1000)PAK VYBERTE "<1000"; ELSE SELECT ">1000"; KONEC KDYŽ; KONEC

CREATE DEFINER = ` roman ` @ ` localhost ` PROCEDURE ` proc5 ` () ZAČÍT deklarovat int; nastavit a = "VYBRAT COUNT(*) Z města jako"; if (a > 1000 ) THEN VYBRAT"<1000" ; JINÝ VYBERTE ">1000" ; END IF ;

Možná jste si všimli, že v rámci procedury nepředáváme žádné hodnoty. Navíc může mít MySQL neznámý počet parametrů v uložené proceduře, které pak mohou být předány prostřednictvím nových deklarací proměnných umístěných uvnitř smyček.

Chcete-li zahájit postup, přejděte do prohlížeče dotazů MySQL. Nejprve zadejte svůj účet a heslo a poté vlevo v „Object Explorer“ najdeme složku s požadovanou databází. Zbytek sledu akcí je znázorněn na dalším obrázku.

Spuštění procedury v PHP

Nyní se podívejme, jak se v PHP nazývá uložená procedura MySQL. K tomu budeme muset mírně „překreslit“ kód našeho předchozího příkladu. Do procedury přidáme výstupní parametr a také změníme kód požadavku:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal) BEGIN SELECT COUNT(*) do col FROM city; KONEC

CREATE DEFINER = ` root ` @ ` localhost ` PROCEDURE ` proc6 ` (out col decimal) ZAČÍT SELECT COUNT (*) do sloupce FROM city;

Chcete-li volat z soubor PHP postup a výsledek výstup využijeme možností třídy PDOStament, vytvořené speciálně pro práci s databází přes SQL

Tato třída byla implementována relativně nedávno a PHP ji podporuje od verze 5.1.0. Před použitím vám doporučuji zkontrolovat verzi jazyka, který používáte, pomocí vestavěné funkce phpversion().