详解MySQL中存储函数创建与触发器设置

目录
  • 1、创建存储函数
  • 2、调用存储函数
  • 3、创建触发器
  • 4、在触发器中调用存储过程
  • 5、删除触发器

存储函数也是过程式对象之一，与存储过程相似。他们都是由SQL和过程式语句组成的代码片段，并且可以从应用程序和SQL中调用。然而，他们也有一些区别：

1、存储函数没有输出参数，因为存储函数本身就是输出参数。

2、不能用CALL语句来调用存储函数。

3、存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中

1、创建存储函数

使用CREATE FUNCTION语句创建存储函数

语法格式：

CREATE FUNCTION 存储函数名 （[参数[,...]]）
RETURNS 类型
函数体

注：存储函数不能拥有与存储过程相同的名字。存储函数体中必须包含一个RETURN值语句，值为存储函数的返回值。

例：创建一个存储函数，其返回Book表中图书数目作为结果

DELIMITER $$
CREATE FUNCTION num_book()
RETURNS INTEGER
BEGIN
RETURN(SELECT COUNT(*)FROM Book);
END$$
DELIMITER ;

RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。虽然该存储函数没有参数，使用时也要用()，如num_book()。

例：创建一个存储函数来删除Sell表中有但Book表中不存在的记录

DELIMITER $$
CREATE FUNCTION del_sell(book_bh CHAR(20))
RETURNS BOOLEAN
BEGIN
DECLARE bh CHAR(20);
SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=book_bh;
IF bh IS NULL THEN
DELETE FROM Sell WHERE 图书编号=book_bh;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;

该存储函数给定图书编号作为输入参数，先按给定的图书编号到Book表查找看有没有该图书编号的书，如果没有，返回false，如果有，返回true。同时还要到Sell表中删除该图书编号的书。要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。

2、调用存储函数

存储函数创建完后，调用存储函数的方法和使用系统提供的内置函数相同，都是使用SELECT关键字。

语法格式：

SELECT 存储函数名（[参数[,...]]）

例：创建一个存储函数publish_book，通过调用存储函数author_book获得图书的作者，并判断该作者是否姓“张”，是则返回出版时间，不是则返回“不合要求”。

DELIMITER $$
CREATE FUNCTION publish_book(b_name CHAR(20))
RETURNS CHAR(20)
BEGIN
DECLARE name CHAR(20);
SELECT author_book(b_name)INTO name;
IF name like'张%' THEN
RETURN(SELECT 出版时间 FROM Book WHERE 书名=b_name);
ELSE
RETURN'不合要求';
END IF;
END$$
DELIMITER ;

调用存储函数publish_book查看结果：

SELECT publish_book('计算机网络技术');

删除存储函数的方法和删除存储过程的方法基本一样，使用DROP FUNCTION语句

语法格式：

DROP FUNCTION [IF EXISTS]存储函数名

注：IF EXISTS子句是MySQL的扩展，如果函数不存在，它防止发生错误

例：删除存储函数a

DROP FUNCTION IF EXISTS a;

3、创建触发器

使用CREATE TRIGGER语句创建触发器

语法格式：

CREATE TRIGGER 触发器名 触发时间 触发事件
ON 表名 FOR EACH ROW 触发器动作

触发时间有两个选项：BEFORE和AFTER，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行通常使用AFTER选项。如果想要验证新数据是否满足使用的限制，则使用BEFORE选项。

触发器不能返回任何结果到客户端，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句。同样，也不能调用将数据返回客户端的存储过程。

例： 创建一个表table1，其中只有一列a，在表上创建一个触发器，每次插入操作时，将用户变量str的值设为TRIGGER IS WORKING。

CREATE TABLE table1(a INTEGER);
CREATE TRIGGER table1_insert AFTER INSERT
ON table1 FOR EACH ROW
SET@str='TRIGGER IS WORKING';

要查看数据库中有哪些触发器可以使用SHOW TRIGGERS命令。

在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：NEW.column_name或者OLD.column_name。NEW.column_name用来引用新行的一列，OLD.column_name用来引用更新或删除它之前的已有行的一列。

对于INSERT语句，只有NEW是合法的，对于DELETE语句，只有OLD才合法。而UPDATE语句可以与NEW和OLD同时使用。

例：创建一个触发器，当删除表Book中某图书的信息时，同时将Sell表中与该图书有关的数据全部删除。

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 图书编号=OLD.图书编号;
END$$
DELIMITER ;

当触发器要触发的是表自身的更新操作时，只能使用BEFORE触发器，而AFTER触发器将不被允许。

4、在触发器中调用存储过程

