存储过程与函数

第十一章：存储过程与函数

💡 存储过程和函数是预编译的SQL代码块，存储在数据库服务器中，可以重复调用

11.1 存储过程概述

11.1.1 什么是存储过程

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

image-8d96700f

11.1.2 存储过程的优点

优点 说明
减少网络流量 只传输过程名和参数，不传输大量SQL
提高执行效率 预编译，执行计划被缓存
代码复用 一次编写，多次调用
安全性 可以限制用户只能通过存储过程访问数据
便于维护 修改存储过程不影响应用程序

11.1.3 存储过程分类

类型 说明
系统存储过程 MySQL内置的存储过程
用户自定义存储过程 用户创建的存储过程

11.2 创建存储过程

11.2.1 基本语法

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

💡 DELIMITER 说明

  • MySQL默认以;作为语句结束符
  • 存储过程内部包含多条SQL语句，也用;结束
  • 需要临时更改结束符为//，避免冲突
  • 定义完成后再改回;

11.2.2 无参数的存储过程

DELIMITER //
CREATE PROCEDURE 查询所有学生()
BEGIN
    SELECT * FROM 学生;
END //
DELIMITER ;

-- 调用
CALL 查询所有学生();

11.2.3 带输入参数的存储过程

DELIMITER //
CREATE PROCEDURE 按姓名查询学生(IN p_name VARCHAR(20))
BEGIN
    SELECT * FROM 学生 WHERE 姓名 = p_name;
END //
DELIMITER ;

-- 调用
CALL 按姓名查询学生('张三');

-- 使用变量调用
SET @name = '李四';
CALL 按姓名查询学生(@name);

11.2.4 带输出参数的存储过程

