Справочник вопросов и ответов
QUOR - электронный справочник

Сохраненная процедура - Stored procedure

Тег: Другие предметы

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

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

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

Содержание

  • 1 Реализация
  • 2 Сравнение со статическим SQL
  • 3 Другое использование
  • 4 Сравнение с функциями
  • 5 Сравнение с подготовленными операторами
  • 6 Сравнение со смарт-контрактами
  • 7 Недостатки
  • 8 Ссылки
  • 9 Внешние ссылки

Реализация

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

процедура CALL (...)

или

EXECUTE procedure (...)

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

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

Система баз данныхязык реализации
CUBRID Java
IBM DB2 SQL PL (близкий к стандарту SQL / PSM ) или Java
Firebird PSQL (Fyracle также поддерживает части Oracle PL / SQL)
Informix Java
Microsoft SQL Server Transact-SQL и различные языки .NET Framework
MySQL собственные хранимые процедуры, тесно связанные с SQL / PSM стандартный
NuoDB SQL или Java
OpenLink Virtuoso Virtuoso SQL Rules (VSP); также расширяется с помощью Java, C и других языков программирования
Oracle PL / SQL или Java
PostgreSQL PL / pgSQL, также может использовать собственные языки функций, такие как как PL / Perl или PL / PHP
SAP HANA SQLScript или R
Sybase ASE Transact-SQL

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

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

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

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

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

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

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

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

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

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

Недостатки

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

Ссылки

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

284