第23章 使用存储过程

23.1 存储过程

存储过程，是为了完成一个复杂任务，而编写的多条sql语句的集合，语句执行次序不定，需要根据检索条件判断。

存储过程实际上是一种函数，因此存储过程名后要加括号（），来放置参数，即使没有参数也要括号。

23.2 为何使用存储过程

3个优点：
简单：将复杂语句封装，简单调用即可；业务逻辑更改，直接修改存储过程。
安全：不需反复编写复杂代码，防止错误发生；执行权限和访问权限分开，保障代码安全。
高性能：比单独执行sql语句快，编码灵活。

23.3 使用存储过程

23.3.1创建存储过程

例：返回产品的平均价格：
delimiter //
create procedure product_avgprice()  #括号来放置参数，即使没有参数也要括号。
begin
select avg(prod_price) as avg_price  from products;
end//
delimiter ;

自定义分隔符：
DELIMITER // 自定义定界符、分隔符，除了\，都可以作为分隔符。
自定义分隔符作用：替换系统分隔符-分号（;），防止系统报错。因为系统分不清（;）是存储过程结尾，还是sql语句结尾。

23.3.2 执行存储过程

例：查询产品平均价：call product_avgprice();  #括号来放置参数，即使没有参数也要括号。

23.3.3 删除存储过程

例：删除prodcut_avgprice2存储过程：
drop procedure if exists product_avgprice2;  #注意这里过程名称后不带括号。

关键字IF EXISTS：表示如果存储过程存在就删除，不存在也不会报错，如果不加次关键字，存储过程不存在，mysql服务器会报错。

23.3.4 使用参数

变量：内存中一个特定位置，用于临时存放数据。

参数类型：
IN：传入存储过程
OUT：传出存储过程
INOUT：既传入又传出存储过程

参数的数据类型：和表的数据类型一样，但一个参数只能对应一个值，不能是多行的记录集。

例：创建存储过程，得到产品表中产品的最高检、最低价、平均价：

delimiter // #定义分隔符为//，注意空格
create procedure prod_price(
out pl decimal(8,2), #括号内为存储过程要传出的参数
out ph decimal(8,2),
out pa decimal(8,2)
)
begin #过程体开始
select min(prod_price),max(prod_price),avg(prod_price) into pl,ph,pa from products;
end
//        #过程体结束
delimiter ;  #定义分隔符改回分号；，注意空格。

执行存储过程prod_price：call prod_price(@lowprice,@highprice,@averageprice);

显示平均价、最高价、最低价：select @averageprice,@highprice,@lowprice;

例：根据订单号显示订单总价：
delimiter //
create procedure totalprice(
in order_number int,
out order_total decimal(8,2)
)
begin
select sum(quantity*item_price) into order from orderitems where order_num=order_number;
end 
//
delimiter ;

显示订单20005的总价：
(1)执行存储过程，订单号20005：call totalprice(20005,@order_total);
(2)查询总价：select @order_total;

23.3.5 智能存储过程

例：根据订单号显示订单总价，根据客户不同，总价可能要包含营业税：

delimiter //
-- name:odertotal 存储过程名：订单总价
-- parameters: onumber= order number 订单编号
--                    taxable= 0 if not taxable, 1 if taxable 是否含税
--                    ototal= order total variable 订单总价变量

create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)  comment 'obtain order total,optionally add tax'
begin
-- declare variable for total
declare total decimal(8,2);
-- declare tax percentage
declare taxrate int default 6;
-- get the order total
select sum(quantity*item_price)
from orderitems
where order_num=onumber
into total;

-- is this taxable？
if taxable then
-- yes,so add taxrate to the total
select total+(total/100*taxrate ) into total;
end if;
-- and finally,save to our variable
select total into ototal;
end//
delimiter ;

例：执行存储过程，并查询订单20005的不含税总价合计：
call ordertotal(20005,0,@total);
select @total;

例：执行存储过程，并查询订单20005的不含税总价合计：
call ordertotal(20005,1,@t);
select @t;

“注释”有三种方法：

1、comment '注释内容'

2、符号 “-- ” ，注意：做注释时，--后面要有空格，且必须放在行首，不可以和sql语句放在同一行。

3、符号“#” ，可以和sql语句放在同一行。
注释的内容会在查询存储过程状态时显示：show procedure status like ‘过滤模式’ \G;

23.3.6 检查存储过程

例：查询存储过程的创建语句：show create procedure ordertotal \G;

例：查询存储过程的状态：show procedure status like '过程名' \G;
# \G表示垂直显示文字。
# like后也可用通配符过滤模式，和select语句中一样。

《mysql必知必会》是一本好书，是一本sql语言入门书，豆瓣评分很高。

作者是英国的Ben Forta，世界知名的技术作家，由人民邮电出版社发行，我觉得原作名: MySQL Crash Course，直译为:《MYSQL速成》更具畅销书潜质，只是比较俗吧，呵呵。

书中从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全文本的搜索、存储过程、游标、触发器、表约束，等等。

前三章是基础概念，讲了SQL和数据库的基本概念，Mysql数据库的概念和使用方法，第四章开始SQL实操练习，这里是本书的一些实操练习笔记，有兴趣的话可以按这个练习几遍，相信对新手会很有帮助，让你不经意间功力大增。