Хранимые процедуры (компонент Database Engine)
Хранимая процедура в 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 Encryption.
Повторное использование кода
Если какой-то код многократно используется в операции базы данных, то отличным решением будет произвести его инкапсуляцию в процедуры. Это устранит необходимость излишнего копирования того же кода, снизит уровень несогласованности кода и позволит осуществлять доступ к коду любым пользователям или приложениям, имеющим необходимые разрешения.
Более легкое обслуживание
Если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры. Уровень приложения остается незатронутым изменениями в схемах баз данных, связях или процессах.
повышение производительности.
По умолчанию компиляция процедуры и создание плана выполнения, используемого для последующих выполнений, производится при ее первом запуске Поскольку обработчику запросов не нужно создавать новый план, обычно обработка процедуры занимает меньше времени.
Если в таблицах или данных, на которые ссылается процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана может вызвать замедление работы процедуры. В этом случае перекомпиляция процедуры и принудительное создание нового плана выполнения может улучшить производительность.
Типы хранимых процедур
Определяемые пользователем маршруты
Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных Resource . Процедура может быть разработана в Transact-SQL или в качестве ссылки на метод Microsoft платформа .NET Framework CLR.
Временный диск
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.
Система
Системные процедуры включены в SQL Server. Физически они хранятся во внутренней скрытой базе данных Resource . Логически они отображаются в схеме sys каждой системной и пользовательской базы данных. В дополнение к этому, база данных msdb также содержит системные хранимые процедуры в схеме dbo . Эти процедуры используются для планирования предупреждений и заданий. Поскольку названия системных процедур начинаются с префикса sp_ , этот префикс не рекомендуется использовать при создании пользовательских процедур. Полный список системных процедур см. в разделе Системные хранимые процедуры (Transact-SQL).
SQL Server поддерживает системные процедуры, предоставляющие интерфейс от SQL Server до внешних программ для различных действий по обслуживанию. Эти расширенные процедуры имеют префикс xp_. Полный список расширенных процедур см. в разделе Общие расширенные хранимые процедуры (Transact-SQL).
Расширенные пользовательские процедуры
Расширенные процедуры позволяют создавать внешние подпрограммы на языке программирования, таком как C. Эти процедуры представляют собой библиотеки DLL, которые экземпляр SQL Server может динамически загружать и запускать.
Примечание
Расширенные хранимые процедуры будут удалены в следующей версии SQL Server. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Вместо них рекомендуется создавать процедуры CLR. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.
Связанные задачи
Описание задачи | Раздел |
Описывает создание хранимой процедуры. | Создание хранимой процедуры |
Описывает изменение хранимой процедуры. | Изменение хранимой процедуры |
Описывает удаление хранимой процедуры. | Удаление хранимой процедуры |
Описывает выполнение хранимой процедуры. | Выполнение хранимой процедуры |
Описывает предоставление разрешений на хранимую процедуру. | Предоставление разрешений на хранимую процедуру |
Описывает возврат данных из хранимой процедуры в приложение. | Возврат данных из хранимой процедуры |
Описывает перекомпиляцию хранимой процедуры. | Перекомпиляция хранимой процедуры |
Описывает переименование хранимой процедуры. | Изменение имени хранимой процедуры |
Описывает просмотр определения хранимой процедуры. | Просмотр определения хранимой процедуры |
Описывает просмотр зависимостей хранимой процедуры. | Просмотр зависимостей хранимой процедуры |