存储过程与函数进阶

一、存储过程基础

1.1 存储过程概念

1.1.1 什么是存储过程

存储过程：预编译的SQL语句集合，存储在数据库中，可重复调用。

东巴文理解：

存储过程 = 预制菜
提前准备好的菜品
需要时直接加热食用
存储过程提前编译，调用时直接执行

1.1.2 存储过程的优缺点

优点：

• 预编译执行，性能高
• 减少网络传输
• 代码复用
• 安全性高

缺点：

• 难以调试
• 可移植性差
• 业务逻辑分散

1.2 创建存储过程

1.2.1 基本语法

语法格式：

CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    SQL语句;
END;

1.2.2 第一个存储过程

示例：

-- 修改分隔符
DELIMITER //

-- 创建存储过程
CREATE PROCEDURE get_all_users()
BEGIN
    SELECT * FROM users;
END //

-- 恢复分隔符
DELIMITER ;

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

1.3 参数类型

1.3.1 IN参数

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

1.3.2 OUT参数

OUT参数：输出参数，返回值给调用者。

示例：

DELIMITER //

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

DELIMITER ;

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

1.3.3 INOUT参数

INOUT参数：既可输入也可输出。

示例：

DELIMITER //

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

DELIMITER ;

-- 调用
SET @number = 10;
CALL double_number(@number);
SELECT @number;  -- 输出20

1.4 调用存储过程

1.4.1 CALL语句

语法格式：

CALL 存储过程名([参数列表]);

示例：

-- 无参数
CALL get_all_users();

-- IN参数
CALL get_user_by_id(1);

-- OUT参数
CALL get_user_count(@count);
SELECT @count;

-- INOUT参数
SET @number = 10;
CALL double_number(@number);
SELECT @number;

二、变量与流程控制

2.1 变量

2.1.1 局部变量

声明变量：

DECLARE 变量名 数据类型 [DEFAULT 默认值];

示例：

DELIMITER //

CREATE PROCEDURE test_variables()
BEGIN
    -- 声明变量
    DECLARE name VARCHAR(50) DEFAULT '张三';
    DECLARE age INT DEFAULT 18;
    
    -- 赋值
    SET name = '李四';
    SET age = 20;
    
    -- 使用变量
    SELECT name, age;
END //

DELIMITER ;

CALL test_variables();

2.1.2 用户变量

用户变量：以@开头，连接期间有效。

示例：

-- 设置用户变量
SET @name = '王五';
SET @age = 25;

-- 使用用户变量
SELECT @name, @age;

-- 在存储过程中使用
DELIMITER //

CREATE PROCEDURE use_user_variable()
BEGIN
    SELECT @name, @age;
END //

DELIMITER ;

CALL use_user_variable();

2.1.3 系统变量

系统变量：MySQL服务器变量。

示例：

-- 查看系统变量
SHOW VARIABLES LIKE 'max_connections';

-- 修改系统变量（会话级别）
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- 修改系统变量（全局级别）
SET GLOBAL max_connections = 1000;

2.2 条件语句

2.2.1 IF语句

语法格式：

IF 条件 THEN
    语句;
ELSEIF 条件 THEN
    语句;
ELSE
    语句;
END IF;

示例：

DELIMITER //

CREATE PROCEDURE get_grade(IN score INT, OUT grade VARCHAR(10))
BEGIN
    IF score >= 90 THEN
        SET grade = '优秀';
    ELSEIF score >= 80 THEN
        SET grade = '良好';
    ELSEIF score >= 70 THEN
        SET grade = '中等';
    ELSEIF score >= 60 THEN
        SET grade = '及格';
    ELSE
        SET grade = '不及格';
    END IF;
END //

DELIMITER ;

-- 调用
CALL get_grade(85, @grade);
SELECT @grade;  -- 输出：良好

2.2.2 CASE语句

语法格式：

CASE 表达式
    WHEN 值1 THEN 语句1
    WHEN 值2 THEN 语句2
    ELSE 语句3
END CASE;

示例：

