SQL Server 中的存储过程

2025年3月17日 | 阅读 10 分钟

存储过程是**一组预编译的 SQL 语句**，被组合成一个逻辑单元。它作为对象存储在数据库服务器内部。在通用计算语言中，它是一个子程序或子程序，已在数据库中创建并存储。SQL Server 中的每个过程总是**包含名称、参数列表和 Transact-SQL 语句**。SQL 数据库服务器将存储过程存储为**命名对象**。我们可以使用触发器、其他过程和应用程序（如 Java、Python、PHP 等）来调用过程。它几乎支持所有关系数据库系统。

SQL Server 在存储过程首次调用时构建执行计划，并将其存储在缓存内存中。SQL Server 在后续执行存储过程时会重用该计划，从而使其运行得更快、更高效。

SQL Server 中存储过程的特点

以下是 SQL Server 中存储过程的特点

减少流量：存储过程减少了应用程序和数据库服务器之间的网络流量，从而提高了性能。这是因为应用程序只需发送存储过程的名称及其参数，而不是发送多个 SQL 语句。
更强的安全性：过程始终是安全的，因为它管理我们可以执行哪些进程和活动。它消除了在数据库对象级别授予权限的需要，并简化了安全层。
可重用性：存储过程是可重用的。它减少了代码不一致，防止了对同一代码的不必要重写，并使代码对所有应用程序或用户透明。
易于维护：在不重新启动或部署应用程序的情况下，可以更轻松地维护过程。
提高性能：存储过程提高了应用程序性能。一旦我们创建了存储过程并在第一次编译它们后，它就会创建一个执行计划，该计划会在后续执行中重用。通常，处理过程的速度更快，因为查询处理器不必创建新计划。

存储过程的类型

SQL Server 主要将存储过程分为两类

用户定义的存储过程
系统存储过程

用户定义的存储过程

用户定义的存储过程由数据库开发人员或数据库管理员构建。这些过程提供一个或多个 SQL 语句，用于从数据库表中选择、更新或删除数据。用户指定的存储过程接受输入参数并返回输出参数。DDL 和 DML 命令在用户定义的过程中一起使用。

我们可以将此过程进一步分为两类

T-SQL 存储过程：Transact-SQL 过程是 SQL Server 过程中最流行的类型之一。它接受参数并返回它们。这些过程处理带参数或不带参数的 INSERT、UPDATE 和 DELETE 语句，以及输出行数据。
CLR 存储过程：SQL Server 过程是一组 SQL 命令，CLR 表示公共语言运行时。CLR 存储过程由 CLR 和一个存储过程组成，该存储过程是用基于 CLR 的语言（如 VB.NET 或 C#）编写的。CLR 过程是运行在 SQL Server 数据库内存中的 .Net 对象。

系统存储过程

服务器的管理任务主要依赖于系统存储过程。安装 SQL Server 时，它会创建系统过程。系统存储过程可防止管理员直接查询或修改系统和数据库目录表。开发人员通常会忽略系统存储过程。

SQL Server 存储过程语法

以下是在 SQL Server 中创建存储过程的基本语法

CREATE PROCEDURE [schema_name].procedure_name
                @parameter_name data_type, 
                .... 
                parameter_name data_type
AS
   BEGIN
      -- SQL statements
      -- SELECT, INSERT, UPDATE, or DELETE statement
   END

参数解释

存储过程语法具有以下参数

Schema_name：这是你的数据库或模式的名称。默认情况下，过程与当前数据库相关联，但我们也可以通过指定 DB 名称将其创建到另一个数据库中。

Procedure_Name：它代表你的存储过程的名称，应该是易于识别的名称，以便你能够快速识别它们。它不能是系统保留关键字。

Parameter_Name：它代表参数的数量。根据用户要求，可以为零个或多个。我们必须确保使用了合适的数据类型。例如，@Name VARCHAR(50)。

存储过程中的 SET NOCOUNT ON

