Mysql储存过程，游标，事件，预处理，函数简单记录

写在前面

本文使用的数据库版本为
5.7.36

案例表

create table `student` (
	`studentId` int (11),
	`studentName` varchar (300),
	`studentAge` int (11),
	`studentSex` int (11),
	`studentDetail` varchar (300),
	`sexDouble` double 
); 
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('1','1111','1','1','aaaaa','179.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('2','bbb','123','2','bbbbb','268.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('3','ccc','444','2','ccccc','359.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('9','阿松大','1','1','阿松大','449.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('10','阿松大10','2','1','阿松大10','450.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('11','张三','3','1','描述','101.50');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('20','测试','4','1','测试描述','649.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('28','嘻嘻嘻','1','1','测试描述','589.00');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('29','李四','1','1','123','212.50');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('34','里哈哈','2','3','javaJDBC','1088.90');
insert into `student` (`studentId`, `studentName`, `studentAge`, `studentSex`, `studentDetail`, `sexDouble`) values('37','4','4','4','4','93.00');

一. 储存过程

语法

-- 创建储存过程
DELIMITER $$
CREATE PROCEDURE 储存过程名称(参数（可选）)
BEGIN
	-- 根据需求定义变量
	DECLARE id, age INT;
	DECLARE str VARCHAR(50);
	sql逻辑;
END$$

-- 调用储存过程
CALL 储存过程名称(参数)

注：
DELIMITER 为零时修改mysql默认结束符';' 为$$
BEGIN 为储存过程开始标识
END 为储存过程结束标识

1. 无参数储存过程

DELIMITER $$
CREATE  PROCEDURE `testTwo`()
BEGIN
	SELECT * FROM student ;
END$$

2. 有参储存过程

DELIMITER $$
CREATE PROCEDURE `testTwoT`(IN id INT)
BEGIN
	SELECT * FROM student WHERE studentId = id;
END$$

注:
IN 表示入参
	OUT 表示执行储存过程后要传出去的值
	INOUT 为IN和OUT的结合版
id 参数名称（注意要和储存过程中的变量名称/字段名称区分开）
INT 为参数类型 可以为字符串/时间等根据需求变更，多参数以,隔开
例如：IN id INT, IN str INT

二. 游标

语法

-- 游标的创建和储存过程类似
DELIMITER $$
CREATE PROCEDURE cursor_student()
BEGIN
-- 创建 游标值映射变量（注意中文VARCHAR(50) 要加字符编码CHARACTER SET utf8mb4）
DECLARE id, age INT;
-- 创建 游标中处理逻辑用到的变量
DECLARE total INT DEFAULT 0;
-- 创建 游标结束标识
DECLARE flag INT DEFAULT FALSE;
-- 创建游标
DECLARE 游标名称 CURSOR FOR 逻辑查询;
-- 判定游标结束时 结束标识 的值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
-- 打开游标
OPEN 游标名称 ;
-- 开始循环
循环名称:LOOP
	-- 判断游标是否结束
	IF flag THEN
		-- 跳出循环
		LEAVE load_cursor_loop;
	END IF;
	-- 获取一行数据 id和age为上面订单的“游标值映射变量”
	FETCH 游标名称 INTO id, age;
	-- 一下可做游标数据逻辑处理
	-- 。。。
-- 结束循环游标
END LOOP;
-- 关闭游标
CLOSE 游标名称;
END$$

实战

DELIMITER $$
CREATE PROCEDURE cursor_student()
BEGIN
DECLARE str VARCHAR(50) CHARACTER SET utf8mb4;
DECLARE test_id BIGINT;
DECLARE age INT;
DECLARE total INT DEFAULT 0;
DECLARE flag INT DEFAULT FALSE;
DECLARE test_cursor CURSOR FOR SELECT studentId, studentName, studentAge FROM `student`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
OPEN test_yb ;
load_cursor_loop:LOOP
	FETCH test_cursor INTO test_id, str, age;
	IF flag THEN
		LEAVE load_cursor_loop;
	END IF;
	IF test_id%2=0 THEN
		SET total = total + age;
	END IF;
END LOOP;
CLOSE test_cursor;
SELECT total;
END$$
注：
这个游标的功能是查询student表，将表中studentId为偶数的studentId相加最终输出。
load_cursor_loop 为循环名称可自定义
mysql储存过程中支持3中循环，可自行百度‘mysql循环’

注:

mysql游标就像是java中list集合循环，遍历的时候处理每一条数据

三. 预处理

语法

-- 定义预处理sql
SET 变量值 = sql;
-- 定义预处理变量s（可自定义）从哪个sql中获取的
PREPARE s FROM 变量值;
-- 执行预处理s
EXECUTE s;
-- 释放预处理s（释放数据库资源）
DEALLOCATE PREPARE s;

实战

SELECT COUNT(*) INTO @c FROM student;
SET @sj = FLOOR(@c * RAND());
SET @sql = CONCAT('select * from student limit ', @sj, ',3');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

注：
此预处理语句内容为
随机取出student表中3条数据
可能回导致一条也取不出（例如student表只有10条数据，在变量@sj的值为9的时候则一条也取不出，有待优化）
注：
在mybatis中不能直接执行预处理语句，必须写成储存过程，例如
DELIMITER $$
CREATE PROCEDURE `sj_data`()
BEGIN
SELECT COUNT(*) INTO @c FROM student;
SET @sj = FLOOR(@c * RAND());
SET @sql = CONCAT('select * from student limit ', @sj, ',3');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
END$$

四. 事件

语法

-- 创建事件
CREATE EVENT 事件名称
ON SCHEDULE EVERY 执行间隔
ON COMPLETION 执行策略
COMMENT '事件注释'
DO 事件中执行的sql;

执行间隔
	SECOND 秒
	MINUTE 分
	DAY    天
	MONTH  月
	HOUR   时
执行策略
	PRESERVE 	 循环
	NOT PRESERVE 只执行一次

开启事件
	SET GLOBAL event_scheduler = ON;
关闭事件
	SET GLOBAL event_scheduler = off;
查看事件是否开启
	SHOW VARIABLES LIKE 'event_scheduler';
	
零时关闭事件
	ALTER EVENT 事件名称 DISABLE;
开启零时关闭的事件
	ALTER EVENT 事件名称 ENABLE;
注：零时关闭/开启事件只在'SET GLOBAL event_scheduler = ON'后生效

查看数据库已有事件
SELECT * FROM information_schema.EVENTS

实战

CREATE EVENT event_test_three
ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
COMMENT '测试定时修改student表sexDouble字段2'
DO UPDATE `student` SET sexDouble = sexDouble + 1;

注：
此事件的内容为
每个3秒中将student表中sexDouble字段+1

注:

mysql事件就像是java中的定时任务

五. 函数

语法

DELIMITER $$
CREATE FUNCTION  test_function(参数列表)
RETURNS 返回类型
BEGIN
	sql逻辑
	RETURN 返回值;
END$$

实战

DELIMITER $$
CREATE FUNCTION  test_function(str VARCHAR(100) CHARSET utf8)RETURNS INT
BEGIN
	DECLARE c INT;
	SELECT COUNT(*) INTO c FROM student WHERE POSITION(str IN studentName);
	RETURN c;
END$$

# 此代码逻辑为模糊查询student表中studentName匹配的数据条数
注：
当入参为中文时 要在入参后面指定字符编码