SQL 存储过程：自动化和优化查询

教程
SQL

SQL 存储过程是一组保存并存储在数据库中的 SQL 语句。它们可以按需执行以进行数据操作和验证任务，减少了为常见操作编写重复 SQL 代码的需要。存储过程通过提高效率和可重用性在数据库管理中发挥了重要作用。此外，它们还支持增强的数据库安全性和可维护性。本文将讨论如何创建和执行 SQL 存储过程、常见用例以及最佳实践。

在开始之前，我强烈建议您参加 DataCamp 的 SQL 入门 和 学习 SQL 课程，以学习使用 SQL 提取和分析数据的基础知识。此外，您可以下载的 SQL 基础备忘单 是一个有用的参考，因为它包含所有最常见的 SQL 函数。

什么是 SQL 中的存储过程？

SQL 中的存储过程是一组保存在数据库中的 SQL 语句。SQL 存储过程的目的是对数据库执行一系列操作，例如查询、插入、更新或删除数据。

与作为单独命令执行的常规 SQL 查询不同，存储过程封装了一组 SQL 语句，使得重用代码变得容易，无需重复编写 SQL 命令。

SQL 存储过程的好处包括：

  • 代码重用性： 一旦创建了存储过程，它可以被调用多次，从而消除 SQL 代码中的冗余。
  • 性能提升：存储过程通常执行得更快，因为它们是预编译的并存储在数据库服务器上，从而减少了网络延迟和编译时间。
  • 安全性： 存储过程可以通过授予用户执行存储过程的权限而不直接访问表，从而提高数据安全性和对敏感数据访问的控制。

基本语法和结构

创建存储过程的语法可能会因数据库系统（例如 MySQL、SQL Server、Oracle）而略有不同。以下是使用 SQL Server 语法的一般示例：

-- 创建一个名为 ProcedureName 的存储过程
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL 语句在此处
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

在上述语法中；

  • 创建存储过程: 此命令用于定义一个新的存储过程。

  • 过程名称: 赋予存储过程的名称。它在数据库中应该是唯一的。

  • @参数1, @参数2: 参数是可选的；它们允许过程接收数据输入。每个参数以 @ 符号和数据类型（例如，INT，VARCHAR(50)）定义。

  • AS BEGIN…END: 在BEGIN和END之间的SQL语句构成了存储过程的主体，主要逻辑在此执行。该过程根据特定条件从表中检索记录。

输入和输出参数

输入和输出参数允许您在存储过程之间传递值。

例如，如果 @Parameter1 被定义为输入参数，则在调用过程时可以分配任何值，这将影响 SQL 逻辑或输出。在下面的示例中，@UserID 参数检索特定于提供的 UserID 的数据。

-- 创建一个过程，通过 UserID 检索特定用户的数据
CREATE PROCEDURE GetUserData 
 -- 输入参数：要检索的用户的 ID
    @UserID INT 
AS
BEGIN
    -- 从 Users 表中选择所有列，其中 UserID 与输入参数匹配
    SELECT * FROM Users WHERE UserID = @UserID;
END;

输出参数由 OUTPUT 关键字定义，允许存储过程将值发送回调用环境。例如，如果一个过程计算折扣，它可以使用输出参数将其传回调用程序。

-- 创建一个过程来计算折扣后的价格
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- 输出：折扣后的最终价格
AS
BEGIN
    -- 通过将折扣率应用于原价来计算最终价格
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

要调用此过程，您可以使用：

-- 声明一个变量以存储折扣后的最终价格
DECLARE @FinalPrice DECIMAL(10, 2);
-- 使用价格为100和10%折扣执行CalculateDiscount过程
-- 将输出存储在@FinalPrice变量中
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- 选择并显示最终折扣后的价格
SELECT @FinalPrice AS FinalPrice;

MySQL中的存储过程

正如我提到的，在SQL中执行存储过程可以根据数据库系统和使用的工具以不同方式进行。

在MySQL中创建存储过程

在MySQL中创建存储过程涉及定义过程的名称、参数和构成其主体的SQL语句。以下示例创建了一个名为GetEmployeeDetails的过程，该过程以EmployeeID作为输入参数，并检索该特定员工的详细信息。

DELIMITER $
-- 创建一个过程以通过 EmployeeID 检索特定员工的详细信息
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- 选择 Employees 中所有列，其中 EmployeeID 与输入参数匹配
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

在 MySQL 中执行存储过程

执行存储过程最常见的方法是使用 SQL 命令。在 MySQL 中，我们使用 CALL 命令来执行存储过程。

CALL ProcedureName();

使用定义的 GetEmployeeDetails 过程，执行查询看起来像这样：

-- 执行存储过程以检索 EmployeeID 101 的详细信息
CALL GetEmployeeDetails(101);

SQL Server 中的存储过程

SQL Server 提供了特定的语法和命令来创建、执行和管理存储过程。这种方法使得构建高效且可重用的 SQL 例程变得更容易，这些例程可以在最小重复的情况下处理复杂任务。

在 SQL Server 中创建存储过程

在 SQL Server 中创建存储过程涉及定义过程的名称、参数以及构成其主体的 SQL 语句。以下示例创建了一个名为 GetEmployeeDetails 的过程，该过程将 @EmployeeID 作为输入参数，并检索该特定员工的详细信息。

-- 创建一个过程，通过 EmployeeID 检索特定员工的详细信息
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- 输入参数：要检索的员工 ID
AS
BEGIN
    -- 从 Employees 中选择所有列，其中 EmployeeID 与输入参数匹配
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

