учебные пособия

Хранимые процедуры и триггеры в MySQL

  Дата публикации: May/2024
{title}

Некоторые инструменты, предоставляемые ядром базы данных MySQL, представляют собой хранимые процедуры, функции и триггеры, которые используются для выполнения транзакций или операций, таких как вставка или изменение записей.

Хранимые процедуры - это небольшие программы, разработанные в коде SQL. Хранимая процедура - это набор команд SQL, которые хранятся вместе с базой данных.

{title}

Преимущество хранимой процедуры заключается в том, что мы можем создать ее в любом текстовом редакторе и даже на сервере, она выполняется ядром базы данных и недоступна пользователям, а только администратору.

Хранимая процедура отправляет свои результаты в приложение, чтобы отображать их на экране, избегая перегрузки сервера, в учебнике:

  • Хранимые процедуры MYSQL - создание, запросы и вставка данных

Я объяснил, как их создавать, здесь мы добавим функции и триггеры или триггеры. Мы увидим пример в базе данных по недвижимости, которую мы назовем арендной платой, а затем создадим таблицы. 

 - Структура таблицы для таблицы «недвижимость» CREATE TABLE ЕСЛИ НЕ СУЩЕСТВУЕТ «недвижимость» (`id` int (11) NOT NULL, ` userid` int (11) DEFAULT NULL, `idtipoimueble` int (6) DEFAULT '0 ', `цена` десятичная (10.2) ПО УМОЛЧАНИЮ' 0.00 ', ` комиссия` десятичная (10.0) NOT NULL, текст `description`, ` dateHigh` date DEFAULT' 0000-00-00 ', `idprovincia` int (10) DEFAULT NULL, `idlocality` int (10) DEFAULT NULL, ` address` varchar (150) DEFAULT NULL, `floorydepto` varchar (100) DEFAULT NULL, ` entre_calles` text, `idoperacion` 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, `antiquity` varchar (100) DEFAULT NULL, ` mt2covers` int (11) DEFAULT NULL, `lot_face` int (11) DEFAULT NULL, ` активированный` enum ('yes', 'no') NOT NULL DEFAULT ' да ') ENGINE = MyISAM AUTO_INCREMENT = 196 CHARSET ПО УМОЛЧАНИЮ = latin1; - индексы таблицы `недвижимость` ALTER TABLE` недвижимость` ADD PRIMARY KEY (` id`);

{title}

Теперь мы разработаем хранимую процедуру для каждой транзакции для просмотра, вставки, изменения и удаления записи.

Мы можем использовать Phpmyadmin или такой менеджер, как Heidisql, который бесплатен и работает на Windows или Linux с Wine.

Мы создаем хранимую процедуру для просмотра таблицы недвижимости: 

 DELIMITER // CREATE PROCEDURE pa_listainmuebles () BEGIN SELECT * FROM properties; END // РАЗДЕЛИТЕЛЬ;
MYSQL понимает, что оператор заканчивается точкой с запятой. Оператор DELIMITER изменяет символ завершения на любой другой символ, по соглашению // используется для указания конца хранимой процедуры, так что MySQL не завершает хранимую процедуру при поиске первой точки с запятой.

{title}

Мы можем перейти на вкладку «Подпрограммы», чтобы увидеть каждую транзакцию, которую мы создали, и оттуда мы можем изменить, выполнить, экспортировать или удалить код.

{title}

Для выполнения хранимой процедуры мы используем команду CALL на вкладке SQL или также на языке программирования, таком как .NET или Java. Далее мы вызываем хранимую процедуру, созданную с помощью команды. 

 CALL pa_listainmuebles ();

{title}

Далее мы создадим хранимую процедуру для вставки свойства, для этого нам понадобятся параметры типа IN, то есть мы назначим данные и входные переменные хранимой процедуре для выполнения транзакции, в этом случае сохраняем их в базе данных. 

 РАЗДЕЛИТЕЛЬ // ПРОЦЕДУРА СОЗДАНИЯ pa_nuevoinmueble (IN ID INT, IN ID пользователя INT, В цене DECIMAL, В комиссии DECIMAL) НАЧАЛО ВСТАВИТЬ В здание` (`id`, ` user`, `price`, ` комиссия`) ЗНАЧЕНИЯ (id, id, цена, комиссия) КОНЕЦ // РАЗДЕЛИТЕЛЬ;

{title}

Затем мы можем выполнить хранимую процедуру, вызвав и назначив параметры. 

 ВЫЗОВ `pa_nuevoinmueble` ('12 ', ' 15 ', ' 10.00 ', ' 0.05 ')
Мы также можем ввести данные, запустив процедуру из Phpmyadmin.