例：假设Bookstore数据库中有一个与Members表结构完全一样的表member_b，创建一个触发器，在Members表中添加数据的时候，调用存储过程，将member_b表中的数据与Members表同步。

1、定义存储过程：创建一个与Members表结构完全一样的表member_b

DELIMITER $$
CREATE PROCEDURE data_copy()
BEGIN
REPLACE member_b SELECT * FROM Members;
END$$

2、创建触发器：调用存储过程data_copy()

DELIMITER $$
CREATE TRIGGER members_ins AFTER INSERT
ON Members FOR EACH ROW
CALL data_copy();
DELIMITER ;

5、删除触发器

语法格式：

DROP TRIGGER 触发器名

例：删除触发器members_ins

DROP TRIGGER members_ins;

以上就是详解MySQL中存储函数创建与触发器设置的详细内容，更多关于MySQL存储函数 触发器的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL系列之五 视图、存储函数、存储过程、触发器

    目录 一.视图 1.视图的创建 2.查看视图定义 3.删除视图 二.存储函数 1.系统函数 2.自定义函数(user-defined function:UDF) 三.存储过程 四.触发器 总结 一.视图 视图:VIEW,虚表,保存有实表的查询结果,实际数据不保存在磁盘 物化视图:实际数据在磁盘中有保存,加快访问,MySQL不支持物化视图 基表:视图依赖的表 视图中的数据事实上存储于"基表"中,因此,其修改操作也会针对基表实现.其修改操作受基表限制. 注意:修改视图时是修改的原表 1.视

  • 浅谈MySql 视图、触发器以及存储过程

    视图 什么是视图?视图的作用是什么? 视图(view)是一种虚拟存在的表,是一个逻辑表,它本身是不包含数据的.作为一个select语句保存在数据字典中的. 通过视图,可以展现基表(用来创建视图的表叫做基表base table)的部分数据,说白了视图的数据就是来自于基表. 视图的优点: 简单:使用视图的用户完全不需要关心后面对应的表的结构.关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集. 安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是

  • MySQL数据库实验之 触发器和存储过程

    目录 一.实验目的 二.实验要求 三.实现内容及步骤 1.创建一个不带参数的简单存储过程 2.创建一个带输入参数的存储过程 3.创建一个带输入输出参数的存储过程 4.触发器的创建与使用 四.实验总结 观前提示:本篇内容为mysql数据库实验,代码内容经测试过,可能一小部分有所疏漏,也有会不符合每个人实验的要求的地方,因此以下内容建议仅做思路参考. 一.实验目的 1.掌握某主流DBMS支持的SQL编程语言和编程规范,规范设计存储过程: 2.能够理解不同类型触发器的作用和执行原理,验证触发器的有效性

  • 如何测试mysql触发器和存储过程

    1. 为了测试触发器和存储过程,首先建立一张简单的表: 复制代码 代码如下: CREATE TABLE `airuser` ( `userId` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(128) NOT NULL, PRIMARY KEY (`userId`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 2. 为该表的插入操作,创建一张记录表: 复制代码 代码如下: CREATE TABLE `airus

  • MySQL存储过程中的基本函数和触发器的相关学习教程

    MySQL存储过程的常用函数 一.字符串类 CHARSET(str) //返回字串字符集 CONCAT (string2 [,... ]) //连接字串 INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 LCASE (string2 ) //转换成小写 LEFT (string2 ,length ) //从string2中的左边起取length个字符 LENGTH (string ) //string长度 LOAD_FI

  • Mysql存储过程、触发器、事件调度器使用入门指南

    目录 一.存储过程的简单使用 二.存储过程中的变量 三.变量的作用域 四.存储过程参数 五.逻辑控制语句 1.条件语句 2.循环语句 3.case分支 六.游标 七.自定义函数 八.触发器 九.事件 存储过程(Stored Procedure)是一种在数据库中存储复杂程序的数据库对象.为了完成特定功能的SQL语句集,经过编译创建并保存在数据库中. 一.存储过程的简单使用 创建存储过程 create procedure test() begin select * from users; end;

  • 详解MySQL中存储函数创建与触发器设置

    目录 1.创建存储函数 2.调用存储函数 3.创建触发器 4.在触发器中调用存储过程 5.删除触发器 存储函数也是过程式对象之一,与存储过程相似.他们都是由SQL和过程式语句组成的代码片段,并且可以从应用程序和SQL中调用.然而,他们也有一些区别: 1.存储函数没有输出参数,因为存储函数本身就是输出参数. 2.不能用CALL语句来调用存储函数. 3.存储函数必须包含一条RETURN语句,而这条特殊的SQL语句不允许包含于存储过程中 1.创建存储函数 使用CREATE FUNCTION语句创建存储

  • 详解 MySQL中count函数的正确使用方法

    1. 描述 在MySQL中,当我们需要获取某张表中的总行数时,一般会选择使用下面的语句 select count(*) from table; 其实count函数中除了*还可以放其他参数,比如常数.主键id.字段,那么它们有什么区别?各自效率如何?我们应该使用哪种方式来获取表的行数呢? 当搞清楚count函数的运行原理后,相信上面几个问题的答案就会了然于胸. 2. 表结构 为了解决上述的问题,我创建了一张 user 表,它有两个字段:主键id和name,后者可以为null,建表语句如下. CRE

  • 详解MySQL中concat函数的用法（连接字符串）

    MySQL中concat函数 使用方法: CONCAT(str1,str2,-) 返回结果为连接参数产生的字符串.如有任何一个参数为NULL ,则返回值为 NULL. 注意: 如果所有参数均为非二进制字符串,则结果为非二进制字符串. 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串. 一个数字参数被转化为与之相等的二进制字符串格式:若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQ

  • 详解mysql中if函数的正确使用姿势

    为了今天要写的内容,运行了将近7个小时的程序,在数据库中存储了1千万条数据.-- 今天要说的是mysql数据库的IF()函数的一个实例. 具体场景如下, 先看看表结构: CREATE TABLE `message` ( `id` varchar(30) NOT NULL, `title` varchar(30) DEFAULT NULL, `content` text, `send_time` datetime DEFAULT NULL, `type` int(1) DEFAULT NULL C

  • 详解mysql中的存储引擎

    mysql存储引擎概述 什么是存储引擎? MySQL中的数据用各种不同的技术存储在文件(或者内存)中.这些技术中的每一种技术都使用不同的存储机制.索引技巧.锁定水平并且最终提供广泛的不同的功能和能力.通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能. 例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎.内存存储引擎能够在内存中存储所有的表格数据.又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力). 这些不同的技术以及配套的相关

  • 详解MySQL中的存储过程和函数

    详解MySQL中的存储过程和函数

    目录 区别 优点 创建储存函数和过程 储存过程 储存函数 查看储存过程 操作 变量 赋值 变量例子 定义条件和处理过程 条件 处理程序 游标 流程控制语句 储存过程和函数就是数据器将一些处理封装起来,调用 区别 调用储存过程只需要使用CALL,然后指定储存名称和参数,参数可以是IN.OUT和INOUT 调用储存函数只需要使用SELECT,指定名称和参数,储存函数必须有返回值,参数只能是IN 优点 良好的封装性 应用程序和SQL逻辑分离 让SQL也具有处理能力 减少网络交互 能够提高系统性能 降低

  • 详解MySql中InnoDB存储引擎中的各种锁

    详解MySql中InnoDB存储引擎中的各种锁

    目录 什么是锁 InnoDB存储引擎中的锁 锁的算法 行锁的3种算法 幻像问题 锁的问题 脏读 不可重复读 丢失更新 死锁 什么是锁 现实生活中的锁是为了保护你的私有物品,在数据库中锁是为了解决资源争抢的问题,锁是数据库系统区别于文件系统的一个关键特性.锁机制用于管理对共享资源的并发访. 数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性 InnoDB存储引擎区别于MyISAM的两个重要特征就是:InnoDB存储引擎支持事务和行级别的锁,MyISAM只支持表级别的锁 In

  • 详解MySQL中数据类型和字段类型

    目录 1. mysql的数据类型 (1)数值型 (2)字符(串)型 (3)日期和时间型 (4)null值 2. mysql的列(字段)类型 2.1数值列类型 2.2字符串列类型 2.3日期时间列类型 1. mysql的数据类型 在mysql中有如下几种数据类型: (1)数值型 数值是诸如32 或153.4 这样的值.mysql 支持科学表示法,科学表示法由整数或浮点数后跟“e”或“e”.一个符号(“+”或“-”)和一个整数指数来表示.1.24e+12 和23.47e-1 都是合法的科学表示法表示

  • 详解mysql中的冗余和重复索引

    mysql允许在相同列上创建多个索引,无论是有意还是无意,mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能. 重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复索引,发现以后也应该立即删除.但,在相同的列上创建不同类型的索引来满足不同的查询需求是可以的. CREATE TABLE test( ID INT NOT NULL PRIMARY KEY, A INT NOT NULL, B INT NOT NULL, UNI

  • 详解MySQL中EXPLAIN解释命令及用法讲解

    1,情景描述:同事教我在mysql中用explain,于是查看了一番返回内容的含义 2,现就有用处的内容做如下记录: 1,explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了: explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_d

随机推荐