DELIMITER //

CREATE PROCEDURE get_month_name(IN month_num INT, OUT month_name VARCHAR(20))
BEGIN
    CASE month_num
        WHEN 1 THEN SET month_name = '一月';
        WHEN 2 THEN SET month_name = '二月';
        WHEN 3 THEN SET month_name = '三月';
        WHEN 4 THEN SET month_name = '四月';
        WHEN 5 THEN SET month_name = '五月';
        WHEN 6 THEN SET month_name = '六月';
        WHEN 7 THEN SET month_name = '七月';
        WHEN 8 THEN SET month_name = '八月';
        WHEN 9 THEN SET month_name = '九月';
        WHEN 10 THEN SET month_name = '十月';
        WHEN 11 THEN SET month_name = '十一月';
        WHEN 12 THEN SET month_name = '十二月';
        ELSE SET month_name = '无效月份';
    END CASE;
END //

DELIMITER ;

-- 调用
CALL get_month_name(5, @name);
SELECT @name;  -- 输出：五月

2.3 循环语句

2.3.1 WHILE循环

语法格式：

WHILE 条件 DO
    语句;
END 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 ;

-- 调用
CALL sum_numbers(100, @sum);
SELECT @sum;  -- 输出：5050

2.3.2 REPEAT循环

语法格式：

REPEAT
    语句;
UNTIL 条件 END 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 ;

-- 调用
CALL sum_numbers_repeat(100, @sum);
SELECT @sum;  -- 输出：5050

2.3.3 LOOP循环

语法格式：

循环标签: LOOP
    语句;
    IF 条件 THEN
        LEAVE 循环标签;
    END IF;
END LOOP 循环标签;

示例：

DELIMITER //

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

DELIMITER ;

-- 调用
CALL sum_numbers_loop(100, @sum);
SELECT @sum;  -- 输出：5050

2.3.4 ITERATE语句

ITERATE：跳过当前循环，继续下一次循环。

示例：

DELIMITER //

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

DELIMITER ;

-- 调用
CALL sum_even_numbers(10, @sum);
SELECT @sum;  -- 输出：30 (2+4+6+8+10)

三、游标

3.1 游标概念

3.1.1 什么是游标

游标：用于遍历查询结果集的数据库对象。

东巴文理解：

游标 = 书签
看书时用书签标记位置
游标标记结果集的位置
逐行读取数据

3.2 使用游标

3.2.1 游标操作步骤

1. 声明游标
2. 打开游标
3. 读取数据
4. 关闭游标

3.2.2 声明游标

语法格式：

DECLARE 游标名 CURSOR FOR SELECT语句;

3.2.3 打开游标

语法格式：

OPEN 游标名;

3.2.4 读取数据

语法格式：

FETCH 游标名 INTO 变量列表;

3.2.5 关闭游标

语法格式：

CLOSE 游标名;

3.3 游标示例

3.3.1 基本示例

DELIMITER //

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

DELIMITER ;

CALL process_users();

3.3.2 统计示例

DELIMITER //

CREATE PROCEDURE calculate_statistics()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dept_id INT;
    DECLARE dept_name VARCHAR(50);
    DECLARE emp_count INT;
    DECLARE total_salary DECIMAL(10, 2);
    
    DECLARE dept_cursor CURSOR FOR 
        SELECT id, dept_name FROM departments;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS dept_stats (
        dept_id INT,
        dept_name VARCHAR(50),
        employee_count INT,
        total_salary DECIMAL(10, 2)
    );
    
    TRUNCATE TABLE dept_stats;
    
    OPEN dept_cursor;
    
    stats_loop: LOOP
        FETCH dept_cursor INTO dept_id, dept_name;
        
        IF done THEN
            LEAVE stats_loop;
        END IF;
        
        -- 计算统计信息
        SELECT COUNT(*), IFNULL(SUM(salary), 0)
        INTO emp_count, total_salary
        FROM employees
        WHERE dept_id = dept_id;
        
        -- 插入结果
        INSERT INTO dept_stats VALUES (dept_id, dept_name, emp_count, total_salary);
    END LOOP;
    
    CLOSE dept_cursor;
    
    -- 返回结果
    SELECT * FROM dept_stats;
    
    DROP TEMPORARY TABLE IF EXISTS dept_stats;
