• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

mysql8学习笔记21–游标触发器

开发技术 开发技术 1周前 (05-03) 8次浏览
cursor游标
• Cursor游标用来声明一个数据集
• 游标的声明必须在变量和条件声明之后,在handler声明之前
• Cursor close语句用来关闭之前打开的游标

mysql8学习笔记21--游标触发器

mysql8学习笔记21--游标触发器

• 如果关闭一个未打开的游标,则MySQL会报错
• 如果在存储过程和函数中未使用此语句关闭已经打开的游标,则游标会在声
明的begin…end语句块执行完之后自动关闭
• Cursor declare语句用来声明一个游标和指定游标对应的数据集合,通常数据
集合是一个select语句

mysql8学习笔记21--游标触发器

• Select_statement代表一个select语句
• Cursor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予
后面的变量
• 数据集中的字段需要和INTO语句中定义的变量一一对应
• 数据集中的数据都fetch完之后,则返回NOT FOUND
• Open cursor语句用来打开一个之前已经声明好的游标

mysql8学习笔记21--游标触发器

以下是游标例子:

DROP PROCEDURE
IF EXISTS pro_test2;
delimiter $$


CREATE PROCEDURE pro_test2 ()
BEGIN

DECLARE no_more_record INT DEFAULT 0 ;
DECLARE stubirth_year INT ;
DECLARE stu_id INT ;
DECLARE stu_birth VARCHAR (10) ;
DECLARE cursor_test CURSOR FOR SELECT
    stuid,
    stubirth
FROM
    tb_student3 ; /*首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT found
SET no_more_record = 1 ; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
OPEN cursor_test ; /*接着使用OPEN打开游标*/
-- 开始循环
read_loop :
LOOP
    -- 提取游标里的数据,这里只有一个,多个的话也一样;
    FETCH cursor_test INTO stu_id,
    stu_birth ; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
    -- 声明结束的时候
SET stubirth_year = CONVERT (
    SUBSTRING_INDEX(stu_birth, '-', 1),
    SIGNED
) ; /*截取年份*/
-- 注意:这里的循环体可以根据自己的需要设定(while,while...do,if...then等等)
IF no_more_record = 1 THEN
    LEAVE read_loop ;
END
IF ; -- 这里做你想做的循环的事件
IF stubirth_year > 1990
AND stubirth_year < 2000 THEN
    UPDATE tb_student3
SET comments = '90后'
WHERE
    stuid = stu_id ;
ELSEIF stubirth_year > 1980
AND stubirth_year < 1990 THEN
    UPDATE tb_student3
SET comments = '80后'
WHERE
    stuid = stu_id ;
ELSEIF stubirth_year > 1970
AND stubirth_year < 1980 THEN
    UPDATE tb_student3
SET comments = '70后'
WHERE
    stuid = stu_id ;
ELSE
    UPDATE tb_student3
SET comments = '有问题'
WHERE
    stuid = stu_id ;
END
IF ;
END
LOOP
    read_loop ; CLOSE cursor_test ; /*用完后记得用CLOSE把资源释放掉*/
    END$$
delimiter ;
 
create trigger语句
• create trigger语句用来创建一个触发器,触发器的作用是当表上
有对应SQL语句发生时,则触发执行
• 触发器创建时需要指定对应的表名tbl_name
CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
• Definer关键词用来指定trigger的安全环境
• Trigger_time指定触发器的执行时间,BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行
 
• Trigger_event指定触发该触发器的具体事件
• INSERT当新的一行数据插入表中时触发,比如通过执行insert,loaddata,replace语句插入新数据
• UPDATE当表的一行数据被修改时触发,比如执行update语句时
• DELETE当表的一行数据被删除时触发,比如执行delete,replace语句时
• 当执行insert into … on duplicate key update语句时,当碰到重复行执行update时,则触发update下的触发器
• 从5.7.2版本开始,可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器,默认情况下按照创建的时间依次执行,通过指定FOLLOWS/PRECEDES改变执行顺序,即FOLLOWS时表示新创建的触发器后执行,PRECEDES则表示新触发器先执行
• Trigger_body表示触发器触发之后要执行的一个或多个语句,在内部可以引用涉及表的字段,OLD.col_name表示行数据被修改或删除之前的字段数据,NEW.col_name表示行数据被插入或修改之后的字段数据
 
 

drop TRIGGER if EXISTS simple_trigger;
delimiter//
create TRIGGER simple_trigger
AFTER UPDATE
on teacher for EACH ROW
BEGIN
insert into h_teacher(tno,new_tname,old_tname,sdate) VALUES(new.tno,new.tname,old.tname,now());

END;
//
delimiter ;

 

 

mysql> select * from teacher ;
+------+--------+
| tno  | tname  |
+------+--------+
| t001 | 刘冬   |
| t002 | 刘冬   |
| t003 | 刘冬   |
| t004 | 刘冬   |
| t005 | 刘冬   |
+------+--------+
5 rows in set (0.00 sec)

mysql> select * from h_teacher ;
Empty set (0.01 sec)

mysql> update teacher set tname='刘冬2' where tno='t001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from h_teacher ;
+------+-----------+-----------+---------------------+
| tno  | new_tname | old_tname | sdate               |
+------+-----------+-----------+---------------------+
| t001 | 刘冬2     | 刘冬      | 2021-05-03 10:56:45 |
+------+-----------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> 

 

触发器实现的效果,在程序中也能实现,更多的是放在程序中实现,比如写了触发器后期没用维护给忘了,会出现些难排查的场景。

比如程序员不知道有触发器的话,就不会想到主键冲突的原因了。

mysql> show create table teacher;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `tno` varchar(10) COLLATE utf8_bin NOT NULL,
  `tname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update teacher set tname='刘冬2' where tno='t001';
ERROR 1062 (23000): Duplicate entry 't001' for key 'PRIMARY'
mysql> 

 

 

注意事项:比如上面的执行update操作时,与触发器是在同一个事务内的。

 

 

 

 
 
 
 
 
 
 
 
 
 
 
 

程序员灯塔
转载请注明原文链接:mysql8学习笔记21–游标触发器
喜欢 (0)