介绍
有些操作,需要 执行多条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;
执行结果如下:
但统计分析后,没法通过一条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()