MySQL的存储过程

MySQL的存储过程

存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用执行它。
核心优点
1. 减少网络流量：只需传递存储过程名和参数，而非多条 SQL 语句。
2. 提高性能：预编译，执行速度快。首次执行后，执行计划驻留在缓存中，后续调用直接使用。
3. 增强安全性和数据完整性：可以授权用户执行存储过程，而非直接操作底层表，实现更精细的权限控制。
4. 代码复用和模块化设计：将复杂业务逻辑封装，便于维护和重用。
存储过程在实际开发中应用很普遍，尤其是在基于SQL SERVER的项目中，如ERP或一些财务软件等，程序员习惯于把程序的逻辑写在存储过程中。 这样做的好处是简化了程序的代码，却给售后的维护和程序的修改带来无尽的麻烦。同时还会增加数据库服务器的负荷。我个人是不主张这行做的。 而在基于MYSQL的项目中则很少见到这样的情况。
所以我们在写工程时候通常是把一些，复杂的、多重的SQL语句写在一个存储过程中，调用的时候只需要传入参数即可。

基本语法

1. 创建存储过程 一般形式

DELIMITER // -- 临时修改分隔符，避免将过程中的 `;` 误认为结束符
CREATE PROCEDURE procedure_name (
[IN | OUT | INOUT] parameter_name parameter_type[(length)], ...
)
[characteristic ...] -- 特性，如 COMMENT、LANGUAGE SQL 等
BEGIN
-- 存储过程体（SQL 语句集合）
END //
DELIMITER ; -- 将分隔符改回分号

DELIMITER：临时更改语句分隔符，确保存储过程体内的分号不被误解。
参数模式：
    1. IN (默认)：输入参数，调用者传入值给存储过程。
    2. OUT：输出参数，存储过程通过它返回值给调用者。
    3. INOUT：输入输出参数，调用者传入值，存储过程修改后返回。
    4. 过程体：包含有效的 SQL 语句，也可以使用流程控制语句（IF、CASE、LOOP 等）。

2 调用存储过程 CALL语句的使用

CALL procedure_name([parameter, ...]);

3 存储过程的查看

1. 查看定义
SHOW CREATE PROCEDURE procedure_name;
2. 查看数据库中的所有存储过程：
SHOW PROCEDURE STATUS WHERE Db = '你的数据库名';

具体案例

1. 简单的无参存储过程，实现查询所有用户电话

基于表user创建的存储过程

DELIMITER //
CREATE PROCEDURE Get_all_phones()
BEGIN
SELECT phone FROM user;
END //
DELIMITER ;
-- 调用
CALL Get_all_phones();

结果图：

2 带IN参数的存储过程

根据传入的学生ID查找

DELIMITER //
CREATE PROCEDURE Get_user_by_id(IN uid INT)
BEGIN
SELECT * FROM user WHERE id = uid;
END //
DELIMITER ;
-- 调用
CALL Get_user_by_id(2);

结果图：

3 带OUT参数的存储过程

获取电话以138开头的用户的个数

DELIMITER //
CREATE PROCEDURE Get_count_by_phone(IN phone_num_start INT,OUT phone_count INT)
BEGIN
SELECT COUNT(*) INTO phone_count FROM user WHERE phone like concat('',phone_num_start,'%'); -- 使用 INTO 将结果赋值给 OUT 参数
END //
DELIMITER ;
-- 调用
-- 先定义一个用户变量（以@开头）来接收 OUT 参数的值
CALL Get_count_by_phone(138, @count);
-- 查看返回的结果
SELECT @count AS 'Number of phones';

结果图：

4 带INOUT参数的存储过程

把一个数乘2后返回

DELIMITER //
CREATE PROCEDURE Double_number(INOUT num INT)
BEGIN
SET num = num * 2; -- 直接修改 INOUT 参数
END //
DELIMITER ;
-- 调用
SET @my_num = 5; -- 设置一个用户变量并赋初值
CALL Double_number(@my_num);
SELECT @my_num; -- 输出 10

结果图：

优网科技秉承"专业团队、品质服务" 的经营理念，诚信务实的服务了近万家客户，成为众多世界500强、集团和上市公司的长期合作伙伴！

优网科技成立于2001年，擅长网站建设、网站与各类业务系统深度整合，致力于提供完善的企业互联网解决方案。优网科技提供PC端网站建设（品牌展示型、官方门户型、营销商务型、电子商务型、信息门户型、微信小程序定制开发、移动端应用（手机站、APP开发）、微信定制开发（微信官网、微信商城、企业微信）等一系列互联网应用服务。