SQL存储过程教程，如何快速上手编写？-建站教程-锦华智联科技

SQL 存储过程终极教程：从入门到精通

什么是存储过程？
为什么使用存储过程？（优点与缺点）
创建与执行存储过程
- 基本语法
- 第一个存储过程：Hello, World!
- 带参数的存储过程
- 参数类型：IN, OUT, INOUT
高级特性
- 变量声明与赋值
- 流程控制：IF...ELSE, CASE
- 循环：WHILE
- 游标
- 事务处理
修改与删除存储过程
查看与管理存储过程
最佳实践与注意事项
完整示例：用户管理系统

什么是存储过程？

存储过程（Stored Procedure）是一组为了完成特定功能的 预编译 SQL 语句 的集合，你可以把它想象成在数据库中编写的一个“函数”或“脚本”。

（图片来源网络，侵删）

它存储在数据库中，可以通过一个名字（存储过程名）来调用,并可以接收参数和返回结果。

核心特点：

预编译：存储过程在首次创建时会被数据库编译并优化，之后每次调用，都直接执行编译好的版本，而不是重新解析和编译,这大大提高了执行效率。
封装性：将复杂的业务逻辑封装在数据库中，客户端代码只需调用存储过程，无需关心具体的 SQL 实现。
可重用性：同一个存储过程可以在多个应用程序或地方被调用。
安全性：可以通过授予用户执行存储过程的权限，而不授予其直接访问底层表的权限,从而实现更细粒度的数据控制。

为什么使用存储过程？（优点与缺点）

优点

性能更高：如前所述,预编译和优化的特性使其执行速度更快。
减少网络流量：一个复杂的业务操作可能需要多条 SQL 语句，如果这些语句在客户端执行，需要多次往返数据库，而使用存储过程，只需要一次网络调用（执行存储过程）,所有操作都在数据库端完成。
代码重用与维护性：将核心逻辑集中在数据库中，当业务逻辑需要修改时，只需修改存储过程,而不需要修改所有调用它的应用程序代码。
安全性增强：可以限制用户对表的直接访问权限，只允许他们调用特定的存储过程，一个用户可以调用一个“添加用户”的存储过程，但不能直接 INSERT 到 Users 表中。
一致性：确保所有应用程序模块都使用相同的逻辑来执行某个操作,保证了数据的一致性。

缺点

可移植性差：不同数据库（如 MySQL, SQL Server, Oracle, PostgreSQL）的存储过程语法和函数有很大差异,从一个数据库迁移到另一个数据库可能需要重写大量的存储过程。
调试困难：相比于高级语言（如 Java, Python）,存储过程的调试工具和体验通常较差。
过度使用：如果将过多的业务逻辑都放在存储过程中，会导致数据库服务器负载过重，违背了“数据库只负责数据，应用负责逻辑”的分层思想。
版本控制：存储过程是数据库的一部分，与代码库分离,管理起来可能比较麻烦。

存储过程是强大的工具，但应 适度使用，最适合用于复杂的、核心的、对性能要求高的数据操作。

创建与执行存储过程

我们以 MySQL 和 SQL Server 两种最常见数据库为例进行讲解,它们的语法非常相似。

（图片来源网络，侵删）

基本语法

-- 创建存储过程
CREATE PROCEDURE procedure_name ([parameter[, ...]])
BEGIN
    -- SQL 语句
    -- 变量声明
    -- 流程控制
END;
-- 调用存储过程
CALL procedure_name ([argument[, ...]]);

第一个存储过程：`Hello, World!`

目标：创建一个不带参数的存储过程，执行后返回 "Hello, World!"。

MySQL / SQL Server 语法:

-- 创建
CREATE PROCEDURE sp_SayHello()
BEGIN
    SELECT 'Hello, World!';
END;
-- 调用
CALL sp_SayHello();

执行结果：

Hello, World!
Hello, World!

注意：

在 SQL Server 中，GO 是一批 Transact-SQL 语句的结束符，通常在创建存储过程后使用 GO 来分隔批处理。
在 SQL Server 中，默认情况下，SELECT 语句的结果会作为输出返回，在 MySQL 中也是如此。

带参数的存储过程

参数让存储过程更加灵活,参数分为三种类型：

IN：输入参数，调用者向存储过程传递值,这是最常见的参数类型。
OUT：输出参数,存储过程向调用者返回值。
INOUT：输入输出参数，既可以传入值,也可以传出值。

示例1：带 `IN` 参数

目标：创建一个存储过程,根据用户ID查询用户信息。

-- 假设有一个 Users 表
-- CREATE TABLE Users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
-- 创建存储过程
CREATE PROCEDURE sp_GetUserById(IN user_id INT)
BEGIN
    SELECT id, name, email
    FROM Users
    WHERE id = user_id;
END;
-- 调用，查询 ID 为 1 的用户
CALL sp_GetUserById(1);

示例2：带 `OUT` 参数

目标：创建一个存储过程，根据用户ID获取用户名，并通过 OUT 参数返回。

-- 创建存储过程
CREATE PROCEDURE sp_GetUserNameById(IN user_id INT, OUT user_name VARCHAR(100))
BEGIN
    SELECT name INTO user_name
    FROM Users
    WHERE id = user_id;
END;
-- 调用存储过程
-- @u_name 是一个用户定义的变量，用于接收 OUT 参数的值
CALL sp_GetUserNameById(2, @u_name);
-- 查看变量值以获取结果
SELECT @u_name;

执行结果：

@u_name
John Doe

示例3：带 `INOUT` 参数

目标：创建一个存储过程，将输入的数字乘以 2 并返回。

-- 创建存储过程
CREATE PROCEDURE sp_DoubleValue(INOUT value INT)
BEGIN
    SET value = value * 2;
END;
-- 调用存储过程
-- 先声明一个变量
SET @my_value = 10;
-- 调用，传入 @my_value，它会被修改
CALL sp_DoubleValue(@my_value);
-- 查看结果
SELECT @my_value; -- 结果应为 20

高级特性

变量声明与赋值

在存储过程中，可以使用 DECLARE 声明变量，使用 SET 或 SELECT ... INTO ... 为变量赋值。

CREATE PROCEDURE sp_DemoVariables()
BEGIN
    -- 声明变量
    DECLARE my_var INT DEFAULT 100;
    DECLARE another_var VARCHAR(50);
    -- 赋值
    SET my_var = my_var + 50;
    -- 使用 SELECT ... INTO ... 从查询结果中赋值
    SELECT 'Hello' INTO another_var;
    -- 输出变量
    SELECT my_var AS 'My Variable', another_var AS 'Another Variable';
END;
CALL sp_DemoVariables();

流程控制：`IF...ELSE`

CREATE PROCEDURE sp_CheckAge(IN age INT)
BEGIN
    IF age >= 18 THEN
        SELECT 'Adult';
    ELSE
        SELECT 'Minor';
    END IF;
END;
CALL sp_CheckAge(20); -- 输出 Adult
CALL sp_CheckAge(16); -- 输出 Minor

流程控制：`CASE`

CREATE PROCEDURE sp_GetGrade(IN score INT)
BEGIN
    DECLARE grade CHAR(1);
    CASE
        WHEN score >= 90 THEN SET grade = 'A';
        WHEN score >= 80 THEN SET grade = 'B';
        WHEN score >= 70 THEN SET grade = 'C';
        WHEN score >= 60 THEN SET grade = 'D';
        ELSE SET grade = 'F';
    END CASE;
    SELECT CONCAT('Your grade is: ', grade) AS result;
END;
CALL sp_GetGrade(85); -- 输出 Your grade is: B

循环：`WHILE`

