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

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

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

Сохраненные процедуры могут возвращаться наборы результатов, т.е. результаты ВЫБРАТЬ утверждение. Такие наборы результатов можно обрабатывать с помощью курсоры другими хранимыми процедурами путем связывания локатора набора результатов или приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоры, которые позволяют ему перебирать несколько строк в таблице. Операторы управления потоком хранимых процедур обычно включают ЕСЛИ, ПОКА, ПЕТЛЯ, ПОВТОРЕНИЕ, и ДЕЛО заявления и многое другое. Хранимые процедуры могут получать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, как и где объявлена ​​переменная.

Выполнение

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

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

или же

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

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

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

Система баз данныхЯзык реализации
КубридЯва
IBM DB2SQL PL (близко к SQL / PSM стандарт) или Ява
Жар-птицаPSQL (Fyracle также поддерживает части Oracle PL / SQL)
InformixЯва
Microsoft SQL ServerTransact-SQL и различные .NET Framework языки
MySQLсобственные хранимые процедуры, строго соблюдающие SQL / PSM стандарт
NuoDBSQL или же Ява
OpenLink VirtuosoВиртуальные процедуры SQL (VSP);[2] также расширяется с помощью Java, C и других языков программирования
OraclePL / SQL или же Ява
PostgreSQLPL / pgSQL, также может использовать собственные функциональные языки, такие как PL / Perl или PL / PHP.
SAP HANASQLScript или же р
Sybase ASETransact-SQL

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

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

Другое использование

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

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

  • Функция - это подпрограмма, написанная для выполнения определенных вычислений.
  • Скалярная функция возвращает только одно значение (или NULL), тогда как табличная функция возвращает (реляционную) таблицу, содержащую ноль или более строк, каждая строка с одним или несколькими столбцами.
  • Функции должны возвращать значение (используя ВОЗВРАЩАТЬСЯ ключевое слово), но для хранимых процедур это не обязательно.
  • Хранимые процедуры могут использовать ВОЗВРАЩАТЬСЯ ключевое слово, но без передачи значения.
  • Функции могут использоваться в ВЫБРАТЬ операторы, при условии, что они не обрабатывают данные. Однако процедуры не могут быть включены в ВЫБРАТЬ заявления.
  • Хранимая процедура может возвращать несколько значений с помощью ИЗ параметр или не вернуть значение.
  • Хранимая процедура экономит время компиляции запроса.
  • Хранимая процедура - это объект базы данных.
  • Хранимая процедура - это материальный объект.

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

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

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

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

Недостатки

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

Рекомендации

  1. ^ «Вызов хранимой процедуры из вашего приложения». Получено 11 сентября 2019.
  2. ^ «Глава 11. Руководство по языку процедур SQL». Документация OpenLink. Получено 11 сентября 2019.

внешняя ссылка