mysql的触发器与存储过程

一 触发器

能监视: 增,删,改

触发操作: 增,删,改

四要素:

监视地点 监视事件 触发时间 触发事件

创建触发器的语法

Create trigger triggerName
After/before insert/update/delete  on 表名
For each row  #这句话是固定的
Begin
Sql语句;  # 一句或多句,insert/update/delete范围内
End;

删除触发器的语法:

Drop trigger 触发器名

查看触发器

Show triggers

如何在触发器引用行的值

 

对于insert而言, 新增的行 用new 来表示,

行中的每一列的值 ,用new.列名来表示.

 

对于 delete来说, 原本有一行,后来被删除,

想引用被删除的这一行,用old,来表示,  old.列名,就可以引用被删行中的值.

 

对于update来说

被修改的行,

修改前的数据 ,用 old来表示, old.列名引用被修改之前行中的值

修改后的数据,用new 来表示, new.列名引用被修改之后行中的值

 

触发器里after 和before的区别

 

After 是先完成数据的增,删,改再触发,

触发的语句晚于监视的增,删,改,无法影响前面的增删改动作.

 

Before是先完成触发,再增删改,

触发的语句先于监视的增,删,改发生,我们有机会判断,修改即将发生的操作

例子：以下有两张表

goods(商品表)

ord(订单表，省略uid)

 

当一个用户下订单时，在goods表中对应的商品库存量也会相应的减少

写一个触发器：

第一步：

Create trigger tr1 

after insert  on ord

For each row

Begin

update goods set stock = stock-? where id = ? 

End;

那么？里面写成什么呢？

Create trigger tr1 

after insert  on ord

For each row

Begin

update goods set stock = stock-new.num where id = new.gid;

End;

goods表要更改的商品库存减少量等于ord表新增的那条订单商品数量，goods表要更改的商品id等于ord表中新增的那条订单gid

PS ： 要注意的是：如果语句在mysql命令行执行，要先更改mysql结束命令符：

正确的做法：

delimiter $

Create trigger tr1 

after insert  on ord

For each row

Begin

update goods set stock = stock-new.num where id = new.gid;

End$

for each row : 每一行都影响，行级触发器

如果在phpmyadmin里创建触发器：

 

如果订单表的商品数量超过库存表的商品数量，就会出错，修改成：

 

phpmyadmin:

BEGIN

DECLARE

total int;

SELECT stock INTO total FROM goods WHERE gid = new.gid;

IF new.num > total THEN

set new.num = total;

end if;

update goods set stock = stock-new.num where id = new.gid;

END

意思是当超过库存量是订单量改为库存量

二 存储过程

CREATE PROCEDURE procedureName(IN p_in int)

->BEGIN

->#sql语句

->END$

调用存储过程

CALL procedureName()

关于参数类型：

1 2 3 4 5

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）   OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）   INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

例1：(注意修改结束定界符)

delimiter #

create procedure p1(in name int)

begin

select name;

set name=2;

select name;

end#

调用

参数@name的值可以传入

例二：

delimiter #

create procedure p2(out name int)

begin

select name;

set name=2;

select name;

end#

调用：

mysql > set @name = 1#

 

因为out是向调用者输出参数，不接收输入的参数

 

例三：

delimiter #

create procedure p3(inout name int)

begin

select name;

set name=2;

select name;

end#

set @name = 2#

 

调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

三 存储过程涉及的知识

变量：

1  变量声明

局部变量声明一定要放在存储过程体的开始

DECLARE n int default 40;

2 变量赋值

SET n = 1;

3 用户变量

SELECT 'Hello World' into @x;

SELECT @x; --Hello world--

4 注释

两个横杆--：该风格一般用于单行注释。



c 风格： 一般用于多行注释。 //

5 查询存储过程

select 'name' from mysql.proc where db = 'db_name' and `type` = 'PROCEDURE';

select routine_name from information_schema.routines where routine_schema='mall';

 

show procedure status where db='mall';

6 查看某个存储过程详情

show create procedure db_name.proc_name;

7 删除某个存储过程

DROP PROCEDURE proc_name;

8 if语句

 

if contion then 

--..-- 

else 

--..--

end if;

9 case 语句

set var = 2;

case var  

when 0 then   

--..--

when 1 then   

--..-- 

else   

--..--

end case;

10 循环语句

 10.1 while

while 条件 do
--循环体
endwhile

10.2 repeat

repeat  

--循环体until 循环条件  

end repeat;

11 游标

create procedure p5() 

begin

declare declare row_name varchar(20);

declare flag int default 1;

declare getgoods cursor for select goosName from goods;

declare continue handler for NOT FOUND set flag = 0;

open cursor;

fetch getgoods into row_name;

repeat

select row_name;

fetch getgoods into row_name;

until flag=0 end repeat;

end$

上面的意思是：里面声明了一个游标，任务是从goods表中取出goodsName，fetch表示取出值，open表示打开游标，close表示关闭游标，

continue handler for NOT FOUND ... 表示如果游标执行的结果没有是则会执行这一句，但程序继续执行（exit handler：程序会结束）。