CREATE PROCEDURE sp_CountToTen()
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= 10 DO
        SELECT counter;
        SET counter = counter + 1;
    END WHILE;
END;
-- 注意：这个存储过程会返回10个结果集
-- CALL sp_CountToTen();

游标

游标用于遍历查询结果集，这是一个比较复杂的特性，容易出错,应谨慎使用。

场景：从 Orders 表中统计每个客户的订单总数，并插入到一个新表 CustomerOrderCount 中。

-- 1. 准备表
-- CREATE TABLE Orders (id INT, customer_id INT, amount DECIMAL(10, 2));
-- INSERT INTO Orders VALUES (1, 101, 100), (2, 102, 200), (3, 101, 150);
-- CREATE TABLE CustomerOrderCount (customer_id INT, total_orders INT);
-- 2. 创建存储过程
CREATE PROCEDURE sp_CountOrdersByCustomer()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_total_orders INT;
    -- 声明游标
    -- 游标名为 order_cursor，查询结果是唯一的 customer_id
    DECLARE order_cursor CURSOR FOR
        SELECT customer_id, COUNT(*) 
        FROM Orders 
        GROUP BY customer_id;
    -- 声明 CONTINUE HANDLER
    -- 当游标没有更多数据时，将 done 设为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 打开游标
    OPEN order_cursor;
    -- 开始循环
    read_loop: LOOP
        -- 从游标中获取数据到变量
        FETCH order_cursor INTO v_customer_id, v_total_orders;
        -- 检查是否结束
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 执行操作（插入到新表）
        INSERT INTO CustomerOrderCount (customer_id, total_orders)
        VALUES (v_customer_id, v_total_orders);
    END LOOP;
    -- 关闭游标
    CLOSE order_cursor;
    SELECT 'Process finished.' AS message;
END;
-- 3. 调用
-- CALL sp_CountOrdersByCustomer();
-- SELECT * FROM CustomerOrderCount;

事务处理

存储过程可以包含一个完整的事务，确保一组操作要么全部成功,要么全部失败。

场景：从一个账户转账到另一个账户。

