数据库21/11/7

数据库

存储过程

游标

如果我们要创建存储过程，需要返回查询语句中查询到的多条数据，该怎么实现

游标可以用来一次查询结果集中的每一条数据————逐条读取查询结果集的记录

create procedure pro_text2(out result varchar(200))
BEGIN
  declare bname varchar(50);
  declare bprice decimal(10,2);
  declare num int; -- 表示有几条记录
  declare i int; -- 用于循环
  declare str varchar(50); -- 用于存放多条记录
  declare cur cursor for select book_name,book_price from books;
  select count(1) into num from books; -- 一共有几条记录
  -- 打开游标
  open cur;
  -- 使用游标要结合循环语句
  set i=0;
  while i<num do 
    -- 使用游标：提取游标当前指向的记录（提取之后，游标自顶下移
    fetch cur into bname,bprice;
    set i=i+1;
    set str=concat_ws('~',bname,bprice);
    set result=concat_ws(',',result,str);
  end while;
end;


set @r='';
call pro_text2(@r);
select @r from dual;

触发器

• 触发器就是一种特殊的存储过程，是存储在数据库服务器上的SQL片段，但触发器无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段的执行
• 在MySQL只有执行insert，delete，update操作时，才能触发触发器的执行

案例：创建触发器，实现当向students表中添/修改/删除信息时，同时在stulogs表中添加一条日志信息

1.创建学生表

create table students(
  stu_num char(8) primary key,
  stu_name varchar(20) not null,
  stu_gender char(2) not null,
  stu_age int not null
);

2.创建学生信息日志表

create table stulogs(
id int primary key auto_increment,
time TIMESTAMP,
logtext varchar(200)
);

3.创建触发器

create trigger tri_test
after insert on students -- 在向学生表添加数据后
for each row -- 声明为行级触发器(只要操作一条记录就触发触发器执行一次)
insert into stulogs(time,logtext) values(now(),concat('添加',NEW.stu_num,'学生信息'));

-- 查看触发器
show triggers;

4.测试向学生表添加数据

insert into students(stu_num,stu_name,stu_gender,stu_age) values('2021004','黄明明','男',21);

5.删除触发器

-- 删除触发器
drop trigger tri_test;

NEW 与 OLD

触发器用于监听数据表中数据的insert，delete，update，在触发器中通常处理一些DML的关联操作。可以通过使用new，old关键字获取触发这个触发器DML的数据
new：在触发器中用于获取insert操作的数据，update操作修改后的记录
old：在触发器中用于获取delete删除前的数据，或者update修改前的数据

1.案例：监听学生表，当学生表修改时，在日志中添加相应信息

CREATE TRIGGER tri_test
after update on students
for each ROW 
INSERT into stulogs(time,logtext) values(NOW(),CONCAT("修改学生信息为",new.stu_num));

UPDATE students set stu_name="李华" where stu_num="2021002";

2.使用触发器的建议：
1）在互联网项目中，应尽量避免使用触发器（得不偿失）
2）对于并发量不大的项目可以选择使用存储过程，在互联网项目中不提倡使用存储过程（存储过程是将实现业务的逻辑交给数据库处理，增加了数据库的负载，二则不利于数据库的迁移）