存储过程和函数

存储过程和存储函数是MySQL中非常重要的编程特性，它们允许将一组SQL语句封装起来，形成一个可重复使用的代码块。

什么是存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户可以通过指定存储过程的名字并给定参数（如果有）来调用执行。

存储过程的优点

-- 存储过程的优点：
-- 1. 提高代码重用性：一次编写，多次调用
-- 2. 减少网络流量：只需传输调用语句，而非大量SQL
-- 3. 提高性能：预编译执行，比动态SQL更快
-- 4. 安全性：可以限制用户只能通过存储过程操作数据
-- 5. 封装业务逻辑：将复杂业务逻辑封装在数据库层

创建存储过程

基本语法

-- 创建存储过程的基本语法
CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    -- SQL语句
END;

-- 注意：如果存储过程体只有一条语句，可以省略BEGIN...END

第一个存储过程

-- 创建一个简单的存储过程：查询所有员工
DELIMITER //  -- 更改语句结束符，避免与存储过程中的分号冲突

CREATE PROCEDURE GetAllEmployees()
BEGIN
    -- 查询所有员工信息
    SELECT * FROM employees;
END //

DELIMITER ;  -- 恢复默认的语句结束符

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

使用参数

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

-- IN：输入参数（默认），调用者向存储过程传递值
-- OUT：输出参数，存储过程向调用者返回值
-- INOUT：输入输出参数，既可以传入也可以返回

-- 示例表结构
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- IN参数示例：根据部门查询员工
DELIMITER //

CREATE PROCEDURE GetEmployeesByDept(
    IN dept_name VARCHAR(50)  -- 输入参数：部门名称
)
BEGIN
    -- 根据部门名称查询员工
    SELECT id, name, salary 
    FROM employees 
    WHERE department = dept_name;
END //

DELIMITER ;

-- 调用带IN参数的存储过程
CALL GetEmployeesByDept('技术部');

OUT参数示例

-- OUT参数示例：统计部门员工数量
DELIMITER //

CREATE PROCEDURE CountEmployeesByDept(
    IN dept_name VARCHAR(50),    -- 输入参数：部门名称
    OUT emp_count INT            -- 输出参数：员工数量
)
BEGIN
    -- 统计指定部门的员工数量
    SELECT COUNT(*) INTO emp_count  -- 将结果存入输出参数
    FROM employees 
    WHERE department = dept_name;
END //

DELIMITER ;

-- 调用带OUT参数的存储过程
-- 需要先定义一个变量来接收输出值
CALL CountEmployeesByDept('技术部', @count);

-- 查看输出参数的值
SELECT @count AS '技术部员工数量';

INOUT参数示例

-- INOUT参数示例：将数值翻倍
DELIMITER //

CREATE PROCEDURE DoubleValue(
    INOUT num INT  -- 既是输入也是输出
)
BEGIN
    -- 将传入的值翻倍后返回
    SET num = num * 2;
END //

DELIMITER ;

-- 调用带INOUT参数的存储过程
SET @value = 10;          -- 设置初始值
CALL DoubleValue(@value); -- 调用存储过程
SELECT @value;            -- 结果为20

变量

局部变量

-- 局部变量：只在存储过程内部有效
-- 使用DECLARE声明，SET或SELECT INTO赋值

DELIMITER //

CREATE PROCEDURE VariableDemo()
BEGIN
    -- 声明局部变量
    DECLARE v_name VARCHAR(50);        -- 声明变量
    DECLARE v_salary DECIMAL(10,2) DEFAULT 0;  -- 声明并设置默认值
    DECLARE v_count INT;
    
    -- 使用SET赋值
    SET v_name = '张三';
    SET v_salary = 8000.00;
    
    -- 使用SELECT INTO赋值
    SELECT COUNT(*) INTO v_count
    FROM employees;
    
    -- 输出变量值
    SELECT v_name AS '姓名', v_salary AS '薪资', v_count AS '员工总数';
END //

DELIMITER ;

CALL VariableDemo();

用户变量

-- 用户变量：以@开头，在会话期间有效
-- 不需要声明，直接使用SET赋值

-- 设置用户变量
SET @user_name = '李四';
SET @user_age := 25;  -- 也可以使用:=

-- 在存储过程中使用用户变量
DELIMITER //

