存储过程基本知识

微软官方文档：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms190782%28v%3dsql.110%29

 

SQL Server 中的存储过程是由一个或多个 Transact-SQL 语句或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用构成的一个组。

Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。 CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。 对于 SQL Server 用户和应用程序开发人员来说，CLR 集成意味着您现在可以使用任何 .NET Framework 语言（包括 Microsoft Visual Basic .NET 和 Microsoft Visual C#）编写存储过程、触发器、用户定义类型、用户定义函数（标量函数和表值函数）以及用户定义的聚合函数。

公共语言运行时 (CLR) 是 Microsoft .NET Framework 的核心，它为所有 .NET Framework 代码提供执行环境。 在 CLR 中运行的代码称为托管代码。 CLR 提供执行程序所需的各种函数和服务，包括实时 (JIT) 编译、分配和管理内存、强制类型安全、异常处理、线程管理和安全性。

使用在 Microsoft SQL Server 中驻留的 CLR（称为 CLR 集成），可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合。 由于托管代码在执行之前才编译为本机代码，因此在某些情形下可以大幅提高性能。

编写存储过程、触发器和用户定义函数时，必须决定是使用传统的 Transact-SQL 还是使用诸如 Visual Basic .NET 或 Visual C# 这样的 .NET Framework 语言。 如果代码主要执行没有或只有很少过程逻辑的数据访问，请使用 Transact-SQL。 如果要编写有复杂逻辑并且 CPU 占用量大的函数和过程，或者想使用 .NET Framework 的 BCL，则使用托管代码。

托管代码比 Transact-SQL 更适合用于计算和复杂的执行逻辑，并可提供对很多复杂任务（包括字符串处理和正则表达式）的广泛支持。 使用在 .NET Framework 库中提供的功能，可以访问数千个预建的类和例程。 可以很容易从任何存储过程、触发器或用户定义函数访问它们。 基类库 (BCL) 包括可为字符串操纵、高级数学操作、文件访问、加密等提供功能的类。

 

存储过程特点：

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

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

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

 

使用存储过程的好处：

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

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

• 更强的安全性

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

可在 CREATE PROCEDURE 语句中指定 EXECUTE AS 子句以便实现对其他用户的模拟，或者使用户或应用程序无需针对基础对象和命令的直接权限，即可执行某些数据库活动。 例如，某些操作（如 TRUNCATE TABLE）没有可授予的权限。 若要执行 TRUNCATE TABLE，用户必须对指定表具有 ALTER 权限。 授予用户对表的 ALTER 权限可能不是最佳方法，因为用户将拥有超出截断表的能力的权限。 通过将 TRUNCATE TABLE 语句纳入模块中并指定该模块作为一个有权修改表的用户执行，您可以将截断表的权限扩展至授予其对模块的 EXECUTE 权限的用户。

在通过网络调用过程时，只有对执行过程的调用是可见的。 因此，恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。

使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码，所以，攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。

可以对过程进行加密，这有助于对源代码进行模糊处理。

• 代码的重复使用

任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性，并且允许拥有所需权限的任何用户或应用程序访问和执行代码。

• 更容易维护

在客户端应用程序调用过程并且将数据库操作保持在数据层中时，对于基础数据库中的任何更改，只有过程是必须更新的。 应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。

• 改进的性能

默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。 因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。如果过程引用的表或数据有显著变化，则预编译的计划可能实际上会导致过程的执行速度减慢。 在此情况下，重新编译过程和强制新的执行计划可提高性能。

 

存储过程的类型

• 用户定义

用户定义的过程可在用户定义的数据库中创建，或者在除了 Resource 数据库之外的所有系统数据库中创建。 该过程可在 Transact-SQL 中开发，或者作为对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用开发。

• 临时

临时过程是用户定义过程的一种形式。 临时过程与永久过程相似，只是临时过程存储于 tempdb 中。 临时过程有两种类型：本地过程和全局过程。 它们在名称、可见性以及可用性上有区别。 本地临时过程的名称以单个数字符号 (#) 开头；它们仅对当前的用户连接是可见的；当用户关闭连接时被删除。 全局临时过程的名称以两个数字符号 (##) 开头，创建后对任何用户都是可见的，并且在使用该过程的最后一个会话结束时被删除。

 

• 系统

系统过程是 SQL Server 随附的。 它们物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的 sys 架构中。 此外，msdb 数据库还在 dbo 架构中包含用于计划警报和作业的系统存储过程。 因为系统过程以前缀 sp_ 开头，所以，我们建议您在命名用户定义过程时不要使用此前缀。

SQL Server 支持在 SQL Server 和外部程序之间提供一个接口以实现各种维护活动的系统过程。 这些扩展过程使用 xp_ 前缀。

 

• 扩展的用户定义过程

通过扩展的过程，可以使用 C 之类的编程语言创建外部例程。 这些过程是 SQL Server 实例可以动态加载和运行的 DLL。注意：SQL Server 的未来版本中将删除扩展存储过程。 请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。 请改为创建 CLR 过程。 此方法提供了更为可靠和安全的替代方法来编写扩展过程。

 

 

•