65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 存储过程概述

Md. Marufuzzaman
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.93/5 (92投票s)

2009 年 8 月 2 日

CPOL

10分钟阅读

viewsIcon

1290374

SQL Server 存储过程概述

目录

1. 概述

当您需要存储或检索数据时，在 SQL Server 数据库中访问和操作数据是一项相当直接的操作，因此我们将使用 T-SQL 和四个简单的命令 – SELECT、INSERT、DELETE 和 UPDATE 来完成所有必需的任务。操作数据库的技能在于不仅能够使用这些命令，而且还能够高效地完成每个工作单元。

我们将介绍 SQL Server 中使用的不同类型的存储过程及其用法。我们还将研究存储过程的工作原理、潜在的棘手领域以及如何避免它们。

为了提高性能，每位开发人员在开发过程中都应考虑许多因素。但是，我们将重点介绍在存储过程内部运行 T-SQL；我们将研究构成存储过程的内容并讨论不同的类型。我们将介绍

  • 系统存储过程与用户存储过程的区别
  • 创建和设计存储过程
  • 参数使用和最佳实践
  • 流程控制语句
  • 递归
  • 返回值

2. 什么是存储过程？

如果需要在应用程序中执行重复的 T-SQL 任务，那么它最好的存储位置是存储在 SQL Server 中的一个名为存储过程的程序。将代码存储在 SQL Server 对象内部为我们提供了许多优势，例如

  • 通过加密实现安全
  • 通过编译获得性能提升
  • 能够将代码保存在中央存储库中
    • 在 SQL Server 中更改代码，而无需在多个不同程序中复制
    • 能够保留代码的统计信息以保持其优化
  • 通过将代码保留在服务器上来减少通过网络传输的数据量
  • 隐藏原始数据，只允许存储过程访问数据

您可能已经执行了一些临时查询来完成诸如插入数据、查询其他系统中的信息或创建新数据库对象（如表）等任务。所有这些任务都可以包含在存储过程中，以便任何开发人员都可以运行相同的代码，而无需重新创建 T-SQL 命令。此外，将代码通用化以适用于所有值使其具有通用性和可重用性。

存储过程不仅仅是执行重复任务的工具。存储过程主要有两种类型——系统存储过程和用户定义的存储过程。我们还有可以作为系统或用户定义类型的扩展存储过程。扩展存储过程提供了不一定包含在 SQL Server 中的功能，例如允许运行 DOS 命令和处理电子邮件。您也可以创建自己的扩展存储过程。

存储过程的示例

    /*
    DECLARE @OutPutValue VARCHAR(100)
    EXEC spExample 'CodeProject', @OutPutValue OUTPUT
    PRINT @OutPutValue
    */
    CREATE PROCEDURE [dbo].[spExample]
      @parameter1 VARCHAR(100)
     ,@parameter2 VARCHAR(200) OUTPUT
    AS
    BEGIN
    DECLARE @parameter3 VARCHAR(100)
    SET @parameter3 = ' Your development resources.'
        IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1
            SELECT @parameter2 = 'The ' 
                       + @parameter1 
                                           + @parameter3
        ELSE SELECT  @parameter2 = 'CodeProject is cool!'
    RETURN
    
    END
    GO

有关 SQL Server 与 OLE 对象集成方式的更多详细信息，请访问此链接。

2.1 系统存储过程

在 SQL Server 中，可以使用系统存储过程执行许多管理和信息活动。每次我们添加或修改表、制定备份计划或从企业管理器内部执行任何其他管理功能时，我们实际上都在调用一个专门编写以完成所需操作的存储过程。这些存储过程称为系统存储过程，它们是增强 SQL Server 本身基本功能的函数，方法是扩展现有系统存储过程的功能或创建增强现有功能的新功能。

系统存储过程的前缀是 sp_，因此建议不要为我们创建的任何存储过程使用 sp_ 前缀，除非它们是我们 SQL Server 安装的一部分。创建以 sp_ 为前缀并将存储过程放在 master 数据库中，将使其可用于任何数据库，而无需在存储过程前加上数据库名称。更多详细信息可以在此链接中找到。

让我们通过一个例子来澄清这一点。如果我们采用 <code>sp_who 存储过程，将其命名为 sp_mywho，将其存储在 master 数据库中，然后移动到另一个数据库（例如 northwind），我们仍然可以执行 sp_mywho，而不必以完全限定的方式（如 master.dbo.sp_mywho）指定该过程。

2.2 用户存储过程

用户存储过程是在 SQL Server（但不包括 master 数据库）中存储和编译的任何程序，并且以 sp_ 作为前缀。用户存储过程可以分为三种不同的类型

  • 用户存储过程
  • 触发器，以及
  • 用户定义函数

2.3 创建存储过程

创建过程取决于我们想要它做什么，现在让我们看看创建存储过程的语法

语法

下面是一个简单的存储过程示例，它接受两个数字作为输入，并列出这两个数字的中点

CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int
AS
BEGIN

   DECLARE @Mid int
   IF @LowerNumber > @HigherNumber
      RAISERROR('You have entered your numbers the wrong way round',16,1)

   SET @Mid = ((@HigherNumber - @LowerNumber) / 2) + @LowerNumber

   SELECT @Mid

END

在创建时，SQL Server 会获取我们的代码并对其进行解析以查找任何语法错误。列名和变量在编译时会检查是否存在。即使它们不存在，在存储过程中创建的任何临时表也将通过编译阶段。这被称为延迟名称解析。它可以是优点也可以是缺点，因为我们可以创建仅在存储过程执行期间存在的临时表，这是可取的，但如果我们稍后在定义临时表时出错（例如，列名错误），则编译不会捕获此错误。

