mysql触发器和存储过程

什么是触发器

触发器(trigger)是与表事件相关的特殊存储过程，它的执行不由程序调用，也非手工启动，而是由事件触发、激活从而实现执行。

简单来说，触发器就是事先为某张表绑定一段代码，当表中的某些内容发生增、删、改时，系统会自动触发代码并执行。

存储过程: 存储过程则需要主动调用其名字执行。

注意：触发器由事件触发执行，存储过程主动调用。

创建触发器

创建触发器的语法

DROP TRIGGER IF EXISTS databaseName.tri_Name;  
CREATE TRIGGER tri_Name  tirgger_time  trigger_event on tableName  
FOR EACH ROW   // 表示任何一条记录上的操作满足触发事件都会触发该触发器。 
BEGIN  
    sql语句;  
END

trigger_name：触发器名称
tirgger_time：触发执行事件

• BEFORE：事件之前触发
• AFTER：事件之后触发

trigger_event：触发事件

• INSERT：插入某一行时激活触发器，INSERT,LOAD DATA,REPLACE语句可以触发
• UPDATE：更改某一行时激活触发器，UPDATE语句可以触发
• DELETE：删除某一行时激活触发器，DELETE,REPLACE语句可以触发

tb_name：触发器要执行的哪张表
FOR EACH ROW：触发频率为每一行触发一次
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

BEGIN…END

在mysql中的语法是：

BEGIN
[statement_list]
END

statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：

delitmiter [一个新的符号]
例如：
delitmiter $

触发器经典案例

讲解触发器的经典案例，就是下订单，比如存货100件，如果下订单购买10件，该商品的库存量需相应减少，即买几个商品就减少多少个库存量，先看建表语句:

create table goods(
  gid int,
  name varchar(20),
  num smallint
);
create table ord(
  oid int,
  gid int,
  much smallint
);
insert into goods values(1,'cat',100);
insert into goods values(2,'dog',200);
insert into goods values(3,'pig',300);

编写触发器语句

CREATE TRIGGER t1 
AFTER
INSERT
ON ord
FOR EACH ROW
BEGIN
 UPDATE goods SET num=num-2 WHERE gid = 1;
END

我们现在已经建立了一个简单的存储过程，只要订单表ord的gid=1的商品有INSERT(只要买了猫cat，猫的存货就减少2，当然，实际开发过程中减去的具体值，需要是订单购买数量值)，看如下过程:

我们执行一下插入操作：INSERT INTO ord VALUE(1,10,66)

查看触发器

语法：SHOW TRIGGERS FROM [库名]

将查出来整个库下面的所有触发器：

show triggers from databaseLkm；

删除触发器

语法：DROP TRIGGER [库名].[触发器名称]

drop trigger databaseLkm.t1；

存储过程简介

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

mysql存储过程的创建

语法

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

• IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
• OUT:该值可在存储过程内部被改变，并可返回
• INOUT:调用时指定，并且可被改变和返回

过程体

过程体的开始与结束使用BEGIN与END进行标识。下面的测试实例本人都在mysql的5.5版本测试全部通过.

IN参数例子

drop procedure if exists in_param;

delimiter $$
create procedure in_param (in p_in int)
begin
    select p_in ;       # 输出1
    set p_in = 2 ;      # 改变值
    select p_in ;       # 输出2
end $$
delimiter ;

#调用
set @p_in = 1;           # 初始p_in的值
call in_param (@p_in);   # 会输出1,然后输出2
select
    @p_in;               # 输出1

以上可以看出，p_in虽然在存储过程中被修改，但并不影响初始@p_in的值，所以in类型相当于java中的值传递.

OUT参数例子

drop procedure if exists out_param;

#存储过程out参数
delimiter //
  create procedure out_param(out p_out int)
    begin
      select p_out;  # 第一次要输出输出p_out，没有设置值, 所以伪null, 这里千万不要以为要输出的是参数p_out, 因为它的类型是out，所以要改变值才行
      set p_out=2;
      select p_out;  # 第二次输出2, 因为改变了值, 就算这里之前把它设为set p_out=1, 也会输出1,因为只要改变就可以，而且影响全局的
    end;
    //
delimiter ;
#调用
set @p_out=1;
call out_param(@p_out);  # 输出null， 然后2
select @p_out;           # 输出2
以上可以看出，p_out在存储过程中被修改，同时影响了初始@p_out的值，所以out类型相当于java中的引用传递(地址传递).

INOUT参数例子

drop procedure if exists inout_param;

#存储过程inout参数
delimiter //
  create procedure inout_param(inout p_inout int)
  begin
    select p_inout;   #输出1
    set p_inout = 2;  
    select p_inout;   #输出2
    end //
delimiter ;

set @p_inout = 1;
call inout_param(@p_inout);
select @p_inout;     #在存储过程中改变了值,所以这里输出是2

变量

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

• declare用于声明变量；
• variable_name表示变量名称；
• datatype为 MySQL 的数据类型；
• default用于声明默认值;

数值类型

日期和时间类型

字符串类型

变量赋值

语法：SET 变量名 = 变量值 [,变量名= 变量值 …]

用户变量一般以@开头
注意：滥用用户变量会导致程序难以理解及管理

select 'hello world' into @x;
select @x;   # 输出hello world
set @y='goodbye cruel world';
select @y;   # 输出goodbye cruel world
set @z=1+2+3;
select @z;   # 输出6

注释

MySQL存储过程可使用两种风格的注释：

• #号：#，该风格一般用于单行注释
• C风格：/* code */ 一般用于多行注释

存储过程的管理

存储过程的管理包括了：显示过程、显示过程源码、删除过程。
显示存储过程：

show procedoue status;
show procedoue status where db ='shooldb';----显示特定数据库的存储过程

删除存储过程：

drop PROCEDURE 过程名称;