MySQL存储过程怎么写？看完这篇秒懂

今天测试一个数据展示模块，依赖于数据部推送数据，但是他们没有人员配合，为了赶工，于是自己徒手造数据，有些页面，要查看翻页和权限等相关的功能，手动造是不可能的，因为我懒....哈哈哈哈，于是写了个存储过程，分分钟搞定，又多了些许的摸鱼时间~

一、MySQL存储过程简介

MySQL存储过程是一组预定义的SQL语句，被存储在数据库中，以便可以重复调用和执行。存储过程通常用于将一系列SQL操作封装到单个逻辑单元中，以提高代码的重用性、性能和安全性。

二、存储过程的利弊

一）存储过程的优点

1. 性能优化： 存储过程可以预编译并存储在数据库中，提高了查询的执行效率，减少了重复的解析和编译操作。
2. 代码重用和模块化： 存储过程可以将一系列SQL语句封装成可重用的代码块，提高了开发效率并减少了代码的冗余。
3. 安全性和权限控制： 存储过程可以限制对底层表的直接访问，只允许通过存储过程进行特定操作，从而提高了数据的安全性。
4. 简化复杂业务逻辑： 存储过程可以在数据库层面上实现复杂的业务规则，使应用层面的代码更简洁和可维护。
5. 事务管理： 存储过程可以用于管理事务，确保一系列数据库操作的一致性和完整性。
6. 减少网络通信： 存储过程的执行在数据库服务器上进行，减少了与数据库之间的网络通信次数，提高了性能。
7. 版本控制和管理： 存储过程的更改可以被跟踪和管理，有助于维护和更新数据库逻辑。

二）存储过程的缺点

1. 复杂性： 编写和维护存储过程可能比较复杂，特别是对于复杂的业务逻辑，可能需要更深入的数据库知识。
2. 可移植性： 存储过程的语法和实现在不同的数据库管理系统之间可能有差异，可能会影响到应用程序的可移植性。
3. 难以调试： 存储过程的调试可能比较困难，特别是在一些数据库系统中没有提供强大的调试工具的情况下。
4. 陷阱和性能问题： 不当的使用存储过程可能导致性能问题，如死锁、性能下降等。
5. 可维护性： 随着业务逻辑的变化，存储过程可能变得复杂和难以维护，需要适当的文档和规范。
6. 数据库版本升级： 在数据库版本升级时，存储过程的兼容性可能会成为问题，需要仔细测试和调整。

三、适用场景

存储过程在许多情况下都可以发挥重要作用，特别是在处理复杂业务逻辑和优化数据库性能方面。以下是一些适用场景，可以考虑使用存储过程：

1. 复杂业务逻辑： 当业务逻辑变得复杂时，使用存储过程可以将逻辑集中在数据库中，简化应用程序代码，并提高代码的可维护性。
2. 数据验证和处理： 存储过程可以用于执行数据验证、转换和处理，确保数据的一致性和完整性。
3. 数据转换和清洗： 在数据仓库或ETL（Extract, Transform, Load）过程中，存储过程可以用于进行数据转换、清洗和整理。
4. 权限控制： 存储过程可以用于限制对底层表的直接访问，从而实现更精细的权限控制。
5. 自动化任务： 存储过程可以在特定时间点或特定条件下自动触发，执行一系列操作，如数据备份、报表生成等。

我们在测试过程中，有很多场景用到了存储过程，例如：

1. 订单处理： 在电子商务应用中，存储过程可以用于创建、更新和取消订单，以及执行库存检查和价格计算等操作。
2. 报表生成： 存储过程可以用于生成复杂的报表，汇总数据并按照指定格式呈现

四、MySQL基本语句格式

