什么是 SQL Server 存储过程？

存储过程保存了一组或多组语句，这些语句存储在 SQL Server 中。在这里，我们解释了如何通过示例创建、重命名、修改和删除存储过程。以下是使用它们的好处。

  • 所有 SQL Server 存储过程都是预编译的，并且它们的执行计划会被缓存。因此，当您再次执行相同的存储过程时，它将使用缓存。
  • 它将帮助您封装业务规则和策略。例如，数据库管理员将创建一个过程，多个用户将从 JAVA、C#、Python、R 等访问它。
  • 与其发送数百行代码，不如使用存储过程，这样我们可以调用单个语句（其名称），而不是编写复杂的语句或通过网络发送它。
  • 存储过程帮助您以更安全和统一的方式访问数据库对象
  • 网络带宽节约（防止损坏或延迟）

我建议您参考以下示例，以详细了解 SQL 存储过程

  1. SELECT 了解如何在其中编写 SELECT 语句
  2. INSERT 文章，按照步骤在其中编写 INSERT 语句。
  3. UPDATE 在其中编写 UPDATE 语句。
  4. 输入参数 声明和使用输入参数，包括单个或多个参数值。
  5. 输出参数：输出参数帮助输出值。
  6. 返回值
  7. 临时表：创建临时过程，包括本地临时过程或全局临时过程。
  8. 一些有用的系统存储过程。

在我们进入实际示例之前，让我们先看看语法。

SQL Server 存储过程语法

存储过程的基本语法如下所示

CREATE [OR ALTER] PROCEDURE [Schema_Name].Procedure_Name
                @Parameter_Name Data_type, 
                .... 
                @Parameter_Name Data_type
AS
   BEGIN
      -- Query
      -- SELECT, Insert, Update, Or Delete Statements
      -- You can Use CTE also 
   END

但是，首先，让我解释一下语法。

  • Schema_name：请指定架构名称。例如，dbo 或 Humanresource 等。
  • Procedure_Name：您可以指定任何您希望给定的 SQL Server 存储过程名称，除了系统保留关键字。请尝试使用有意义的名称，以便您可以快速识别它们。
  • @Parameter_Name：每个存储过程根据用户要求接受零个或多个参数。声明参数时，不要忘记适当的数据类型。例如 (@FullName VARCHAR(50), @Age INT)

在 SQL Server 中创建存储过程

我忘了我们可以用两种方式创建它们——我们用于此演示的数据。

使用 SQL Server Management Studio 创建存储过程

在我们开始创建任何存储过程之前，让我们看看我们的数据库中是否存在任何现有的存储过程。要查看现有的存储过程，请选择 数据库 -> 可编程性 -> SP。从下面的屏幕截图中，您可以看到我们的数据库中没有。

右键单击“存储过程”文件夹将打开上下文菜单。请从其中选择“新建 -> 存储过程…”选项。

Create a New Stored Procedures in SQL Server 1

一旦您点击“新建”选项，它将打开一个新的查询窗口，其中包含默认模板。

Auto Generated Stored Procedures Code 2

在这里，您可以添加名称、参数（如果需要）以及您要使用的事务查询。

使用查询创建 SQL 存储过程

此示例演示如何使用 Create 语句创建 SP。

CREATE PROCEDURE SelectingAllRecordsInEmployee 
AS
BEGIN

 SET NOCOUNT ON;
 SELECT [FirstName]
              ,[LastName]
              ,[Education]
              ,[Occupation]
              ,[YearlyIncome]
              ,[Sales]
              ,[HireDate]
 FROM [Employee]
END
GO

输出

Messages
--------
Command(s) completed successfully.

如您所见，命令已成功完成。以下屏幕截图显示了我们之前创建的简单存储过程。

View SPS

在 SQL Server 中执行存储过程

我们可以通过两种方式执行存储过程

使用管理工具执行

要使用管理工具 (SSMS) 执行存储过程，请导航到“可编程性”->“SP”。接下来，选择要执行的存储过程 (SelectingAllRecordsInEmployee)，右键单击它将打开上下文菜单。然后，请选择“执行”选项。

Execute SQL Server Stored Procedure 5

选择“执行”选项后，管理工具中将打开“执行存储过程”窗口。如果存储过程有任何参数，我们必须分配/传递这些值，然后单击“确定”执行。我们的存储过程此处没有参数，因此单击“确定”运行。

Execute General Tab

单击“确定”按钮后，将打开一个新查询窗口，其中包含以下自动生成的代码。

DECLARE @return_value int

EXEC @return_value = [dbo].[SelectingAllRecordsInEmployee]

SELECT 'Return Value' = @return_value

GO
Execute EXEC result 7

使用 EXEC 执行 SP

使用 EXEC 命令（执行命令）执行存储过程

EXEC  [dbo].[SelectingAllRecordsInEmployee]
Select records from sp using EXEC

在 SQL Server 中重命名存储过程

没有所谓的重命名存储过程。因为重命名它不会改变其在 sys.sql_modules 中的相应对象名称。因此，如果您想重命名任何现有的存储过程，请先 DROP 它，然后以新名称重新创建它。