END //

DELIMITER ;

CALL calculate_statistics();

四、错误处理

4.1 错误处理程序

4.1.1 DECLARE HANDLER

语法格式：

DECLARE {CONTINUE|EXIT} HANDLER FOR 
    {错误代码|SQLSTATE值|条件名} 
    处理语句;

参数说明：

• CONTINUE：继续执行
• EXIT：退出存储过程

4.1.2 错误处理示例

示例：

DELIMITER //

CREATE PROCEDURE safe_insert_user(
    IN user_name VARCHAR(50),
    IN user_email VARCHAR(100)
)
BEGIN
    -- 声明错误处理程序
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 回滚事务
        ROLLBACK;
        -- 返回错误信息
        SELECT '插入失败' AS result;
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 插入数据
    INSERT INTO users (name, email) VALUES (user_name, user_email);
    
    -- 提交事务
    COMMIT;
    
    SELECT '插入成功' AS result;
END //

DELIMITER ;

-- 调用
CALL safe_insert_user('张三', 'zhangsan@example.com');

4.2 自定义错误

4.2.1 SIGNAL语句

语法格式：

SIGNAL SQLSTATE '状态码'
SET MESSAGE_TEXT = '错误信息';

示例：

DELIMITER //

CREATE PROCEDURE withdraw_money(
    IN user_id INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE current_balance DECIMAL(10, 2);
    
    -- 查询当前余额
    SELECT balance INTO current_balance 
    FROM accounts 
    WHERE id = user_id;
    
    -- 检查余额是否足够
    IF current_balance < amount THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 扣款
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = user_id;
    
    SELECT '扣款成功' AS result;
END //

DELIMITER ;

-- 调用
CALL withdraw_money(1, 1000);

五、存储函数

5.1 存储函数概念

5.1.1 什么是存储函数

存储函数：返回单个值的存储过程。

与存储过程的区别：

5.2 创建存储函数

5.2.1 基本语法

语法格式：

CREATE FUNCTION 函数名([参数列表])
RETURNS 返回类型
[DETERMINISTIC|NOT DETERMINISTIC]
BEGIN
    SQL语句;
    RETURN 返回值;
END;

参数说明：

• DETERMINISTIC：相同输入总是返回相同输出
• NOT DETERMINISTIC：相同输入可能返回不同输出

5.2.2 存储函数示例

示例1：计算年龄：

DELIMITER //

CREATE FUNCTION calculate_age(birth_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE age INT;
    SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
    RETURN age;
END //

DELIMITER ;

-- 调用
SELECT calculate_age('1990-05-15');

示例2：格式化字符串：

DELIMITER //

CREATE FUNCTION format_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    RETURN CONCAT(last_name, ' ', first_name);
END //

DELIMITER ;

-- 调用
SELECT format_name('三', '张');

示例3：计算折扣价：

DELIMITER //

CREATE FUNCTION calculate_discount_price(
    original_price DECIMAL(10, 2),
    discount_rate DECIMAL(5, 2)
)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE discount_price DECIMAL(10, 2);
    SET discount_price = original_price * (1 - discount_rate / 100);
    RETURN discount_price;
END //

DELIMITER ;

-- 调用
SELECT calculate_discount_price(100.00, 20);

5.3 存储函数应用

5.3.1 在查询中使用

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    birth_date DATE,
    salary DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO employees (name, birth_date, salary) VALUES
('张三', '1990-05-15', 10000),
('李四', '1985-08-20', 15000),
('王五', '1995-03-10', 8000);

-- 在查询中使用函数
SELECT 
    name,
    birth_date,
    calculate_age(birth_date) AS age,
    salary,
    calculate_discount_price(salary, 10) AS tax
FROM employees;

六、存储过程与函数管理

6.1 查看存储过程和函数

6.1.1 查看存储过程列表

-- 查看存储过程列表
SHOW PROCEDURE STATUS;

-- 查看指定数据库的存储过程
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

-- 模糊查询
SHOW PROCEDURE STATUS LIKE 'get_%';

6.1.2 查看存储函数列表

-- 查看存储函数列表
SHOW FUNCTION STATUS;

-- 查看指定数据库的存储函数
SHOW FUNCTION STATUS WHERE Db = 'database_name';

6.1.3 查看定义

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

-- 查看存储函数定义
SHOW CREATE FUNCTION calculate_age;

-- 从information_schema查询
SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_NAME = 'get_user_by_id';

6.2 修改存储过程和函数

MySQL不支持直接修改存储过程和函数，需要先删除再创建：

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

-- 重新创建
DELIMITER //

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

DELIMITER ;

6.3 删除存储过程和函数

语法格式：

DROP PROCEDURE [IF EXISTS] 存储过程名;
DROP FUNCTION [IF EXISTS] 函数名;

示例：

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

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

七、实战案例

7.1 分页存储过程

DELIMITER //

CREATE PROCEDURE paginate_users(
    IN page_num INT,
    IN page_size INT,
    OUT total_count INT,
    OUT total_pages INT
)
BEGIN
    DECLARE offset_num INT;
    
    -- 计算偏移量
    SET offset_num = (page_num - 1) * page_size;
    
    -- 查询总数
    SELECT COUNT(*) INTO total_count FROM users;
    
    -- 计算总页数
    SET total_pages = CEIL(total_count / page_size);
    
    -- 分页查询
    SELECT * FROM users
    LIMIT offset_num, page_size;
END //

DELIMITER ;

-- 调用
CALL paginate_users(1, 10, @total, @pages);
SELECT @total, @pages;

7.2 批量插入存储过程

DELIMITER //

CREATE PROCEDURE batch_insert_users(
    IN user_count INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_name VARCHAR(50);
    
    WHILE i <= user_count DO
        SET user_name = CONCAT('用户', i);
        INSERT INTO users (name, email, created_at)
        VALUES (user_name, CONCAT(user_name, '@example.com'), NOW());
        
        SET i = i + 1;
    END WHILE;
    
    SELECT CONCAT('成功插入 ', user_count, ' 条记录') AS result;
END //

DELIMITER ;

-- 调用
CALL batch_insert_users(100);

7.3 数据迁移存储过程

DELIMITER //

CREATE PROCEDURE migrate_data()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(50);
    DECLARE user_email VARCHAR(100);
    
    DECLARE user_cursor CURSOR FOR 
        SELECT id, name, email FROM old_users;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 开始事务
    START TRANSACTION;
    
    OPEN user_cursor;
    
    migrate_loop: LOOP
        FETCH user_cursor INTO user_id, user_name, user_email;
        
        IF done THEN
            LEAVE migrate_loop;
        END IF;
        
        -- 插入新表
        INSERT INTO users (name, email, created_at)
        VALUES (user_name, user_email, NOW())
        ON DUPLICATE KEY UPDATE email = user_email;
    END LOOP;
    
    CLOSE user_cursor;
    
    -- 提交事务
    COMMIT;
    
    SELECT '数据迁移完成' AS result;
END //

DELIMITER ;

-- 调用
CALL migrate_data();

八、本章小结

8.1 核心要点

✅ 存储过程是预编译的SQL语句集合 ✅ 参数类型有IN、OUT、INOUT ✅ 变量有局部变量、用户变量、系统变量 ✅ 流程控制有IF、CASE、WHILE、REPEAT、LOOP ✅ 游标用于遍历结果集 ✅ 存储函数必须返回一个值

8.2 验证清单

完成本章学习后，请确认您能够：

• 创建和调用存储过程
• 使用不同类型的参数
• 使用变量和流程控制
• 使用游标遍历结果集
• 创建和使用存储函数
• 处理存储过程中的错误