Предыдущая лекция		Следующая лекция
Представления (View)	Содержание	Создание подключения к БД MySQL. Получение данных с сервера.

Хранимые процедуры. Триггеры. Транзакции.

Хранимые процедуры

Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.

За

Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Ограничитель (Delimiter) - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. В обычных запросах в качестве ограничителя используется символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую символ | в качестве ограничителя.

Триггеры

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

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

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

Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Общий вид синтаксиса для создания тригера:

CREATE TRIGGER 
    trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

где:

trigger_name - название триггера (уникальное в пределах БД);
trigger_time - время срабатывания триггера. BEFORE — перед событием. AFTER — после события;
trigger_event - событие:
- insert — событие возбуждается операторами insert, data load, replace;
- update — событие возбуждается оператором update;
- delete — событие возбуждается операторами delete, replace. Операторы DROPTABLE и TRUNCATE не активируют выполнение триггера;
Важно понимать, что trigger_event не представляет литеральный тип инструкции SQL, которая активизирует триггер. Например, триггер INSERT активизируется не только инструкцией INSERT, но и LOAD DATA, потому что обе инструкции вставляют строки в таблицу.
tbl_name — название таблицы;
trigger_body — выражение, которое выполняется при активации триггера. Если Вы хотите выполнять много инструкций, используйте операторную конструкцию BEGIN ... END. Это также дает возможность Вам использовать те же самые инструкции, которые являются допустимыми внутри сохраненных подпрограмм.

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.

Триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм "обновляемого представления".

Например: создадим две таблицы test и log, напишем триггер, который после добавления записи в таблицу test будет вести лог этого события:

-- таблица, за которой мы будем следить
CREATE TABLE `test` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `content` TEXT NOT NULL
);

-- лог
CREATE TABLE `log` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `msg` VARCHAR( 255 ) NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `row_id` INT( 11 ) NOT NULL
);

-- триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
   INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END;
|

Здесь оператор DELIMITER служит для определения литерала начала/окончания процедуры и может состоять более, чем из одного символа (необходимо выбирать разделитель, который не будет использоваться в процедуре).

На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов OLD и NEW. OLD.col_name указывает на столбец с именем col_name до изменения или удаления данных. NEW.col_name относится к колонке новой строки после вставки или существующей - сразу после её обновления.

Триггеры имеют несколько важных особенностей использования:

триггеры могут создаваться только пользователем с привилегией SUPER;
при использовании запроса, затрагивающего N - записей, триггер будет запускаться N - раз;
после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.

Например, напишем триггер, который при продаже товара будет автоматически уменьшать его количество на складе:

DELIMITER не указан, так как в триггере обошлись одним знаком ;, а он как раз и является стандартным разделителем SQL-инструкций

create trigger sale_item
before insert
on sales
for each row
 	update warehouse 
        set quantity=quantity - NEW.quantity 
        where id=NEW.item_id;

Но если подумать, то склад у нас не безразмерный, количество товара ограничено. Можно поправить предыдущий запрос, вставив проверку ухода количества в минус, но лучше написать триггер для таблицы склад (мало ли где ещё мы будем менять количество)

delimiter $$
CREATE trigger 
update_warehouse
before update 
on warehouse 
for EACH row begin
	if NEW.quantity<0 THEN 
		SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'На складе недостаточно товара';
	END if;
end;
$$

Здесь, если после изменения количество становится отрицательным, мы генерируем ошибку, которая откатывает всю транзакцию.

Транзакции

Содрано с хабра

Транзакция — это набор операций по работе с базой данных (БД), объединенных в одну атомарную пачку.

Транзакционные базы данных (базы, работающие через транзакции) выполняют требования ACID, которые обеспечивают безопасность данных. В том числе финансовых данных =) Поэтому разработчики их и выбирают.

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

Что такое транзакция

Транзакция — это архив для запросов к базе. Он защищает ваши данные благодаря принципу «всё, или ничего».

Представьте, что вы решили послать другу 10 файликов в мессенджере. Какие есть варианты:

Кинуть каждый файлик отдельно.
Сложить их в архив и отправить архив.