在 SQL Server 中执行存储过程

在 SQL Server 中，EXEC 或 EXECUTE 命令用于调用存储过程。以下示例演示了如何使用特定输入参数执行存储过程 GetEmployeeDetails。

-- 执行 GetEmployeeDetails 过程，将 EmployeeID 设置为 102
EXEC GetEmployeeDetails @EmployeeID = 102;

您还可以通过在命令中声明变量来执行输出参数。在下面的示例中，@TotalSales 被声明为一个变量，用于接收来自 CalculateTotalSales 的输出。

-- 声明一个变量以存储总销售额
DECLARE @TotalSales DECIMAL(10, 2);
-- 为销售人员ID 5 执行 CalculateTotalSales，将结果存储在 @TotalSales 中
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- 显示总销售额
SELECT @TotalSales AS TotalSales;

我建议您参加我们的 SQL Server 入门 课程，以了解 SQL Server 在查询数据方面的不同功能。此外，请考虑我们的完整 SQL Server 开发人员 职业轨迹，这不仅会使您掌握创建、更新和执行存储过程的技能，还将帮助您掌握聚合函数、连接、插入和删除表等更多内容。

存储过程的常见用途

SQL 存储过程在需要重复复杂任务的场景中非常有用。以下是存储过程在数据管理和业务运营中的实际应用。

数据验证和完整性执行

存储过程可以用于在更新或插入之前验证数据。在下面的例子中，一个存储过程检查客户的电子邮件是否唯一，然后再将新记录插入到 Customers 表中，从而确保数据的一致性。这将验证逻辑集中在数据库中，减少冗余，并确保在不同应用程序中统一执行。

-- 创建一个过程以添加新客户，检查重复的电子邮件
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- 检查电子邮件是否已存在于 Customers 表中
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- 如果电子邮件已被使用，则抛出错误
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- 如果电子邮件唯一，则插入新客户详细信息
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

自动化数据处理和报告

您还可以使用存储过程生成定期报告或处理大型数据集。例如，存储过程可以汇总来自电子商务平台的每日销售数据并将其存储在报告表中，使团队更容易访问销售洞察，而无需运行复杂的查询。

-- 创建一个过程以生成每日销售报告
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- 将今天的日期和总销售额插入到 SalesReport 表中
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- 从 Sales 表中选择当前日期和今天的销售总额
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

事务管理

通过使用存储过程，您可以确保多个操作作为单个事务执行。例如，在银行系统中，存储过程可以同时处理资金转移中的借记和贷记操作，确保这两个操作要么同时成功，要么同时失败。

-- 创建一个程序在账户之间转账
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- 开始一个事务以确保原子性
    -- 从发送者的账户余额中扣除指定金额
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- 将指定金额添加到接收者的账户余额中
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- 检查错误并在发生错误时回滚；否则，提交事务
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- 如果发生错误，则撤销所有更改
    ELSE
        COMMIT TRANSACTION;    -- 如果没有错误，则确认更改
END;

访问控制和数据安全

您还可以使用SQL存储过程来控制对敏感信息的数据访问。例如，存储过程可以通过允许用户调用仅检索相关字段（如账户余额）而不包括交易细节的过程，来限制对直接表的访问。

-- 创建一个程序以检索账户余额，并进行授权检查
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- 检查账户是否存在，并且是否由指定用户拥有
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- 如果授权，选择并返回账户余额
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- 如果未经授权，抛出错误
        THROW 50000, 'Unauthorized access.', 1;
END;

数据迁移和ETL过程

存储过程也用于在系统之间加载、转换和迁移数据。存储过程可以自动从源数据库中提取数据，根据需要进行转换，并将其插入目标表，从而简化报告或分析的数据集成。

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- 提取
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- 转换
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- 加载
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

存储过程的最佳实践

编写高效且易于维护的存储过程可确保您的数据库性能最佳。以下是为您的SQL数据库编写存储过程的建议。

  • 使用一致的命名约定： 为了使存储过程易于识别和理解，请使用一致且具有描述性的命名格式。同时，避免在 SQL Server 中使用 sp_ 前缀，该前缀保留给系统过程，以防止潜在的冲突和性能问题。

  • 实现错误处理：将 SQL 语句包装在 TRY...CATCH 块中，以捕获和处理错误并维护数据完整性。

  • 优化性能： 尽量减少游标的使用，因为它们可能较慢且消耗资源。相反，尝试使用基于集合的操作，这通常更高效。此外，为常用列建立索引，并避免在大表中使用复杂的连接，以减少内存开销并提高效率。

  • 参数化存储过程： 使用参数而不是硬编码值，以便您可以将动态值传递到您的过程，使其更加灵活和可重用。

查看我们的中级SQL课程，了解更多关于使用聚合函数和连接来筛选数据的知识。此外，尝试我们的SQL Server基础和SQL基础技能轨道，以提高您连接表和数据分析的技能。

结论

SQL存储过程提高了数据库管理中的代码重用性和性能优化。存储过程还通过控制访问和确保数据完整性增强了数据库安全性。作为数据从业者，我鼓励您练习创建和执行存储过程，以掌握最佳的数据库管理实践。

如果您有兴趣成为一名熟练的数据分析师，请查看我们的SQL 数据分析师助理职业路径，以学习必要的技能。如果您想学习如何使用 SQL 构建专业的仪表板，SQL 报告课程也非常合适。最后，我建议获得SQL 助理认证，以展示您在数据分析中使用 SQL 的精通程度，并在其他数据专业人士中脱颖而出。

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure