sql-stored-procedures

SQL 存储过程：现代指南

什么是存储过程？

存储过程是您可以在数据库中保存的一段预编译的 SQL 代码块，以便该代码可以重复使用。它就像编程语言中的函数，但它存在于您的数据库内部。

您可以向存储过程传递参数，以便它能根据您提供的数据进行操作。它们是封装业务逻辑、执行复杂数据库操作和确保数据一致性的强大工具。

注意：虽然与用户定义函数（UDF）类似，但存储过程更加灵活。它们可以执行数据操作语言（DML）操作（INSERT、UPDATE、DELETE），并且不一定非要返回值；而函数通常设计用于计算并返回单个值，并且对副作用有严格限制。

基本语法（MySQL/MariaDB）

创建存储过程的语法因数据库系统而异。本教程将使用 MySQL/MariaDB 语法。核心概念可应用于其他系统，如 PostgreSQL（使用 PL/pgSQL）或 SQL Server（使用 T-SQL）。

DELIMITER //

CREATE PROCEDURE ProcedureName(parameter_mode parameter_name DATATYPE)
BEGIN
    -- 您的 SQL 语句在此处
END //

DELIMITER ;

• DELIMITER //: 此命令将标准分隔符 (;) 更改为 (//)，以便数据库客户端知道整个过程定义的结束位置。这是一个客户端命令，不属于 SQL 过程语法本身。
• CREATE PROCEDURE: 开始定义新过程的关键字。
• BEGIN…END: 此块包含过程的主体，包括所有 SQL 逻辑。
• parameter_mode: 可以是 IN、OUT 或 INOUT，我们将在下面探讨。

创建您的第一个存储过程（带 IN 参数）

让我们从一个实际示例开始。我们将使用 Customers 表来根据客户的年龄获取客户详细信息。IN 参数是默认模式，用于将值传递到存储过程中。

示例：设置数据

首先，让我们使用现代数据类型创建并填充 Customers 表。

CREATE TABLE Customers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    City VARCHAR(100),
    Salary DECIMAL(10, 2)
);

INSERT INTO Customers (Name, Age, City, Salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

示例：创建存储过程

这个存储过程 GetCustomersByAge 接受一个整数（targetAge）并返回所有该年龄的客户。

DELIMITER //

CREATE PROCEDURE GetCustomersByAge(IN targetAge INT)
BEGIN
    -- 选择与输入年龄匹配的客户
    SELECT ID, Name, Age, City, Salary
    FROM Customers
    WHERE Age = targetAge;
END //

DELIMITER ;

如何使用

您可以使用 CALL 语句执行存储过程。

CALL GetCustomersByAge(25);

此调用将返回一个结果集，其中包含两名年龄为 25 岁的客户。

ID	Name	Age	City	Salary
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00

使用 OUT 参数返回数据

OUT 参数用于将值从存储过程返回给调用者。当您需要返回单个值（如计算出的计数或状态消息）而不是完整的结果集时，这非常有用。

示例：统计城市中的客户数量

此存储过程统计给定城市中有多少客户，并通过 OUT 参数返回计数。

DELIMITER //

CREATE PROCEDURE CountCustomersInCity(
    IN targetCity VARCHAR(100),
    OUT customerCount INT
)
BEGIN
    SELECT COUNT(*)
    INTO customerCount
    FROM Customers
    WHERE City = targetCity;
END //

DELIMITER ;

如何使用

调用带有 OUT 参数的存储过程时，您必须提供一个会话变量（以 @ 开头）来接收返回值。

-- 调用存储过程，传入一个变量来保存输出
CALL CountCustomersInCity('Mumbai', @mumbaiCount);

-- 查询变量以查看结果
SELECT @mumbaiCount AS NumberOfCustomersInMumbai;

输出

NumberOfCustomersInMumbai
1

使用 INOUT 参数修改数据

INOUT 参数是 IN 和 OUT 的组合。它允许您将值传递到存储过程内部，在存储过程内部修改它，并将新值在存储过程外部可用。

示例：应用工资奖金

此存储过程接受客户 ID 和奖金百分比。它计算新工资并更新到表中。它还会更新输入的工资变量以反映新工资。

DELIMITER //

CREATE PROCEDURE ApplyBonus(
    IN customerId INT,
    INOUT salary DECIMAL(10, 2),
    IN bonusPercentage DECIMAL(5, 2)
)
BEGIN
    -- 计算新工资
    DECLARE newSalary DECIMAL(10, 2);
    SET newSalary = salary * (1 + (bonusPercentage / 100.0));

    -- 更新表
    UPDATE Customers
    SET Salary = newSalary
    WHERE ID = customerId;

    -- 将 INOUT 参数更新为新值
    SET salary = newSalary;
END //

DELIMITER ;

如何使用

-- 首先，获取客户 1 的当前工资到一个变量中
SELECT Salary INTO @currentSalary FROM Customers WHERE ID = 1;

-- 调用存储过程应用 10% 的奖金
CALL ApplyBonus(1, @currentSalary, 10.0);

-- 检查变量中的更新值
SELECT @currentSalary AS NewSalary;

客户 1 的原始工资是 2000.00。在 10% 的奖金之后，新工资是 2200.00。

NewSalary
2200.00

基本最佳实践

• 命名约定： 使用一致的前缀，例如 usp_（用户存储过程）或动词-名词格式（例如 GetCustomers、UpdateSalary）。
• 单一职责： 每个存储过程应做好一件事。避免创建处理几十种不同情况的庞大存储过程。
• 注释： 直接在代码中记录复杂的逻辑、参数用法和过程的目的。
• 使用模式： 在大型数据库中，将存储过程组织到模式中，以对相关逻辑进行分组（例如 sales.UpdateOrder、reporting.GenerateDailySummary）。
• 版本控制： 将您的存储过程定义存储在像 Git 这样的版本控制系统中。使用数据库迁移工具（例如 Flyway、Liquibase）系统地管理和部署更改。

错误处理和事务

实际的存储过程必须健壮。如果 UPDATE 失败怎么办？您应该将逻辑包装在事务中并包含错误处理，以确保数据完整性。

DELIMITER //

CREATE PROCEDURE TransferFunds(
    IN fromAccountId INT,
    IN toAccountId INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    -- 如果发生任何错误则退出
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 回滚事务并发出错误信号
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- 从第一个账户扣款
    UPDATE Accounts SET balance = balance - amount WHERE ID = fromAccountId;

    -- 存入第二个账户
    UPDATE Accounts SET balance = balance + amount WHERE ID = toAccountId;

    COMMIT;
END //

DELIMITER ;

在此示例中，如果借记或贷记的 UPDATE 操作失败，EXIT HANDLER 会捕获异常，ROLLBACK 会撤销所有更改，并且存储过程停止，从而防止了部分错误事务的发生。

安全性：防止 SQL 注入

存储过程的关键安全优势之一是它们有助于防止 SQL 注入攻击。通过使用参数（例如 IN targetAge INT），数据库引擎会将输入视为数据，而非可执行代码。这是使用它们的正确方式。

切勿通过将用户输入与字符串连接来构建动态 SQL。这极其危险。

-- 危险 - 不要这样做！
SET @sql = CONCAT('SELECT * FROM Customers WHERE Age = ', userInput);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

务必始终使用我们第一个示例中展示的内置参数功能。它本质上更安全。

优点和缺点

优点

• 性能提升： 存储过程由数据库预编译和缓存，通常比即席查询执行更快，尤其对于复杂逻辑更是如此。
• 减少网络流量： 应用程序无需通过网络发送冗长复杂的查询，只需发送 CALL MyProcedure(1, 'A')，逻辑便在服务器上执行。
• 增强安全性： 您可以授予用户对存储过程的 EXECUTE 权限，而无需直接授予他们对底层表的访问权限，从而强制执行严格的数据访问 API。
• 代码复用性和一致性： 将业务逻辑封装在一个地方，可确保在所有需要的地方一致应用，并简化维护。

缺点

• 厂商锁定： 存储过程的语法高度特定于数据库系统（T-SQL 与 PL/pgSQL 与 MySQL 语法），这使得迁移到另一个数据库变得困难。
• 调试挑战： 调试存储过程可能比调试应用程序代码更复杂，通常需要专门的数据库工具。
• 增加服务器负载： 存储过程中的繁重逻辑会增加数据库服务器的 CPU 负载，这在某些架构中可能成为瓶颈。
• 脆弱性： 数据库模式的更改（例如，重命名列）可能会破坏存储过程，并且这些依赖关系有时难以追踪。