-- Renaming SP
-- Check whether there is a SP with the Following Name
IF OBJECT_ID ( 'SelectingAllRecordsInEmployee', 'P' ) IS NOT NULL   
    -- If so, Drop that Procedure
     DROP PROCEDURE SelectingAllRecordsInEmployee;  
GO  

-- Creating  SP with New name
CREATE PROCEDURE SelectingEmployeeRecords 
AS
BEGIN

 SET NOCOUNT ON;
 SELECT [FirstName]
          ,[LastName]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
 FROM [Employee]
END
GO

输出

Messages
-------
Command(s) completed successfully.

从下面的截图中，我们成功删除了旧的并创建了一个全新的。

View SQL Server Stored Procedures in Object Explorer 10

修改 SQL Server 中的存储过程

以下示例将帮助您学习使用 Management Studio (SSMS) 和查询来更改它们的步骤。

使用 SSMS 更改

要使用管理工具更改存储过程，请右键单击要更改的名称 (SelectingEmployeeRecords)，然后选择“修改”选项。

一旦您选择该选项，将打开一个新的查询窗口，其中包含自动生成的 ALTER PROCEDURE 代码。您可以根据您的要求进行更改。

Alter/Modify Stored Procedures by the Management Studio

使用 Alter 修改 SQL Server 中的存储过程

此示例将向您展示使用 ALTER PROCEDURE 语句修改现有存储过程的步骤。为此，单击“新建查询”并返回以下查询。

ALTER PROCEDURE [dbo].[SelectingEmployeeRecords] 
AS
BEGIN
 SET NOCOUNT ON;
 SELECT [FirstName]
              ,[LastName]
              ,[Education]
              ,[Occupation] AS Profession
              ,[YearlyIncome]
             ,[Sales]
 FROM [Employee]
END

运行上述 alter SP 查询

Messages
--------
Command(s) completed successfully.

让我们执行以上命令来检查我们是否成功更改。

EXEC [dbo].[SelectingEmployeeRecords]
GO
EXEC result

删除 SQL Server 中的存储过程

以下示例将向您展示如何使用 Transact Query 和 Management Studio (SSMS) 删除或删除存储过程。

使用管理工具删除

在此示例中，我们将展示如何使用 SSMS 或管理工具删除它。为此，请导航到要删除的项。接下来，右键单击名称以打开上下文菜单。最后，您可以单击“删除”选项，如下所示。

Delete SQL Server Stored Procedure 15

为了进行此演示，我们将删除 SelectingEmployeerecords。一旦您选择删除选项，将打开一个“删除对象”窗口，如下所示。接下来，单击“显示依赖项”按钮以检查依赖项，然后单击“确定”以删除它们。

Delete Object window

删除或丢弃

此示例向您展示如何使用查询删除或丢弃。

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

提示：使用 IF OBJECT_ID IS NOT NULL 检查数据库中是否存在名称或存储过程是一个很好的做法。

SQL 存储过程最佳实践

以下是一些可能有助于您提高存储过程性能的建议。

  • 在创建或引用任何数据库对象时，请尝试使用架构名称。这将减少数据库引擎的处理时间。
  • 始终在 SELECT 语句中指定所需的列名。并避免使用 SELECT * 语句。
  • 在使用 (CREATE TABLE 或 ALTER TABLE) 创建或修改表时，请使用 DEFAULT 关键字为列分配默认值。这将防止 NULL 值并为列数据分配这些默认值。
  • 在存储过程中创建临时表时，必须明确指定列是否接受 NULL 值。这可以通过使用 NULL 或 NOT NULL 来完成。
  • 与其提取或插入大量数据，不如尝试处理较不重要的数据。这减少了查询处理负载并提高了查询性能。
  • 在 SQL Server 存储过程内使用 SET NOCOUNT ON 语句。这将关闭 SQL Server 发送给客户端的消息。它包括更新、删除等行数。
  • 除非您正在寻找不同的值，否则请尝试将 UNION 运算符或 OR 运算符替换为 UNION ALL 运算符。
  • 如果可能，请避免在 SELECT 语句中使用返回大量数据的标量函数。这是因为标量函数作用于每一行（基于行），这会影响查询性能。
  • 为了处理错误，它允许我们在存储过程内部使用 TRY CATCH 功能。因此，请尝试使用 TRY CATCH 功能。
  • 始终在存储过程中使用 BEGIN..COMMIT TRANSACTION。请记住，事务应该尽可能短。否则，存在死锁或长时间锁定的危险。

创建存储过程的最佳方法

这将是在实时环境中创建的理想方式

IF OBJECT_ID ( 'SelectingEmployeeRecords', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectingEmployeeRecords;  
GO
CREATE PROCEDURE SelectingEmployeeRecords 
AS
BEGIN

 SET NOCOUNT ON;
 SELECT [FirstName]
          ,[LastName]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
 FROM [Employee]
END
GO

加密存储过程

我们可以通过添加 WITH ENCRYPTION 关键字来创建加密存储过程。例如，如果我们向上述查询中添加以下行。

CREATE PROCEDURE SelectingEmployeeRecords 
WITH ENCRYPTION
AS
BEGIN
....
........

评论已关闭。