一、 为什么使用存储过程?
简化操作:将多步操作封装成一个命令。
提高效率:一次编译,多次运行,减少网络传输。
减少错误:集中处理业务逻辑,避免在应用程序中重复编写和出错。
二、 创建存储过程的基本语法
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] )
BEGIN
<过程体>
END;
1. 过程名 (`<过程名>`)
命名规则同一般标识符。
建议使用具有描述性的名称(如 `GetUserOrders`, `CalculateMonthlyRevenue`)。
避免使用MySQL内置函数名。
2. 过程参数 (`[IN | OUT | INOUT] <参数名> <类型>`)**
这是存储过程灵活性的关键。
参数类型 | 关键字 | 说明 | 补充说明 |
---|---|---|---|
输入参数 | IN | 调用者向存储过程传入值(默认模式,可省略)。参数在过程体内是只读的。 | 最常用 |
输出参数 | OUT | 存储过程通过该参数向调用者返回值。传入的初始值会被忽略,在过程体内可写。 | 用于返回结果 |
输入/输出参数 | INOUT | 同时具备 IN 和 OUT 的功能。调用者传入值,存储过程修改后再返回给调用者。 | 双向传递 |
注意:参数名不要与数据表的列名相同,以免发生不可预知的错误。
3. 过程体 (`<过程体>`)
包含存储过程要执行的核心SQL语句。
简单的过程体可以只有一条SQL语句。
复杂的过程体可以包含变量声明、流程控制(IF, CASE, LOOP)、错误处理等。
使用 `BEGIN ... END` 来构成一个复合语句块。
三、 至关重要的 `DELIMITER` 命令
问题:MySQL默认遇到分号`;`就执行语句。但存储过程体内部有多条以`;`结束的SQL语句,这会导致MySQL在遇到第一个`;`时就误以为CREATE语句结束了。
解决方案:临时修改命令行客户端的语句结束分隔符。
操作步骤:
1. 声明新的分隔符(如 `//` 或 `$$`)。
DELIMITER //
2. 编写完整的CREATE PROCEDURE语句。此时过程体内的分号`;`不会导致语句执行。
CREATE PROCEDURE MyProcedure()
BEGIN
SELECT * FROM table1;
SELECT * FROM table2; -- 这里的分号是安全的
END //
整个过程定义以新的分隔符`//`结束
3. 将分隔符恢复为分号。
DELIMITER ;
四、 创建存储过程示例
例1:创建无参数的存储过程 `ShowStuScore`
(从成绩表中查询所有学生成绩)
DELIMITER // 步骤1:修改分隔符
CREATE PROCEDURE ShowStuScore()
BEGIN
过程体:可以包含多条SQL语句
SELECT * FROM tb_students_score;
END //
DELIMITER ; 步骤3:恢复分隔符
调用存储过程
CALL ShowStuScore();
例2:创建带有输入(IN)参数的存储过程 `GetScoreByStu`
(根据学生姓名查询成绩)
DELIMITER //
CREATE PROCEDURE GetScoreByStu(IN name VARCHAR(30))
BEGIN
SELECT student_score FROM tb_students_score
WHERE student_name = name; -- 这里的`name`是参数,不是列名
END //
DELIMITER ;
调用存储过程并传入参数
CALL GetScoreByStu('Dany');
例3:创建带有输出(OUT)参数的存储过程 `GetMaxScore`
(查询最高分并将其通过参数返回)
DELIMITER //
CREATE PROCEDURE GetMaxScore(OUT max_score INT)
BEGIN
将查询结果 INTO 赋值给输出参数
SELECT MAX(student_score) INTO max_score FROM tb_students_score;
END //
DELIMITER ;
调用存储过程
首先定义一个用户变量(@ms)来接收OUT参数传出的值
CALL GetMaxScore(@ms);
查看返回的结果
SELECT @ms AS 'Max Score';
五、 权限要求
要创建存储过程,用户必须被授予 `CREATE ROUTINE` 权限。
您的材料已经涵盖了最核心和基础的部分。下一步学习可以深入到:
1. 变量使用:如何在过程体内定义和使用局部变量(`DECLARE`)。
2. 流程控制:使用 `IF...THEN...ELSE`, `CASE`, `WHILE...DO`, `LOOP` 等实现复杂逻辑。
3. 查看和删除:使用 `SHOW PROCEDURE STATUS` 查看所有存储过程,使用 `DROP PROCEDURE <过程名>` 删除存储过程。
C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:
一点编程也不会写的:零基础C语言学练课程
解决困扰你多年的C语言疑难杂症特性的C语言进阶课程
从零到写出一个爬虫的Python编程课程
只会语法写不出代码?手把手带你写100个编程真题的编程百练课程
信息学奥赛或C++选手的 必学C++课程
蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程
手把手讲解近五年真题的蓝桥杯辅导课程