mysql之存储过程：如何提高程序的性能和安全性。

1、什么是存储过程

存储过程：一系列sql语句预先存储在mysql服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

存储过程语法结构：

mysql> DELIMITER //
-> CREATE PROCEDURE demo.dailyoperation(IN a INT,OUT b INT)
-> BEGIN -- 开始程序体
-> set b = a + 1  --赋值语句，用于对变量进行赋值
-> SELECT MAX(b) FROM heros where role_main=s INTO b  --SELECT..INTO，把从数据表中查询的结果存放到变量中，即为变量赋值
-> END
-> //
->DELIMITER  ;

创建存储过程：create procedure 存储过程名称 （[ IN | OUT | INOUT] 参数名称 类型）程序体
查看存储过程：show create procedure 存储过程名称
调用存储过程：call demo.dailyoperation('2022-03-19')
删除存储过程：DROP PROCEDURE 存储过程名称;

存储过程中有如下内容：

• 存储过程名
• 程序体
• 参数定义
• 数据类型定义

存储过程的参数定义和类型定义
存储过程可以有参数，也可以没有参数。

IN ：输入的参数，存储过程只是读取这个参数的值。默认就是 IN。
OUT：输出的参数，存储过程在执行的过程中，把某个计算结果值赋给这个参数，执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT ：这个参数既可以作为输入参数，又可以作为输出参数使用。

image.png

DELIMITER 的作用
DELIMITER 临时定义新的结束符。

3、存储过程的步骤

第一步，我们把 SQL 语句的分隔符改为“//”。因为存储过程中包含很多 SQL 语句，如果不修改分隔符的话，MySQL 会在读到第一个 SQL 语句的分隔符“;”的时候，认为语句结束并且执行，这样就会导致错误。
第二步，我们来创建存储过程，把要处理的日期作为一个参数传入（关于参数，下面我会具体讲述）。同时，用 BEGIN 和 END 关键字把存储过程中的 SQL 语句包裹起来，形成存储过程的程序体。
第三步，在程序体中，先定义 2 个数据类型为 DATETIME 的变量，用来记录要计算数据的起始时间和截止时间。
第四步，删除保存结果数据的单品统计表中相同时间段的数据，目的是防止数据重复。
第五步，计算起始时间和截止时间内单品的销售数量合计、销售金额合计、成本合计、毛利和毛利率，并且把结果存储到单品统计表中。

存储过程创建实例：

mysql> DELIMITER // -- 设置分割符为//
-> CREATE PROCEDURE demo.dailyoperation(transdate TEXT)
-> BEGIN -- 开始程序体
-> DECLARE startdate,enddate DATETIME; -- 定义变量
-> SET startdate = date_format(transdate,'%Y-%m-%d'); -- 给起始时间赋值
-> SET enddate = date_add(startdate,INTERVAL 1 DAY); -- 截止时间赋值为1天以后
-> DELETE FROM demo.dailystatistics   -- 删除原有数据
-> WHERE  salesdate = startdate;
-> INSERT into dailystatistics
-> (
-> salesdate,
-> itemnumber,
-> quantity,
-> actualvalue,
-> cost,
-> profit,
-> profitratio
-> )   -- 插入新计算的数据
-> SELECT
-> LEFT(b.transdate,10),
-> a.itemnumber,
-> SUM(a.quantity), -- 数量总计
-> SUM(a.salesvalue), -- 金额总计
-> SUM(a.quantity*c.avgimportprice), -- 计算成本
-> SUM(a.salesvalue-a.quantity*c.avgimportprice), -- 计算毛利
-> CASE sum(a.salesvalue) WHEN 0 THEN 0
-> ELSE round(sum(a.salesvalue-a.quantity*c.avgimportprice)/sum(a.salesvalue),4) END -- 计算毛利率
-> FROM
-> demo.transactiondetails AS a
-> JOIN
-> demo.transactionhead AS b
-> ON (a.transactionid = b.transactionid)
-> JOIN
-> demo.goodsmaster c
-> ON (a.itemnumber=c.itemnumber)
-> WHERE
-> b.transdate>startdate AND b.transdate<enddate
-> GROUP BY
-> LEFT(b.transdate,10),a.itemnumber
-> ORDER BY
-> LEFT(b.transdate,10),a.itemnumber;
-> END
-> // -- 语句结束，执行语句
Query OK, 0 rows affected (0.01 sec)
-> DELIMITER ; -- 恢复分隔符为；

image.png