MySQL存储过程与触发器实战解析

原创

已于 2024-07-27 09:16:16 修改 · 1.1k 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #oracle #数据库开发 #数据库管理系统 #dba #存储过程与触发器

于 2024-07-26 20:44:51 首次发布

内容导读

MySQL存储过程
MySQL触发器

一、MySQL存储过程

1.1 存储过程介绍

1、存储过程作用

将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

2、存储过程优缺点

（1）优点

SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性
存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升
存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务
存储过程中可以使用事务管理，避免了数据的不一致或错误的问题

（2）缺点

存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程
存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题
在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库进行处理）

1.2 创建存储过程

# 语法:
create procedure 存储过程名称 ([IN/OUT args])
begin
-- SQL
end;
# 创建一个存储过程实现加法运算:Java语法中，方法是有参数和返回值的，存储过程中，是有输入参数和输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
   SET c = a+b;
end;

1.3 调用存储过程

# 调用存储过程
# 定义变量@m
set @m = 0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

1.4 存储过程中变量的使用

存储过程中的变量分为两种：局部变量和用户变量

定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使用

# 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name> <type> [default value];
create procedure proc_test2(IN a int,OUT r int)
begin
 declare x int default 0;  -- 定义x  int类型，默认值为0
 declare y int default 1;  -- 定义y
 set x = a*a;
 set y = a/2; 
 set r = x+y;
end;

定义用户变量

用户变量：相当于全局变量，定义的用户变量可以通过select @attrName from dual进行查询

# 用户变量会存储在mysql数据库的数据字典中（dual）
# 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1;

给变量设置值

无论是局部变量还是用户变量，都是使用set关键字修改值

set @n=1;
call proc_test2(6,@n);
select @n from dual;

将查询结果赋值给变量

在存储过程中使用select..into..给变量赋值

# 查询学生数量
create procedure proc_test3(OUT c int)
begin
   select count(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;

# 调用存储过程
call proc_test3(@n);
select @n from dual;

用户变量使用注意事项

因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

1.5 存储过程的参数

MySQL存储过程的参数一共有三种：IN \ OUT \ INOUT

1、IN 输入参数

输入参数：在调用存储过程中传递数据给存储过程的参数（在调用的过程必须为具有实际值的变量或者字面值）

# 创建存储过程：添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender char(2), IN age int, IN cid int, IN remark varchar(255))
begin
  insert into