MySQL存储过程+游标+触发器

【0】README

0.1）本文旨在 arrange mysql 存储过程及如何在存储中使用游标  的相关知识；

0.2）delimieter的用法：参见 http://blog.csdn.net/pacosonswjtu/article/details/51407756；

【1】存储过程基础

1）intro to procedure：简单来说，存储过程就是为以后的使用而保存的一条或多条MySQL 语句的集合。可将其视为 批文件；

2）使用存储过程的理由（reasons）：

r1）通过把处理封装在容易使用的单元中，简化复杂的操作 ；

r2）提高性能：因为使用存储过程比使用单独的SQL语句要快；

r3）安全性：通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会；

r4）存在一些只能用在单个请求中的MySQL 元素和特性，存储过程可以使用它们来编写 功能更强大更灵活的代码；

3）存储过程带来的缺陷（defects）

d1）存储过程的编写比基本SQL语句更加复杂，编写存储过程需要更高的技能，更更丰富的经验；

d2）你可能没有创建存储过程的安全访问权限。许多 admin 限制存储过程的创建 权限，允许用户使用存储过程，但不允许他们创建存储过程 ；

Attention）MySQL 将编写存储过程的安全和访问权限 和 执行存储过程的安全和访问权限区分开来。所以，即使你不能编写自己的存储过程，与可以在适当的时候执行别的存储过程；

【2】存储过程

1）执行存储过程：执行过程称为存储过程的调用；

2）创建存储过程：

problem+solution）

problem：默认的MySQL 语句分隔符为；（分号），MySQL 命令行实用程序(mysql.exe)也使用；作为分隔符；如果命令行实用程序要解释存储过程自身内的； 字符，则它们最终不会称为存储过程 的一部分，这会使得存储过程中的SQL 出现句法错误；

solution：解决方法是 临时更改命令行实用程序的语句分隔符，如下所示：

delimiter //
drop procedure if exists avg_price //
create procedure avg_price()
begin
 select avg(price) as avg_price from product;
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）delimiter // ：告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的end 定义为 end // 而不是 end；

A2）这样在存储过程体内的； 保持不动，并且正确地传递给数据库引擎。最后为恢复为原来的 分隔符，可以使用 delimieter；

A3）除了 '\' 符号外，任何字符都可以用作语句分隔符；

3）如何使用这个存储过程？ call procedure_name;

【2.1】在存储过程中使用参数

1）一般存储过程并不显示结果，而是吧结果返回给你指定的变量；

2）变量：内存中一个特定的位置，用来临时存储数据；

3）看个荔枝

delimiter //
drop procedure if exists min_avg_max_price //
create procedure min_avg_max_price(
 out p1 decimal(8,2),
 out p2 decimal(8,2),
 out p3 decimal(8,2)
)
begin
 select min(price) into p1
 from product;

 select avg(price) into p2
 from product;

 select max(price) into p3
 from product;
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）该存储过程接收3个参数： p1, p2, p3；

A2）关键字out：指出了相应的参数用来从存储过程中传入一个值（返回给调用者）；（也即是看做 参数的数据类型）

A3）MySQL 支持3中参数类型： in（传递给存储过程），out（从存储过程传递给调用者） 和 inout （对存储过程传入和传出）；

A4）存储过程的执行代码： 位于 begin 和 end 语句内；

4）再看个荔枝（in 和 out 参数并用）

delimiter //
drop procedure if exists order_sum //
create procedure order_sum(
 in p_name varchar(30),
 in p_num int,
 out p_sum decimal(8,2)
)
begin
 select p_num*p.price into p_sum from product p where p.name=p_name;
end //
delimiter ;

对以上代码的分析（Analysis）： 注意它们的in 和 out 参数类型就是；

【2.2】建立智能存储过程

1）只有在存储过程内包含业务规则和智能处理时，存储过程 的 威力才真正显现出来；

2）任务需求（requirements）:返回合计（带税和不带税）；

