函数触发器存储过程及事件

数据库技术与应用袁宝库

存储程序为了便于MySQL代码维护，以及提高MySQL代码的重用性，MySQL开发人员经常将频繁使用的业务逻辑封装成存储程序，MySQL的存储程序分为四类：函数触发器存储过程事件MySQL编程基础知识常量变量运算符与表达式条件控制语句IF、CASE循环语句WHILE、REPEAT、LOOP游标变量系统变量（以@@开头）用户自定义变量用户会话变量（以@开头）局部变量（不以@开头）游标-cursor在数据库中，游标是一个十分重要的概念。它提供了一种对从表中检索出的数据进行操作的灵活手段。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL查询语句相关联，因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。游标-cursor声明游标打开游标从游标中提取数据关闭游标函数函数是由一个或多个SQL语句组成的子程序，它可用于封装代码以提供代码共享的功能。在概念上类似于一般的程序设计语言中定义的函数。函数系统函数数学函数字符串函数数据类型转换函数条件控制函数系统信息函数日期和时间函数其他函数自定义函数自定义函数语法：CREATEFUNCTION函数名（参数1，参数2，…）RETURNS返回值的数据类型[函数选项]BEGIN

函数体; RETURN语句;END;

存储过程存储过程是SQL语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。它使得对数据库的管理和操作更加容易、效率更高。存储过程语法：CREATEPROCEDURE存储过程名（参数1，参数2，…）[存储过程选项]BEGIN存储过程语句块;END;存储过程与函数之间的共同特点应用程序调用存储过程或者函数时，只需要提供存储过程名或者函数名，以及参数信息，无需将若干条MySQL命令或SQL语句发送到服务器，节省了网络开销。存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程序开发人员的工作量。使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。存储过程与函数之间的不同函数必须有且仅有一个返回值，且必须指定返回值数据类型（返回值类型目前仅仅支持字符串、数值类型）。存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用out或者inout参数定义。函数体内可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果（或者结果集）。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。存储过程与函数之间的不同函数可以直接嵌入到SQL语句（例如select语句中）或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用（例如select语句中），调用时需要使用call关键字。函数中的函数体限制比较多，有些语句不能使用。存储过程的限制相对就比较少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。事件基本概念mysql5.1版本开始引进event概念。event既“时间触发器”，与triggers的事件触发不同，event类似与linuxcrontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。适用范围对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。事件开启定时器要使event起作用，MySQL的常量GLOBALevent_scheduler必须为on或者是1。语法：CREATEEVENT

[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE][COMMENT'comment']DOsql_statement实现过程完整性过程完整性是指在服务器端通过编写实现约束的一段代码来实现数据完整性约束，这段代码就称为触发器。触发器是用编程的方法实现复杂的商业规则，它可以实现一般的数据完整性约束实现不了的复杂的完整性约束。事务的基本概念事务事务的特征事务事务是用户定义的数据操作系列，这些操作作为一个完整的工作单元，一个事务内的所有语句被作为一个整体，要么全部执行，要么全部不执行。例如：对于一个转帐活动：A帐户转帐给B帐户n元钱，这个活动包含两个动作：第一个动作：A帐户－n第二个动作：B帐户＋n事务的特征原子性（Atomicity）：指事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。一致性（Consistency）：指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性（Isolation）：指数据库中一个事务的执行不能被其它事务干扰。持久性（Durability）：也称为永久性指事务一旦提交，则其对数据库中数据的改变就是永久的。事务的特征保证事务的ACID特性是事务处理的重要任务。事务的ACID特性可能遭到破坏的因素有：多个事务并行运行时，不同事务的操作有交叉情况；事务在运行过程中被强迫停止。

提交与回滚COMMIT可以显示地提交更新操作。使用下面的MySQL语句，可以隐式地提交更新操作：begin、set

mit=1；start

transaction、rename

table；truncate

table等语句；数据定义（create、alter、drop）语句；ROLLBACK可以回滚（也叫撤销）更新操作。关闭自动提交关闭自动提交的方法有两种：一种是显示地关闭自动提交，一种是隐式地关闭自动提交。方法一：显示地关闭自动提交使用MySQL命令“setmit=0;”方法二：隐式地关闭自动提交使用MySQL命令“start

transaction;”隐式地关闭自动提交，不会修改系统会话变量@@mitte的值。触发器是一种特殊的存储过程，不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。触发器的优点完成比CHECK约束更复杂的数据约束。为保证数据库性能而维护的非规范化数据。可实现复杂的商业规则。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。创建触发器

语法格式：createtrigger触发器名触发时间触发事件on表名foreachrowbegin

触发程序end创建触发器

MySQL的触发事件有三种：insert、update、delete；触发器的触发时间有两种：before与after；因此严格意义上讲一个数据库表最多可以设置六种类型的触发器。foreachrow表示行级触发器。old与new当向表插入新记录时，在触发程序中可以使用new关键字表示新记录；当从表中删除某条旧记录时，在触发程序中可以使用old关键字表示旧记录；当修改表的某条记录时，在触发程序中可以使用old关键字表示修改前的旧记录、使用new关键字表示修改后的新记录；old记录是只读的，可以引用它，但不能更改它；在before触发程序中，可使用“setnew.col_name=value”更改new记录的值。示例用触发器实现check约束DELIMITER

$$CREATETRIGGERupd_checkBEFOREUPDATEONscFOREACHROW示例BEGIN DECLAREmsgvarchar(200); IF(NEW.grade<0ORNEW.grade>100) THEN SETmsg='成绩必须在0到100之间'; SIGNALSQLSTATE'SC000' SET MESSAGE_TEXT=msg; ENDIF;END;$$注意事项触发程序中如果包含select语句，该select语句不能返回结果集。同一个表不能创建两个相同触发时间、触发事件的触发程序。（现在已经有变化）触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句，如starttransaction、commit、rollback或者setmit=0等语句。MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致更新操作性能降低。注意事项在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用触发器可以保证更新操作与触发程序的原子性，此时触发程序和更新操作是在同一个事务中完成。InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系时，但可以使用触发器实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系。注意事项使用触发器维护InnoDB外键约束的级联选项时，数据库开发人员究竟应该选择after触发器还是before触发器？答