SQL触发器（2）

1.触发器

# 借车系统触发器

这道题 2018 年的题目，这是 mysql 案例，在处理更新前后的时候，是有不同的方案的。

目前来讲，是用 new.vname / old.vname 来处理 update 前后的情况。

create trigger bad_tag after update on users for each row
begin
  if new.balance < 0 then
    insert into bads
      select 'f0002', br.userid, br.brid, current_timestamp
    from borrows as br
    where br.userid = new.userid and br.etime is null;
  end if;
end;

 

# 结果

参考

https://dev.mysql.com/doc/refman/8.4/en/trigger-syntax.html

 

2.视图检测

代码如下。检测视图的时候，当成 table 来用，基础数据 ready 。

注意，having 子句可以带有计算的，和 where 差异。而且，where 是先切片，可以提升查找效率。

create view bads_detail ( userid, name, brid, cid, stime, etime, total ) as 
select bads.userid, users.name, bads.brid, cars.cid, stime, etime, sum(cprice) as total
from borrows as br, bads, cars, users
where br.brid=bads.brid and br.cid=cars.cid and users.userid=bads.userid
group by bads.userid, users.name, bads.brid, cars.cid, stime, etime 
having sum(cprice) > 200 ;

 

# 结果

 

3.不可重复读、脏读、幻读

我测试了下，在 procedure 里定义的时候，很难以直接写【事务】。但是可以写在 panel 上，对于会话进行定位。

例如

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

4.场景：两个表格同时插入，用触发器

create trigger oos1_trg after insert on sales1 for each row
begin
  insert into oos1 
  select sales1.vemno, 
    sales1.gno, 
    sales1.sdate, 
    sales1.stime
  from sales1
  where sales1.vemno=new.vemno
    and sales1.gno=new.gno
    and sales1.sdate=new.sdate  
  group by sales1.vemno, 
    sales1.gno, 
    sales1.sdate, 
    sales1.stime ;
end; ||

先检查 oos1 表格

再运行以下代码

insert into sales1 (sno, vemno, gno, sdate, stime ) values ( 
's014', 'v002', 'g002',   current_timestamp, current_timestamp  );  ||

搞定！

这里的注意点，如果使用 group by，跟着的字段，一定到“最贴近聚合函数的”。

例如，地区，街道，户数。其中【户数】，是聚合函数，通常 sum(cnt) as cnt。

如果使用 group by，写成

group by 地区， 街道  -- 【户数】前的字段有几个写几个。

 

5.游标练习

参考

https://www.cnblogs.com/xiongzaiqiren/p/sql-cursor.html
https://www.cnblogs.com/thomasbc/p/14911458.html

https://cloud.tencent.com/developer/article/2661053

create table student_course( 
student_id int,
courses varchar(100)
);
insert into student_course values( 1, '数学'), (2,'语文'),(3,'英语' );
insert into student_course values( 1, "物理,化学,生物" );
insert into student_course values( 1, "数理统计, 解析几何, 数论" );

为了进一步考察 游标 。

create table student_course_1 ( 
student_id int,
course_name varchar(20)
);

游标的定义

delimiter ||

create procedure split_course()
begin
declare done int default 0;
declare v_stu_id int;
declare v_courses varchar(100);
declare tmpc varchar(100);
declare comma_pos int;

declare cur cursor for select student_id, courses from student_course;
declare continue handler for not found set done=1;

open cur;
read_loop: LOOP

fetch cur into v_stu_id, v_courses;
if done then leave read_loop;
end if;

while length( v_courses ) > 0 do
  set comma_pos = locate( ',', v_courses  );
  if comma_pos = 0 then 
    set tmpc=v_courses;
    set v_courses='';
  else
    set tmpc=substring( v_courses, 1, comma_pos - 1 );
    set v_courses = substring(v_courses, comma_pos + 1);
  end if;
  insert into student_course_1 values ( v_stu_id, trim(tmpc) );
end while;

end loop;
close cur;
end

注释

locate(',' , string) # 逗号在字符串中首次出现的位置

 

首先查看

两个表格【初始状态】。

 

 

进行调用

call split_course()

 

 

 

 

 

 

商业赋能行业

https://shoelesscai.com/ 

 

游标从入门到实战：MySQL逐行处理的4步法与5大避坑指南！-腾讯云开发者社区-腾讯云