一旦编译，存储过程的详细信息就存储在相关数据库的三个系统表中，它们如下

sysobjects

包含数据库中每个对象（约束、默认值、日志、规则、存储过程等）的一行。仅在 tempdb 中，此表才包含每个临时对象的一行。

有关更多详细信息，请访问此链接。

sysdepends

包含数据库中对象（视图、过程、触发器）与定义中包含的对象（表、视图、过程）之间的依赖信息。

有关更多详细信息，请访问此链接。

syscomments

包含数据库中每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的条目。text 列包含原始 SQL 定义语句。

有关更多详细信息，请访问此链接。

2.3.1 性能考虑

当存储过程被创建时，它会经历几个步骤。首先，T-SQL 被解析并解析，然后保存到磁盘并在 SQL Server 中存储。首次执行该过程时，将根据任何数据查询和通过参数传递的值来检索和优化该过程。SQL Server 将检查代码并尝试使用表中引用的最佳索引，方法是检查为这些表保留的统计信息。

然后，查询计划会在 SQL Server 中缓存，以备后续执行。SQL Server 将始终使用此计划，前提是它不会淘汰该计划。因此，存储过程的性能提升来自编译的缓存计划。

2.3.2 网络考虑

您可能会考虑传递 T-SQL 语句来将一行插入表中，字符数很少。但是，创建存储过程并仅传递存储过程的名称、参数及其值可以减少所需的字符数。通过将此差异乘以调用次数，我们可以看到 T-SQL 语句对我们网络的额外开销。这可能是一个重大问题，尤其是在互联网上使用时。

将以下两个语句进行比较

CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int
AS
BEGIN

   DECLARE @Mid int
   IF @LowerNumber > @HigherNumber
      RAISERROR('You have entered your numbers the wrong way round',16,1)

   SET @Mid = ((@HigherNumber - @LowerNumber) / 2) + @LowerNumber

   SELECT @Mid

END

第一个语句有 74 个字符，而第二个语句有 46 个字符，仅相差 28 个字符。但是，如果这是一个列更密集的插入，例如每天进行 10,000 次这样的插入，那么这相当于近 280k 的带宽浪费！如果正在上传或下载图像数据类型？任何二进制数据类型，如图像或声音等，都将作为二进制值发送。这些将被转换为字符字符串，当使用内联 T-SQL 时，这将使我们发送的临时查询的大小加倍。

3. 存储过程的工作原理

当我们运行存储过程时，Adaptive Server 会准备一个执行计划，使过程的执行速度非常快。存储过程可以

  • 接受参数
  • 调用其他过程
  • 向调用过程或批处理返回状态值，以指示成功或失败以及失败的原因
  • 将参数的值返回给调用过程或批处理
  • 在远程 Adaptive Server 上执行

编写智能存储过程的能力极大地增强了 SQL 的功能、效率和灵活性。编译后的过程极大地提高了 SQL 语句和批处理的性能。此外，如果您的服务器和远程服务器都配置为允许远程登录，则可以执行其他 Adaptive Server 上的存储过程。您可以在本地 Adaptive Server 上编写触发器，在发生某些事件（如本地删除、更新或插入）时在远程服务器上执行过程。

存储过程与普通 SQL 语句和 SQL 语句批处理不同，因为它们是预编译的。当您第一次运行过程时，Adaptive Server 的查询处理器会对其进行分析并准备一个最终存储在系统表中的执行计划。之后，过程将根据存储的计划执行。由于大部分查询处理工作已经完成，因此存储过程的执行速度几乎是即时的。

4. 参数使用

即使存储过程的目的不是返回信息，数据也会通过存储过程传递。所有定义为参数的变量都必须以 @ 符号作为前缀。通常的做法是在任何输出参数之前定义输入参数。

与数据库中的任何列一样，请使用最适合该参数的数据类型，如果该参数必须与表中的列匹配，请确保两种数据类型完全匹配。

如果参数不与列匹配但仍将使用，例如作为 join 条件或筛选条件，则使用适合数据类型和大小的数据类型，而不是全部使用字符数据类型。此外，尽可能使用 varchar 或 nvarchar 而不是 char，就像在内联 T-SQL 中一样，这将避免在网络上传输不必要的空格。

5. 调用存储过程

许多人倾向于忽略与执行或调用存储过程相关的性能增强。例如，如果您想在 northwind 数据库中调用 [Ten Most Expensive Products] 存储过程，您可以这样做

[Ten Most Expensive Products]

您可以省略 EXEC(UTE)，但如果您在一个存储过程中调用另一个存储过程，则需要它。

然而，这不是调用存储过程的最有效方法。当处理此类命令时，SQL Server 有一种分层的方法来查找和执行存储过程。首先，它会获取要执行该过程的用户的登录 ID，并检查是否存在同名的存储过程。如果不存在，SQL Server 将在 DBO 登录下查找该存储过程。因此，请始终使用所有者完全限定您的存储过程。

摘要

我们讨论了有关存储过程的几个问题。我们已经了解了什么是存储过程，如何创建存储过程，以及存储过程如何确保您的开发达到最佳状态。即使数据库正在被复制，存储过程也可以并且确实可以提高性能。

关注点

创建存储过程后不要就此忘记它们，就像您永远不会忘记索引一样。当向表中添加新索引或对任何依赖对象进行任何类型的修改时，整个查询树都需要重新编译。

最后，不要将存储过程用于它们不适用的目的。除非 CHECK 约束无法提供足够的功能，否则不要用存储过程替换 SQL Server 功能（如 CHECK 约束）。

历史

  • 2009 年 8 月 2 日：初始帖子
  • 2010 年 7 月 13 日：格式修复
© . All rights reserved.