MySQL-存储过程 作者：马育民 • 2023-08-18 19:16 • 阅读：10048 # 介绍 有些操作，需要 **执行多条sql语句**，此时就应该使用存储过程 ### 好处 提高代码重用性、简化操作、减少编译次数，减少和数据库连接次数 # 创建 ``` delimiter $$ # 告诉程序使用 $$ 作为结束分隔符 CREATE PROCEDURE 存储过程名(过程参数列表) BEGIN ...... END $$ # 表示创建存储过程结束 ``` # 调用 ``` call 存储过程名(参数) ``` # 删除 ``` drop procedure 存储过程名 ``` # 案例 每个月分析各个岗位的最高工资、最低工资、平均工资、工资总和、人数，并存储到表中 ### 创建表 保存分析出来的数据 ``` CREATE TABLE `sal_anal` ( `id` INT NOT NULL AUTO_INCREMENT, `job` VARCHAR(50) NOT NULL DEFAULT '0', `max` DECIMAL(7,2) NOT NULL DEFAULT '0', `min` DECIMAL(7,2) NOT NULL DEFAULT '0', `avg` DECIMAL(7,2) NOT NULL DEFAULT '0', `sum` DECIMAL(7,2) NOT NULL DEFAULT '0', `count` INT NOT NULL DEFAULT '0', `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) COLLATE='utf8mb4_unicode_ci' ; ``` ### 传统方式 传统方式，需要先统计分析： ``` SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1) FROM emp GROUP BY job; ``` 执行结果如下： [](/upload/0/0/1IX64YidZ6yR.jpg) 但统计分析后，没法通过一条sql将这些数据保存到表中 ### 创建存储过程 **注意：**变量名不要与列名相同，否则可能报错，也可能不报错，但执行会丢数据 ``` DROP PROCEDURE if exists pro_sal_anal; delimiter $$ # 告诉程序使用 $$ 作为结束分隔符 create procedure pro_sal_anal() begin -- 定义变量 DECLARE s int DEFAULT 0; DECLARE vjob VARCHAR(50); DECLARE vmax DECIMAL(7,2); DECLARE vmin DECIMAL(7,2); DECLARE vavg DECIMAL(7,2); DECLARE vsum DECIMAL(7,2); DECLARE vcount int; DECLARE report CURSOR FOR SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1) FROM emp GROUP BY job; -- 声明当游标遍历完后将标志变量置为某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1; -- 打开游标 OPEN report; -- 将游标中的值赋值给变量，注意：变量名不要与列名相同，变量顺序要和sql结果列的顺序一致 FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vCOUNT ; WHILE s <> 1 DO -- 当s等于1时代表遍历已完成，退出循环 -- 执行业务逻辑 INSERT INTO sal_anal (sjob,MAX_sal,MIN_sal,AVG_sal,SUM_sal,COUNT_emp) VALUES (vjob,vMAX,vMIN,vAVG,vSUM,vCOUNT) ; -- 将游标中的值再赋值给变量，供下次循环使用 FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vcount; END WHILE; -- 关闭游标 CLOSE report; END $$ # 表示创建存储过程结束 ``` ### 调用 ``` CALL pro_sal_anal() ``` 原文出处：http://www.malaoshi.top/show_1IX64Yj2YCAS.html