存储过程(数据库引擎)

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsMicrosoft Fabric中的仓库Microsoft Fabric中的SQL数据库

SQL Server 中的存储过程是由一个或多个 Transact-SQL 语句或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用构成的一个组。 过程与其他编程语言中的构造相似,这是因为它们都可以:

  • 接受输入参数并以输出参数的格式向调用程序返回多个值。

  • 包含用于在数据库中执行操作的编程语句。 这些语句包括调用其他函数。

  • 向调用程序返回状态值,以指明成功或失败(以及失败的原因)。

使用存储过程的好处

下表介绍了使用过程的一些好处。

减少了服务器/客户端网络流量

过程中的命令作为代码的单个批处理执行。 此方法可以显著减少服务器和客户端之间的网络流量,因为仅通过网络发送执行过程的调用。 如果没有过程提供的代码封装,每个单独的代码行都不得不跨网络发送。

更强的安全性

多个用户和客户端程序可以通过过程对基础数据库对象执行操作,即使用户和程序对这些基础对象没有直接权限。 过程控制执行哪些进程和活动,并且保护基础数据库对象。 此方法消除了在单个对象级别授予权限并简化安全层的要求。

可在 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)创建外部例程。这些过程是指 SQL Server 的实例可以动态加载和运行的 DLL。

Note

SQL Server 的未来版本中将删除扩展存储过程。 请不要在新的开发工作中使用此功能,并尽快修改当前还在使用该功能的应用程序。 请改用创建 CLR 存储过程。 此方法提供了更为可靠和安全的替代方法来编写扩展过程。

平台支持

Fabric Data Warehouse 支持存储过程 Transact-SQL,但不支持 CLR 存储过程。 Fabric Data Warehouse 不支持 EXECUTE AS 和 FOR REPLICATION 程序选项。 有关详细信息,请参阅 CREATE PROCEDURE。

任务说明 Article
介绍如何创建存储过程。 创建存储过程
介绍如何修改存储过程。 修改存储过程
介绍如何删除存储过程。 删除存储过程
描述了如何执行存储过程。 执行存储过程
介绍如何授予对存储过程的权限。 授予对存储过程的权限
介绍如何将来自存储过程的数据返回到应用程序。 从存储过程中返回数据
介绍如何重新编译存储过程。 重新编译存储过程
介绍如何重命名存储过程。 重命名存储过程
介绍如何查看存储过程的定义。 查看存储过程的定义
介绍如何查看存储过程的依赖关系。 查看存储过程的依赖关系
介绍如何在存储过程中使用参数。 Parameters