CREATE PROCEDURE UseUserVariable()
BEGIN
    -- 用户变量可以在存储过程内外使用
    SELECT @user_name AS '用户名', @user_age AS '年龄';
    
    -- 修改用户变量
    SET @user_age = @user_age + 1;
END //

DELIMITER ;

CALL UseUserVariable();
SELECT @user_age;  -- 结果为26

系统变量

-- 系统变量：MySQL服务器维护的变量
-- @@global：全局系统变量
-- @@session：会话系统变量

-- 查看系统变量
SELECT @@version;           -- MySQL版本
SELECT @@autocommit;        -- 自动提交状态
SELECT @@max_connections;   -- 最大连接数

-- 在存储过程中使用系统变量
DELIMITER //

CREATE PROCEDURE ShowSystemVars()
BEGIN
    -- 显示当前数据库
    SELECT DATABASE() AS '当前数据库';
    
    -- 显示当前用户
    SELECT CURRENT_USER() AS '当前用户';
    
    -- 显示版本
    SELECT @@version AS 'MySQL版本';
END //

DELIMITER ;

流程控制

IF语句

-- IF条件语句
DELIMITER //

CREATE PROCEDURE CheckSalary(
    IN emp_id INT,
    OUT result VARCHAR(20)
)
BEGIN
    DECLARE v_salary DECIMAL(10,2);
    
    -- 获取员工薪资
    SELECT salary INTO v_salary
    FROM employees
    WHERE id = emp_id;
    
    -- 根据薪资判断等级
    IF v_salary >= 10000 THEN
        SET result = '高薪';
    ELSEIF v_salary >= 5000 THEN
        SET result = '中等';
    ELSE
        SET result = '基础';
    END IF;
END //

DELIMITER ;

-- 测试
CALL CheckSalary(1, @level);
SELECT @level AS '薪资等级';

CASE语句

-- CASE语句：多条件判断
DELIMITER //

CREATE PROCEDURE GetQuarter(
    IN month_num INT,
    OUT quarter_name VARCHAR(20)
)
BEGIN
    -- 使用CASE判断季度
    CASE 
        WHEN month_num IN (1, 2, 3) THEN
            SET quarter_name = '第一季度';
        WHEN month_num IN (4, 5, 6) THEN
            SET quarter_name = '第二季度';
        WHEN month_num IN (7, 8, 9) THEN
            SET quarter_name = '第三季度';
        WHEN month_num IN (10, 11, 12) THEN
            SET quarter_name = '第四季度';
        ELSE
            SET quarter_name = '无效月份';
    END CASE;
END //

DELIMITER ;

-- 测试
CALL GetQuarter(5, @q);
SELECT @q;  -- 第二季度

WHILE循环

-- WHILE循环：先判断后执行
DELIMITER //

