Някои инструменти, предоставени от двигателя на базата данни MySQL, са съхранени процедури, функции и спусък, които се използват за извършване на транзакции или операции като вмъкване или промяна на записи.
Съхранените процедури са малки програми, разработени в SQL код. Съхранена процедура е набор от SQL команди, които се съхраняват заедно с базата данни.
Предимството на съхранената процедура е, че можем да я създадем във всеки текстов редактор и дори на сървъра, тя се изпълнява от двигателя на базата данни и не е достъпна за потребителите, а само за администратора.
Съхранена процедура изпраща резултатите си до приложение, така че да ги показва на екрана, като се избягва претоварването на сървъра, в урока:
- MYSQL съхранени процедури - създаване, заявки и вмъкване на данни
Бях обяснил как да ги създам, тук ще добавим функции и тригери. Ще видим пример в база данни на недвижим имот, който ще наречем място за отдаване под наем и след това ще създадем таблиците.
- Структура на таблицата за таблица „недвижими имоти“ СЪЗДАВАЙТЕ ТАБЛИЦА, АКО НЕ СЪЩЕСТВАВА „недвижими имоти“ („id“ int (11) NOT NULL, „userid“ int (11) DEFAULT NULL, „idtype property“ int (6) DEFAULT “0 ', `price` decimal (10,2) DEFAULT' 0.00 ',` Commission` decimal (10,0) NOT NULL, `description` text,` highdate` date DEFAULT' 0000-00-00 ', `id на провинция` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `етаж и апартамент` varchar (100) DEFAULT NULL,` between_streets` текст, `idoperation` int (100 ) DEFAULT NULL, `featured` 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,` активиран` enum ('да', 'не') НЕ NULL DEFAULT 'si') ДВИГАТЕЛ = MyISAM AUTO_INCREMENT = 196 КАРТЕТА ПО подразбиране = latin1; - Индекси на таблицата `недвижими имоти` АЛТЕР ТАБЛИЦА` недвижими имоти` ДОБАВЯНЕ НА ОСНОВЕН КЛЮЧ (` id`);
Сега ще разработим съхранена процедура за всяка транзакция, за да запитваме, вмъкваме, променяме и изтриваме запис.
Можем да използваме Phpmyadmin или мениджър като Heidisql, който е безплатен и работи на Windows или Linux с Wine.
Създаваме съхранена процедура за запитване към таблицата за недвижими имоти:
DELIMITER // CREATE PROCEDURE pa_listainmuebles () BEGIN SELECT * FROM properties; END // DELIMITER;MYSQL разбира, че изявлението завършва с точка и запетая. The Изявление DELIMITER промяна на крайния знак на всеки друг знак, по конвенция // се използва за обозначаване на края на съхранената процедура, така че MySQL да не прекратява съхранената процедура, когато срещне първата точка и запетая.
Можем да отидем до Раздел Рутини за да видите всяка транзакция, която сме създали и оттам можем да модифицираме, изпълним, експортираме или изтрием кода.
За да изпълним процедура чрез съхранение, използваме Команда CALL от SQL раздел или също от език за програмиране като .NET или Java. След това извикваме съхранената процедура, създадена с командата.
CALL pa_listainmuebles ();
След това ще създадем съхранена процедура за вмъкване на свойство, за това ще ни трябват параметри тип IN, тоест ще присвоим данни и входни променливи на съхранената процедура за извършване на транзакция, в този случай ги запишете в базата данни.
DELIMITER // CREATE PROCEDURE pa_nuevoinmueble (IN id INT, IN userid INT, IN price DECIMAL, IN комисионна DECIMAL) BEGIN INSERT INTO property `(` id`, `userid`,` price`, `Commission`) VALUES (id, userid ), цена, комисионна) END // DELIMITER;
Увеличете
След това можем да изпълним съхранената процедура, като извикаме и зададем параметрите.
CALL `pa_newinmueble` ('12 ',' 15 ',' 10.00 ',' 0.05 ')Можем също да въвеждаме данни, като изпълняваме рутината от Phpmyadmin.
Увеличете
След това ще създадем съхранената процедура за редактиране на свойство от редактора на Phpmyadmin, в този случай ще променим само цената.
Можем да създаваме роли от полето Definer, където можем да зададем потребител, дефиниран в сървъра Mysql, в този случай основния потребител на локалния хост хост, така че да има достъп до съхранената процедура.
Ако искаме да го направим от SQL код, трябва да изпълним следните команди:
CREATE DEFINER = `root` @` localhost` ПРОЦЕДУРА `pa_editarinmueble` (IN` ново свойство` DECIMAL (10,2), IN` свойство id` INT (11)) НАЧАЛО АКТУАЛИЗИРАНЕ на свойството SET цена = ново свойство WHERE id = идентификатор на имот ; КРАЙПускате го и сте готови.
Използване на Trigger или Triggers в Mysql
Trigger или Trigger в MySQL е набор от SQL изрази, които зависят от съхранена процедура и се използват за автоматично изпълнение, когато се случи определено събитие в нашата база данни. Тези събития се задействат от транзакции или изявления като INSERT, UPDATE и DELETE.
Пример е, когато промяна се запише в регистър, ние автоматично правим резервно копие или записваме одитен файл, за да знаем какви данни са променени, кога и кой ги е променил. Те могат да се използват за всякакви манипулации, които засягат данните, за поддържане или генериране на нова информация.
Ще създадем таблица за одит на недвижими имоти по -долу:
CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDBЩе създадем тригер, който запазва съобщение при одит, ако някой промени цената на имот.
CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` СЛЕД АКТУАЛИЗАЦИЯ НА` недвижими имоти` ЗА ВСЕКИ РЕД ВЪВЕЖДАНЕ В одит (потребител, описание, дата) СТОЙНОСТИ (потребител (), CONCAT ('Цената на имота е променена', NEW.id, '(', OLD.price, ') by (', NEW.price, ')'), NOW ())Този тригер се изпълнява автоматично след актуализиране на цената, можем да включим още полета, ако желаем, с OLD ние посочваме полето със стойността преди модификацията и с NEW ние посочваме новата въведена стойност, с NOW () ние посочваме датата и текущото време.
Създаваме задействащ механизъм, който ще има след актуализация на свойствата като събитие, тоест, след като се случи актуализация в таблицата със свойства, в този случай ще добавим потребителя, извършил промяната, новата цена и предишната цена.
Извършвам актуализация на имот:
CALL `pa_editarinmueble` ('80000', '170')След това отиваме към одиторската таблица и можем да видим промяната:
Можем също да видим резултатите в доклад в печатна версия от Phpmyadmin. Можем да видим как са запазени данните, които идентифицират свойството, направената промяна и потребителят, който я е направил, имаме и датата и часа на промяната.
След това ще видим друго възможно приложение, ако имот се наеме, след което автоматично променя състоянието си на неактивен или ще го направим недостъпен.
За това трябва да имаме проста таблица, където да съхраняваме кой имот е нает, за практически пример няма да вземем много строгост в данните.
CREATE TABLE `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 'ДВИГАТЕЛ = InnoDB;След това ще създадем съхранената процедура, за да вмъкнем нов запис в таблицата за отдаване под наем.
CREATE DEFINER = `root` @` localhost` ПРОЦЕДУРА `pa_newrental` (IN` идентификатор на свойството` INT, В` идентификатор на наемател` INT) ЕЗИК SQL НЕ ОПРЕДЕЛЕН СЪДЪРЖА SQL SQL SIGURITY DEFINER COMMENT ““ INSERT INTO` rentals` (`id на имота `,` tenant id`) VALUES (идентификатор на наемател, идентификатор на наемател)
И след това спусъкът за промяна на свойствата се активира:
CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` AFTER INSERT ON` наеми` ЗА ВСЯКА РЕДА АКТУАЛИЗАЦИЯ недвижим имот SET активиран = 'не' където id = НОВО. PropertyidСлед това извикваме съхранената процедура, където присвояваме идентификатора на имота и идентификатора на клиента или наемателя, който наемам.
ОБАЖДАНЕ за нов наем (170.11)След това отиваме към таблицата с недвижими имоти и трябва да видим, че промяната на състоянието на активираното поле АКО е активна, за да НЕ е активна.
Видяхме предимствата на използването на тригер със съхранени процедури в MySQL за:
- Одитирайте и записвайте събития или дейности за промяна на данни в таблица.
- Променете състоянието на поле, като активирате или откажете разрешения и действия върху таблица
- Той също така позволява запазване на последователността на данните чрез изпълнение на действия според събития, които засягат една или повече таблици.