DELIMITER //
CREATE PROCEDURE 统计学生人数(OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM 学生;
END //
DELIMITER ;

-- 调用
CALL 统计学生人数(@total);
SELECT @total AS 学生总数;

11.2.5 带输入和输出参数的存储过程

DELIMITER //
CREATE PROCEDURE 统计某班级人数(
    IN p_class VARCHAR(30),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM 学生
    WHERE 班级 = p_class;
END //
DELIMITER ;

-- 调用
CALL 统计某班级人数('22软件1班', @count);
SELECT @count AS 班级人数;

11.2.6 INOUT 参数

DELIMITER //
CREATE PROCEDURE 增加金额(INOUT p_amount DECIMAL(10,2))
BEGIN
    SET p_amount = p_amount * 1.1;  -- 增加10%
END //
DELIMITER ;

-- 调用
SET @money = 1000;
CALL 增加金额(@money);
SELECT @money;  -- 1100

11.2.7 参数类型对比

参数类型 说明 是否需要传入 是否返回值
IN 输入参数 ✓ ✗
OUT 输出参数 ✗（可选） ✓
INOUT 输入输出参数 ✓ ✓

11.3 存储过程实例

11.3.1 查询学生选修的课程

DELIMITER //
CREATE PROCEDURE 查询学生选课(IN p_name VARCHAR(20))
BEGIN
    SELECT 课程.课程名称
    FROM 课程
    INNER JOIN 选课成绩 ON 选课成绩.课程编号 = 课程.课程编号
    INNER JOIN 学生 ON 选课成绩.学号 = 学生.学号
    WHERE 学生.姓名 = p_name;
END //
DELIMITER ;

-- 调用
CALL 查询学生选课('张三');

11.3.2 统计学生选课数量

DELIMITER //
CREATE PROCEDURE 查询选课数(
    IN p_name VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM 选课成绩
    WHERE 学号 = (
        SELECT 学号 FROM 学生 WHERE 姓名 = p_name
    );
END //
DELIMITER ;

-- 调用
CALL 查询选课数('张三', @count);
SELECT @count AS 选课门数;

11.3.3 更新数据的存储过程

DELIMITER //
CREATE PROCEDURE 更新学生性别(
    IN p_name VARCHAR(20),
    IN p_gender CHAR(2)
)
BEGIN
    UPDATE 学生
    SET 性别 = p_gender
    WHERE 姓名 = p_name;

    -- 返回受影响的行数
    SELECT ROW_COUNT() AS 影响行数;
END //
DELIMITER ;

-- 调用
CALL 更新学生性别('张三', '女');

11.3.4 带条件判断的存储过程

DELIMITER //
CREATE PROCEDURE 成绩评级(
    IN p_score INT,
    OUT p_grade VARCHAR(10)
)
BEGIN
    IF p_score >= 90 THEN
        SET p_grade = '优秀';
    ELSEIF p_score >= 80 THEN
        SET p_grade = '良好';
    ELSEIF p_score >= 60 THEN
        SET p_grade = '及格';
    ELSE
        SET p_grade = '不及格';
    END IF;
END //
DELIMITER ;

-- 调用
CALL 成绩评级(85, @grade);
SELECT @grade;  -- '良好'

11.3.5 带循环的存储过程

DELIMITER //
CREATE PROCEDURE 批量插入测试数据(IN p_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 开启事务
    START TRANSACTION;

    WHILE i <= p_count DO
        INSERT INTO 测试表(名称) VALUES (CONCAT('测试', i));
        SET i = i + 1;
    END WHILE;

    -- 提交事务
    COMMIT;

    SELECT CONCAT('成功插入', p_count, '条数据') AS 结果;
END //
DELIMITER ;

-- 调用
CALL 批量插入测试数据(100);

11.4 查看存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看当前数据库的存储过程
SHOW PROCEDURE STATUS WHERE Db = '数据库名';

-- 查看存储过程的创建语句
SHOW CREATE PROCEDURE 存储过程名;

-- 从information_schema查询
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = '数据库名'
  AND ROUTINE_TYPE = 'PROCEDURE';

11.5 修改和删除存储过程

11.5.1 修改存储过程

-- MySQL不支持直接修改存储过程的内容
-- 只能修改存储过程的特性

-- 修改存储过程特性
ALTER PROCEDURE 存储过程名
COMMENT '注释内容';

-- 要修改内容，需要先删除再重新创建
DROP PROCEDURE IF EXISTS 存储过程名;
CREATE PROCEDURE 存储过程名...

11.5.2 删除存储过程

-- 删除存储过程
DROP PROCEDURE 存储过程名;

-- 安全删除
DROP PROCEDURE IF EXISTS 存储过程名;

-- 删除多个存储过程（需要分开写）
DROP PROCEDURE IF EXISTS 存储过程1;
DROP PROCEDURE IF EXISTS 存储过程2;

11.6 存储函数

11.6.1 函数与存储过程的区别

特性 存储过程 存储函数
返回值 可以有多个OUT参数 必须有且只有一个返回值
调用方式 CALL 在SQL语句中像函数一样调用
是否能在SELECT中使用 ✗ ✓
是否能执行DML ✓ 不推荐（应该是只读的）

11.6.2 创建函数

DELIMITER //
CREATE FUNCTION 函数名(参数列表)
RETURNS 返回类型
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- 函数体
    RETURN 返回值;
END //
DELIMITER ;

💡 DETERMINISTIC说明

  • DETERMINISTIC：相同输入总是产生相同输出
  • NOT DETERMINISTIC：相同输入可能产生不同输出（如使用RAND()）
  • MySQL 8.0默认要求指定其中之一

11.6.3 函数示例

-- 计算年龄的函数
DELIMITER //
CREATE FUNCTION get_age(birthday DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birthday, CURDATE());
END //
DELIMITER ;

-- 使用函数
SELECT 姓名, 出生日期, get_age(出生日期) AS 年龄 FROM 学生;
SELECT get_age('2000-06-15');

-- 成绩等级函数
DELIMITER //
CREATE FUNCTION grade_level(score INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(10);

    IF score >= 90 THEN
        SET level = '优秀';
    ELSEIF score >= 80 THEN
        SET level = '良好';
    ELSEIF score >= 60 THEN
        SET level = '及格';
    ELSE
        SET level = '不及格';
    END IF;

    RETURN level;
END //
DELIMITER ;

-- 使用函数
SELECT 学号, 课程编号, 成绩, grade_level(成绩) AS 等级
FROM 选课成绩;

11.6.4 查看和删除函数

-- 查看函数
SHOW FUNCTION STATUS WHERE Db = '数据库名';
SHOW CREATE FUNCTION 函数名;

-- 删除函数
DROP FUNCTION IF EXISTS 函数名;

11.7 快速参考

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE 名称([IN|OUT|INOUT 参数名 类型, ...])
BEGIN
    -- SQL语句
END //
DELIMITER ;

-- 调用存储过程
CALL 存储过程名(参数);

-- 创建函数
DELIMITER //
CREATE FUNCTION 名称(参数列表) RETURNS 类型
DETERMINISTIC
BEGIN
    RETURN 值;
END //
DELIMITER ;

-- 查看
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE 名称;
SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION 名称;

-- 删除
DROP PROCEDURE IF EXISTS 名称;
DROP FUNCTION IF EXISTS 名称;

⬅️ MySQL 编程基础 🏠 00-数据库 ➡️ 用户管理与权限控制