• DELIMITER $$：这是一个特殊的分隔符设置，用于告诉 MySQL 在定义存储过程时使用不同于默认分隔符（通常为分号）的分隔符。这是因为存储过程体内部可能包含多个分号，使用不同的分隔符可以避免冲突。
• CREATE PROCEDURE 数据库名.存储过程名([in变量名 类型,OUT 参数 2，...])：这是创建存储过程的语句。数据库名.存储过程名是存储过程起的名称，存储过程的参数，可以包含输入参数（IN）、输出参数（OUT）和输入/输出参数（INOUT）。
• 输入参数（IN）： 代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定，值不会改变。
• 输出参数（OUT）：代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。
• 输入/输出参数（INOUT）： 是一种结合了输入和输出功能的参数。它们既可以传递数据给存储过程，也可以从存储过程中传递数据给外部。
• BEGIN：这标志着存储过程体的开始。存储过程体是包含实际的 SQL 语句和控制流程语句的代码块。
• END：这标志着存储过程体的结束。
• DELIMITER ;：这是恢复默认分隔符为分号的语句。

在 BEGIN 和 END 之间，可以编写一系列的 SQL 语句、控制流程语句（如 IF、CASE、LOOP 等），以及声明变量和处理异常等，这些语句构成了存储过程的具体逻辑。

五、项目实战

好了，前面的基础知识科普完了，下面我们进入正题，跟大家分享下我写的存储过程，我用的数据库客户端连接工具是SQLyog~

一）项目需求

简单点，就是造数据，有一个表bar-day，主要用来显示每个代理商下每天每个gid的keep_pc信息，一个代理商下有多个gid，现在要造的数据就是在三种不同代理商下，造多天多个gid的信息，用来验证每个代理商下每天展示的gid数据展示是不是正确的。

数据库表结构：

我要是手动插入的话，造一个月的数据，得造到地老天荒，于是故事就来了~

二）创建存储过程步骤

以客户端连接工具SQLyog为例：

1、创建存储过程

在数据库下找到存储过程，鼠标右键，创建存储过程

2、命名存储过程

3、创建成功

创建成功后，就可以填写对应的SQL语句了，在BEGIN和END之间补充.

4、编写SQL存储过程

现在需要造每天的数据，思路如下：

1）传参

所以定义了两个入参，一个开始日期，一个结束日期，例如：2023-08-01

2）定义并初始化变量

开始时间和keep_pc作为变量，并初始化

3）while循环

每循环一次，开始时间和keep_pc的值+1，直到开始时间=结束时间，循环结束。

4）编写SQL存储过程

编写好的SQL存储过程如下：

语句如下：

DELIMITER $$CREATEPROCEDURE `cbap`.`test-bar-day`(IN start_date DATE,IN end_date DATE)BEGINDECLARE current_date1 DATE; -- 当前日期DECLARE keep_pc INT;SET current_date1 = start_date;-- 初始化变量的值SET keep_pc = 60;WHILE current_date1<= end_date DO-- while循环INSERT INTO `cbap`.`cbap_bar_day` (`dtime`, `gid`, `bar_name`, `agent_id`, `agent_name`, `keep_pc`)VALUES (current_date1, '1010001', '测试吧01', '562501', '小张', keep_pc);SET current_date1 = ADDDATE(current_date1, 1);-- 日期+1天SET keep_pc = keep_pc +1 ; -- 数据+1END WHILE;END$$DELIMITER ;

5）执行存储过程

SQLyog的用法是，在SQL页面，鼠标右键，执所有查询

执行完成后，在左边的存储过程下才会有该文件，否则没有。

5、调用存储过程

SQLyog的用法是，找到存储过程中用到的表cbap_bar_day，右键查询，在查询窗口，调用语法是：call 存储过程名称（传参），直接执行所有查询即可，例如：

CALL `bar-day`('2023-07-01', '2023-07-02');

说明：其他的MySQL客户端连接工具，可能操作步骤不同，思路大同小异。

这样，一个存储过程就完成了，比起手动一条一条的增加，这效率少说增加10倍不过分吧，赶紧安排起来，给自己多一些摸鱼时间~