Базовый курс SQL. Хранимые процедуры

[к оглавлению]

Что это такое

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

• Студент получил оценку на экзамене. Выставляем её в таблицу grades
• Для того, чтобы заполнить строку в grades, понадобится создать строку в таблице экзаменов
• Экзамен проходит в рамках определённой сессии - новая строка в таблице sessions
• А сессия, в свою очередь, принадлежит определённому семестру

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

Как нам написать такой код?

• Конечно, можно записать все возможные варианты SQL-запросов и выполнять их каждый раз в разной комбинации, диктуемой ситуацией. Но это подразумевает, что каждый раз вам придётся выстраивать запросы вручную и следить за тем, чтобы не упустить из виду изменяющиеся условия
• А можно создать хранимую процедуру. Это будет своего рода командный файл: набор запросов в сохранённой последовательности

Поддержка в разных СУБД

Хранимые процедуры начали поддерживаться в MySQL с версии 5, а в SQLite и Access и вовсе не поддерживаются. Представленные ниже примеры будут соответствовать синтаксису SQL Server и Oracle.

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

Назначение хранимых процедур

Давайте рассмотрим основные примеры применения хранимых процедур:

• Упрощение сложных запросов: соединение их в один блок, запускающийся одной командой
• Сохранение целостности данных за счёт минимизации количества выполняемых операций. Чем меньше операций, тем меньше потенциальных ошибок, чем меньше ошибок, тем меньше вероятность внести неверные данные в базу и впоследствии наткнуться на противоречия. К тому же, если все разработчики проекта используют одинаковую процедуру, результаты более предсказуемы, т.к. исключается человеческий фактор
• Также снижается риск повреждения данных за счёт того, что прямого доступа посредством запросов к ним нет, а если изменения необходимы, они вносятся только в саму хранимую процедуру
• Обычно хранимые процедуры хранятся в БД в скомпилированном виде, это снижает время на их обработку, а значит, повышает производительность кода
• Хранимые процедуры дают возможность писать более гибкий и мощный код, реализовывать сложные операции с данными

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

Не можете написать - просто используйте

Хранимые процедуры написать сложнее, чем обычные SQL-запросы, велик шанс допустить ошибку. Также сложности могут возникнуть с переносимостью кода, т.к. синтаксис хранимых процедур сильно различается в разных СУБД. Поэтому администраторы нередко ограничивают доступ обычным пользователям на их создание. К счастью, это не запрещает использовать уже готовые процедуры, если таковые имеются в вашей базе данных.

Как запустить хранимую процедуру

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

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

EXECUTE AddNewGrade('5', 92527, 20119);

Данная хранимая процедура выполняет добавление новой оценки в таблицу grades и принимает 3 аргумента: значение оценки grade_value, идентификатор студента student_id и идентификатор экзамена exam_id. Аргументы передаются внутрь процедуры, обрабатываются там определённым образом и строка таблицы заполняется. Но в таблице есть ещё одно поле, которое не передано в качестве аргумента - это grade_id. В grades оно является первичным ключом и должно быть уникальным. Обычно, чтобы избежать ошибок, при создании идентификаторов для новых строк используют автоматическую генерацию ключей. В данном случае, это будет также выполнять хранимая процедура. Таким образом AddNewGrade:

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

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

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

Как создать хранимую процедуру

Следующая страница. Транзакции.
Оглавление.