在某些情况下，我们在存储过程中使用 SET NOCOUNT ON 语句。此语句会阻止显示 SQL 查询受影响行数的提示。NOCOUNT 表示计数已关闭。这意味着如果设置了 SET NOCOUNT ON，将不会出现任何指示受影响行数的提示。

如何执行/调用存储过程？

我们可以使用 **EXEC 命令** 来调用/执行 SQL Server 中的存储过程。以下语法说明了存储过程的执行

EXEC procedure_name;
Or,
EXECUTE procedure_name;

如果我们使用 SSMS，我们需要使用以下步骤来执行存储过程

导航到**可编程性** -> **存储过程**。
接下来，选择存储过程菜单并展开它。您将看到可用的存储过程。
右键单击要执行的存储过程，然后选择**执行存储过程**
将出现“执行过程”窗口。如果过程有任何参数，我们必须在单击“确定”执行它之前为其赋值/传递。如果没有定义参数，请单击“确定”运行该过程。

存储过程简单示例

我们可以通过两种方式在 SQL Server 中创建存储过程

使用 T-SQL 查询
使用 SQL Server Management Studio

我们将使用一个**学生表**来演示存储过程示例。此表具有以下数据

下面的示例使用 **CREATE PROCEDURE SQL 语句** 创建一个存储过程，该过程按 STUDENT 表中选定数据库的工资递增顺序显示学生列表

CREATE PROCEDURE studentList
AS
BEGIN
    SELECT name, age, salary
    FROM STUDENT
    ORDER BY salary;
END; 

在此语法中，**studentList** 是存储过程的名称，**AS** 关键字区分存储过程的头部和主体。对于存储过程中的单个语句，**BEGIN** 和 **END** 关键字是可选的。但是，最好包含它们以使代码更易于理解。

当我们运行此语句时，如果一切正确，将出现以下消息：“**命令已成功完成**。”这表明存储过程已成功编译并保存到数据库系统。

我们可以使用以下命令执行此过程

它将返回如下输出：

如果我们使用 SSMS，请使用以下步骤创建存储过程

步骤 1：选择数据库 -> 可编程性 -> 存储过程。

步骤 2：右键单击“存储过程”文件夹以打开菜单，然后选择“新建” -> “存储过程”选项，如下所示

步骤 3：当我们选择“新建存储过程”选项时，我们将获得一个包含默认存储过程模板的新查询窗口。在这里，我们可以添加过程名称、参数（如果存在）以及我们想要使用的 SQL 查询。

如何在 SQL Server 中重命名存储过程？

SQL Server 不允许我们更改存储过程的名称。因为重命名存储过程不会更改 **sys.sql_modules** 中相应对象的名称。因此，如果我们想更改现有的存储过程，只需将其删除，然后用新名称重新创建它。

如何在 SQL Server 中修改存储过程？

我们需要在一段时间内更新或修改存储过程。SQL Server 允许我们通过两种方式更新或修改现有的存储过程

使用 T-SQL 查询
使用 SQL Server Management Studio

使用 SSMS 修改存储过程

以下步骤有助于了解我们如何修改或更改存储过程

步骤 1：导航到数据库 -> 可编程性 -> 存储过程。

步骤 2：展开“存储过程”文件夹，右键单击要修改的存储过程，然后选择“修改”选项，如下所示

步骤 3：单击“修改”选项后，我们将获得一个包含自动生成的 **ALTER PROCEDURE** 代码的新查询窗口。在这里，我们可以根据需要进行更改。

使用 T-SQL 查询修改存储过程

SQL Server 提供了 ALTER PROCEDURE 语句来修改现有的存储过程。如果我们想修改上面创建的存储过程，我们可以写 ALTER PROCEDURE 语句如下

ALTER PROCEDURE [dbo].[studentList]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT name, salary
    FROM STUDENT
    ORDER BY salary;
END; 

