存储过程与触发器的使用

lmio 收录于类别数据库和系列 mysql数据库

2023-05-26 2023-05-26 约 2211 字预计阅读 5 分钟

系列 - mysql数据库

存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集。存储过程是数据库中的一个重要对象。
它存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点

允许模块化程序设计（创建一次多次使用）
允许更快执行
减少网络流量
更好的安全机制

创建存储过程

1
2
3
4
5
6


DELIMITER //
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ] 参数名 数据类型...)
BEGIN
SQL语句
END //
DELIMITER;

In 表示参数从外部传入到里面使用（过程内部使用）； Out 表示参数从过程里边把数据保存到变量中，交给外部使用，所有传入的必须是变量如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空,设为null； Inout 数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返回给外部。

例. 创建存储过程findAll，查找所有学生

1
2
3
4
5


delimiter //
create procedure findAll()
begin
  select * from student;
end //

调用存储过程

call 存储过程名(); # 注意调用的时候不带参数的可以不用加括号

查看所有的存储过程show procedure status; 查看创建的存储过程show create procedure 过程名; 删除过程 drop procedure 过程名;

例. 调用存储过程findAll

1

call findAll();

触发器

触发器是一种特殊类型的存储过程，它又不同于存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。触发器可在写入数据表前，强制检验或转换数据。触发器发生错误时，异动的结果会被撤销。

创建触发器

1
2
3
4
5
6
7


DELIMITER //
Create trigger 触发器名字 触发时机 触发事件 on 表名
for each row
Begin
操作的内容
End //
DELIMITER ;

触发对象：on 表名 for each row 触发器绑定实质是表中的所有行，因此当每一行发生改变的时候，就会触发触发器

触发时机：每张表中对应的行都会有不同的状态，当SQL 指令发生的时候，都会令行中的数据发生改变，每一行总会有两个状态。操作数据之前(before)，操作数据之后(after)。

触发事件:Mysql中触发器针对的目标是数据发生改变，对应的操作只有增（insert），删（delete），改（update）。查询不发生数据的改变，所以触发事件里面没有查询。

注意事项: 一张表中，每一个触发器时机绑定的触发事件对应的触发器类型只能有一个。一张表中只能有一个after insert 触发器因此，一张表中最多的触发器只能有六个。

例. 创建触发器，实现student_count自动修改

1
2
3
4
5
6
7
8


create trigger stu_count
after insert
on student for each row
update student_count set student_count=student_count+1;
create trigger stu_count_del
after delete
on student for each row
update student_count set student_count=student_count-1;

触发器的使用

触发器是特殊的存储过程，触发某种条件，从而执行的一段程序，自动执行，一般不要有返回值。

查看所有的触发器：show triggers \G;

删除触发器：drop trigger 触发器名字;

我们先创建本章的数据库p09。这一章我们还是以学生student表为例，来讲解存储过程及触发器的相关知识。

创建存储过程findAll

现教务处要查询所有学生的信息。由于学生的信息在多处地方都要用到，也就是说查询学生信息的频率是比较大的，所以我们就可以考虑创建一个存储过程findAll，专门用来查询所有学生的信息，后面如果我们还想查询所有学生的信息，只需要简单地调用一下存储过程findAll即可。

其创建语法格式如下：

1
2
3
4
5


delimiter //
create procedure findAll()
begin
  select * from student;
end //

由于我们的student表中还没有数据，所以调用存储过程findAll，是查不到任何数据的。

所以我们来创建一个存储过程addStu，用来插入数据。

4.2 创建存储过程addStu

添加学生也是一个比较常用的语句，每次添加学生都需要写一遍sql语句的话，是很繁琐的，而且效率不高。于是我们就可以创建一个存储过程addStu，专门用来添加学生数据，以后我们想要添加学生，只需要调用一下存储过程addStu，并传入该学生的学号及姓名即可完成添加学生操作，简化了添加学生的sql语句。

其创建语法格式如下：

1
2
3
4


create procedure addStu(in name varchar(10))
begin
  insert into student values(null,name);
end //

现在我们来使用存储过程插入数据。

现在我们再调用一下存储过程findAll，看看是什么效果。可以看到已经可以查到刚刚插入的数据了。

存储过程名不区分大小写

4.3 创建触发器

在创建触发器之前，我们先创建一个student_count表，用于统计学生的数量。

1
2
3
4
5
6


create table student_count(

student_count int default 0

);
// # //表示结束，是通过delimiter //设置的

现在student_count表是用来统计学生的数量的。我们怎么让它实现自动更新呢？难道每次添加学生我们就手动让它加1，每次删除学生我们就手动让它减1吗？当然是不可能的，这样太繁琐了。于是我们就可以创建两个触发器，一个是触发器stu_count，它可以帮我们实现每次添加学生之后，就让student_count表里的student_count加1，另一个是触发器stu_count_del，它可以帮我们实现每次删除学生之后，就让student_count表里的student_count减1。现在我们来创建触发器，让student_count表实现自动更新。

其创建语句格式如下：

1
2
3
4
5
6
7
8
9


create trigger stu_count
after insert
on student for each row
update student_count set student_count=student_count+1//

create trigger stu_count_del
after delete
on student for each row
update student_count set student_count=student_count-1//

因为在刚刚我们已经在student表中插入了数据，所以我们需要设置一下现在学生的数量。

INSERT INTO student_count VALUES(3);

现在我们再次调用存储过程addStu，看看添加学生的同时，学生数量是否会同步更新。

可以看到插入数据的同时，student_count表添加学生数量的字段，也更新了。

删除学生的时候，也是正常的，会实时更新。

目录