跳到主要内容

SQL 存储过程

存储过程是一组预编译的 SQL 语句集合，存储在数据库服务器端，可以被多次调用。本章将详细介绍存储过程的创建和使用。

什么是存储过程？​

存储过程（Stored Procedure）是数据库中的一个重要对象，它封装了一组 SQL 操作，可以被应用程序调用执行。

为什么使用存储过程？​

优点：

  • 减少网络流量：多条 SQL 语句只需一次网络调用
  • 提高性能：预编译并缓存执行计划
  • 代码复用：多个应用程序可以共享同一个存储过程
  • 安全性：可以限制用户只能通过存储过程访问数据
  • 封装业务逻辑：将复杂的业务逻辑封装在数据库层

缺点：

  • 可移植性差：不同数据库的存储过程语法差异较大
  • 调试困难：数据库调试工具不如应用开发工具丰富
  • 扩展性受限：难以进行水平扩展

创建存储过程​

基本语法​

CREATE PROCEDURE procedure_name([参数列表])
BEGIN
    -- SQL 语句
END;

第一个存储过程​

-- 修改分隔符，避免与 SQL 语句中的分号冲突
DELIMITER //

CREATE PROCEDURE get_all_users()
BEGIN
    SELECT * FROM users;
END //

DELIMITER ;

-- 调用存储过程
CALL get_all_users();

解释：

  • DELIMITER // 将语句分隔符改为 //，这样存储过程体内的分号不会被误认为语句结束
  • 存储过程创建后，使用 CALL 命令调用
  • 最后恢复默认的分隔符 ;

参数类型​

存储过程支持三种参数类型：

1. IN 参数（输入参数）​

DELIMITER //

CREATE PROCEDURE get_user_by_id(
    IN user_id INT
)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

DELIMITER ;

-- 调用
CALL get_user_by_id(1);

2. OUT 参数（输出参数）​

DELIMITER //

CREATE PROCEDURE get_user_count(
    OUT total_count INT
)
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END //

DELIMITER ;

-- 调用
SET @count = 0;
CALL get_user_count(@count);
SELECT @count;  -- 输出用户总数

3. INOUT 参数（输入输出参数）​

DELIMITER //

CREATE PROCEDURE double_value(
    INOUT num INT
)
BEGIN
    SET num = num * 2;
END //

DELIMITER ;

-- 调用
SET @value = 10;
CALL double_value(@value);
SELECT @value;  -- 输出 20

