MySQL数据库存储过程、触发器、自定义函数

最新推荐文章于 2024-06-22 10:24:03 发布

程健001

最新推荐文章于 2024-06-22 10:24:03 发布

阅读量711

点赞数 2

本文链接：https://blog.csdn.net/qq_41690477/article/details/84303920

版权

触发器

什么是触发器

触发器是与表有关的数据库对象，是一种特殊类型的存储过程，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手动启用，而是由事件来触发。

创建触发器

创建触发器的语法如下：

create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt

trigger_name：触发器的名称
trigger_time：触发时机，为before或者after
trigger_event：触发事件，为insert、delete或者update
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
before insert、before delete、before update
after insert、after delete、after update

其中，触发器名参数指要创建的触发器的名字
before 和 after 参数指定了触发执行的时间，在事件之前或是之后
for each row表示任何一条记录上的操作满足触发事件都会触发该触发

创建有多个执行语句的触发器：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

　　为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

　　当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

案例：

现有两张表，一个学生表（student）和一个班级表（class）

写一个触发器，当学生表添加新的学生时，使得触发器启用让新学生所在的班级学生人数（stuCount）加一

编写过程如下

mysql> DELIMITER ||
mysql> create trigger tri_addStu after insert on student for each row 
    -> BEGIN
    ->  declare sum int; //定义int变量sum
    ->	set sum = (select stuCount from class where classID = new.classID);  //给sum赋值
    ->  update class set stuCount = sum +1 where classID = new.classID;
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

触发器创建好了之后，新增了一个新的学生进去，新学生对应的班级人数直接加一了。

NEW和OLD的使用:

根据以上的表格，可以使用一下格式来使用相应的数据：

NEW.columnname：新增行的某列数据

OLD.columnname：删除行的某列数据

tips：一张表同一事件不能有两个触发器，即不能有两个after insert 。

存储过程

什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

存储过程的特性

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
函数的普遍特性：模块化，封装，代码复用；
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

创建一个简单的存储过程

案例：

有一个银行表（bank），存有三个字段卡号（cardid）、开户人（name）、金钱额度（money）。

写一个存储过程，传入转账人卡号，收账人卡号，转账额度，完成两个字段的金钱数量更新。

创建存储过程

CREATE PROCEDURE bank_zz(in zkh INT, in skh INT , in qian INT)
BEGIN 
	UPDATE bank set money = money - qian WHERE cardid = zkh;
	UPDATE bank set money = money + qian WHERE cardid = skh;
END

在Navicat中运行，输出结果创建成功。