Хранимые процедуры (компонент Database Engine)Stored Procedures (Database Engine)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Хранимая процедура в SQL ServerSQL Server — это группа из одной или нескольких инструкций Transact-SQLTransact-SQL или ссылка на метод MicrosoftMicrosoft .NET Framework.NET Framework среды CLR.A stored procedure in SQL ServerSQL Server is a group of one or more Transact-SQLTransact-SQL statements or a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method. Процедуры аналогичны конструкциям в других языках программирования, поскольку обеспечивают следующее:Procedures resemble constructs in other programming languages because they can:

  • обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;Accept input parameters and return multiple values in the form of output parameters to the calling program.

  • содержат программные инструкции, которые выполняют операции в базе данных,Contain programming statements that perform operations in the database. включая вызов других процедур;These include calling other procedures.

  • возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).Return a status value to a calling program to indicate success or failure (and the reason for failure).

Преимущества хранимых процедурBenefits of Using Stored Procedures

В следующем списке описываются преимущества использования процедур.The following list describes some benefits of using procedures.

Снижение сетевого трафика между клиентами и серверомReduced server/client network traffic
Команды в процедуре выполняются как один пакет кода.The commands in a procedure are executed as a single batch of code. Это позволяет существенно сократить сетевой трафик между сервером и клиентом, поскольку по сети отправляется только вызов на выполнение процедуры.This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Без инкапсуляции кода, предоставляемой процедурой, по сети бы пришлось пересылать все отдельные строки кода.Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

Большая безопасностьStronger security
Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам.Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных.The procedure controls what processes and activities are performed and protects the underlying database objects. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды.The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя.For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу.To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы.Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

При вызове процедуры через сеть виден только вызов на выполнение процедуры.When calling a procedure over the network, only the call to execute the procedure is visible. Следовательно, злоумышленники не смогут видеть имена объектов таблиц и баз данных, внедрять свои инструкции Transact-SQLTransact-SQL или выполнять поиск важных данных.Therefore, malicious users cannot see table and database object names, embed Transact-SQLTransact-SQL statements of their own, or search for critical data.

Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL».Using procedure parameters helps guard against SQL injection attacks. Поскольку входные данные параметра обрабатываются как литеральные значения, а не как исполняемый код, злоумышленнику будет труднее вставить команду в инструкции Transact-SQLTransact-SQL в процедуре и создать угрозу безопасности.Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQLTransact-SQL statement(s) inside the procedure and compromise security.

Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код.Procedures can be encrypted, helping to obfuscate the source code. Дополнительные сведения см. в статье SQL Server Encryption.For more information, see SQL Server Encryption.

Повторное использование кодаReuse of code
Если какой-то код многократно используется в операции базы данных, то отличным решением будет произвести его инкапсуляцию в процедуры.The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. Это устранит необходимость излишнего копирования того же кода, снизит уровень несогласованности кода и позволит осуществлять доступ к коду любым пользователям или приложениям, имеющим необходимые разрешения.This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

Более легкое обслуживаниеEasier maintenance
Если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры.When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. Уровень приложения остается незатронутым изменениями в схемах баз данных, связях или процессах.The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

Повышенная производительностьImproved performance
По умолчанию компиляция процедуры и создание плана выполнения, используемого для последующих выполнений, производится при ее первом запускеBy default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Поскольку обработчику запросов не нужно создавать новый план, обычно обработка процедуры занимает меньше времени.Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

Если в таблицах или данных, на которые ссылается процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана может вызвать замедление работы процедуры.If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. В этом случае перекомпиляция процедуры и принудительное создание нового плана выполнения может улучшить производительность.In this case, recompiling the procedure and forcing a new execution plan can improve performance.

Типы хранимых процедурTypes of Stored Procedures

Пользовательские процедурыUser-defined
Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных Resource .A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. Процедура может быть разработана либо на языке Transact-SQLTransact-SQL , либо как ссылка на метод MicrosoftMicrosoft .NET Framework.NET Framework среды CLR.The procedure can be developed in either Transact-SQLTransact-SQL or as a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method.

