存储过程、函数、触发器

触发器原理：
          触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。
触发器的作用：
触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同，触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性；强制数据间的完整性。创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。
触发器与存储过程的区别：
         触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。

 

存储过程 
是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 
可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点： 
可以在单个存储过程中执行一系列 SQL 语句。 
可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。 
存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 

触发器 
是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。例如，可以控制是否允许基于顾客的当前帐户状态插入定单。 
触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。有关详细信息，请参见表关系。 
使用触发器的优点 
触发器的优点如下： 
触发器是自动的：它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 
触发器可以通过数据库中的相关表进行层叠更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。 
触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是，触发器可以引用其它表中的列。例如，触发器可以回滚试图对价格低于 10 美元的书（存储在 titles 表中）应用折扣（存储在 discounts 表中）的更新。

 

以下的文章主要是介绍MySQL 触发器，存储过程以及函数与视图的实例演示过程，以下就是触发器，存储过程以及函数与视图的具体操作方案的描述，希望在你今后的学习中会对你有所帮助。

MySQL 触发器，存储过程以及函数与视图的实例演示：

0.test数据库有userinfo用户信息表 和userinfolog用户信息日志表

 

1.建立一个userinfo表新增记录时的MySQL 触发器 将新增日志加入到userinfolog

 

2.建立一个向userinfo表新增记录的存储过程

 

3.根据userinfo表的出生日期字段 我们将建立一个简单算得年龄的自定义函数

 

4.创建一个userinfo的视图 调用年龄函数

 

 

0.准备相关表

 

1. MySQL> use test;  
2. MySQL> create table userinfo(userid int,username varchar(10),userbirthday date);  
3. MySQL> create table userinfolog(logtime datetime,loginfo varchar(100));  
4. MySQL> describe userinfo; 

 

1.MySQL 触发器

 

1. MySQL> delimiter |  
2. MySQL> create trigger beforeinsertuserinfo  
3. -> before insert on userinfo  
4. -> for each row begin  
5. -> insert into userinfolog values(now(),CONCAT(new.userid,new.username));  
6. -> end;  
7. -> |  
8. MySQL> delimiter ;  
9. MySQL> show triggers; 

 

2.存储过程

 

1. MySQL> delimiter //  
2. MySQL> create procedure spinsertuserinfo(  
3. -> puserid int,pusername varchar(10)  
4. -> ,puserbirthday date  
5. -> )  
6. -> begin  
7. -> insert into userinfo values(puserid,pusername,puserbirthday);  
8. -> end;  
9. -> //  
10. MySQL> show procedure status like 'spinsertuserinfo';  
11. MySQL> call spinsertuserinfo(1,'zhangsan',current_date);  
12. MySQL> select * from userinfo; 

3.自定义函数

1. MySQL> update userinfo  
2. -> set userbirthday='2000.01.01' 
3. -> where userid='1';  
4. MySQL> drop function if exists fngetage;  
5. MySQL> delimiter //  
6. MySQL> create function fngetage(pbirthday date)  
7. -> returns integer  
8. -> begin  
9. -> return year(now()) - year(pbirthday);  
10. -> end  
11. -> // 

 

4.视图

 

1. MySQL> create view viewuserinfo  
2. -> as select * ,fngetage(userbirthday) as userage from userinfo;  
3. MySQL> select * from viewuserinfo; 

 

清除日志记录

 

1. MySQL> truncate table userinfolog;  
2. MySQL> delete from userinfolog; 

以上的相关内容就是对MySQL 触发器 存储过程 函数 视图的介绍，望你能有所收获。

 

比较项目	存储过程	函数
是否有返回值	可以有，也可以没有	必须有且只有一个
是否可以单独执行	可以	必须通过execute执行
SQL语句（DML或SELECT）可否调用	不可以	可以，且可以位于FROM关键字的后面（由于可以返回表对象）
参数类型	可以使用IN、OUT、IN OUT三种模式的参数	只有IN，Oracle可以使用IN、OUT、IN OUT三种参数
返回值类型	可以通过OUT、IN OUT参数返回零个或多个参数值	单一值或一个表对象

1）一般来说，存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强。 
2）存储过程一般是作为一个独立的部分来执行（参照下面“存储过程-调用方法”），而函数可以作为查询语句的一个部分来调用（用在select后面，或者from后面）。由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。