Что такое хранимая процедура в sql

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

Храни́мая процеду́ра — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

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

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

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

Содержание

Реализация хранимых процедур

Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle — PL/SQL, в InterBase и Firebird — PSQL, в PostgreSQL — PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 — SQL/PL (англ.), в Informix — SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.

В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название — UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке .NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java. [1] В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix. [2]

В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ.  packages ). Пакет состоит из двух частей — спецификации (англ.  package specification ), в которой указывается определение хранимой процедуры, и тела (англ.  package body ), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации.

В СУБД IBM DB2 хранимые процедуры можно объединять в модули.

Назначение и преимущества хранимых процедур [3]

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

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

Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными — изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.

В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.

Возможности программирования

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

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

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

Эти функции безопасности позволяют изолировать от пользователя структуру базы данных, что обеспечивает целостность и надежность базы.

Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.

Что такое хранимая процедура?

Что такое хранимая процедура? Как они работают? Какова структура хранимой процедуры (вещи, которые каждый должен должен быть хранимой процедурой)?

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

Для создания хранимой процедуры синтаксис довольно прост:

Преимущество хранимых процедур заключается в том, что вы можете централизовать логику доступа к данным в одном месте, которое затем легко оптимизировать администраторам баз данных. Хранимые процедуры также имеют преимущество в плане безопасности, поскольку вы можете предоставлять права на выполнение хранимой процедуры, но пользователю не нужно иметь разрешения на чтение/запись для базовых таблиц. Это хороший первый шаг против внедрения SQL.

Хранимые процедуры имеют свои недостатки, в основном это обслуживание, связанное с вашей основной CRUD операцией. Допустим, для каждой таблицы у вас есть Вставка, Обновление, Удаление и хотя бы один выбор на основе первичного ключа, это означает, что в каждой таблице будет 4 процедуры. Теперь возьмите базу данных достойного размера из 400 таблиц, и у вас будет 1600 процедур! И это при условии, что у вас нет дубликатов, которые вы, вероятно, будете иметь.

Вот где использование ORM или другого метода для автоматической генерации ваших основных операций CRUD имеет массу достоинств.

Русские Блоги

Расширенные функции MYSQL [хранимые процедуры и функции]

1. Определение

Хранимые процедуры можно разделить на хранимые процедуры и функции.

1.1 Определение хранимых процедур

Хранимая процедураЭто набор операторов SQL для выполнения определенных функций. Хранимая процедура вызывается снова после первой компиляции в базе данных без повторной компиляции.Пользователь выполняет ее, указывая имя хранимой процедуры и задавая параметры (если хранимая процедура имеет параметры).

1.2 Определение функции

Сохраненная функция (сокращеннофункция) По сути, отличий от хранимой процедуры нет.

Просто функция имеет ограничение: она может возвращать только одну переменную, в то время как хранимая процедура может возвращать несколько. Функции могут быть встроены в SQL и могут вызываться в select, но хранимые процедуры — нет.

Во-вторых, создайте хранимые процедуры и функции.

Процесс создания хранимых процедур и функций очень похож.

2.1 Создание хранимой процедуры

CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

proc_parameter определяет список параметров хранимой процедуры, формат списка следующий:

Где in представляет входной параметр, out представляет выходной параметр, inout представляет как вход, так и выход; param_name представляет имя параметра; type представляет тип параметра

Тип может быть любым типом в базе данных MYSQL

Доступны следующие значения:

LANGUAGE SQL : Объясните, что часть подпрограммы_body состоит из операторов SQL, язык, поддерживаемый текущей системой, — это SQL, а SQL — единственное значение функции LANGUAGE.

[NOT] DETERMINISTIC : Укажите, правильный ли результат выполнения хранимой процедуры. ДЕТЕРМИНИСТИЧЕСКИЙ означает, что результат определен. Каждый раз, когда выполняется хранимая процедура, один и тот же ввод будет