delimiter //
drop procedure if exists tax_order_sum //
create procedure tax_order_sum(
 in taxable boolean,
 in p_name varchar(30),
 in p_num int,
 out p_sum decimal(8,2)
)comment 'return order sum with and without tax'
begin
 -- declare variable for tax_order_sum.
 -- attention for the difference between variable and parameter.
 declare taxrate int default 6;
 
 select p_num*p.price into p_sum from product p where p.name=p_name;
 if taxable then
  select p_sum + p_sum*taxrate/100 into p_sum;
 end if;
end //
delimiter ;

对上述代码的分析（Analysis）：

A1）增加了注释 comment；

A2）添加了另外一个参数 taxable，是否交税；（缴税）

A3）declare用于声明局部变量；（干货——注意参数和变量的区别）；

A4）上述代码还使用到了 if end if 语句；

A5）boolean 类型的参数：0表假，1表真；

supplement）检查存储过程： show create procedure tax_order_sum;

【3】使用游标

1）mysql 检索操作返回一组称为结果集的行；有时候需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因；

2）intro to cursor：游标是一个存储在mysql 服务器上的数据库查询，他不是一条select语句， 而是被该语句检索出来的结果集；

3）在存储了游标之后，应用程序可以根据需要 滚动或浏览 其中的数据；

Attention）游标只能用于存储过程；（干货——游标只能用于存储过程）

【3.1】使用游标

1）使用游标涉及几个steps：

step1）在能够使用游标前，必须声明（定义）它；

step2）一旦声明后，必须打开游标以供使用；

step3）对于填有数据的游标，根据需要取出（检索）各行；

step4）在结束游标使用时，必须关闭游标；

Attention）在声明游标后， 可根据需要 频繁地打开和关闭游标。 在游标打开后， 可根据需要频繁地执行取操作；

2）创建游标

-- create a temp table.
 drop table if exists temp_sum_price
 create table temp_sum_price(id int,sum_price decimal(8,2));

3）打开游标， 使用游标数据和关闭游标

-- open the cursor.
 open mycursor; 

-- declare variables.
 declare vendor_id int;
 declare sum_price decimal(8,2);

-- use cursor data.
fetch mycursor into vendor_id,sum_price;

-- close the cursor.
 close mycursor; 

4）看个荔枝：利用游标统计每个vendor供应商的产品单价的总和；

delimiter //
drop procedure if exists total_price_with_cursor;

create procedure total_price_with_cursor(
) comment 'computing sum of price grouped by vendor'
begin
 -- declare variables.
 declare vendor_id int;
 declare sum_price decimal(8,2);
 declare done boolean; 

 -- declare the cursor.(highlight line)
 declare mycursor cursor for
 select v.id, sum(price) from product p,vendor v where p.vendor=v.id group by vendor;
	
 -- declare continue handler.
 declare continue handler for sqlstate '02000' set done=1; 
	
 -- create a temp table.
 drop table if exists temp_sum_price;
 create table temp_sum_price(
  vendor_id int ,
  sum_price decimal(8,2)
 );  
 -- open the cursor.
 open mycursor; 
	
 -- repeat to call fetch clause
 repeat
  fetch mycursor into vendor_id,sum_price; -- (highlight line)
  insert into temp_sum_price values(vendor_id,sum_price);
 until done end repeat;
	
 -- close the cursor.
 close mycursor; 
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）declare continue handler for sqlstate '02000' set done=1;这条语句定义了一个 continue handler， 它是在条件出现时被执行的代码。

A2）上述代码指出当，SQLSTATE ‘02000’出现时，set done=1。SQLSTATE ‘02000’ 是一个未找到的条件，当repeat 由于没有更多的行提供循环而不能继续时，出现这个条件；

A3）declare 语句的次序：用declare 语句定义的局部变量必须在定义任意游标或句柄前定义，而句柄必须在游标后定义，不遵循此顺序将产生错误消息；

【4】使用触发器

