Uložené procedury MySQL (Create, List, Alter, &Drop)

Úvod

Uložené procedury MySQL seskupují více úloh do jednoho a uložte úlohu na serveru pro budoucí použití.

Uložené procedury zjednodušují správu databáze a snižují síťový provoz. Například zadání dotazu na server MySQL zpracuje dotaz a vrátí výsledky. Použití uložených procedur uloží dotazy na server, takže je lze provést později.

V tomto kurzu se naučíte vytvářet, vypisovat, upravovat a rušit uložené procedury.

Předpoklady

• Nainstalován MySQL Server a MySQL Workbench
• Uživatelský účet MySQL s právy root

Co jsou uložené procedury v MySQL?

Uložené procedury MySQL jsou předkompilované Příkazy SQL uloženy v databázi. Jsou to podprogramy obsahující název, seznam parametrů a příkazy SQL.

Všechny relační databázové systémy podporují uložené procedury a nevyžadují žádné další balíčky runtime-environment.

Jak používat uložené procedury?

Chcete-li vyvolat uložené procedury, můžete použít CALL příkaz nebo jiné uložené procedury. Při prvním vyvolání uložené procedury ji MySQL vyhledá v katalogu databáze, zkompiluje kód a umístí jej do vyrovnávací paměti a provede jej.

Následné běhy ve stejné relaci provádějí uložené procedury z mezipaměti, takže jsou mimořádně užitečné pro opakující se úkoly.

Uložené procedury využívají parametry předávat hodnoty a přizpůsobovat výsledky. Parametry se používají k určení sloupců v tabulce, ve které dotaz pracuje a vrací výsledky.

Uložené procedury mohou také obsahovat IF , CASE a LOOP výkazy toku řízení které procedurálně implementují kód.

Vytvořit uloženou proceduru

Vytvořte uloženou proceduru dvěma způsoby:

1. Použijte prostředí MySQL

K vytvoření uložené procedury v MySQL použijte následující syntaxi:

DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN    
    SQL statements
END //
DELIMITER ;

Ve výchozím nastavení je syntaxe přidružena k používané databázi, ale můžete také použít syntaxi pro jinou databázi zadáním názvu databáze následujícím způsobem:database_name.procedure_name .

Zde je první DELIMITER argument nastaví výchozí oddělovač na // , zatímco poslední DELIMITER argument jej nastaví zpět na středník ; . Chcete-li použít více příkazů, zadejte různé oddělovače, například $$ .

Název procedury následuje za CREATE PROCEDURE argument. Za názvem procedury zadejte pomocí závorek parametry, které se mají v proceduře použít, název parametru, datový typ a délku dat. Jednotlivé parametry oddělte čárkou.

Režimy parametrů jsou:

• IN – Použijte k předání parametru jako vstupu. Když je definován, dotaz předá argument uložené proceduře. Hodnota parametru je vždy chráněna.
• OUT – Použijte k předání parametru jako výstupu. Hodnotu můžete změnit v rámci uložené procedury a nová hodnota je předána zpět volajícímu programu.
• INOUT – Kombinace IN a OUT parametry. Volající program předá argument a procedura může upravit INOUT parametr, předá novou hodnotu zpět programu.

Například:

Uloženou proceduru spusťte jejím voláním:

CALL procedure_name;

Dotaz vrátí výsledky pro uloženou proceduru.

2. Použijte MySQL Workbench

Dalším způsobem, jak vytvořit uloženou proceduru, je použít průvodce MySQL Workbench Wizard. Průvodce je intuitivní a zjednodušuje proces, protože nemusíte umisťovat oddělovače ani se starat o formát.

Postupujte takto:

Krok 1: Klikněte pravým tlačítkem na Uložené procedury v okně Navigátor MySQL Workbench a zvolte Vytvořit uloženou proceduru… spustíte průvodce.

Krok 2: Zadejte název procedury a zadejte kód do BEGIN … KONEC blokovat.

Krok 3: Zkontrolujte kód a klikněte na Použít .

Krok 4: Potvrďte provedení kliknutím na Použít a vytvořte postup kliknutím na Dokončit .

Krok 5: Proveďte postup, abyste zjistili, zda funguje. Vytvořte novou kartu SQL pro provádění dotazů.

Krok 6: CALL postup na záložce SQL a klikněte na Provést .

Pokud se nevrátí žádné chyby, MySQL provede uloženou proceduru a zobrazí výsledky.

Seznam uložených procedur

Existují tři způsoby, jak zobrazit seznam všech uložených procedur:

1. Použijte prostředí MySQL

Chcete-li získat seznam všech uložených procedur, ke kterým máte přístup, včetně jejich charakteristik, použijte následující syntaxi:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

SHOW PROCEDURE STATUS příkaz vrací dlouhý výstup. Příkaz zobrazuje názvy a charakteristiky uložených procedur, ke kterým máte na serveru přístup.

Procházejte výstup a najděte procedury aktuálně na serveru.

LIKE argument najde uložené procedury obsahující ve svém názvu konkrétní slovo. Použijte % nahradit libovolný počet znaků, včetně nuly.

Například:

WHERE argument umožňuje vypsat uložené procedury pouze v konkrétní databázi.

Například:

V tomto příkladu vrací příkaz pouze uložené procedury pro ’seznam_zákazníků databáze.

