MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）https://developer.aliyun.com/article/1534304
3.3.10、循环语句和循环控制
先介绍循环控制：
- leave：类似于 break
- iterate：类似于 continue
1. while 循环
语法：
[标签]: while 循环条件 do 循环体 end while [标签];
测试：
delimiter $$ create procedure func11() begin declare i int default 0; declare num int default (select count(*) from emp); while i<num do select i; set i = i+1; end while; end $$ delimiter ; call func11();
测试结果就是会创建 num 张表，表内就是 i 的值。
2. repeat 循环
语法：
[标签:] repeat 循环体; until 条件表达式 end repeat 标签;
测试：
delimiter $$ create procedure func12() begin declare i int default 0; label: repeat set i = i+1; until i > 10 end repeat label; select '循环结束'; end $$ delimiter ; call func12();
3. loop 循环
语法：
[标签:] loop 循环体 if 条件表达式 then leave 标签; endif; end loop;
测试：
delimiter $$ create procedure func13() begin declare i int default 0; label: loop if i!=5 then set i = i+1; else leave label; end if; end loop; end $$ delimiter ; call func13();
3.3.11、存储过程之游标（Cursor）
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.
语法：
-- 声明语法 declare cursor_name cursor for select_statement -- 打开语法 open cursor_name -- 取值语法 fetch cursor_name into var_name [, var_name] ... -- 关闭语法 close cursor_name
测试：
-- 游标 delimiter $$ create procedure func14(in id int) begin -- 声明局部变量 declare eid int; declare ename char(20); declare esalary decimal(10,2); -- 声明游标 declare my_cursor cursor for select emp_id,emp_name,salary from emp where emp_id=id; -- 打开游标 open my_cursor; -- 通过游标获取每一行 label: loop fetch my_cursor into eid,ename,esalary; select eid,ename,esalary; leave label; end loop; -- 关闭游标 close my_cursor; end $$ delimiter ; drop procedure func14; call func14(1);
注意：循环体中必须有退出的条件，否则就是死循环！
3.3.12、句柄 handler
4、存储函数（自定义函数）
注意：自定义函数之前必须设置全局变量：
-- 信任函数的创建者 set global log_bin_trust_function_creators=TRUE;
-- 信任函数的创建者 set global log_bin_trust_function_creators=TRUE; delimiter $$ create function oneToNum(n int) returns int begin declare sum int default 0; while n!=0 do set sum = sum + n; set n = n-1; end while; return sum; end $$ delimiter ; select oneToNum(3); --6
注意：自定义函数不能包含递归，递归需要使用专门的语法。
5、触发器
5.1、介绍
- 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。
- 在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行
- 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
- 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
5.2、触发器的特性
- 什么条件会触发：I、D、U
- 什么时候触发：在增删改前或者后
- 触发频率：针对每一行执行
- 触发器定义在表上，附着在表上
5.3、触发器语法
5.3.1、创建只有一个执行语句方触发器
注意：这里的触发事件只能是 insert、update、delete。
create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句;
5.3.2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件 on 表名 for each row begin 执行语句列表 end;
5.4、触发器的使用
5.4.1.创建触发器
-- 触发器 drop trigger if exists trigger_emp; -- 创建受触发器影响的表格 create table emp_log( id int primary key auto_increment, time timestamp, log_text varchar(255) ); -- 创建触发器 create trigger trigger_emp after insert on emp for each row insert into emp_log values (NULL,now(),'新的员工注册'); insert into emp values (NULL,'刘海柱',8999,'技术部');
当向 emp 表进行 insert 操作时，就会触发触发器向 emp_log 插入一条日志。
5.4.2、NEW 和 OLD
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：
这让我很自然的联想到了 MySQL 的 binlog 功能，我们数仓中使用 MaxWell 来监听 binlog 实现数据同步，但是 binlog 的底层并不是触发器。
语法：
NEW.列名 OLD.列名
测试：
-- 不要影响到其它触发器对 emp_log 的操作，比如这里增加了两个字段， -- 当对该表进行insert 操作时，因为给 emp_log 增加字段之后没有更新触发器的行为 -- 就会导致给 emp 和 emp_log 插入数据是全部失败 alter table emp_log add old varchar(50); alter table emp_log add new varchar(50); create trigger trigger_test after update on emp for each row insert into emp_log values ( null,now(), concat('更新数据'), concat(OLD.emp_id,OLD.emp_name,OLD.salary,OLD.department), concat(NEW.emp_id,NEW.emp_name,NEW.salary,NEW.department) ); update emp set emp_name = '李元芳' where emp_id = 3; select * from emp_log;
5.5、触发器使用的注意事项
- MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发
- 尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。
- 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（5）