Временные процедурыTemporary
Временные процедуры — это один из видов пользовательских процедур.Temporary procedures are a form of user-defined procedures. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb.The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb. Существует два вида временных процедур: локальные и глобальные.There are two types of temporary procedures: local and global. Они отличаются друг от друга именами, видимостью и доступностью.They differ from each other in their names, their visibility, and their availability. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение.Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

Системные функцииSystem
Системные процедуры включены в SQL ServerSQL Server.System procedures are included with SQL ServerSQL Server. Физически они хранятся во внутренней скрытой базе данных Resource . Логически они отображаются в схеме sys каждой системной и пользовательской базы данных.They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. В дополнение к этому, база данных msdb также содержит системные хранимые процедуры в схеме dbo . Эти процедуры используются для планирования предупреждений и заданий.In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Поскольку названия системных процедур начинаются с префикса sp_, этот префикс не рекомендуется использовать при создании пользовательских процедур.Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. Полный список системных хранимых процедур см. в статье Системные хранимые процедуры (Transact-SQL)For a complete list of system procedures, see System Stored Procedures (Transact-SQL)

SQL ServerSQL Server поддерживает системные процедуры, обеспечивающие интерфейс между SQL ServerSQL Server и внешними программами для выполнения различных действий по обслуживанию системы.supports the system procedures that provide an interface from SQL ServerSQL Server to external programs for various maintenance activities. Эти расширенные процедуры имеют префикс xp_.These extended procedures use the xp_ prefix. Полный список расширенных хранимых процедур см. в статье Основные расширенные хранимые процедуры (Transact-SQL).For a complete list of extended procedures, see General Extended Stored Procedures (Transact-SQL).

Расширенные пользовательские процедурыExtended User-Defined
Расширенные процедуры позволяют создавать внешние подпрограммы на языках программирования, таких как С. Они представляют собой библиотеки DLL, которые могут динамически загружаться и выполняться экземпляром SQL ServerSQL Server .Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL ServerSQL Server can dynamically load and run.

Примечание

Расширенные хранимые процедуры в будущих версиях SQL ServerSQL Serverбудут удалены.Extended stored procedures will be removed in a future version of SQL ServerSQL Server. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется.Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Вместо них рекомендуется создавать процедуры CLR.Create CLR procedures instead. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.This method provides a more robust and secure alternative to writing extended procedures.

Описание задачиTask Description РазделTopic
Описывает создание хранимой процедуры.Describes how to create a stored procedure. Создание хранимой процедурыCreate a Stored Procedure
Описывает изменение хранимой процедуры.Describes how to modify a stored procedure. Изменение хранимой процедурыModify a Stored Procedure
Описывает удаление хранимой процедуры.Describes how to delete a stored procedure. Удаление хранимой процедурыDelete a Stored Procedure
Описывает выполнение хранимой процедуры.Describes how to execute a stored procedure. Выполнение хранимой процедурыExecute a Stored Procedure
Описывает предоставление разрешений на хранимую процедуру.Describes how to grant permissions on a stored procedure. Предоставление разрешений на хранимую процедуруGrant Permissions on a Stored Procedure
Описывает возврат данных из хранимой процедуры в приложение.Describes how to return data from a stored procedure to an application. Возврат данных из хранимой процедурыReturn Data from a Stored Procedure
Описывает перекомпиляцию хранимой процедуры.Describes how to recompile a stored procedure. Перекомпиляция хранимой процедурыRecompile a Stored Procedure
Описывает переименование хранимой процедуры.Describes how to rename a stored procedure. Изменение имени хранимой процедурыRename a Stored Procedure
Описывает просмотр определения хранимой процедуры.Describes how to view the definition of a stored procedure. Просмотр определения хранимой процедурыView the Definition of a Stored Procedure
Описывает просмотр зависимостей хранимой процедуры.Describes how to view the dependencies on a stored procedure. Просмотр зависимостей хранимой процедурыView the Dependencies of a Stored Procedure
Описывается использование параметров в хранимой процедуре.Describes how Parameters are used in a stored procedure. ПараметрыParameters

Хранимые процедуры CLRCLR Stored Procedures
Отложенное разрешение именDeferred name resolution