Вроде бы разницы особой нет. Но что, если что-то пойдет не так? Соединение оборвется на середине, сервер уйдет в ребут или просто выдаст ошибку...

В первом случае ваш друг получит 9 файлов, но не получит один.

Во втором не получит ничего. Нет промежуточных состояний. Или получил всё, или не получил ничего. Но зато если произошла ошибка, вы снова перешлете сообщение. И друг получит все файлики разом, не придется проверять «не потерялся ли кто».

Казалось бы, ну недополучил файлик, что с того? А если это критично? Если это важные файлики? Например, для бухгалтерии. Потерял один файлик? Значит, допустил ошибку в отчете для налоговой. Значит, огребешь штраф и большие проблемы! Нет, спасибо, лучше файлы не терять!

И получается, что тебе надо уточнять у отправителя:

— Ты мне сколько файлов посылал?

— 10

— Да? У меня только 9... Давай искать, какой потерялся.

И сидите, сравниваете по названиям. А если файликов 100 и потеряно 2 штуки? А названия у них вовсе не «Отчет 1», «Отчет 2» и так далее, а «hfdslafebx63542437457822nfhgeopjgrev0000444666589.xml» и подобные... Уж лучше использовать архив! Тогда ты или точно всё получил, или не получил ничего и делаешь повторную попытку отправки.

Так вот! Транзакция — это тот же архив для запросов. Принцип «всё, или ничего». Или выполнены все запросы, которые разработчик упаковал в одну транзакцию, или ни один.

Допустим, вы переводите все деньги с одной карточки на другую. Выглядит это "внутри" системы как несколько операций:

>delete from счет1 where счет = счет 1
>
>insert into счет2 values ('сумма')
>```
>
>Принцип «всё или ничего» тут очень помогает. Было бы обидно, если бы деньги со счета1 списались, но на счет2 не поступили... Потому что соединение оборвалось или вы в номере счета опечатались и система выдала ошибку...
>
>Но благодаря объединению запросов в транзакцию при возникновении ошибки зачисления мы откатываем и операцию списания. Деньги снова вернулись на счет 1!

Если говорить по-научному, то транзакция — упорядоченное множество операций, переводящих базу данных из одного согласованного состояния в другое. Согласованное состояние — это состояние, которое подходит под бизнес-логику системы. То есть у нас не остается отрицательный баланс после перевода денег, номер счета не «зависает в воздухе», не привязанный к человеку, и тому подобное.

### Как отправить транзакцию

Чтобы обратиться к базе данных, сначала надо открыть соединение с ней. Это называется коннект (от англ. connection, соединение). Коннект — это просто труба, по которой мы посылаем запросы.

Чтобы сгруппировать запросы в одну атомарную пачку, используем транзакцию. Транзакцию надо:

1. Открыть.
1. Выполнить все операции внутри.
1. Закрыть.

Как только мы закрыли транзакцию, труба освободилась. И ее можно переиспользовать, отправив следующую транзакцию.

Можно, конечно, каждый раз закрывать соединение с БД. И на каждое действие открывать новое. Но эффективнее переиспользовать текущие. Потому что создание нового коннекта — тяжелая операция, долгая.

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

Разработчик берет соединение из пула и отправляет по нему транзакцию. Как только транзакция закрывается (неважно, успешно она прошла или откатилась), соединение возвращается в пул, и его может использовать следующая бизнес-операция.

### Как открыть транзакцию

Зависит от базы данных. В Oracle транзакция открывается сама, по факту первой изменяющей операции. А в MySql надо явно писать «start transaction».
 
### Как закрыть транзакцию

Тут есть 2 варианта:

* COMMIT — подтверждаем все внесенные изменения;
* ROLLBACK — откатываем их;

И вся фишка транзакционной базы в том, что база сначала применяет запрос «виртуально», реально ничего в базе не изменив. Ты можешь посмотреть, как запрос изменит базу, ничего при этом не сохраняя.

Например, я пишу запрос:

sql insert into clients (name, surname) values ('Иван', 'Иванов'); -- добавь в таблицу клиентов запись с именем «Иван» и фамилиев «Иванов»


Запрос выполнен успешно, хорошо! Теперь, если я сделаю select из этой таблицы, прям тут же, под своим запросом — он находит Иванова! Я могу увидеть результат своего запроса.

Но! Если открыть графический интерфейс программы, никакого Иванова мы там не найдем. И даже если мы откроем новую вкладку в sql developer (или в другой программе, через которую вы подключаетесь к базе) и повторим там свой select — Иванова не будет.

А все потому, что я не сделал коммит, не применил изменения:

sql insert into clients (name, surname) values ('Иван', 'Иванов');

commit;


Я могу добавить кучу данных. Удалить полтаблицы. Изменить миллион строк. Но если я закрою вкладку sql developer, не сделав коммит, все эти изменения потеряются.

На самом деле это удобно. Ведь если ты выполняешь сложную операцию, можно посмотреть на результат. Например, удаляем тестовые данные. Написали кучу условий из серии:

>Если имя = «Тест»
>
>И фамилия = «Тестовый»
>
>...

Удалили. Делаем select count — посмотреть количество записей в таблице. А там вместо миллиона строк осталось 100 тысяч! Если база реальная, то это очень подозрительно. Врядли там было СТОЛЬКО тестовых записей.

Проверяем свой запрос, а мы там где-то ошиблись! Вместо «И» написали «ИЛИ», или как-то еще. Упс... Хорошо еще изменения применить не успели. Вместо коммита делаем rollback.

Тут может возникнуть вопрос — а зачем вообще нужен ROLLBACK? Ведь без коммита ничего не сохранится. Можно просто не делать его, и всё. Но тогда транзакция будет висеть в непонятном статусе. Потому что ее просто так никто кроме тебя не откатит.

Или другой вариант. Нафигачили изменений:

Удалить все строки, где имя «Иван»;

Поменять код города с 495 на 499;

... ```