让我们运行该过程以检查我们是否成功更新了 **studentList** 过程。使用 EXECUTE 语句，我们将得到以下输出，其中我们可以看到我们的存储过程已成功修改。

如何在 SQL Server 中列出所有存储过程？

当我们有多个过程时，列出所有过程非常重要。因为有时许多数据库中的过程名称是相同的。在这种情况下，此查询非常有用。我们可以按如下方式列出当前数据库中的所有存储过程

列出数据库中所有用户定义存储过程的最佳方法是使用 **ROUTINES** 信息架构视图，如下所示

SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

或，

SELECT * 
 FROM db_name.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'

返回存储过程列表的另一种方法是查询 **sys.objects** 系统目录视图。

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name
FROM sys.objects
WHERE type = 'P';

如何删除/丢弃 SQL Server 中的存储过程？

我们可以永久删除 SQL Server 中的存储过程。SQL Server 以两种方式删除存储过程

使用 T-SQL 查询
使用 SQL Server Management Studio

使用 SSMS 丢弃存储过程

以下步骤有助于了解我们如何删除存储过程

步骤 1：转到数据库 -> 可编程性 -> 存储过程。

步骤 2：展开“存储过程”文件夹，右键单击要删除的存储过程，然后选择“删除”选项，如下所示

步骤 3：单击“删除”选项后，我们将得到一个“删除对象”窗口。我们可以通过单击“显示依赖项”按钮来检查依赖项，然后单击“确定”删除存储过程。

使用 T-SQL 查询删除存储过程

SQL Server 提供了 DROP PROCEDURE 语句来删除现有的存储过程。我们可以按如下方式编写 DROP PROCEDURE 语句

IF OBJECT_ID ('procedure_name', 'P') IS NOT NULL   
    DROP PROCEDURE procedure_name;  

注意：最好使用 IF OBJECT ID ('procedure name', 'P') IS NOT NULL 来查看存储过程是否存在于数据库中。

存储过程中的输入参数

SQL Server 允许我们创建输入参数存储过程。这种类型的存储过程可以使我们传递一个或多个参数以获得过滤后的结果。让我们通过示例来理解它。考虑以下“**客户**”表

下面的语句创建了一个带输入参数的存储过程

CREATE PROCEDURE getEmployeeDetails (@States VARCHAR(50))
AS
BEGIN
  SET NOCOUNT ON;
  SELECT c_name, email, state
  FROM customer
  WHERE state = @States
END

如果我们想执行此存储过程，我们需要为 **@States** 参数传递值。我们可以通过以下任何一种方式传递参数值

EXEC [dbo].[getEmployeeDetails] 'California';

--OR we can write

EXEC [dbo].[getEmployeeDetails] @States = 'New York';

我们将得到以下输出：

存储过程中的输出参数

SQL Server 使我们能够在存储过程中提供多个输出参数。这些输出参数可以是任何有效的数据类型，例如整数、日期或字符。我们可以使用以下语法在存储过程中创建输出参数

让我们通过示例了解如何在存储过程中使用输出参数。下面的语句将创建一个名为 **countStudent** 的过程，在该过程中，我们将声明一个整数类型的变量 **@StudentCount** 并使用 **OUTPUT** 关键字。该过程使用 **COUNT** 函数查找 STUDENT 表中的学生数量，然后将该值分配给输出参数。

CREATE PROCEDURE countStudent (@StudentCount INT OUTPUT)
AS
BEGIN
    SELECT @StudentCount = COUNT(Id)FROM STUDENT;
END;

现在，我们将执行存储过程。在这里，我们需要按如下方式传递输出参数 **@StudentaCouns**

-- Declare an Int Variable that corresponds to the Output parameter in SP
DECLARE	@TotalStudents INT 

-- Don't forget to use the keyword OUTPUT
EXEC  [dbo].[countStudent] @TotalStudents OUTPUT

-- Print the result
PRINT @TotalStudents 

