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

Хранимая процедура ( также называемая prc , proc , storp , sproc , StoPro , StoredProc , StoreProc , sp или SP ) — это подпрограмма , доступная приложениям, которые обращаются к системе управления реляционными базами данных (RDBMS). Такие процедуры хранятся в словаре данных базы данных .

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

Хранимые процедуры могут возвращать наборы результатов , т. е. результаты оператора SELECT. Такие наборы результатов могут обрабатываться с помощью курсоров , другими хранимыми процедурами, путем связывания локатора набора результатов или приложениями. Хранимые процедуры также могут содержать объявленные переменные для обработки данных и курсоры, которые позволяют им проходить по нескольким строкам в таблице. Операторы управления потоком хранимых процедур обычно включают в себя операторы IF, WHILE, LOOP, REPEAT, CASEи и многое другое. Хранимые процедуры могут получать переменные, возвращать результаты или изменять переменные и возвращать их в зависимости от того, как и где объявлена переменная.

Выполнение

Хранимые процедуры похожи на пользовательские функции (UDF). Главное отличие состоит в том, что UDF можно использовать как любое другое выражение в операторах SQL, тогда как хранимые процедуры должны вызываться с помощью CALLоператора. ^[1]

Процедура ВЫЗОВА(...)

или

ВЫПОЛНИТЬ процедуру(...)

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

Растущее принятие хранимых процедур привело к введению процедурных элементов в язык SQL в стандартах SQL:1999 и SQL:2003 в части SQL/PSM . Это сделало SQL императивным языком программирования . Большинство систем баз данных предлагают фирменные и специфичные для поставщика расширения, превосходящие SQL/PSM. Существует стандартная спецификация для хранимых процедур Java , а также SQL/JRT .

Сравнение со статическим SQL

Накладные расходы: Поскольку операторы хранимых процедур хранятся непосредственно в базе данных, они могут полностью или частично устранить накладные расходы на компиляцию, которые обычно необходимы, когда программные приложения отправляют встроенные (динамические) запросы SQL в базу данных. (Однако большинство систем баз данных реализуют кэши операторов и другие методы, чтобы избежать повторной компиляции динамических операторов SQL.) Кроме того, хотя они избегают некоторых предварительно скомпилированных SQL, операторы увеличивают сложность создания оптимального плана выполнения, поскольку не все аргументы оператора SQL предоставляются во время компиляции. В зависимости от конкретной реализации и конфигурации базы данных, смешанные результаты производительности будут видны из хранимых процедур по сравнению с общими запросами или пользовательскими функциями.

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

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

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

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

Другие применения

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

Сравнение с функциями

Функция — это подпрограмма, написанная для выполнения определенных вычислений.
Скалярная функция возвращает только одно значение (или NULL), тогда как табличная функция возвращает (реляционную) таблицу, содержащую ноль или более строк, каждая строка содержит один или несколько столбцов.
Функции должны возвращать значение (используя RETURNключевое слово), но для хранимых процедур это не обязательно.
Хранимые процедуры могут использовать RETURNключевые слова, но без передачи значения.
Функции могут использоваться в SELECTоператорах, при условии, что они не выполняют манипуляции данными. Однако процедуры не могут быть включены в SELECTоператоры.
Хранимая процедура может возвращать несколько значений с использованием OUTпараметра или не возвращать ни одного значения.
Хранимая процедура экономит время компиляции запроса.
Хранимая процедура — это объект базы данных .
Хранимая процедура — это материальный объект.

Сравнение с подготовленными отчетами

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

Сравнение со смарт-контрактами

Смарт-контракт — это термин, применяемый к исполняемому коду, хранящемуся в блокчейне, в отличие от СУРБД. Несмотря на то, что механизмы консенсуса результатов выполнения публичных сетей блокчейнов принципиально отличаются от традиционных частных или федеративных баз данных, они выполняют якобы ту же функцию, что и хранимые процедуры, хотя обычно с ощущением транзакции ценности.

Недостатки

Языки хранимых процедур часто зависят от поставщика. Смена поставщиков баз данных обычно требует переписывания существующих хранимых процедур.
Изменения в хранимых процедурах сложнее отслеживать в системе контроля версий, чем в другом коде. Изменения должны быть воспроизведены как скрипты, которые будут сохранены в истории проекта, чтобы быть включенными, а различия в процедурах может быть сложнее объединить и правильно отслеживать.
Ошибки в хранимых процедурах не могут быть обнаружены на этапе компиляции или сборки в среде разработки приложения — то же самое происходит, если хранимая процедура пропала или была случайно удалена.
Языки хранимых процедур от разных поставщиков имеют разный уровень сложности.
Поддержка инструментов для написания и отладки хранимых процедур часто не так хороша, как для других языков программирования, но это зависит от поставщика и языка.
- Например, и PL/SQL, и T-SQL имеют специальные IDE и отладчики. PL/PgSQL можно отлаживать из различных IDE.

Ссылки

^ "Db2 12 - Программирование приложений и SQL - Вызов хранимой процедуры из вашего приложения". www.ibm.com . Получено 2022-05-26 .
^ "Глава 11. Руководство по языку процедур SQL". Документация OpenLink . Получено 11 сентября 2019 г.
^ "Глава 42. Процедурные языки". Документация PostgreSQL . 9 ноября 2023 г. Получено 20 ноября 2023 г.

Внешние ссылки

Часто задаваемые вопросы о хранимых процедурах в MySQL
Обзор поддержки процедурного языка PostgreSQL
Использование хранимой процедуры в Sybase ASE
Процедуры PL/SQL
Справочник языка PL/SQL для Oracle Database
Справочник по SQLScript SAP HANA