存储过程(数据库引擎)Stored Procedures (Database Engine)

本主题适用于:是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse 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.

可在 CREATE PROCEDURE 语句中指定 EXECUTE AS 子句以便实现对其他用户的模拟,或者使用户或应用程序无需针对基础对象和命令的直接权限,即可执行某些数据库活动。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 EncryptionFor 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

用户定义的过程可在用户定义的数据库中创建,或者在除了 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 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.

系统过程是 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
扩展过程允许你使用编程语言(例如 C)创建外部例程。这些过程是指 SQL ServerSQL Server 的实例可以动态加载和运行的 DLL。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

CLR 存储过程CLR Stored Procedures