2. Použijte datový slovník

informační_schéma databáze obsahuje tabulku nazvanou rutiny , který obsahuje informace o uložených procedurách a funkcích souvisejících se všemi databázemi na aktuálním serveru MySQL.

Chcete-li zobrazit všechny uložené procedury pro databázi, použijte následující syntaxi:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'database_name';

3. Použijte MySQL Workbench

Pro přístup k prohlížení uložených procedur pomocí GUI použijte MySQL Workbench. Chcete-li zobrazit uložené procedury, postupujte takto:

Krok 1 :Poklepejte na databázi, kterou chcete použít, v Navigátoru sekce.

Krok 2 :Rozbalte Uložené procedury rozevírací položku.

Tato položka zobrazuje všechny uložené procedury pro aktuální databázi.

Změnit uloženou proceduru

Změnit uloženou proceduru znamená změnit charakteristiky postupu. Neexistuje žádné prohlášení v MySQL pro úpravu parametrů nebo těla uložené procedury. Chcete-li změnit parametry nebo tělo, zrušte uloženou proceduru a vytvořte novou.

Uloženou proceduru můžete změnit dvěma způsoby:

1. Použijte prostředí MySQL

Změňte charakteristiku procedury pomocí ALTER PROCEDURE prohlášení. Můžeme například přidat komentář k postupu, který jsme vytvořili dříve. Syntaxe je:

ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';

2. Použijte MySQL Workbench

MySQL Workbench GUI umožňuje uživatelům měnit uloženou proceduru, kde uživatelé mohou přidávat parametry nebo měnit kód. MySQL Workbench klesá existující uloženou proceduru a vytvoří nový po provedení změn.

Postupujte takto:

Krok 1: V části Navigátor klikněte pravým tlačítkem na uloženou proceduru, kterou chcete upravit. Vyberte Změnit uloženou proceduru… položka.

Krok 2: Když se karta otevře, proveďte požadované změny ve stávající uložené proceduře a klikněte na Použít .

Krok 3: Okno kontroly skriptu SQL zobrazí se proces – odstranění existující uložené procedury a vytvoření nové obsahující změny.

Klikněte na Použít a poté Dokončit v dalším okně pro spuštění skriptu.

Zrušte uloženou proceduru

Chcete-li vypustit (smazat) proceduru:

1. Použijte prostředí MySQL

Odstraňte uloženou proceduru ze serveru pomocí DROP PROCEDURE prohlášení.

Základní syntaxe je:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

IF EXISTS parametr zruší uloženou proceduru, pouze pokud na serveru existuje. Místo stored_procedure_name zadejte název uložené procedury syntaxe.

Například:

Protože neexistuje žádná procedura s názvem „test “ na serveru výstup uvádí, že bylo ovlivněno 0 řádků a že zadaný postup neexistuje.

Vypuštění neexistující procedury bez IF EXISTS parametr vrací chybu.

2. Použijte MySQL Workbench

Chcete-li zrušit uloženou proceduru pomocí MySQL Workbench, postupujte takto:

Krok 1: Rozbalte položku Uložené procedury v části Navigátor. Klikněte pravým tlačítkem na uloženou proceduru, kterou chcete odstranit, a zvolte Drop Stored Procedure… v kontextové nabídce.

Krok 2: V potvrzovacím okně klikněte na Drop Now pro smazání uložené procedury.

Tato akce trvale smaže postup.

Výhody a nevýhody uložených procedur MySQL

Uložené procedury mají několik výhod a nevýhod, protože se přizpůsobují konkrétním potřebám. Níže jsou uvedeny některé výhody a nevýhody.

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

Výhody uložených procedur jsou:

Snížení síťového provozu

Uložené procedury pomáhají snížit síťový provoz mezi aplikacemi a serverem MySQL tím, že veškerou programovací logiku ponechávají na serveru. Namísto odesílání více výsledků dotazů přes síť, aplikace odesílají pouze název procedury a vstup parametru.

Vylepšené zabezpečení

Správce databáze uděluje aplikacím oprávnění volat a přistupovat pouze ke konkrétním uloženým procedurám, aniž by jim uděloval přímý přístup k tabulkám. Uložené procedury pomáhají předcházet útokům vkládání skriptů, protože vstupní parametry jsou považovány za hodnoty, nikoli jako spustitelný kód.

Centralizovaná obchodní logika

Uložené procedury zapouzdřují obchodní logiku opakovaně použitelnou více aplikacemi. To pomáhá omezit duplikování stejné logiky v mnoha různých aplikacích a činí databázi konzistentnější.

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

Nevýhody uložených procedur jsou:

Využití zdrojů

Použití mnoha uložených procedur a logických operací způsobí, že se využití paměti a CPU výrazně zvýší pro každé připojení.

Žádná přenositelnost

Není snadné přenést uložené procedury napsané v určitém jazyce z jedné instalace do druhé. Spoléhání se na uloženou proceduru také váže uživatele ke konkrétní databázi.

Odstraňování problémů a testování

MySQL neposkytuje nástroje pro testování a ladění uložených procedur, takže může být obtížné je ladit. Vývoj a údržba uložených procedur vyžaduje rozsáhlé znalosti. To je výzva pro nové vývojáře a vede k dalším nákladům na údržbu.