mysql存储过程&触发器

最新推荐文章于 2024-09-09 18:46:12 发布

changfan6000000

最新推荐文章于 2024-09-09 18:46:12 发布

阅读量1.2k

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/changfan6000000/article/details/136440647

版权

存储过程

概念

存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合。

优点

通过把处理封装在容易使用的单元中，简化复杂的操作；

简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码；

MySQL 存储过程按需编译。在编译存储过程之后，MySQL 将其放入缓存中。MySQL 为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询；

缺点

如果您在存储过程中过度使用大量逻辑操作，则 CPU 使用率也会增加，因为 MySQL 数据库最初的设计侧重于高效的查询，不利于逻辑运算

存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难

创建

简单创建

SQL
create procedure my_pro(in a int,b int,out sum int)
begin
set sum = a + b;
end

调用

SQL
call mypro(1,2,@s);-- 调用存储过程
select @s;-- 显示过程输出结果

存储过程语法解读

create procedure 用来创建过程；

mypro 用来定义过程名称；

(in a int,in b int,out sum int)表示过程的参数，其中 in 表示输入参数，out 表示输出参数。类似于 Java 定义方法时的形参和返回值;

begin 与end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；

call用来调用过程，@s 是用来接收过程输出参数的变量

变量

变量定义

SQL
declare variable_name [,variable_name...] datatype [DEFAULT value];

declare用于声明变量；

variable_name表示变量名称；

datatype为 MySQL 的数据类型；

default用于声明默认值;

变量赋值

SQL
set 变量名 = 表达式值 [,variable_name = expression ...]

流程控制

case when

while

loop 循环语句

循环语句，用来重复执行某些语句。

执行过程中可使用 leave语句或 iterate 跳出循环，也可以嵌套 IF等判断语句。

leave语句效果相当于 java 中的 break，用来终止循环；

iterate语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

SQL
-- 创建
create procedure my_proc1(out int sum)
begin
declare num int default 0;
set sum=0;
loop_sum:loop
set num = num + 1;
set sum = sum + num;
if num>=10 then
    leave loop_sum;
else if;
    end loop loop_sum;
end;

-- 调用
call my_proc1(@sum);
select @sum;

存储过程管理

显示存储过程

SQL
show procedure status;

显示特定数据库的存储过程

SQL
show procedure status where db='changfan';

显示特定模式的存储过程，要求显示名称中包含“my”的存储过程

SQL
show procedure status where name like '%my%';

显示存储过程“mypro1”的源码

SQL
show create procedure mypro1;

删除"mypro1"的存储过程

SQL
drop procedure mypro1;

触发器

概念

当我们对一个表数据进行操作时（增加/修改/删除数据），需要同步对其他表执行相应的操作。触发器由事件来触发某个操作，这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。

创建

SQL
CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT
ON TABLE_NAME FOR EACH ROW trigger_STMT

参数解读

SQL
参数trigger_name表示要创建的触发器名；
参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；
参数trigger_EVENT表示触发事件，即触发器执行条件（触发事件），包含DELETE、INSERT和UPDATE语句；
参数TABLE_NAME表示触发事件的操作表名；
参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；
参数trigger_STMT（sql语句）表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句，只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。

注意：
不能创建具有相同名字的触发器。
对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器（比如after insert插入之后有一个触发器那么就不能再有触发器是after的了，但是before insert是可以的）。

例如，对下面的class班级表和student学生表之间创建触发器，要求当student表中插入学生数据时，班级表同步更新班级人数：

insert触发器

对于insert事件，使用new才表示插入的记录，因此这里要使用class.id=new.class_id;

SQL
create trigger my_trigger after insert
on student for each row
update class set count = count+1 where class.id=new.class_id;

插入一些数据

SQL
insert into student
    values(1,'小花',101,'M'),
          (2,'小红',102,'F'),
          (3,'小军',102,'F'),
          (4,'小白',101,'F');

delete触发器

对于delete事件，使用old才表示删除的记录

SQL
create trigger my_tri1 after drop
on student for each row
update class set count = count- 1 where class.id=old.class_id;

触发器包含多个语句

SQL
CREATE   trigger trigger_name BEFORE|AFTER trigger_EVENT
ON TABLE_NAME FOR EACH ROW
    BEGIN
        trigger_STMT
    END;

在MySQL中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELIMITER语句。例如，"DELIMITER $'$"可以将结束符号设置成“$$”。

SQL
DELIMITER $$   #将分号“；”声明为“$$”
create trigger tri_delete_student after delete on student for each row
    BEGIN
        Delete from grade where id = OLD.id; #删除成绩表中的记录
        update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录
    END; #分号要有
$$
DELIMITER ; #再将分号声明回来

触发器管理

查看触发器

SQL
use information_schema;   #选择数据库information_schema
select * from triggers;
select * from triggers where trigger_name='tri_delete_student'; #查询系统表triggers中的触发器

删除触发器

SQL
DROP TRIGGER trigger_name;

参考文档

MySQL---存储过程详解_mysql存储过程-CSDN博客

【MySQL触发器】触发器的使用、创建、修改及删除_创建一个insert触发器-CSDN博客