Хранимые процедуры (компонент 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