Některé nástroje poskytované databázovým strojem MySQL jsou uložené procedury, funkce a spoušť, které se používají k provádění transakcí nebo operací, jako je vkládání nebo úprava záznamů.
Uložené procedury jsou malé programy vyvinuté v kódu SQL. Uložená procedura je sada příkazů SQL, které jsou uloženy společně s databází.
Výhodou uložené procedury je, že ji můžeme vytvořit v libovolném textovém editoru a dokonce i na serveru, provádí ji databázový stroj a není přístupná uživatelům, ale pouze správci.
Uložená procedura odešle své výsledky do aplikace, aby je zobrazila na obrazovce, aby se předešlo přetížení serveru, v tutoriálu:
- MYSQL uložené procedury - tvorba, dotazy a datové přílohy
Vysvětlil jsem, jak je vytvořit, zde přidáme funkce a spouště. Uvidíme příklad v databázi nemovitostí, které budeme říkat nájemné a poté vytvoříme tabulky.
- Struktura tabulky pro tabulku `nemovitost` VYTVOŘIT TABULKU, KDYŽ NEEXISTUJE` nemovitost` (`id` int (11) NOT NULL,` userid` int (11) DEFAULT NULL, `idtype property` int (6) DEFAULT '0 ', `cena` desetinná (10,2) VÝCHOZÍ' 0,00 ',` provize` desetinná (10,0) NENÍ NULÁ, text popisu, `datum vysokého data VÝCHOZÍ' 0000-00-00 ',` provincie id` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `floor and apartment` varchar (100) DEFAULT NULL,` Between_streets` text, `idoperation` int (100 ) DEFAULT NULL, `feature` char (3) DEFAULT 'no',` image1` varchar (255) DEFAULT NULL, `image2` varchar (255) DEFAULT NULL,` image3` varchar (255) DEFAULT NULL, `image4` varchar (255) DEFAULT NULL, `old` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL,` activated` enum ('yes', 'no') NOT NULL DEFAULT 'si') ENGINE = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Indexy tabulky `nemovitost` ALTER TABLE` nemovitost` PŘIDAT PRIMÁRNÍ KLÍČ (` id`);
Nyní pro každou transakci vyvineme uloženou proceduru pro dotaz, vložení, úpravu a odstranění záznamu.
Můžeme použít Phpmyadmin nebo správce jako Heidisql, který je zdarma a funguje na Windows nebo Linuxu s Wine.
Vytvoříme uloženou proceduru pro dotaz na realitní tabulku:
DELIMITER // VYTVOŘIT POSTUP pa_listainmuebles () ZAČÍT VÝBĚR * FROM vlastnosti; KONEC // DELIMITER;MYSQL chápe, že příkaz končí středníkem. The Prohlášení DELIMITER změnit koncový znak na jakýkoli jiný znak, podle konvence // se používá k označení konce uložené procedury, aby MySQL neuložilo uloženou proceduru při setkání s prvním středníkem.
Můžeme jít do Karta rutiny abychom viděli každou transakci, kterou jsme vytvořili, a odtud můžeme kód upravit, spustit, exportovat nebo odstranit.
K provedení procedury uložením používáme CALL příkaz z Karta SQL nebo také z programovacího jazyka, jako je .NET nebo Java. Dále vyvoláme uloženou proceduru vytvořenou příkazem.
CALL pa_listainmuebles ();
Dále vytvoříme uloženou proceduru pro vložení vlastnosti, k tomu budeme potřebovat parametry typu IN, to znamená, že uložené transakci přiřadíme data a vstupní proměnné k provedení transakce, v tomto případě je uložíme do databáze.
DELIMITER // VYTVOŘIT POSTUP pa_nuevoinmueble (IN id INT, IN userid INT, IN price DECIMAL, IN Commission DECIMAL) BEGIN INSERT INTO property` (`id`,` userid`, `price`,` provision`) VALUES (id, userid ), cena, provize) KONEC // DELIMITER;
ZVĚTŠIT
Poté můžeme uloženou proceduru spustit vyvoláním a přiřazením parametrů.
CALL `pa_newinmueble` ('12 ',' 15 ',' 10,00 ',' 0,05 ')Můžeme také zadat data spuštěním rutiny z Phpmyadminu.
ZVĚTŠIT
Dále vytvoříme uloženou proceduru pro úpravu vlastnosti z editoru Phpmyadmin, v tomto případě pouze upravíme cenu.
Můžeme vytvářet role z pole Definer, kde můžeme přiřadit uživatele definovaného na serveru MySQL, v tomto případě kořenového uživatele hostitele localhost, aby měl přístup k uložené proceduře.
Pokud to chceme udělat z kódu SQL, musíme spustit následující příkazy:
CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_editarinmueble` (IN` new property` DECIMAL (10,2), IN` property id` INT (11)) BEGIN UPDATE property SET price = new property WHERE id = property id; KONECSpustíte to a máte hotovo.
Použití Trigger nebo Triggers v MySQL
Spouštěč nebo Trigger v MySQL je sada příkazů SQL, které závisí na uložené proceduře a používají se ke spuštění automaticky, když v naší databázi dojde k určité události. Tyto události jsou spouštěny transakcemi nebo příkazy jako INSERT, UPDATE a DELETE.
Příkladem je, když je změna uložena v registru, automaticky provedeme zálohu nebo zaznamenáme soubor auditu, abychom věděli, jaká data byla změněna, kdy a kdo je změnil. Lze je použít k jakékoli manipulaci, která ovlivňuje data, k podpoře nebo generování nových informací.
Vytvoříme níže uvedenou tabulku auditu nemovitostí:
CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDBVytvoříme spouštěč, který uloží zprávu v auditu, pokud někdo změní cenu nemovitosti.
CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` PO UPDATE ON` real estate` FOR EACH ROW INSERT INTO audit (user, description, date) VALUES (user (), CONCAT ('Real estate price changed', NEW.id, '(', OLD.price, ') od (', NEW.price, ')'), NYNÍ ())Tento spouštěč se spouští automaticky po aktualizaci ceny, na přání můžeme zahrnout více polí, u STARÉ zadáme pole s hodnotou před úpravou a u NOVÉ zadáme novou zadanou hodnotu, TEĎ () určíme datum a aktuální čas.
Vytvoříme spouštěč, který bude mít po aktualizaci na vlastnosti jako událost, to znamená, že po aktualizaci v tabulce vlastností, v takovém případě přidáme uživatele, který provedl úpravu, novou cenu a předchozí cenu.
Provádím aktualizaci nemovitosti:
CALL `pa_editarinmueble` ('80000', '170')Poté přejdeme k tabulce auditu a vidíme změnu:
Výsledky můžeme také vidět ve zprávě v tištěném zobrazení z Phpmyadmin. Vidíme, jak byla uložena data identifikující vlastnost, provedenou změnu a uživatele, který ji provedl, máme také datum a čas, kdy byla změna provedena.
Dále uvidíme další možnou aplikaci, pokud je nemovitost pronajata, pak automaticky změní její stav na neaktivní nebo ji učiníme nedostupnou.
K tomu musíme mít jednoduchou tabulku, kam uložit, který majetek je pronajatý, pro praktický příklad nebudeme v datech příliš přísní.
VYTVOŘIT TABULKU `rentals` (` id` INT (10) NOT NULL, `property id` INT (10) NOT NULL,` tenant id` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci 'ENGINE = InnoDB;Dále vytvoříme uloženou proceduru pro vložení nového záznamu do tabulky nájemného.
CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_newrental` (IN` property id` INT, IN` tenant id` INT) LANGUAGE SQL NOT DETERMINIST CONTAINS SQL SQL SECURITY DEFINER COMMENT '' INSERT INTO` rentals` (`property id `,` tenant id`) HODNOTY (id tenanta, tenant id)
A pak aktivační událost pro úpravu vlastností:
CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` PO INSERT ON` rentals` FOR EACH ROW UPDATE real estate SET activated = 'no' where id = NEW. PropertyidPoté vyvoláme uloženou proceduru, kde přiřadíme id nemovitosti a id klienta nebo nájemce, kterého si pronajmu.
CALL pro nový pronájem (170.11)Dále přejdeme k tabulce nemovitostí a měli bychom vidět, že aktivované pole změní stav, pokud je aktivní, NENÍ aktivní.
Viděli jsme výhody použití spouště s uloženými procedurami v MySQL pro:
- Auditujte a zaznamenávejte události nebo činnosti změny dat v tabulce.
- Změňte stav pole aktivací nebo zamítnutím oprávnění a akcí v tabulce
- Umožňuje také zachování konzistence dat prováděním akcí podle událostí, které ovlivňují jednu nebo více tabulek.
