MySQL存储过程 #

一、存储过程概述 #

1.1 什么是存储过程 #

存储过程是一组预编译的SQL语句，存储在数据库中，可以通过名称调用执行。

text 
┌─────────────────────────────────────┐
│          存储过程                   │
│  ┌─────────────────────────────┐   │
│  │  SQL语句集合                │   │
│  │  - 变量定义                 │   │
│  │  - 流程控制                 │   │
│  │  - 错误处理                 │   │
│  └─────────────────────────────┘   │
│              ↓                      │
│         数据库执行                  │
└─────────────────────────────────────┘

1.2 存储过程的优点 #

优点 说明
性能提升 预编译，减少网络传输
代码复用 一次编写，多次调用
安全性 可以控制数据访问权限
减少网络流量 只传输调用参数和结果

1.3 存储过程的缺点 #

缺点 说明
调试困难 调试工具有限
可移植性差 不同数据库语法不同
维护成本 业务逻辑分散

二、创建存储过程 #

2.1 基本语法 #

sql 
CREATE PROCEDURE procedure_name([parameters])
BEGIN
    -- SQL语句
END;

2.2 第一个存储过程 #

sql 
-- 修改分隔符
DELIMITER //

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

DELIMITER ;

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

2.3 查看存储过程 #

sql 
-- 查看存储过程状态
SHOW PROCEDURE STATUS WHERE db = 'mydb';

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

-- 从information_schema查询
SELECT 
    routine_name,
    routine_definition
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'mydb';

2.4 删除存储过程 #

sql 
DROP PROCEDURE get_all_users;

-- 如果存在则删除
DROP PROCEDURE IF EXISTS get_all_users;

三、参数 #

3.1 参数类型 #

类型 说明
IN 输入参数（默认）
OUT 输出参数
INOUT 输入输出参数

3.2 IN参数 #

sql 
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);

3.3 OUT参数 #

sql 
DELIMITER //

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

DELIMITER ;

-- 调用
CALL get_user_count(@count);
SELECT @count;

3.4 INOUT参数 #

sql 
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

3.5 多个参数 #

sql 
DELIMITER //

CREATE PROCEDURE get_users_by_age_range(
    IN min_age INT,
    IN max_age INT,
    OUT total_count INT
)
BEGIN
    SELECT * FROM users WHERE age BETWEEN min_age AND max_age;
    SELECT COUNT(*) INTO total_count FROM users WHERE age BETWEEN min_age AND max_age;
END //

DELIMITER ;

-- 调用
CALL get_users_by_age_range(20, 30, @count);
SELECT @count;

四、变量 #

4.1 局部变量 #

sql 
DELIMITER //

CREATE PROCEDURE test_variables()
BEGIN
    -- 声明局部变量
    DECLARE v_name VARCHAR(50) DEFAULT 'Unknown';
    DECLARE v_age INT;
    DECLARE v_count INT DEFAULT 0;
    
    -- 赋值
    SET v_name = 'John';
    SET v_age = 25;
    
    -- 使用SELECT INTO赋值
    SELECT COUNT(*) INTO v_count FROM users;
    
    -- 输出
    SELECT v_name, v_age, v_count;
END //

DELIMITER ;

4.2 用户变量 #

sql 
DELIMITER //

CREATE PROCEDURE test_user_variables()
BEGIN
    -- 用户变量以@开头
    SET @user_name = 'John';
    SET @user_age = 25;
    
    SELECT @user_name, @user_age;
END //

DELIMITER ;

-- 用户变量在会话中有效
SELECT @user_name;

4.3 系统变量 #

sql 
DELIMITER //

CREATE PROCEDURE test_system_variables()
BEGIN
    -- 查看系统变量
    SELECT @@version;
    SELECT @@autocommit;
    SELECT @@max_connections;
    
    -- 设置会话变量
    SET SESSION autocommit = 0;
END //

DELIMITER ;

五、流程控制 #

5.1 IF语句 #

sql 
DELIMITER //

CREATE PROCEDURE check_age(IN age INT)
BEGIN
    IF age < 18 THEN
        SELECT '未成年' AS result;
    ELSEIF age < 60 THEN
        SELECT '成年' AS result;
    ELSE
        SELECT '老年' AS result;
    END IF;
END //

DELIMITER ;

-- 调用
CALL check_age(25);

5.2 CASE语句 #

sql 
DELIMITER //

