1) Повышение скорости работы БД

→ Процедуры хранятся в скомпилированном виде, а значит, СУБД не тратит время на компиляцию запроса при каждом его исполнении.
→ Команда для вызова хранимой процедуры значительно короче, чем запрос, содержащийся в коде приложения, поэтому требуется меньше времени и трафика на передачу операторов на сервер БД.
→ Быстродействие хранимых процедур обусловлено еще и тем, что после вызова они загружаются в память и выгружаются из нее с учетом алгоритма вытеснения по давности использования. В Oracle размер памяти для КЭШа хранимых процедур оказывается в файле Init.Ora.
→ С помощью автоматической или ручной перекомпиляции изменяют плана выполнения из-за изменения индексов и т.п. В СУБД SQL Server при создании процедуры предусмотрена опция WITH RECOMPILE, используемая как при создании, так и при вызове процедур.

2) Большая степень свободы

→ Хранимые процедуры поддерживают: входные и выходные параметры, локальные переменные, операторы условного ветвления, циклы, вызовы встроенных операторов ид других процедур, исполнение DDL-операторов.
→ Во многом хранимые процедуры похожи на процедуры языков программирования высокого уровня.
→ Хранимые процедуры могут быть вложенными. Так SQL Server допускает до 32 уровней вложения.

3) Упрощение кода приложения клиента

→ В приложении нет SQL-запросов, а значит, программисту не нужно писать код для их генерации.
→ Для вызова хранимой процедуры необходимо знать только имя и список параметров (аналогично вызову обычных функций/методов в теле приложения).
→ Такой подход сокращает размер кода и улучшает его читабельность, что положительно влияет на качество конечного продукта.

4) Безопасность

→ Использование хранимых процедур позволяет значительно снизить угрозу возникновения уязвимости типа SQL-injection (один из распространенных способов взлома сайтов и программ, работающих с БД, основанный на внедрении в запрос произвольного SQL-кода).
→ Кроме того, можно устанавливать права доступа к объектам БД для каждой хранимой процедуры, что также способствует повышению уровня безопасности приложения.

5) Защита приложения от изменений структуры БД

→ В процессе развития проекта может возникнуть необходимость в изменении структуры БД, например, добавить | удалить | переименовать таблицу или столбец.
→ Если приложение генерирует SQL-запросы, то необходимо внести изменения во все фрагменты кода, отвечающие за генерацию запросов.
→ Организация доступа через хранимые процедуры не требует внесения изменений в код приложения до тех пор, пока имя хранимой процедуры и список параметров (а также ожидаемый результат) остаются прежними

6) Снижение числа ошибок и упрощение отладки

Чаще всего ошибки в работе приложения с БД возникают по следующим причинам:
→ Приложение использует некорректные значения для генерации SQL-запроса.
→ SQL-выражение некорректно описывает ожидаемый результат (т.е. ошибка в запросе).
→ Фрагмент кода приложения, отвечающий за генерацию SQL-запроса, содержит ошибку и не способен правильно построить нужный запрос.

Трудности работы с процедурами

1) Проблемы совместимости
Если необходимо обеспечить легкую переносимость приложения на максимальное количество СУБД, то, вероятно, стоит отдать предпочтение динамической генерации запросов, так как хранимые процедуры поддерживаются не всеми СУБД.

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

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

Особенности использования процедур

• Хранимые процедуры служат только для доступа к данным (извлечение/ обновление/удаление) и ни для чего больше. Использование процедур в иных целях (проверка данных или генерация HTML) является ошибкой.

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

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

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

Комментарии запрещены.