介绍

有些操作，需要 执行多条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()