1）intro to trigger：触发器是mysql 响应以下任意语句而自动执行的一条mysql语句（或位于begin 和 end之间的一组语句）；

2）语句有：delete，insert，update；

【4.1】创建触发器

1）创建触发器时，需要给出4条信息（information）：

i1）唯一的触发器名；

i2）触发器关联的表；

i3）触发器应该响应的活动（delete，insert或update）；

i4）触发器何时执行（处理前或处理后）；

Attention）只有表才有触发器，视图不支持；

2）看个荔枝

drop trigger if exists t_insert_product;
delimiter //
create trigger t_insert_product after insert on product
 for each row 
begin
 insert into temp_auto_increment values(null,new.id); 
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）create trigger 用来创建名为 product的新触发器；触发器可以在一个操作发生之前或之后执行；这里给出了 after insert，所以此触发器将在insert 语句成功执行后执行；

A2）这个触发器还指定了 for each row，因此代码对每个插入行执行；

Attention）

A1）触发器按照每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条insert， update，deleter的之前和之后）；

A2）单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 insert 和 update 操作执行的触发器，则应该定义两个触发器；

【4.2】删除触发器

drop trigger if exists t_insert_product;

【4.3】使用触发器

【4.3.1】insert触发器

1）insert触发器的使用需要知道以下几点（points）：

p1）在insert触发器代码内，可以引用一个名为 new 的虚拟表，访问被插入的行；

p2）在 before insert 触发器内，new 中的值也可以被更新（允许更改被插入的值）；

p3）对于 auto_increment列，new 在 insert执行之前包含0，在insert执行后包含新的自动生成值；

2）看个荔枝：在插入到 product后，执行 begin 和 end 里面的语句（在after insert ，向 temp_auto_increment表插入数据）

drop trigger if exists after_insert_product;
delimiter //
create trigger after_insert_product after insert on product
 for each row 
begin
 insert into temp_auto_increment values(null,new.id); 
end //
delimiter ;

【4.3.2】delete 触发器

1）delete 触发器的使用需要知道以下几点（points）：

p1）在delete触发器代码内，你可以引用一个名为 old 的虚拟表，访问被删除的行；

p2）old中的值全部是只读的，不能更新；

drop trigger if exists after_delete_product;
delimiter //
create trigger after_delete_product after delete on product
 for each row 
begin
 insert into temp_auto_increment values(null,old.id);
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）上面的代码定义了一个触发器 after_delete_trigger，用于在 delete 后触发；

A2）触发事件的工作是向 temp_auto_increment 表中插入 删除行的id；

【4.3.3】update触发器

1）update 触发器的使用需要知道以下几点（points）：

p1）在update 触发器代码内，你可以引用一个名为 old的虚拟表访问以前（update之前）的值，引用一个名为 new的虚拟表访问新更新的值；

p2）在before update 触发器内，new 中的值可能也被更新（允许更改将要用于 update 语句中的值）；

p3）old中的值 全都是只读的，不能更新；

drop trigger if exists before_update_product;
delimiter //
create trigger before_update_product before update on product
 for each row 
begin
 set new.name=upper(new.name);
end //
delimiter ;

对以上代码的分析（Analysis）：

A1）以上代码的定义了一个触发器before_update_product ，在update 前触发；

A2）触发事件的工作是 将update的所在行的name设置为 大写；

Attention）intro 一些使用触发器需要记住的重点；

A1）与其他DBMS相比，MySQL5 中支持的触发器相当初级；

A2）创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果insert，update和delete语句能够执行，则相关的触发器也能执行；

A3）应该用触发器来保证数据的一致性（大小写，格式等）。在触发器中执行这些类型的处理的优点是它总是进行这种处理，而且是透明地进行，与client 引用无关；

A4）触发器的一种非常有意义的 使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易；

A5）MySQL触发器不支持call 语句，表明 不能从触发器内调用存储过程；（干货——MySQL触发器内不能调用存储过程）