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

Относится к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)База данных SQL в Microsoft Fabric

Хранимая процедура в 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.

Повторное использование кода

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

Более легкое обслуживание

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

Улучшенная производительность

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

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

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

User-defined

Определяемую пользователем процедуру можно создать в определяемой пользователем базе данных или во всех системных базах данных, кроме Resource базы данных. Процедура может быть разработана в Transact-SQL или в качестве ссылки на общий язык среды выполнения .NET Framework (CLR).

Temporary

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

System

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

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

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

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

Note

Расширенные хранимые процедуры будут удалены в будущей версии SQL Server. Не используйте эту функцию при новой разработке и как можно скорее измените приложения, которые в настоящее время используют эту функцию. Вместо них рекомендуется создавать процедуры CLR. Этот метод представляет собой более надежную и безопасную альтернативу написанию расширенных хранимых процедур.

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

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

  • Last updated on