[НЕ] ДЕТЕРМИНИСТИЧЕСКИЙ означает, что результат неопределен, и один и тот же вход может иметь разные выходные данные. Если значение не указано, по умолчанию используется [НЕ] ДЕТЕРМИНИСТИЧЕСКИЙ

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA: Укажите ограничение на использование операторов SQL в подпрограммах.

CONTAINS SQL указывает, что подпрограмма содержит операторы SQL, но не содержит операторов, считывающих и записывающих данные;

NO SQL указывает, что подпрограмма не содержит операторов SQL;

READS SQL DATA: указывает, что подпрограмма содержит операторы, которые читают данные;

MODIFIES SQL DATA указывает, что подпрограмма содержит операторы для записи данных.

По умолчанию система укажет CONTAINS SQL

SQL SECURITY : Укажите, кто имеет разрешение на выполнение. DEFINER означает, что только определитель может выполнять

INVOKER означает, что вызывающий с разрешением может выполнять. По умолчанию система обозначена как DEFINER

COMMENT’string ‘: информация комментария, которая может использоваться для описания хранимых процедур или функций.

подпрограмма_body — это содержимое кода SQL, вы можете использовать BEGIN . END, чтобы указать начало и конец кода SQL.

Следующий оператор создает хранимую процедуру для запроса статистики таблицы заказов.

Логика здесь
1. Сначала определите, существует ли хранимая процедура countOrder (), и отбросьте ее, если она есть.
2. Создайте хранимую процедуру countOrder ().
3. Выполните хранимую процедуру countOrder ().

Примечание: функция оператора «DELIMITER //» состоит в том, чтобы установить конец MYSQL в //, поскольку конец оператора MYSQL по умолчанию — точка с запятой; во избежание конфликта с концом оператора SQL в хранимой процедуре, вам нужно использовать DELIMITER, чтобы изменить конечный символ хранимой процедуры, и завершить хранимую процедуру с помощью «END //».
После определения хранимой процедуры используйте DELIMITER; восстановите терминатор по умолчанию. DELIMITER также может указывать другие символы в качестве терминатора! ! ! ! ! ! ! !

2.2 Создание функции

Создайте функцию с помощью оператора CREATE FUNCTION:

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

Тип RETURNS представляет тип возвращаемого значения функции; тело_программы — это тело функции, а тело функции должно содержать оператор значения RETURN.

2.3 Пример функции

Аналогично создаем функцию для расчета средней зарплаты

3. Сложные хранимые процедуры и функции

3.1 Использование переменных

Переменные можно объявлять и использовать в подпрограммах. Объем этих переменных находится в программах BEGIN . END

1. Определите переменные

Определить переменные в хранимой процедуре

DECLARE var_name[,varname]. date_type[DEFAULT VALUE];

var_name — это имя локальной переменной. Предложение DEFAULT VALUE предоставляет значение по умолчанию для переменной. Помимо объявления как константы, значение также может быть указано как выражение.

Если нет предложения DEFAULT, начальное значение равно NULL.

DECLARE MYPARAM INT DEFAULT 100;

2. Присвойте значения переменным.

После того, как переменная определена, присвойте ей значение, чтобы изменить значение переменной по умолчанию. MYSQL использует инструкцию SET для присвоения значения переменной.

Оператор SET в хранимой процедуре является расширенной версией общего оператора SET.

Переменная, которая должна быть установлена, может быть переменной в подпрограмме или глобальной серверной переменной, такой как системная переменная или пользовательская переменная.

Он запускает SET a = x, b = y, .

Объявите 3 переменные, var1, var2 и var3

DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;

MYSQL также может присваивать значения одной или нескольким переменным через SELECT . INTO

DECLARE NAME CHAR(50);
DECLARE id DECIMAL(8,2);
SELECT id,NAME INTO id ,NAME FROM t3 WHERE >

3.2 Использование управления процессом

