Хранимые процедуры (компонент Database Engine)

Хранимая процедура в SQL Server — это группа из одной или нескольких инструкций Transact-SQL или ссылка на метод Microsoft .NET Framework среды CLR. Процедуры аналогичны конструкциям в других языках программирования, поскольку обеспечивают следующее:

• обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;

• содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур;

• возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).

Преимущества хранимых процедур

В следующем списке описываются преимущества использования процедур.

• Снижение сетевого трафика между клиентами и сервером
  Команды в процедуре выполняются как один пакет кода. Это позволяет существенно сократить сетевой трафик между сервером и клиентом, поскольку по сети отправляется только вызов на выполнение процедуры. Без инкапсуляции кода, предоставляемой процедурой, по сети бы пришлось пересылать все отдельные строки кода.

• Большая безопасность
  Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.

  Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.

  При вызове процедуры через сеть виден только вызов на выполнение процедуры. Следовательно, злоумышленники не смогут видеть имена объектов таблиц и баз данных, внедрять свои инструкции Transact-SQL или выполнять поиск важных данных.

  Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». Поскольку входные данные параметра обрабатываются как литеральные значения, а не как исполняемый код, злоумышленнику будет труднее вставить команду в инструкции Transact-SQL в процедуре и создать угрозу безопасности.

  Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код. Дополнительные сведения см. в разделе Шифрование SQL Server.

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

• Более легкое обслуживание
  Если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры. Уровень приложения остается незатронутым изменениями в схемах баз данных, связях или процессах.

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

  Если в таблицах или данных, на которые ссылается процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана может вызвать замедление работы процедуры. В этом случае перекомпиляция процедуры и принудительное создание нового плана выполнения может улучшить производительность.

Типы хранимых процедур

• Пользовательские процедуры
  Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных 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. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.

Связанные задачи

Описание задачи	Раздел
Описывает создание хранимой процедуры.	Создание хранимой процедуры
Описывает изменение хранимой процедуры.	Изменение хранимой процедуры
Описывает удаление хранимой процедуры.	Удаление хранимой процедуры
Описывает выполнение хранимой процедуры.	Выполнение хранимой процедуры
Описывает предоставление разрешений на хранимую процедуру.	Предоставление разрешений на хранимую процедуру
Описывает возврат данных из хранимой процедуры в приложение.	Возврат данных из хранимой процедуры
Описывает перекомпиляцию хранимой процедуры.	Перекомпиляция хранимой процедуры
Описывает переименование хранимой процедуры.	Изменение имени хранимой процедуры
Описывает просмотр определения хранимой процедуры.	Просмотр определения хранимой процедуры
Описывает просмотр зависимостей хранимой процедуры.	Просмотр зависимостей хранимой процедуры

См. также

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