mysql总结(4)-存储过程

mysql总结（数据库版本：8.0.21）

注意：mysql客户端(navicat15)， 新建查询执行以下语句

存储过程
是事先经过编译并存储在数据库中的一段sql语句集合

准备 示例表、示例数据
-- 员工表
CREATE TABLE t_employee(
    empno INT auto_increment PRIMARY KEY COMMENT '员工id',
    empname varchar(40) COMMENT '员工姓名',
    sal DECIMAL(10,2) COMMENT '员工工资'
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '员工表'

INSERT INTO t_employee(empname, sal)VALUES('刘备', 1300);
INSERT INTO t_employee(empname, sal)VALUES('张飞', 980);
INSERT INTO t_employee(empname, sal)VALUES('关羽', 1000);

-- 学生表
CREATE TABLE t_student(
    id INT auto_increment PRIMARY KEY COMMENT '学生id',
    name VARCHAR(40) COMMENT '学生姓名',
    sex VARCHAR(10) COMMENT '学生性别',
    group_id INT COMMENT '分组id'
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '学生表'

示例1 ：创建带输入输出参数的存储过程：往学生表插入一条记录，并返回当前记录的id
新建函数，类型选择procedure,就是存储过程，存储过程sql如下：

DROP PROCEDURE IF EXISTS `create_student`;
CREATE PROCEDURE `create_student`(IN new_name varchar(40), IN new_sex varchar(10), IN new_group_id integer, OUT new_id INT)
BEGIN
INSERT INTO t_student(name, sex, group_id) VALUES(new_name, new_sex, new_group_id );
SELECT MAX(id) INTO new_id FROM t_student;
END;

新建查询，调用存储过程
CALL stuent1('Tom', '男', 1, @new_id);
SELECT @new_id;

示例2 ：统计工资大于999的雇员人数

DROP PROCEDURE IF EXISTS `employee_count`;

CREATE DEFINER = `root`@`%` PROCEDURE `employee_count`(out num integer)
BEGIN

-- 声明变量
DECLARE employee_sal INTEGER;
DECLARE flag  INTEGER;

-- 声明游标
DECLARE cursor_employee CURSOR FOR SELECT sal FROM t_employee;
DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;

-- 设置结束标志
SET flag = 0;
SET num = 0;

-- 打开游标
OPEN cursor_employee;

-- 遍历游标指向的结果集
FETCH cursor_employee INTO employee_sal;
WHILE flag <> 1 DO
IF employee_sal > 999 THEN SET num = num + 1;
END IF;
FETCH cursor_employee INTO employee_sal;
END WHILE;

-- 关闭游标
CLOSE cursor_employee; 

END;

新建查询，调用存储过程
CALL employee_count(@num);
SELECT @num;

示例3 ：使用loop循环重写示例2存储过程

DROP PROCEDURE IF EXISTS `employee_count_loop`;

CREATE DEFINER = `root`@`%` PROCEDURE `employee_count_loop`(out num integer)
    COMMENT '统计工资大于999的雇员人数，使用Loop循环遍历重写employee_count存储过程'
BEGIN
#声明变量
DECLARE employee_sal INTEGER;
DECLARE flag INTEGER; 

#声明游标
DECLARE cursor_employee CURSOR FOR SELECT sal FROM t_employee;
DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;

#设置结束标志
SET flag = 0;
SET num = 0;

#打开游标
open cursor_employee;

#遍历游标
FETCH cursor_employee INTO employee_sal;
loop_abel:LOOP
IF (employee_sal > 999) THEN SET num = num + 1;
END IF;

FETCH cursor_employee INTO employee_sal;
IF (flag = 1) THEN LEAVE loop_abel;
END IF;

END LOOP;

#关闭游标
CLOSE cursor_employee;

END;

示例4 ：使用repeat重写示例2存储过程

DROP PROCEDURE IF EXISTS `employee_count_repeat`;

CREATE DEFINER = `root`@`%` PROCEDURE `employee_count_repeat`(out num integer)
    COMMENT '统计工资大于999的雇员人数，使用repeat循环遍历重写employee_count存储过程'
BEGIN
# 声明变量
DECLARE employee_sal INTEGER;
DECLARE flag INTEGER;

#声明游标
DECLARE cursor_employee CURSOR FOR SELECT sal FROM t_employee;
DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;

#设置结束标志
SET flag = 0;
SET num = 0;

#打开游标
OPEN cursor_employee; 

#遍历游标
FETCH cursor_employee INTO employee_sal;
REPEAT

	IF(employee_sal > 999) THEN SET num = num + 1;
	END IF;
	
	FETCH cursor_employee INTO employee_sal;
	UNTIL flag = 1 # 不能有分号，符合条件就退出

END REPEAT;



#关闭游标
CLOSE cursor_employee;


END;

查看存储过程
SHOW PROCEDURE STATUS LIKE 'employee_count';

查看存储过程定义
SHOW CREATE PROCEDURE employee_count;

删除存储过程
drop procedure procedure_name;
示例： drop procedure employee_count;