Операторы управления потоком в MySQL: IF Заявление, CASE Заявление, LOOP Заявление, WHILE Заявление, LEAVE Заявление, ITERATE Заявление и REPEAT Заявление.

3.2.1 IF заявление

Формат синтаксиса следующий:

3.2.2 Заявление CASE

Оператор CASE имеет два грамматических формата, первый из которых выглядит следующим образом:

Второй выглядит следующим образом:

Примечание. Оператор CASE в сохраненной программе немного отличается от функции CASE, используемой непосредственно в запросе SELECT. Оператор CASE в сохраненной программе не может иметь ELSE NULL Пункт и использование END CASE Вместо END Прекратить.

3.2.3 Оператор LOOP

Синтаксис оператора LOOP следующий:

loop_label — это метка оператора LOOP, этот параметр можно не указывать. Оператор в LOOP выполнялся неоднократно, пока не вышел из цикла, и цикл не использовался LEAVE Заявление.

3.2.4 Заявление LEAVE

Оператор LEAVE используется для выхода из любого отмеченного оператора управления потоком, синтаксис следующий:

3.2.5 Оператор ITERATE

Оператор ITERATE сдвигает порядок выполнения в начало сегмента оператора.ITERATE может появляться только в операторах LOOP, REPEAT и WHILE. Синтаксис следующий:

С точки зрения непрофессионала, это эквивалентно тому, что в C ++ continue 。

3.2.6 Оператор REPEAT

Оператор REPEAT создает цикл с условной оценкой:

3.2.7 Оператор WHILE

Оператор WHILE также создает процесс цикла с условным суждением. Разница в том, что он оценивается первым при каждом выполнении тела цикла:

3.3 Определите условия и процедуры обработки

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

3.3.1 Определить условия

Также используются определенные условия DECLARE Заявление:

И sqlstate_value, и mysql_error_code могут представлять ошибки MySQL, например: ERROR 1064(42000) В значение sqlstate_value равно 42000, а значение mysql_error_code равно 1064.

Этот оператор определяет условия, требующие особой обработки. Он связывает имя с указанным условием ошибки, и это имя может использоваться в последующих обработчиках. Например: определение ‘ERROR 1064(42000)’ Имя ошибки: syntax_error 。

3.3.2 Определите обработчик

Синтаксис обработчика определения следующий:

handler_type: Указывает метод обработки ошибок и может принимать только следующие 3 значения.

  • CONTINUE : Не обрабатывать ошибку и продолжить выполнение;
  • EXIT : Выйти сразу после обнаружения ошибки;
  • UNDO : Отменить предыдущую операцию после обнаружения ошибки, MySQL временно не поддерживает ее.

condition_value: Указывает тип ошибки, который может иметь следующие значения:

  • SQLSTATE ‘sqlstate_value’
  • mysql_error_code
  • condition_name: имя настраиваемого условия
  • SQLWARNING : Сопоставить все коды ошибок SQLSTATE, начинающиеся с 01
  • NOT FOUND : Сопоставить все коды ошибок SQLSTATE, начинающиеся с 02
  • SQLEXCEPTION : Сопоставить все коды ошибок SQLSTATE, которые не фиксируются SQLWARNING или НЕ НАЙДЕНЫ.

sp_statement: Сегмент программного оператора, который указывает хранимую процедуру или функцию, которая должна быть выполнена при обнаружении определенной ошибки.

3.4 Использование курсора

Оператор запроса может возвращать несколько записей. Если объем данных очень велик, необходимо использовать курсор дляПо одномуПрочтите записи в наборе результатов запроса.

3.4.1 Объявить курсор

Курсор должен быть объявлен перед открытием, а используемые в нем переменные или условия должны быть объявлены до объявления курсора. Используется в MySQL DECLARE Ключевые слова для объявления курсора:

Оператор SELECT возвращает набор результатов, используемый для создания курсора.

3.4.2 Открыть курсор

Синтаксис открытия курсора следующий:

