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

Хранимая процедура (также называемая прок , storp , sproc , StoPro , StoredProc , StoreProc , зр , или SP ) является подпрограммой доступны для приложений , которые имеют доступ к реляционной системы управления базами данных (СУБД). Такие процедуры хранятся в словаре данных базы данных .

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

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

Реализация

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

CALL procedure(...)

или

EXECUTE procedure(...)

Точная и правильная реализация хранимых процедур варьируется от одной системы баз данных к другой. Большинство основных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от системы базы данных хранимые процедуры могут быть реализованы на различных языках программирования , например 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-инъекций. Подготовленные операторы, хотя и являются более простыми и декларативными, обычно не пишутся для использования процедурной логики и не могут работать с переменными. Благодаря простому интерфейсу и реализации на стороне клиента подготовленные операторы более широко используются в СУБД.

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

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

Недостатки

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

использованная литература

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

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