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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Связанные материалы

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