Хранимые процедуры (компонент Database Engine)
Хранимая процедура в SQL Server — это группа из одной инструкции Transact-SQL или нескольких или ссылка на метод среды CLR Microsoft .NET Framework. Процедуры аналогичны конструкциям в других языках программирования, поскольку обеспечивают следующее:
обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;
содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур;
возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).
Преимущества хранимых процедур
В следующем списке описываются преимущества использования процедур.
Снижение сетевого трафика между клиентами и сервером
Команды в процедуре выполняются как один пакет кода. Это позволяет существенно сократить сетевой трафик между сервером и клиентом, поскольку по сети отправляется только вызов на выполнение процедуры. Без инкапсуляции кода, предоставляемой процедурой, по сети бы пришлось пересылать все отдельные строки кода.
Большая безопасность
Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.
Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.
При вызове процедуры через сеть виден только вызов на выполнение процедуры. Следовательно, злоумышленники не смогут видеть имена объектов таблиц и баз данных, внедрять свои инструкции Transact-SQL или выполнять поиск важных данных.
Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». Поскольку входные данные параметра обрабатываются как литеральные значения, а не как исполняемый код, злоумышленнику будет труднее вставить команду в инструкции Transact-SQL в процедуре и создать угрозу безопасности.
Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код. Дополнительные сведения см. в статье SQL Server Encryption.
Повторное использование кода
Если какой-то код многократно используется в операции базы данных, то отличным решением будет произвести его инкапсуляцию в процедуры. Это устранит необходимость излишнего копирования того же кода, снизит уровень несогласованности кода и позволит осуществлять доступ к коду любым пользователям или приложениям, имеющим необходимые разрешения.
Более легкое обслуживание
Если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры. Уровень приложения остается незатронутым изменениями в схемах баз данных, связях или процессах.
повышение производительности.
По умолчанию компиляция процедуры и создание плана выполнения, используемого для последующих выполнений, производится при ее первом запуске Поскольку обработчику запросов не нужно создавать новый план, обычно обработка процедуры занимает меньше времени.
Если в таблицах или данных, на которые ссылается процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана может вызвать замедление работы процедуры. В этом случае перекомпиляция процедуры и принудительное создание нового плана выполнения может улучшить производительность.
Типы хранимых процедур
Пользовательские процедуры
Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных Resource . Процедура может быть разработана либо на языке Transact-SQL, либо как ссылка на метод Microsoft .NET Framework среды CLR.
Временные процедуры
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.
Системные функции
Системные процедуры включены в SQL Server. Физически они хранятся во внутренней скрытой базе данных Resource . Логически они отображаются в схеме sys каждой системной и пользовательской базы данных. В дополнение к этому, база данных msdb также содержит системные хранимые процедуры в схеме dbo . Эти процедуры используются для планирования предупреждений и заданий. Поскольку названия системных процедур начинаются с префикса sp_ , этот префикс не рекомендуется использовать при создании пользовательских процедур. Полный список системных хранимых процедур см. в статье Системные хранимые процедуры (Transact-SQL)
SQL Server поддерживает системные процедуры, обеспечивающие интерфейс между SQL Server и внешними программами для выполнения различных действий по обслуживанию системы. Эти расширенные процедуры имеют префикс xp_. Полный список расширенных хранимых процедур см. в статье Основные расширенные хранимые процедуры (Transact-SQL).
Расширенные пользовательские процедуры
Расширенные процедуры позволяют создавать внешние подпрограммы на языках программирования, таких как С. Они представляют собой библиотеки DLL, которые могут динамически загружаться и выполняться экземпляром SQL Server .
Расширенные хранимые процедуры в будущих версиях SQL Serverбудут удалены. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Вместо них рекомендуется создавать процедуры CLR. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.
Хранимые процедуры
Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (stored procedure).
То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
И еще один важный аспект — производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.
Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC .
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
Создадим хранимую процедуру для извлечения данных из этой таблицы:
Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.
После имени процедуры должно идти ключевое слово AS.
Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:
После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures :
Хранимые процедуры в SQL
Доброго времени суток, уважаемые читатели. Продолжаем изучать основные элементы языка SQL и в этой статье разберемся с хранимыми процедурами. Напомню, что мы работаем в СУБД MySQL, если вы работаете в другой СУБД, то некоторые синтаксические конструкции могут отличаться, но суть остается такой же.
Общие сведения
Итак, хранимые процедуры в SQL — это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение — то, что мы делали в прошлых уроках вручную.
Особенностью процедур является то, что есть возможность передавать аргументы, (так же как и функциям в других языка) и выводить различные данные в зависимости от аргумента. Также, процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы.
Хранимые процедуры в MySQL
Хранимые процедуры в СУБД MySQL можно создать несколькими способами, мы кратко опишем 2 способа через утилиту phpmyadmin:
-
Через консоль SQL запросов
Также как и все запросы SELECT, INSERT и т.д код создания хранимых процедур можно прописывать на вкладке SQL. 
Итак, где создавать процедуры описали, а что конкретно прописывать пока непонятно. Как раз к этому и перейдем.
Создание процедур в SQL
Чтобы создать процедуру необходимо воспользоваться оператором CREATE PROCEDURE. После оператора следует указать имя процедуры, а затем в круглых скобках аргументы, если они имеются, вместе с указанием типа данных каждого аргумента. Примерно это будет выглядеть так:
Ну и сразу разберем простой пример, напишем процедуру, которая будет показывать нам список покупателей из города, который мы передадим в качестве аргумента.
Напомню, что мы работаем с уже созданной БД и ее структуру с данными можно посмотреть по этим ссылкам на предыдущие уроки:
Итак, весь код создания процедуры приведем сразу:
Мы используем 1 способ создания процедуры — через консоль SQL для нашей базы данных. Поясним некоторые моменты кода выше:
-
По умолчанию окончанием запроса служит точка с запятой. Оператор DELIMITER задает новый разделитель, то есть новый знак окончания запроса. Это необходимо для того, чтобы запрос создания процедуры заканчивался после ключевого слова END. В противном случае, запрос закончится на точке с запятой, после основного тела процедуры.
Процедура обязательно должна начинаться с BEGIN и заканчиваться END. Внутри процедуры используется уже знакомый запрос SELECT, который сравнивает город со значением аргумента.
Итак, процедура создана и хранится отдельно от таблиц или представлений. Посмотреть все процедуры можно в одноименной вкладке.
Вызов процедур в SQL
Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:
Таким образом, в выводе получим список покупателей, проживающих в Москве.
Операторы хранимых процедур
Итак, хранимые процедуры это достаточно мощный инструмент. И в SQL хранимые процедуры также могут использовать различные операторы, вложенные запросы и т.д. То есть, все, что мы изучали в предыдущих статьях по SQL.
Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как IF-ELSE, CASE и WHILE. Далее, приведем пример использования конструкции CASE.
Создать хранимую процедуру, которая выведет продавцов, оформивших заказы, по диапазонам. Если аргумент имеет значение «Маленькая суммы», то диапазон продаж от 0 до 1000, «Средние суммы» — от 1000 до 1500, «Большие суммы» — свыше 1500.
Вызвать созданную процедуру можно с тремя различными аргументами:
Соответственно, в выводе будут разные данные в зависимости от аргумента.
Примеры
1. Создать хранимую процедуру, которая определяет лучшего продавца по суммарному значению продаж за период дат, полученных в аргументах.
2. Создать хранимую процедуру, которая отобразит информацию о совершенных заказах, стоимость которых выше числа, заданного в аргументе.
Заключение
Итак, мы познакомились с хранимыми процедурами в SQL. Очевидно, что примеров на их использование можно придумать очень много. Конкретно эта статья посвящена созданию и освоению базовых принципов в работе с хранимыми процедурами в SQL программировании.