CREATE PROCEDURE SumNumbers(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    -- 计算1到n的和
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 测试：计算1到100的和
CALL SumNumbers(100, @result);
SELECT @result;  -- 5050

REPEAT循环

-- REPEAT循环：先执行后判断，至少执行一次
DELIMITER //

CREATE PROCEDURE SumNumbersRepeat(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    -- 计算1到n的和
    REPEAT
        SET total = total + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;  -- 条件满足时退出
END //

DELIMITER ;

LOOP循环

-- LOOP循环：需要配合LEAVE退出
DELIMITER //

CREATE PROCEDURE SumNumbersLoop(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    -- 使用标签和LOOP
    add_loop: LOOP
        SET total = total + i;
        SET i = i + 1;
        
        -- 满足条件时退出循环
        IF i > n THEN
            LEAVE add_loop;  -- 退出标签指定的循环
        END IF;
    END LOOP add_loop;
END //

DELIMITER ;

ITERATE语句

-- ITERATE：跳过本次循环，继续下一次（类似continue）
DELIMITER //

CREATE PROCEDURE SumEvenNumbers(
    IN n INT,
    OUT total INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET total = 0;
    
    even_loop: LOOP
        SET i = i + 1;
        
        -- 超过n时退出
        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 even_loop;
END //

DELIMITER ;

-- 测试：计算1到10的偶数和
CALL SumEvenNumbers(10, @result);
SELECT @result;  -- 30 (2+4+6+8+10)

游标

游标（Cursor）用于逐行处理查询结果集。

基本使用

-- 游标的四个步骤：
-- 1. 声明游标
-- 2. 打开游标
-- 3. 获取数据
-- 4. 关闭游标

DELIMITER //

CREATE PROCEDURE ProcessEmployees()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_salary DECIMAL(10,2);
    
    -- 声明游标：查询薪资低于5000的员工
    DECLARE emp_cursor CURSOR FOR
        SELECT id, name, salary 
        FROM employees 
        WHERE salary < 5000;
    
    -- 声明结束处理程序：当没有更多数据时设置done为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        emp_id INT,
        emp_name VARCHAR(50),
        old_salary DECIMAL(10,2),
        new_salary DECIMAL(10,2)
    );
    
    -- 清空临时表
    TRUNCATE TABLE temp_results;
    
    -- 打开游标
    OPEN emp_cursor;
    
    -- 循环获取数据
    read_loop: LOOP
        -- 获取一行数据到变量
        FETCH emp_cursor INTO v_id, v_name, v_salary;
        
        -- 检查是否结束
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据：计算加薪后的薪资
        INSERT INTO temp_results VALUES 
            (v_id, v_name, v_salary, v_salary * 1.1);
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
    
    -- 返回结果
    SELECT * FROM temp_results;
    
    -- 删除临时表
    DROP TEMPORARY TABLE temp_results;
END //

DELIMITER ;

CALL ProcessEmployees();

错误处理

DECLARE HANDLER

-- 错误处理程序语法
-- DECLARE handler_type HANDLER FOR condition_value[,...] statement

-- handler_type:
--   CONTINUE: 继续执行
--   EXIT: 退出存储过程
--   UNDO: 不支持

-- condition_value可以是：
--   SQLSTATE值
--   错误代码
--   SQLWARNING (SQLSTATE以'01'开头)
--   NOT FOUND (SQLSTATE以'02'开头)
--   SQLEXCEPTION (其他SQLSTATE)

DELIMITER //

CREATE PROCEDURE InsertEmployee(
    IN p_name VARCHAR(50),
    IN p_dept VARCHAR(50),
    IN p_salary DECIMAL(10,2)
)
BEGIN
    -- 声明错误处理变量
    DECLARE exit_status VARCHAR(100) DEFAULT '成功';
    
    -- 声明错误处理程序
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 发生错误时执行
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO,
            @text = MESSAGE_TEXT;
            
        SET exit_status = CONCAT('错误: ', @errno, ' - ', @text);
        SELECT exit_status AS '执行结果';
        ROLLBACK;  -- 回滚事务
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 插入数据
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (p_name, p_dept, p_salary, CURDATE());
    
    -- 提交事务
    COMMIT;
    
    SELECT exit_status AS '执行结果';
END //

DELIMITER ;

自定义错误

-- 使用SIGNAL抛出自定义错误
DELIMITER //

CREATE PROCEDURE UpdateSalary(
    IN p_emp_id INT,
    IN p_new_salary DECIMAL(10,2)
)
BEGIN
    DECLARE v_old_salary DECIMAL(10,2);
    
    -- 获取当前薪资
    SELECT salary INTO v_old_salary
    FROM employees
    WHERE id = p_emp_id;
    
    -- 如果员工不存在
    IF v_old_salary IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '员工不存在', MYSQL_ERRNO = 1001;
    END IF;
    
    -- 如果新薪资低于原薪资的50%
    IF p_new_salary < v_old_salary * 0.5 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪资降幅不能超过50%', MYSQL_ERRNO = 1002;
    END IF;
    
    -- 更新薪资
    UPDATE employees 
    SET salary = p_new_salary 
    WHERE id = p_emp_id;
    
    SELECT '薪资更新成功' AS '结果';
END //

DELIMITER ;

存储函数

存储函数与存储过程类似，但有以下区别：

-- 存储函数与存储过程的区别：
-- 1. 函数必须返回一个值，存储过程可以不返回
-- 2. 函数只能在SQL语句中调用，存储过程使用CALL
-- 3. 函数不能有OUT和INOUT参数
-- 4. 函数中不能使用某些SQL语句（如INSERT/UPDATE/DELETE等）

-- 创建存储函数
DELIMITER //

CREATE FUNCTION CalculateBonus(
    emp_id INT,
    performance_score DECIMAL(5,2)
) RETURNS DECIMAL(10,2)
DETERMINISTIC  -- 确定性函数：相同输入总是返回相同结果
READS SQL DATA  -- 声明函数读取数据
BEGIN
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_bonus DECIMAL(10,2);
    
    -- 获取员工薪资
    SELECT salary INTO v_salary
    FROM employees
    WHERE id = emp_id;
    
    -- 根据绩效计算奖金
    -- 绩效分数 1-5，对应奖金比例 10%-50%
    SET v_bonus = v_salary * (performance_score / 10);
    
    RETURN v_bonus;
END //

DELIMITER ;

-- 在SQL语句中使用函数
SELECT 
    id,
    name,
    salary,
    CalculateBonus(id, 4.5) AS bonus  -- 调用函数
FROM employees
WHERE department = '技术部';

函数的特性声明

-- 函数特性声明
DELIMITER //

CREATE FUNCTION GetDeptName(emp_id INT) 
RETURNS VARCHAR(50)
DETERMINISTIC       -- 相同输入返回相同结果
READS SQL DATA      -- 读取数据但不修改
BEGIN
    DECLARE v_dept VARCHAR(50);
    
    SELECT department INTO v_dept
    FROM employees
    WHERE id = emp_id;
    
    RETURN IFNULL(v_dept, '未知部门');
END //

DELIMITER ;

-- 其他特性声明：
-- NO SQL：不包含SQL语句
-- CONTAINS SQL：包含SQL语句（默认）
-- MODIFIES SQL DATA：可以修改数据（存储函数不支持）

查看和管理存储过程

查看存储过程

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

-- 查看特定存储过程的定义
SHOW CREATE PROCEDURE GetAllEmployees;

-- 从information_schema查询存储过程信息
SELECT 
    ROUTINE_NAME AS '名称',
    ROUTINE_TYPE AS '类型',
    CREATED AS '创建时间',
    LAST_ALTERED AS '最后修改时间'
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

-- 查看存储函数
SHOW FUNCTION STATUS WHERE Db = 'your_database';

修改存储过程

-- MySQL不支持直接修改存储过程内容
-- 需要先删除再重新创建，或者使用CREATE OR REPLACE

-- 方法一：删除后重建
DROP PROCEDURE IF EXISTS GetAllEmployees;

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT id, name, department, salary FROM employees;
END //
DELIMITER ;

-- 方法二：使用CREATE OR REPLACE（MySQL 8.0+）
DELIMITER //
CREATE OR REPLACE PROCEDURE GetAllEmployees()
BEGIN
    SELECT id, name, department, salary FROM employees;
END //
DELIMITER ;

删除存储过程

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

-- 删除存储函数
DROP FUNCTION IF EXISTS CalculateBonus;

实战示例

分页查询存储过程

-- 通用的分页查询存储过程
DELIMITER //

CREATE PROCEDURE GetEmployeesByPage(
    IN page_num INT,       -- 页码（从1开始）
    IN page_size INT,      -- 每页记录数
    IN dept_name VARCHAR(50),  -- 部门名称（可选筛选）
    OUT total_count INT    -- 总记录数
)
BEGIN
    DECLARE offset_val INT;
    
    -- 计算偏移量
    SET offset_val = (page_num - 1) * page_size;
    
    -- 获取总记录数
    IF dept_name IS NULL OR dept_name = '' THEN
        SELECT COUNT(*) INTO total_count FROM employees;
    ELSE
        SELECT COUNT(*) INTO total_count 
        FROM employees 
        WHERE department = dept_name;
    END IF;
    
    -- 分页查询
    IF dept_name IS NULL OR dept_name = '' THEN
        SELECT * FROM employees
        ORDER BY id
        LIMIT offset_val, page_size;
    ELSE
        SELECT * FROM employees
        WHERE department = dept_name
        ORDER BY id
        LIMIT offset_val, page_size;
    END IF;
END //

DELIMITER ;

-- 测试：获取第2页，每页5条记录
CALL GetEmployeesByPage(2, 5, NULL, @total);
SELECT @total AS '总记录数';

批量处理存储过程

-- 批量更新员工薪资
DELIMITER //

CREATE PROCEDURE BatchUpdateSalary(
    IN dept_name VARCHAR(50),
    IN increase_rate DECIMAL(5,2)  -- 加薪比例，如10表示10%
)
BEGIN
    DECLARE affected_rows INT DEFAULT 0;
    DECLARE exit_status VARCHAR(200);
    
    -- 声明错误处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '操作失败，已回滚' AS '结果';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 更新薪资
    UPDATE employees
    SET salary = salary * (1 + increase_rate / 100)
    WHERE department = dept_name;
    
    -- 获取影响行数
    SET affected_rows = ROW_COUNT();
    
    -- 提交事务
    COMMIT;
    
    -- 返回结果
    SET exit_status = CONCAT(
        '成功更新 ', affected_rows, ' 条记录，',
        '加薪比例: ', increase_rate, '%'
    );
    
    SELECT exit_status AS '执行结果';
END //

DELIMITER ;

-- 测试：技术部员工加薪10%
CALL BatchUpdateSalary('技术部', 10);

数据统计存储过程

-- 综合数据统计
DELIMITER //

CREATE PROCEDURE GetDepartmentStats(
    IN dept_name VARCHAR(50)
)
BEGIN
    -- 部门统计信息
    SELECT 
        department AS '部门',
        COUNT(*) AS '员工数量',
        ROUND(AVG(salary), 2) AS '平均薪资',
        ROUND(MIN(salary), 2) AS '最低薪资',
        ROUND(MAX(salary), 2) AS '最高薪资',
        ROUND(SUM(salary), 2) AS '薪资总额'
    FROM employees
    WHERE department = dept_name
    GROUP BY department;
    
    -- 薪资分布
    SELECT 
        CASE 
            WHEN salary < 5000 THEN '0-5000'
            WHEN salary < 10000 THEN '5000-10000'
            WHEN salary < 15000 THEN '10000-15000'
            ELSE '15000以上'
        END AS '薪资区间',
        COUNT(*) AS '人数'
    FROM employees
    WHERE department = dept_name
    GROUP BY 
        CASE 
            WHEN salary < 5000 THEN '0-5000'
            WHEN salary < 10000 THEN '5000-10000'
            WHEN salary < 15000 THEN '10000-15000'
            ELSE '15000以上'
        END
    ORDER BY 
        MIN(salary);
END //

DELIMITER ;

-- 测试
CALL GetDepartmentStats('技术部');

最佳实践

命名规范

-- 存储过程命名建议：
-- 1. 使用动词+名词形式，如：GetEmployee, UpdateSalary
-- 2. 使用统一前缀，如：sp_GetEmployee, proc_UpdateSalary
-- 3. 参数使用p_前缀，如：p_emp_id, p_dept_name
-- 4. 局部变量使用v_前缀，如：v_count, v_salary

DELIMITER //

CREATE PROCEDURE sp_GetEmployeeById(
    IN p_emp_id INT,
    OUT p_name VARCHAR(50),
    OUT p_salary DECIMAL(10,2)
)
BEGIN
    SELECT name, salary INTO p_name, p_salary
    FROM employees
    WHERE id = p_emp_id;
END //

DELIMITER ;

性能优化建议

-- 1. 尽量使用集合操作而非游标
-- 不推荐：使用游标逐行处理
DELIMITER //
CREATE PROCEDURE BadExample()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_salary DECIMAL(10,2);
    
    DECLARE cur CURSOR FOR SELECT id, salary FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_salary;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE employees SET salary = salary * 1.1 WHERE id = v_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 推荐：使用集合操作
DELIMITER //
CREATE PROCEDURE GoodExample()
BEGIN
    UPDATE employees SET salary = salary * 1.1;
END //
DELIMITER ;

-- 2. 避免在循环中执行SQL
-- 3. 合理使用索引
-- 4. 控制存储过程的复杂度

本章小结

本章详细介绍了MySQL存储过程和存储函数的相关知识：

1. 存储过程基础：理解存储过程的概念、优点和基本语法
2. 参数类型：掌握IN、OUT、INOUT三种参数的使用方法
3. 变量：学会使用局部变量、用户变量和系统变量
4. 流程控制：掌握IF、CASE、WHILE、REPEAT、LOOP等控制语句
5. 游标：学会使用游标逐行处理结果集
6. 错误处理：掌握DECLARE HANDLER和SIGNAL的使用
7. 存储函数：理解函数与存储过程的区别和使用场景
8. 实战应用：通过实际案例掌握存储过程的应用技巧

存储过程是数据库编程的重要工具，合理使用可以提高代码的重用性和执行效率。下一章我们将学习触发器的相关知识。