我们将得到以下输出

临时存储过程

我们可以像创建临时表一样创建临时过程。tempdb 数据库用于创建这些过程。我们可以将临时过程分为两类

本地临时存储过程
全局临时过程。

本地临时存储过程：我们可以使用 # 作为前缀来创建这种类型的过程，并且只能在创建它们时所在的会话中访问。当连接关闭时，此过程会立即终止。

这是一个创建本地临时过程的示例

CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

全局临时存储过程：我们可以使用 ## 作为前缀来创建这种类型的过程，并且可以从任何会话访问。当用于创建过程的连接关闭时，此过程会自动终止。

这是一个创建全局临时过程的示例

CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

存储过程的缺点

以下是 SQL Server 中存储过程的限制

调试：由于调试存储过程从不简单，因此不建议使用它们编写和执行复杂的业务逻辑。因此，如果我们不能妥善处理，它可能会导致失败。

依赖性：我们知道，专业 DBA 和数据库开发人员在大型组织中处理大量数据集。应用程序开发人员必须依赖他们，因为任何微小的更改都必须引用 DBA，DBA 可以修复现有过程中的错误或构建新过程。

昂贵：存储过程在 DBA 方面管理成本很高，因为组织需要为专业 DBA 支付额外费用。DBA 更胜任处理复杂的数据库过程。

特定于供应商：在一个平台中编写的存储过程无法在另一个平台中运行。由于 Oracle 中编写的过程更复杂，因此我们需要为 SQL Server 重写整个过程。

下一主题SQL Server 中的触发器

SQL Server 中的存储过程

SQL Server 中存储过程的特点

存储过程的类型

用户定义的存储过程

系统存储过程

SQL Server 存储过程语法

参数解释

存储过程中的 SET NOCOUNT ON

如何执行/调用存储过程？

存储过程简单示例

如何在 SQL Server 中重命名存储过程？

如何在 SQL Server 中修改存储过程？

使用 SSMS 修改存储过程

使用 T-SQL 查询修改存储过程

如何在 SQL Server 中列出所有存储过程？

如何删除/丢弃 SQL Server 中的存储过程？

使用 SSMS 丢弃存储过程

使用 T-SQL 查询删除存储过程

注意：最好使用 IF OBJECT ID ('procedure name', 'P') IS NOT NULL 来查看存储过程是否存在于数据库中。

存储过程中的输入参数

存储过程中的输出参数

临时存储过程

存储过程的缺点

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL Server 教程

数据库操作

SQL Server 数据库

聚合函数

SQL Server 运算符

SQL Server 子句

SQL Server 键

SQL Server 进阶

杂项

服务

面试题

SQL Server 中的存储过程

SQL Server 中存储过程的特点

存储过程的类型

用户定义的存储过程

系统存储过程

SQL Server 存储过程语法

参数解释

存储过程中的 SET NOCOUNT ON

如何执行/调用存储过程？

存储过程简单示例

如何在 SQL Server 中重命名存储过程？

如何在 SQL Server 中修改存储过程？

使用 SSMS 修改存储过程

使用 T-SQL 查询修改存储过程

如何在 SQL Server 中列出所有存储过程？

如何删除/丢弃 SQL Server 中的存储过程？

使用 SSMS 丢弃存储过程

使用 T-SQL 查询删除存储过程

注意：最好使用 IF OBJECT ID ('procedure name', 'P') IS NOT NULL 来查看存储过程是否存在于数据库中。

存储过程中的输入参数

存储过程中的输出参数

临时存储过程

存储过程的缺点

相关帖子

SQL Server 注释

SQL Server PROFILER

SQL Server 排序规则

从 SQL Server 表中查找和删除重复项

SQL Server 临时表

SQL Server IDENTITY

SQL Server 复制表

SQL Server CURRENT_TIMESTAMP 函数

SQL Server STUFF() 函数

SQL Server 中的锁

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器