CREATE PROCEDURE get_grade(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 grade;
END //

DELIMITER ;

-- 另一种形式
DELIMITER //

CREATE PROCEDURE get_status_text(IN status INT)
BEGIN
    CASE status
        WHEN 1 THEN SELECT 'Active' AS status_text;
        WHEN 0 THEN SELECT 'Inactive' AS status_text;
        ELSE SELECT 'Unknown' AS status_text;
    END CASE;
END //

DELIMITER ;

5.3 WHILE循环 #

sql 
DELIMITER //

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

DELIMITER ;

-- 调用
CALL sum_numbers(100, @total);
SELECT @total;  -- 5050

5.4 REPEAT循环 #

sql 
DELIMITER //

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

DELIMITER ;

5.5 LOOP循环 #

sql 
DELIMITER //

CREATE PROCEDURE sum_loop(IN n INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET result = 0;
    
    my_loop: LOOP
        SET result = result + i;
        SET i = i + 1;
        
        IF i > n THEN
            LEAVE my_loop;
        END IF;
    END LOOP;
END //

DELIMITER ;

5.6 ITERATE语句 #

sql 
DELIMITER //

CREATE PROCEDURE sum_even(IN n INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET result = 0;
    
    my_loop: LOOP
        SET i = i + 1;
        
        IF i > n THEN
            LEAVE my_loop;
        END IF;
        
        IF i % 2 != 0 THEN
            ITERATE my_loop;  -- 跳过奇数
        END IF;
        
        SET result = result + i;
    END LOOP;
END //

DELIMITER ;

六、游标 #

6.1 游标基本用法 #

sql 
DELIMITER //

CREATE PROCEDURE process_users()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_email VARCHAR(100);
    
    -- 声明游标
    DECLARE cur_users CURSOR FOR 
        SELECT id, name, email FROM users;
    
    -- 声明结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN cur_users;
    
    -- 循环读取
    read_loop: LOOP
        FETCH cur_users INTO v_id, v_name, v_email;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据
        SELECT v_id, v_name, v_email;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur_users;
END //

DELIMITER ;

6.2 游标更新数据 #

sql 
DELIMITER //

CREATE PROCEDURE update_user_status()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_last_login DATETIME;
    
    DECLARE cur_users CURSOR FOR 
        SELECT id, last_login FROM users;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur_users;
    
    update_loop: LOOP
        FETCH cur_users INTO v_id, v_last_login;
        
        IF done THEN
            LEAVE update_loop;
        END IF;
        
        -- 更新状态
        IF v_last_login < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN
            UPDATE users SET status = 0 WHERE id = v_id;
        END IF;
    END LOOP;
    
    CLOSE cur_users;
END //

DELIMITER ;

七、错误处理 #

7.1 DECLARE HANDLER #

sql 
DELIMITER //

CREATE PROCEDURE safe_insert(
    IN p_name VARCHAR(50),
    IN p_email VARCHAR(100)
)
BEGIN
    -- 声明错误处理程序
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back' AS message;
    END;
    
    START TRANSACTION;
    
    INSERT INTO users (name, email) VALUES (p_name, p_email);
    
    COMMIT;
    SELECT 'Insert successful' AS message;
END //

DELIMITER ;

7.2 获取错误信息 #

sql 
DELIMITER //

CREATE PROCEDURE insert_with_error_info(
    IN p_name VARCHAR(50),
    IN p_email VARCHAR(100)
)
BEGIN
    DECLARE v_error_code CHAR(5) DEFAULT '00000';
    DECLARE v_error_msg TEXT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_error_code = RETURNED_SQLSTATE,
            v_error_msg = MESSAGE_TEXT;
        
        SELECT CONCAT('Error: ', v_error_code, ' - ', v_error_msg) AS error;
    END;
    
    INSERT INTO users (name, email) VALUES (p_name, p_email);
END //

DELIMITER ;

7.3 自定义错误 #

sql 
DELIMITER //

CREATE PROCEDURE check_user_age(IN p_age INT)
BEGIN
    IF p_age < 0 OR p_age > 150 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid age value';
    END IF;
    
    SELECT 'Age is valid' AS result;
END //

DELIMITER ;

八、存储过程示例 #

8.1 分页查询 #

sql 
DELIMITER //

CREATE PROCEDURE get_users_page(
    IN p_page INT,
    IN p_page_size INT
)
BEGIN
    DECLARE v_offset INT;
    SET v_offset = (p_page - 1) * p_page_size;
    
    SELECT * FROM users
    ORDER BY id
    LIMIT v_offset, p_page_size;
END //

DELIMITER ;

-- 调用：第2页，每页10条
CALL get_users_page(2, 10);

8.2 批量插入 #

sql 
DELIMITER //

CREATE PROCEDURE batch_insert_users(IN p_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= p_count DO
        INSERT INTO users (name, email, created_at)
        VALUES (
            CONCAT('User', i),
            CONCAT('user', i, '@example.com'),
            NOW()
        );
        SET i = i + 1;
    END WHILE;
    
    SELECT CONCAT('Inserted ', p_count, ' users') AS result;
END //

DELIMITER ;

8.3 统计报表 #

sql 
DELIMITER //

CREATE PROCEDURE get_sales_report(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    -- 总销售额
    SELECT 
        COUNT(*) AS total_orders,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_amount
    FROM orders
    WHERE order_date BETWEEN p_start_date AND p_end_date;
    
    -- 每日销售额
    SELECT 
        DATE(order_date) AS sale_date,
        COUNT(*) AS order_count,
        SUM(amount) AS daily_amount
    FROM orders
    WHERE order_date BETWEEN p_start_date AND p_end_date
    GROUP BY DATE(order_date)
    ORDER BY sale_date;
END //

DELIMITER ;

九、总结 #

存储过程要点：

元素 说明
参数 IN, OUT, INOUT
变量 DECLARE, SET, SELECT INTO
流程控制 IF, CASE, WHILE, REPEAT, LOOP
游标 CURSOR, FETCH
错误处理 DECLARE HANDLER, SIGNAL

最佳实践：

  1. 使用有意义的命名
  2. 添加注释说明
  3. 处理可能的错误
  4. 避免过于复杂的逻辑
  5. 定期维护和优化

下一步，让我们学习事务！

最后更新：2026-03-26