Хранимые процедуры (ядро СУБД)

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

Хранимая процедура в SQL Server — это группа одной или нескольких инструкций Transact-SQL или ссылка на метод microsoft платформа .NET Framework common runtime (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 common runtime language (CLR).

Временные процедуры

Временные процедуры — это один из видов пользовательских процедур. Временные процедуры похожи на постоянную процедуру, за исключением того, что они хранятся в tempdb. Существует два типа временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Локальные временные процедуры имеют единый знак# () в качестве первого символа их имен; они видны только текущему подключению пользователя, и они удаляются при закрытии подключения. Глобальные временные процедуры имеют два знака числа (##) в качестве первых двух символов их имен; они видны любому пользователю после создания, и они удаляются в конце последнего сеанса с помощью процедуры.

Системные

Системные процедуры включены в ядро СУБД. Они физически хранятся в внутренней, скрытой Resource базе данных и логически отображаются в sys схеме каждой системной и определяемой пользователем базы данных. Кроме того, msdb база данных также содержит системные хранимые процедуры в dbo схеме, которая используется для планирования оповещений и заданий. Так как системные процедуры начинаются с префикса sp_, рекомендуется не использовать этот префикс при именовании определяемых пользователем процедур. Полный список системных процедур см. в разделе "Системные хранимые процедуры" (Transact-SQL).

SQL Server поддерживает системные процедуры, которые предоставляют интерфейс от SQL Server к внешним программам для различных действий обслуживания. Эти расширенные xp_ процедуры используют префикс. Полный список расширенных процедур см. в разделе "Общие расширенные хранимые процедуры" (Transact-SQL).

Расширенный определяемый пользователем

Расширенные процедуры позволяют создавать внешние подпрограммы на языке программирования, например C. Эти процедуры представляют собой библиотеки DLL, которые экземпляр SQL Server может динамически загружать и запускать.

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

Связанный контент

• Хранимые процедуры CLR
• Отложенное разрешение имен