3.4.3 Использование курсора

к FETCH Ключевые слова читаются из курсора в переменную одно за другим:

Переменная var_name должна быть определена до объявления курсора.

3.4.4 Закройте курсор

Синтаксис закрытия курсора следующий:

Примечание:Курсор в MySQL можно использовать только в хранимых процедурах и функциях.

3.4.5 Пример

в workers На основе таблицы создайте хранимую процедуру для вывода имен всех сотрудников в городе в соответствии с введенным названием города.

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

В этом примере мы можем понять, как использовать в хранимой процедуре или функцииПеременные, курсоры и управление процессом.

3.5, изменить, удалить хранимые процедуры и функции

использовать ALTER Операторы могут изменять характеристики хранимых процедур или функций, синтаксис следующий:

sp_nameИмя хранимой процедуры или функции,characteristicХарактеристики указанной хранимой процедуры или функции такие же, как и значения параметров процесса создания.

использовать DROP Оператор для удаления хранимой процедуры или функции, синтаксис следующий:

4 Сравнение хранимых процедур и операторов SQL

Преимущество:

1. Повышение производительности
Операторы SQL анализируются и компилируются при создании процесса. Хранимая процедура предварительно скомпилирована. Когда хранимая процедура запускается в первый раз, оптимизатор запросов анализирует и оптимизирует ее и дает план хранения, который, наконец, сохраняется в системной таблице, так что эти накладные расходы могут быть сохранены, когда процедура выполнена.
2. Уменьшите накладные расходы на сеть.
При вызове хранимой процедуры вам нужно только указать имя хранимой процедуры и необходимую информацию о параметрах, тем самым уменьшая сетевой трафик.
3. Упростите перенос кода.
Специалисты по базам данных могут изменить хранимую процедуру в любое время, но это не влияет на исходный код приложения, что значительно улучшает переносимость программы.
4. Повышенная безопасность
1) Системный администратор может ограничить полномочия выполняемой хранимой процедуры, чтобы не допустить доступа неавторизованных пользователей к данным.
2) При вызове процесса по сети виден только вызов процесса выполнения. Следовательно, злоумышленники не могут видеть имена таблиц и объектов базы данных, встраивать собственные операторы Transact-SQL или искать ключевые данные.
3) Использование параметров процесса помогает избежать атак SQL-инъекций. Поскольку ввод параметров обрабатывается как буквальные значения, а не как исполняемый код, злоумышленнику будет сложнее вставлять команды в операторы Transact-SQL внутри процедуры и нарушать безопасность.
4) Процесс может быть зашифрован, что помогает скрыть исходный код.

Недостатки:

1. Хранимые процедуры требуют, чтобы специализированные разработчики баз данных поддерживали
2. Изменения в логике проектирования, изменение хранимых процедур не так гибко, как SQL

Почему хранимые процедуры относительно редко используются в практических приложениях?
Обычно при разработке проекта используется относительно мало хранимых процедур. Почему это так?
Причины анализа следующие:
1) Специального разработчика базы данных нет, обычные программисты выполняют операции с базой данных неполный рабочий день.
2) Программистам часто требуется только работать с программой для завершения доступа к данным, без необходимости разработки на базе базы данных.
3) Требования к проекту часто меняются, и операторы SQL удобно изменять, особенно когда речь идет об изменении логики.

Как выбрать между хранимой процедурой и оператором SQL?
Основываясь на реальном опыте работы с приложением, даются следующие рекомендации:

1. В некоторых проектах с высокой эффективностью или высокими требованиями к стандартам рекомендуется использовать хранимые процедуры.
2. Рекомендуется использовать параметризованные команды для общих проектов, что является компромиссом между хранимыми процедурами и операторами SQL.
3. Для некоторых алгоритмов, требующих относительно высоких требований и использующих несколько логик данных, рекомендуется использовать хранимые процедуры.

Добавить комментарий

Ваш адрес email не будет опубликован.