存储过程
存储过程是存储在数据库目录中的一段声明性SQL语句。
创建存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name()
BEGIN
...
END //
DELIMITER ;
DELIMITER将标准分隔符;暂时换成//，并在创建存储过程结束后将标准分隔符改回;。
删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;
调用存储过程
CALL procedure_name();
声明变量
DECLARE var_name datatype(size) DEFAULT default_value;
DECLARE 可以声明多个同一类型的变量。
变量设值
使用SET设值
SET var_name = var_value;
使用SELECT INTO设值
SELECT COUNT(0) INTO count FROM tb_name;
变量作用域
声明的普通变量的作用域在BEGIN END块内，声明以@开头的变量表明其为会话变量，直到会话结束前它可用和可访问。
存储过程参数
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name(Mode param_name param_type(param_size))
BEGIN
...
END //
DELIMITER ;
Mode的可选值：IN（传入参数，不可被修改）、OUT（交由存储过程修改）、INOUT（传入且可以被修改）。
示例：
DELIMITER //
DROP PROCEDURE IF EXISTS param_test;
CREATE PROCEDURE param_test(IN step INT, INOUT distance INT)
BEGIN
SET distance = distance + step;
END //
DELIMITER ;
SET @distance = 0;
CALL param_test(5, @distance);
SELECT @distance; -- @distance=5
CALL param_test(7, @distance);
SELECT @distance; -- @distance=12
条件判断
if
IF ... THEN ...
ELSEIF ... THEN ...
ELSE ...
END IF;
简单case（必须包含else）
CASE ...
WHEN ... THEN ...
WHEN ... THEN ...
ELSE ...
END CASE;
可搜索case（必须包含else）
在when语句中做条件判断
CASE ...
WHEN expression... THEN ...
WHEN expression... THEN ...
ELSE ...
END CASE;
循环
while循环
WHILE ... DO
...
END WHILE;
repeat循环
REPEAT
statements;
UNTIL expression
END REPEAT;
loop、leave和iterate
leave相当于break、iterate相当于continue
loop_label: LOOP
IF ... THEN
LEAVE loop_label;
END IF;
IF ... THEN
ITERATE loop_label;
END IF;
END LOOP;
MySQL游标
用来遍历SELECT语句的结果集。
只读、不可滚动、敏感
声明
声明游标必须在声明变量之后，游标必须始终与SELECT语句相关联。
DECLARE cursor_name CURSOR FOR SELECT_statement;
处理空行
DECLARE end INT DEFAULT 0;
/* 当右边移动到空行，将end置为1 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end = 1;
初始化游标
OPEN cursor_name;
检索光标下一行并将光标移动到结果集下一行
将游标检索结果赋值给定义的变量
FETCH cursor_name INTO var [var2, ...];
停用光标、释放内存
CLOSE cursor_name;
示例：
DELIMITER //
DROP PROCEDURE IF EXISTS cursor_test;
CREATE PROCEDURE cursor_test(OUT res VARCHAR(255))
BEGIN
DECLARE end INT DEFAULT 0;
DECLARE id VARCHAR(255);
DECLARE cursor_name CURSOR FOR SELECT id FROM tb_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end = 1;
OPEN cursor_name;
WHILE end != 1 DO
FETCH cursor_name INTO id;
SET res = CONCAT(res, ';', id);
END WHILE;
CLOSE cursor_name;
END //
DELIMITER ;
事务
/* 开始事务 */
START TRANSACTION;
/* 回滚 */
ROLLBACK;
/* 提交 */
COMMIT;
查看存储过程详情
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];
SHOW CREATE PROCEDURE procedure_name;
声明处理程序
DECLARE action_name HANDLER FOR condition_value ...;
action_name可选值：
- CONTINUE（继续执行封闭代码块）
- EXIT（处理程序声明封闭代码块的执行终止）
condition_value可选值：
- 一个MySQL错误代码
- 标准SQLSTATE值（SQLWARNING、SQLEXCEPTION、NOT FOUND）
- 其它
触发器
触发器是一种特殊类型的存储过程，当对相关联的表上的数据进行更改时，会自动调用该语句。触发器可以被定义为在INSERT、UPDATE、DELETE语句更改数据BEFORE或AFTER调用。
创建触发器
DELIMITER //
DROP TRIGGER IF EXISTS trigger_name;
CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tb_name
FOR EACH ROW
BEGIN
...
END //
DELIMITER ;
MySQL5.7.2+支持在同一触发事件和动作时间创建多个触发器，可以通过FOLLOWS或PRECEDES来指定触发顺序。
DELIMITER //
DROP TRIGGER IF EXISTS trigger_name;
CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tb_name
FOR EACH ROW [FOLLOWS|PRECEDES] existing_trigger_name
BEGIN
...
END //
DELIMITER ;
管理触发器
/* 查询所有触发器 */
SELECT * FROM information_schema.triggers;
/* 查询本库触发器*/
SHOW TRIGGERS;
动态sql
通过传递不同参数执行不同sql语句
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name(IN col_name VARCHAR(255))
BEGIN
SET @sql_exe = CONCAT('SELECT ', col_name, ' FROM tb_name)');
PREPARE stmt FROM @sql_exe;
EXECUTE stmt;
/* 释放prepare */
DEALLOCATE PREPARE stmt;
END;
DELIMITER ;
另一种写法，不适用参数为字段名的情况
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name(IN id INT)
BEGIN
SET @id = id;
SET @sql_exe = 'SELECT col_name FROM tb_name WHERE id = ?';
PREPARE stmt FROM @sql_exe;
EXECUTE stmt USING @id;
/* 释放prepare */
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
计划事件
开启MySQL计划事件
/* 查看计划事件状态 */
SHOW VARIABLES LIKE 'event_scheduler'
/* 用脚本启动计划事件 */
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
/* 通过my.ini文件设置MySQL支持计划事件 */
event_scheduler = 1
创建计划事件
DELIMITER //
DROP EVENT IF EXISTS event_name;
CREATE EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT]
DO sql_statement;
DELIMITER ;
shecdule是计划时间设置，使用AT + 时间戳来实现单次计划事件，使用EVERY + 数量 + 时间单位 + [STRATS 时间戳] [ENDS 时间戳] 来实现重复的计划事件。
[ON COMPLETION [NOT] PRESERVE]用来说明计划事件不会执行后该事件是否保留。
[ENABLE | DISABLE]表示是否激活事件，通过ALTER EVENT event_name ENABLE/DISABLE来激活/关闭计划事件。
DO可以执行复合sql语句，用BEGIN ... END包含。
例：
DELIMITER //
DROP EVENT IF EXISTS event_name;
CREATE EVENT event_name
ON SCHEDULE EVERY 20 SECOND STARTS '2049-01-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO CALL procedure_name();
DELIMITER ;
在Mybatis中调用存储过程
存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS mybatis_test;
CREATE PROCEDURE mybatis_test(IN col_name VARCHAR(255), OUT count INT)
BEGIN
SELECT COUNT(0) INTO count FROM tb_name;
SET @sql_exe = CONCAT('SELECT ', col_name, ' FROM tb_name');
PREPARE stmt FROM @sql_exe;
EXECUTE stmt;
/* 释放prepare */
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Mybatis xml文件
parameterMap为传入参数（当参数数量为1个时，可以直接用#{ }传入），存储过程中使用了SELECT语句，同样可以通过resultType返回
<select id="mybatisTest" parameterMap="paramMap" statementType="CALLABLE" resultType="xxx">
CALL mybatis_test(?, ?);
</select>
<parameterMap id="paramMap" type="java.util.Map">
<parameter property="colName" mode="IN" jdbcType="VARCHAR"/>
<parameter property="count" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>
不用parameterMap的方式
CALL mybatis_test(
#{colName, jdbcType = VARCHAR, mode = IN},
#{userPhone, jdbcType = INTEGER, mode = OUT}
);
mapper接口
List<xxx> mybatisTest(Map<String, Object> map);
调用
@Test
public void mybatisTest() throws Exception {
Map<String, Object> map = new HashMap<>();
map.put("colName", "id");
List<xxx> xxxs = xxxMapper.mybatisTest(map);
log.info("xxxs: {}", xxxs);
log.info("count: {}", map.get("count"));
}