-- 假设有 Accounts 表
-- CREATE TABLE Accounts (id INT PRIMARY KEY, name VARCHAR(100), balance DECIMAL(10, 2));
-- INSERT INTO Accounts VALUES (1, 'Alice', 1000), (2, 'Bob', 500);
CREATE PROCEDURE sp_TransferFunds(
    IN from_acc_id INT,
    IN to_acc_id INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    -- 声明错误处理变量
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed. Transaction rolled back.' AS message;
    END;
    -- 开始事务
    START TRANSACTION;
    -- 扣款
    UPDATE Accounts SET balance = balance - amount WHERE id = from_acc_id;
    -- 付款
    UPDATE Accounts SET balance = balance + amount WHERE id = to_acc_id;
    -- 提交事务
    COMMIT;
    SELECT 'Transfer successful!' AS message;
END;
-- 调用
-- CALL sp_TransferFunds(1, 2, 100);
-- SELECT * FROM Accounts;

修改与删除存储过程

修改存储过程

使用 ALTER PROCEDURE 语句，注意,这会覆盖原有的存储过程。

ALTER PROCEDURE procedure_name
[characteristic ...]
BEGIN
    -- 新的 SQL 逻辑
END;

更安全的做法：先 DROP 再 CREATE。

DROP PROCEDURE IF EXISTS sp_GetUserById;
CREATE PROCEDURE sp_GetUserById(IN user_id INT)
BEGIN
    -- 新的实现逻辑
    SELECT id, name, email, created_at
    FROM Users
    WHERE id = user_id;
END;

删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

查看与管理存储过程

查看存储过程定义

MySQL:
```
SHOW CREATE PROCEDURE sp_GetUserById;
```

SQL Server:

-- 使用 SQL Server Management Studio (SSMS) 的对象资源管理器更方便
-- 在查询窗口中执行：
EXEC sp_helptext 'sp_GetUserById';

列出所有存储过程

MySQL:
```
SHOW PROCEDURE STATUS;
```
SQL Server:
```
SELECT name FROM sys.procedures;
```

最佳实践与注意事项

命名规范：使用清晰、一致的命名，sp_ 或 usp_ (User Stored Procedure) 前缀。
参数化查询：始终使用参数，而不是将值直接拼接到 SQL 字符串中，以防止 SQL 注入攻击。
单一职责：一个存储过程应该只做一件事，避免创建一个“上帝”存储过程来做所有事情。
注释：为复杂的逻辑添加注释，解释存储过程的功能、参数和返回值。
错误处理：使用 DECLARE ... HANDLER 来捕获和处理异常,确保事务的完整性。
限制副作用：尽量让存储过程是“纯函数”式的，即相同的输入总是产生相同的输出，并避免不必要的写操作（如 UPDATE, DELETE）,除非这是存储过程的主要目的。
性能分析：对于性能敏感的存储过程，使用数据库的执行计划工具（如 MySQL 的 EXPLAIN，SQL Server 的 Execution Plan）来分析和优化。

完整示例：用户管理系统

假设我们有一个简单的用户表,我们将创建几个存储过程来管理它。

表结构:

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

存储过程 1: 添加新用户

DELIMITER // -- MySQL 中需要修改分隔符，因为存储过程体内有分号
CREATE PROCEDURE sp_AddUser(
    IN p_username VARCHAR(50),
    IN p_password_hash VARCHAR(255),
    IN p_email VARCHAR(100),
    OUT p_user_id INT,
    OUT p_status VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'Error: User could not be added.';
        SET p_user_id = -1;
    END;
    START TRANSACTION;
    INSERT INTO Users (username, password_hash, email)
    VALUES (p_username, p_password_hash, p_email);
    -- 获取新插入用户的 ID
    SET p_user_id = LAST_INSERT_ID();
    COMMIT;
    SET p_status = 'Success: User added successfully.';
END //
DELIMITER ;
-- 调用示例:
-- CALL sp_AddUser('testuser', 'hashedpassword123', 'test@example.com', @new_id, @msg);
-- SELECT @new_id, @msg;

存储过程 2: 根据用户名获取用户信息

CREATE PROCEDURE sp_GetUserByUsername(IN p_username VARCHAR(50))
BEGIN
    SELECT id, username, email, created_at
    FROM Users
    WHERE username = p_username;
END;
-- 调用示例:
-- CALL sp_GetUserByUsername('testuser');

存储过程 3: 更新用户邮箱

CREATE PROCEDURE sp_UpdateUserEmail(
    IN p_user_id INT,
    IN p_new_email VARCHAR(100),
    OUT p_status VARCHAR(100)
)
BEGIN
    DECLARE rows_affected INT;
    UPDATE Users
    SET email = p_new_email
    WHERE id = p_user_id;
    SET rows_affected = ROW_COUNT();
    IF rows_affected > 0 THEN
        SET p_status = 'Success: Email updated.';
    ELSE
        SET p_status = 'Error: User not found.';
    END IF;
END;
-- 调用示例:
-- CALL sp_UpdateUserEmail(1, 'newemail@example.com', @status);
-- SELECT @status;

存储过程是数据库编程中一项极其重要的技能，它将计算和逻辑下移到数据层，能显著提升性能、简化应用层代码并增强安全性。

通过本教程,你已经学习了：

存储过程的基本概念和优缺点。
如何创建、执行和管理带不同类型参数的存储过程。
如何在存储过程中使用变量、流程控制、游标和事务。
了解了最佳实践和注意事项。

开始在你的项目中尝试使用存储过程吧！从简单的 CRUD 操作开始,逐步应用到更复杂的业务场景中。

SQL存储过程教程，如何快速上手编写？