Но видим, что операцию надо отменять. Проверочный select заметил, что база стала неконсистентной. А мы решили «Ай, да ладно, коммит то не сделали? Значит, оно и не сохранится». И вернули соединение в пул.

Следующая операция бизнес-логики берет это самое соединение и продолжает в нем работать. А потом делает коммит. Этот коммит относился к тем 3 операциям, что были внутри текущей транзакции. Но мы закоммитили еще и 10 других — тех, что в прошлый раз откатить поленились. Тех, которые делают базу неконсистентной...

Так что лучше сразу сделайте откат. Здоровей система будет!

Итого

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

Одной операции всегда соответствует одна транзакция, но в рамках одной транзакции можно совершить несколько операций (например, несколько разных insert можно сделать, или изменить и удалить данные...).

Чтобы отправить транзакцию к базе, нам нужно создать соединение с ней. Или переиспользовать уже существующее. Соединение называют также коннект (англ connection) — это просто труба, по которой отправляются запросы. У базы есть пул соединений — место, откуда можно взять любое и использовать, они там все свободные.

В некоторых системах транзакцию нужно открыть, в других она открывается сама. А вот закрыть ее нужно самостоятельно. Варианты:

COMMIT — подтверждаем все внесенные изменения;
ROLLBACK — откатываем их;

Делая комит, мы заканчиваем одну бизнес-операцию, и возвращаем коннект в пул без открытой транзакции. То есть просто освобождаем трубу для других. Следующая бизнес-операция берет эту трубу и фигачит в нее свои операции. Поэтому важно сделать rollback, если изменения сохранять не надо. Не откатите и вернете соединение в пул? Его возьмет кто-то другой и сделает коммит. Своих изменений, и ваших, неоткаченных.

Не путайте соединение с базой (коннект) и саму транзакцию. Коннект — это просто труба, операции (update, delete…) мы посылаем по трубе, старт транзакции и commit / rollback — это группировка операций в одну атомарную пачку.

Предыдущая лекция		Следующая лекция
Представления (View)	Содержание	Создание подключения к БД MySQL. Получение данных с сервера.

sql_trigger.md 28 KB Geçmiş Ham

Хранимые процедуры. Триггеры. Транзакции.