Mysql 11： 存储过程全解——从创建到使用

存储过程Stored Procedure是 MySQL 中预编译在数据库服务器上的 SQL 语句集合可以封装复杂业务逻辑、减少网络交互、提升执行效率是数据库开发的核心技能。本文围绕图片中的知识点从创建、使用、删除到拓展带你彻底掌握 MySQL 存储过程。一、核心知识点总览大类细分知识点核心作用1. 创建存储过程1) 变量的使用定义存储过程内的局部变量2) 变量的修改修改变量值实现逻辑计算3) 流程控制IF/WHILE/CASE等控制语句2. 使用存储过程调用存储过程执行预编译逻辑3. 删除存储过程删除不再使用的存储过程4. 拓展一查看存储过程3 种查看存储过程的方法5. 拓展二修改存储过程修改存储过程逻辑6. 拓展三光标的使用遍历查询结果集二、1. 创建存储过程1.1 基础语法DELIMITER // -- 临时修改结束符避免与存储过程内的;冲突 CREATE PROCEDURE 存储过程名(参数列表) BEGIN -- 存储过程体SQL语句、变量、流程控制 END // DELIMITER ; -- 恢复默认结束符关键说明DELIMITER用于临时修改语句结束符因为存储过程体内部需要用;分隔语句避免提前结束创建。1.2 变量的使用与修改1变量定义-- 定义局部变量DECLARE 变量名 数据类型 [DEFAULT 默认值] DECLARE stu_count INT DEFAULT 0; DECLARE avg_score DECIMAL(5,2) DEFAULT 0.00;2变量赋值修改-- 方式1直接赋值 SET stu_count 10; -- 方式2查询结果赋值INTO SELECT COUNT(*) INTO stu_count FROM student; SELECT AVG(score) INTO avg_score FROM score;1.3 流程控制1IF 条件判断IF 条件 THEN -- 执行语句 ELSEIF 条件 THEN -- 执行语句 ELSE -- 执行语句 END IF;2WHILE 循环WHILE 条件 DO -- 循环体 END WHILE;1.4 完整创建示例需求统计学生表人数根据人数输出不同提示-- 1. 准备测试表 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, age INT NOT NULL ); INSERT INTO student (name, age) VALUES (张三,18),(李四,19),(王五,18); -- 2. 创建存储过程 DELIMITER // CREATE PROCEDURE count_student() BEGIN -- 1. 定义变量 DECLARE stu_count INT DEFAULT 0; -- 2. 变量赋值查询学生人数 SELECT COUNT(*) INTO stu_count FROM student; -- 3. 流程控制IF判断 IF stu_count 5 THEN SELECT 学生人数超过5人 AS tip; ELSEIF stu_count 0 THEN SELECT CONCAT(学生人数为, stu_count) AS tip; ELSE SELECT 暂无学生 AS tip; END IF; END // DELIMITER ;三、2. 使用存储过程调用语法CALL 存储过程名(参数);示例-- 调用刚才创建的存储过程 CALL count_student();运行结果tip学生人数为3四、3. 删除存储过程语法DROP PROCEDURE [IF EXISTS] 存储过程名;示例-- 删除存储过程 DROP PROCEDURE IF EXISTS count_student;说明IF EXISTS避免存储过程不存在时报错。五、4. 拓展一查看存储过程3 种方法方法一查看所有存储过程SHOW PROCEDURE STATUS WHERE db DATABASE();方法二查看存储过程的创建语句SHOW CREATE PROCEDURE count_student;方法三从系统表查询详细信息SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME count_student AND ROUTINE_SCHEMA DATABASE();六、5. 拓展二修改存储过程MySQL 没有直接修改存储过程的语法推荐先删除再重建-- 1. 删除旧存储过程 DROP PROCEDURE IF EXISTS count_student; -- 2. 重新创建修改后的存储过程 DELIMITER // CREATE PROCEDURE count_student() BEGIN DECLARE stu_count INT DEFAULT 0; SELECT COUNT(*) INTO stu_count FROM student; -- 修改逻辑新增年龄统计 DECLARE avg_age DECIMAL(5,2) DEFAULT 0.00; SELECT AVG(age) INTO avg_age FROM student; SELECT CONCAT(学生人数, stu_count, 平均年龄, avg_age) AS tip; END // DELIMITER ;七、6. 拓展三光标的使用遍历结果集光标Cursor用于逐行遍历查询结果集适合批量处理数据。语法-- 1. 声明光标 DECLARE 光标名 CURSOR FOR 查询语句; -- 2. 打开光标 OPEN 光标名; -- 3. 读取数据FETCH FETCH 光标名 INTO 变量列表; -- 4. 关闭光标 CLOSE 光标名;完整示例DELIMITER // CREATE PROCEDURE cursor_demo() BEGIN -- 1. 定义变量 DECLARE s_id INT; DECLARE s_name VARCHAR(20); DECLARE done INT DEFAULT FALSE; -- 2. 声明光标 DECLARE cur_student CURSOR FOR SELECT id, name FROM student; -- 3. 声明循环结束条件 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; -- 4. 打开光标 OPEN cur_student; -- 5. 循环遍历 read_loop: WHILE NOT done DO FETCH cur_student INTO s_id, s_name; IF NOT done THEN -- 处理数据打印学生信息 SELECT CONCAT(ID:, s_id, 姓名:, s_name) AS info; END IF; END WHILE read_loop; -- 6. 关闭光标 CLOSE cur_student; END // DELIMITER ; -- 调用 CALL cursor_demo();八、核心总结存储过程本质预编译的 SQL 集合封装业务逻辑减少网络交互核心操作CREATE PROCEDURE创建、CALL调用、DROP PROCEDURE删除关键语法DELIMITER修改结束符、变量定义、流程控制、光标遍历适用场景复杂业务逻辑、批量数据处理、定时任务