MySQL数据库：存储过程和触发器

一、变量

（一）局部变量

DECLARE variable_name datatype(size) DEFAULT default_value;

此处声明的相当于一个局部变量，在end之后便失效。
声明多个：

DECLARE x, y INT DEFAULT 0

（二）会话变量

set @p=1;

此处的session变量不需要声明，mysql会自动根据值类型来确定类型，这种变量要在变量名称前面加上“@”符号，叫做会话变量，代表整个会话过程他都是有作用的，这个有点类似于全局变量一样。
这种变量用途比较广，因为只要在一个会话内（就是某个应用的一个连接过程中），这个变量可以在被调用的存储过程或者代码之间共享数据。

（三）变量赋值的方式

除了set，还可以使用select .....into.....的方式。

（四）两者的区别

在调用存储过程时，以DECLARE声明的变量都会被初始化为NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。

二、存储过程

为了方便现在和以后使用而保存起来的一条或者多条MySQL语句的集合。

（一）创建存储过程

DELIMITER [end_label]
CREATE PROCEDURE sp_name([proc_parameter[,...]])
BEGIN
    [statement_list]
END [end_label]

（二）举例

1. out/in

delimiter //
create procedure pro_a(out t1 int, in t2 int)
begin
select chinese+english+math from student where id = t2 into t1;
end //

2. if分支选择语句

delimiter //
create procedure pro_b(in a int, in b int)
begin
if a>b then select 'a>b';
elseif a<b then select 'b>a';
end if;
end //

3. case分支选择语句

delimiter //
create procedure pro_c(in a int)
begin
case a
when 1 then select 'a=1';
when 2 then select 'a=2';
end case;
end //

4. while循环语句

delimiter //
create procedure pro_d(in a int)
begin
while a>0 do
set a=a-1;
select a;
end while;
end //

5. repeat循环语句

delimiter //
create procedure pro_e(in a int)
begin
repeat 
select a;
set a=a-1;
until a<0 end repeat;
end //

6. loop循环语句

delimiter //
create procedure pro_f(in a int)
begin
test:loop
select a;
set a=a-1;
if a<0 then leave test;
end if;
end loop test;
end //

（三）调用存储过程

CALL sp_name([proc_parameter[,...]]);

（四）删除存储过程

DROP PROCEDURE sp_name;

（五）自定义异常处理

在 MySQL 中，特定异常需要特定处理。这些异常可以联系到错误，以及子程序中的一般流程控制。异常处理定义了在遇到问题时对应当采取的处理方式，并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。

1. 语法

DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement

2. 叙述
   handler_type 表示错误处理方式。
   参数为 CONTINUE | EXIT | UNDO 中的 3 个值之一。
   CONTINUE 表示遇到错误不处理，继续执行；
   EXIT 表示遇到错误时马上退出；
   UNDO 表示遇到错误后撤回之前的操作， MySQL 暂不支持回滚操作。
   condition_value 表示错误类型。

SQLSTATE [VALUE] sqlstate_value| mysql_error_code

sqlstate_value 为包含 5 个字符的字符串错误值；
mysql_error_code 匹配数值类型错误代码；

三、游标

游标主要用于在检索出来的行中进行前后按一行或多行滚动的浏览数据的情况下。游标主要用于交互式应用。MySQL游标只能用于存储过程。

（一）游标使用步骤

1. 必须先声明游标
2. 必须打开游标才能使用
3. 通过游标根据需要取出数据
4. 使用完之后必须关闭游标

（二）举例

delimiter //
create procedure cursor_proc()
begin
declare done boolean default 0;
declare id int;

/*声明游标，select 并不会检索数据*/
declare testcousor cursor
for
select sno from student;

/*自定义异常处理*/
declare continue handler for sqlstate '02000' set done = 1;
/*当sqlstate ‘02000’ 出现时，set done = 1。sqlstate ‘02000’ 是一个未找到状态。*/

open testcousor;
test1:loop
fetch testcousor into id;
if done then leave test1;
end if;
select id,sum(grade) from score where studentsid = id;
end loop;
close testcousor;

end //

四、触发器

触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。

（一）创建触发器

语法

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

叙述
触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将**该对象。

tbl_name

触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。

trigger_time

触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在**它的语句之前或之后触发。

trigger_event

指明了**触发程序的语句的类型。
trigger_event可以是下述值之一：

1. INSERT：将新行插入表时**触发程序。例如，通过INSERT、LOAD DATA和REPLACE语句。
2. UPDATE：更改某一行时**触发程序。例如，通过UPDATE语句。
3. DELETE：从表中删除某一行时**触发程序。例如，通过DELETE和REPLACE语句。

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表,不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFOREINSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

trigger_stmt

当触发程序**时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。这样，就能使用存储子程序中允许的相同语句。

（二）查看触发器

SHOW TRIGGERS

（三）删除触发器

DROP TRIGGER [schema_name.]trigger_name