{title}

Далее мы создадим хранимую процедуру для редактирования свойства из редактора Phpmyadmin, в этом случае мы только изменим цену.

{title}

Мы можем создавать роли из поля «Определитель», в котором мы можем назначить пользователя, определенного на сервере Mysql, в данном случае, корневого пользователя хоста localhost, чтобы он мог получить доступ к хранимой процедуре.
Если мы хотим сделать это из кода SQL, мы должны выполнить следующие команды: 

 CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_editarinmueble` (IN` pre-new` DECIMAL (10, 2), IN` real estate` INT (11)) НАЧАТЬ ОБНОВЛЕНИЕ real set SET price = pre-new WHERE id = idymueble; END
Вы запускаете его и уходите.

$config[ads_text5] not found

Использование триггера или триггеров в Mysql
Триггер или триггер в 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` ПОСЛЕ ОБНОВЛЕНИЯ ON` real estate` ДЛЯ КАЖДОЙ СТРОКИ ВСТАВИТЬ В аудит (user, description, date) VALUES (user (), CONCAT ('Цена объекта была изменена', NEW.id, '(', OLD.price, ') для (', NEW.price, ')'), NOW ())
Этот триггер автоматически выполняется после обновления цены, мы можем включить больше полей, если захотим, с помощью OLD мы указываем поле со значением до изменения, а с помощью NEW мы указываем новое введенное значение, с помощью NOW () мы указываем дату и текущее время

$config[ads_text6] not found

{title}

Мы создаем триггер, который будет иметь событие After Update для недвижимости, то есть после того, как обновление произойдет в таблице недвижимости, в этом случае мы добавим пользователя, который внес изменение, новую цену и предыдущую цену.

Я выполняю обновление для свойства: 

 ВЫЗОВ `pa_editarinmueble` ('80000', '170')
Затем мы переходим к таблице аудита и видим изменение:

{title}

Мы также можем увидеть результаты в отчете в виде печати от Phpmyadmin. Мы можем видеть, как данные, которые идентифицируют свойство, сделанные изменения и пользователя, который их сделал, были сохранены, у нас также есть дата и время, когда было сделано изменение.

Далее мы увидим другое возможное приложение, если свойство будет арендовано, то оно автоматически изменится с состояния на неактивное, или мы сделаем его недоступным.

Для этого у нас должна быть простая таблица, в которой хранится то, какое свойство арендуется, для практического примера мы не будем принимать много строгости в данных. 

 CREATE TABLE `rents` (` id` INT (10) NOT NULL, `idinmueble` INT (10) NOT NULL, ` tenant` INT (11) NOT NULL, первичный ключ (`id`)) COLLATE =" latin1_swedish_ci "ENGINE = InnoDB;
Далее мы создадим хранимую процедуру для вставки новой записи в таблицу аренды. 
 CREATE DEFINER = `root` @` localhost` ПРОЦЕДУРА `pa_new_rental` (IN` idymueble` INT, IN` tenant` INT) ЯЗЫК SQL НЕ ДЕТЕРМИНИСТИЧЕСКИЙ СОДЕРЖИТ КОММЕНТАРИЙ ОПРЕДЕЛИТЕЛЯ БЕЗОПАСНОСТИ SQL SQL 'INSERT INTO` rents` (`idinmueble`` tenant`) ЦЕННОСТИ (idinmueble, idinquilino)

{title}

И тогда активируется триггер для изменения недвижимости: 

 CREATE DEFINER = `root` @` localhost` TRIGGER `rents_after_insert` ПОСЛЕ INSERT ON` rents` НА КАЖДЫЙ СТРОК ОБНОВЛЕНИЕ недвижимости SET активировано =" нет ", где id = NEW.idinmueble
Затем мы вызываем хранимую процедуру, в которой мы назначаем идентификатор свойства и идентификатор клиента или арендатора, которого я арендую. 
 ЗВОНОК pa_new аренда (170.11)
Далее мы переходим к таблице недвижимости и видим, что активированное поле меняет статус, если оно активно, то НЕ активно.

{title}

Мы увидели преимущества использования триггера с процедурой, хранящейся в MySQL, для:

  • Аудит и запись событий или действий по изменению данных в таблице.
  • Измените статус поля, активировав или запретив разрешения и действия в таблице.
  • Это также позволяет сохранить согласованность данных, выполняя действия в соответствии с событиями, которые влияют на одну или несколько таблиц.
В другом уроке мы продолжим программирование условных и повторяющихся структур в хранимой процедуре.

  • 0

СТАТЬЯ ПО ТЕМЕ Увеличение текста и масштабирование Chromebook Chrome OS