完整示例​

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_account VARCHAR(20),
    IN to_account VARCHAR(20),
    IN amount DECIMAL(10, 2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE from_balance DECIMAL(10, 2);
    
    -- 检查转出账户余额
    SELECT balance INTO from_balance 
    FROM accounts 
    WHERE account_no = from_account;
    
    IF from_balance < amount THEN
        SET result = '余额不足';
    ELSE
        -- 执行转账
        START TRANSACTION;
        
        UPDATE accounts SET balance = balance - amount 
        WHERE account_no = from_account;
        
        UPDATE accounts SET balance = balance + amount 
        WHERE account_no = to_account;
        
        COMMIT;
        SET result = '转账成功';
    END IF;
END //

DELIMITER ;

-- 调用
SET @result = '';
CALL transfer_money('A001', 'B002', 100.00, @result);
SELECT @result;

变量​

局部变量​

使用 DECLARE 声明局部变量：

DELIMITER //

CREATE PROCEDURE variable_demo()
BEGIN
    -- 声明变量
    DECLARE name VARCHAR(50) DEFAULT '张三';
    DECLARE age INT;
    DECLARE salary DECIMAL(10, 2);
    
    -- 赋值
    SET age = 25;
    SELECT 5000.00 INTO salary;
    
    -- 使用变量
    SELECT name, age, salary;
END //

DELIMITER ;

用户变量​

使用 @ 前缀的用户变量在会话期间一直存在：

-- 设置用户变量
SET @total = 0;

-- 在存储过程中使用
DELIMITER //
CREATE PROCEDURE count_users()
BEGIN
    SELECT COUNT(*) INTO @total FROM users;
END //
DELIMITER ;

CALL count_users();
SELECT @total;

会话变量​

使用 @@ 前缀访问系统变量：

DELIMITER //

CREATE PROCEDURE show_settings()
BEGIN
    -- 查看当前隔离级别
    SELECT @@transaction_isolation;
    
    -- 查看自动提交设置
    SELECT @@autocommit;
END //

DELIMITER ;

流程控制​

IF 条件判断​

DELIMITER //

CREATE PROCEDURE get_grade(
    IN score INT,
    OUT grade CHAR(1)
)
BEGIN
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSEIF score >= 70 THEN
        SET grade = 'C';
    ELSEIF score >= 60 THEN
        SET grade = 'D';
    ELSE
        SET grade = 'F';
    END IF;
END //

DELIMITER ;

-- 调用
SET @grade = '';
CALL get_grade(85, @grade);
SELECT @grade;  -- B

CASE 语句​

DELIMITER //

CREATE PROCEDURE get_day_name(
    IN day_num INT,
    OUT day_name VARCHAR(10)
)
BEGIN
    CASE day_num
        WHEN 1 THEN SET day_name = '星期一';
        WHEN 2 THEN SET day_name = '星期二';
        WHEN 3 THEN SET day_name = '星期三';
        WHEN 4 THEN SET day_name = '星期四';
        WHEN 5 THEN SET day_name = '星期五';
        WHEN 6 THEN SET day_name = '星期六';
        WHEN 7 THEN SET day_name = '星期日';
        ELSE SET day_name = '无效';
    END CASE;
END //

DELIMITER ;

-- 搜索形式 CASE
DELIMITER //

CREATE PROCEDURE classify_product(
    IN price DECIMAL(10, 2),
    OUT category VARCHAR(20)
)
BEGIN
    CASE
        WHEN price < 100 THEN SET category = '低价商品';
        WHEN price < 500 THEN SET category = '中价商品';
        WHEN price < 1000 THEN SET category = '高价商品';
        ELSE SET category = '奢侈品';
    END CASE;
END //

DELIMITER ;

WHILE 循环​

DELIMITER //

CREATE PROCEDURE sum_numbers(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 调用
SET @sum = 0;
CALL sum_numbers(100, @sum);
SELECT @sum;  -- 5050

REPEAT 循环​

DELIMITER //

CREATE PROCEDURE sum_numbers_repeat(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    REPEAT
        SET total = total + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;
END //

DELIMITER ;

WHILE vs REPEAT：

  • WHILE：先判断条件，再执行循环体（可能一次都不执行）
  • REPEAT：先执行循环体，再判断条件（至少执行一次）

LOOP 循环​

DELIMITER //

CREATE PROCEDURE sum_numbers_loop(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    add_loop: LOOP
        SET total = total + i;
        SET i = i + 1;
        
        IF i > n THEN
            LEAVE add_loop;  -- 退出循环
        END IF;
    END LOOP;
END //

DELIMITER ;

ITERATE 继续循环​

ITERATE 类似于其他语言的 continue：

DELIMITER //

CREATE PROCEDURE sum_even_numbers(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET total = 0;
    
    even_loop: LOOP
        SET i = i + 1;
        
        IF i > n THEN
            LEAVE even_loop;
        END IF;
        
        IF i % 2 = 1 THEN
            ITERATE even_loop;  -- 跳过奇数，继续下一次循环
        END IF;
        
        SET total = total + i;
    END LOOP;
END //

DELIMITER ;

-- 调用：计算 1 到 10 之间的偶数和
SET @sum = 0;
CALL sum_even_numbers(10, @sum);
SELECT @sum;  -- 30 (2 + 4 + 6 + 8 + 10)

游标​

游标用于逐行处理查询结果：

DELIMITER //

CREATE PROCEDURE process_orders(
    IN order_date DATE,
    OUT total_amount DECIMAL(12, 2)
)
BEGIN
    -- 声明局部变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_total DECIMAL(10, 2);
    
    -- 声明游标
    DECLARE order_cursor CURSOR FOR
        SELECT id, total FROM orders WHERE order_date = order_date;
    
    -- 声明结束处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET total_amount = 0;
    
    -- 打开游标
    OPEN order_cursor;
    
    -- 循环读取
    read_loop: LOOP
        FETCH order_cursor INTO order_id, order_total;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理每一行
        SET total_amount = total_amount + order_total;
        
        -- 可以执行其他操作
        -- UPDATE orders SET processed = TRUE WHERE id = order_id;
    END LOOP;
    
    -- 关闭游标
    CLOSE order_cursor;
END //

DELIMITER ;

游标使用步骤：

  1. 声明游标（DECLARE cursor_name CURSOR FOR SELECT...）
  2. 声明结束处理器（DECLARE CONTINUE HANDLER FOR NOT FOUND）
  3. 打开游标（OPEN cursor_name）
  4. 循环读取数据（FETCH cursor_name INTO variables）
  5. 关闭游标（CLOSE cursor_name）

异常处理​

使用 DECLARE HANDLER 处理异常：

DELIMITER //

CREATE PROCEDURE safe_transfer(
    IN from_account VARCHAR(20),
    IN to_account VARCHAR(20),
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败，已回滚' AS message;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount 
    WHERE account_no = from_account;
    
    UPDATE accounts SET balance = balance + amount 
    WHERE account_no = to_account;
    
    COMMIT;
    SELECT '转账成功' AS message;
END //

DELIMITER ;

处理器类型​

类型说明
CONTINUE处理异常后继续执行
EXIT处理异常后退出存储过程

异常条件​

-- 捕获特定错误码
DECLARE HANDLER FOR 1062  -- 主键重复
BEGIN
    SELECT '记录已存在' AS message;
END;

-- 捕获 SQLSTATE
DECLARE HANDLER FOR SQLSTATE '23000'  -- 完整性约束违反
BEGIN
    SELECT '违反约束' AS message;
END;

-- 捕获多个条件
DECLARE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
    SELECT '发生错误或警告' AS message;
END;

-- 使用命名条件
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE HANDLER FOR duplicate_key
BEGIN
    SELECT '主键重复' AS message;
END;

获取错误信息​

DELIMITER //

CREATE PROCEDURE insert_user(
    IN user_name VARCHAR(50),
    IN user_email VARCHAR(100)
)
BEGIN
    DECLARE error_code CHAR(5) DEFAULT '00000';
    DECLARE error_msg TEXT;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            error_code = RETURNED_SQLSTATE,
            error_msg = MESSAGE_TEXT;
        
        SELECT error_code AS code, error_msg AS message;
    END;
    
    INSERT INTO users (name, email) VALUES (user_name, user_email);
END //

DELIMITER ;

查看和管理存储过程​

查看存储过程​

-- 查看数据库中的存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- 查看存储过程定义
SHOW CREATE PROCEDURE procedure_name;

-- 从信息模式查询
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

修改存储过程​

-- MySQL 不支持直接修改存储过程，需要先删除再创建
DROP PROCEDURE IF EXISTS procedure_name;

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- 新的定义
END //
DELIMITER ;

删除存储过程​

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;

存储函数​

存储函数与存储过程类似，但必须返回一个值：

DELIMITER //

CREATE FUNCTION calculate_tax(
    price DECIMAL(10, 2),
    tax_rate DECIMAL(5, 4)
)
RETURNS DECIMAL(10, 2)
DETERMINISTIC  -- 相同输入总是返回相同结果
BEGIN
    DECLARE tax DECIMAL(10, 2);
    SET tax = price * tax_rate;
    RETURN tax;
END //

DELIMITER ;

-- 在 SQL 语句中使用
SELECT 
    product_name,
    price,
    calculate_tax(price, 0.13) AS tax,
    price + calculate_tax(price, 0.13) AS total
FROM products;

存储过程 vs 存储函数​

特性存储过程存储函数
返回值可以有多个 OUT 参数必须返回单个值
调用方式CALL procedure()可在 SQL 中使用
SQL 语句可以执行各种 SQL不能执行修改表的 SQL
参数类型IN、OUT、INOUT只有 IN
用途封装业务逻辑计算和转换数据

动态 SQL​

使用 PREPARE 和 EXECUTE 执行动态 SQL：

DELIMITER //

CREATE PROCEDURE search_table(
    IN table_name VARCHAR(50),
    IN column_name VARCHAR(50),
    IN search_value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, 
                      ' WHERE ', column_name, ' = ?');
    
    PREPARE stmt FROM @sql;
    SET @value = search_value;
    EXECUTE stmt USING @value;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用
CALL search_table('users', 'name', '张三');

最佳实践​

1. 使用有意义的名称​

-- 好的命名
CREATE PROCEDURE get_user_by_email(IN email VARCHAR(100))
CREATE PROCEDURE calculate_order_total(IN order_id INT)

-- 不好的命名
CREATE PROCEDURE sp1(IN p1 VARCHAR(100))
CREATE PROCEDURE proc(IN x INT)

2. 添加注释​

DELIMITER //

-- 功能：计算用户订单总金额
-- 参数：user_id - 用户ID
-- 返回：订单总金额
CREATE PROCEDURE get_user_order_total(
    IN p_user_id INT,
    OUT p_total DECIMAL(12, 2)
)
BEGIN
    -- 验证用户是否存在
    IF NOT EXISTS (SELECT 1 FROM users WHERE id = p_user_id) THEN
        SET p_total = -1;  -- 用户不存在返回 -1
    ELSE
        -- 计算总金额
        SELECT COALESCE(SUM(total), 0) INTO p_total
        FROM orders
        WHERE user_id = p_user_id;
    END IF;
END //

DELIMITER ;

3. 错误处理​

DELIMITER //

CREATE PROCEDURE safe_insert(
    IN p_name VARCHAR(50),
    IN p_email VARCHAR(100)
)
BEGIN
    DECLARE v_error VARCHAR(200);
    
    -- 错误处理器
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_error = MESSAGE_TEXT;
        SELECT CONCAT('错误：', v_error) AS error_message;
    END;
    
    -- 参数验证
    IF p_name IS NULL OR p_email IS NULL THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '姓名和邮箱不能为空';
    END IF;
    
    INSERT INTO users (name, email) VALUES (p_name, p_email);
    SELECT '插入成功' AS message;
END //

DELIMITER ;

小结​

本章我们学习了：

  1. 存储过程概念：预编译的 SQL 语句集合
  2. 参数类型：IN、OUT、INOUT
  3. 变量：局部变量、用户变量、会话变量
  4. 流程控制：IF、CASE、WHILE、REPEAT、LOOP
  5. 游标：逐行处理查询结果
  6. 异常处理：DECLARE HANDLER
  7. 存储函数：返回单个值的存储程序

练习​

  1. 创建一个存储过程，根据用户 ID 查询用户的所有订单
  2. 创建一个存储过程，实现分页查询
  3. 使用游标处理查询结果，计算每个部门的平均工资
  4. 创建一个存储函数，计算两个日期之间的工作日数量

参考资源​