第二十三章 存储过程
创建和使用存储过程
存储过程
大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都是如此简单，经常会有一个完整的操作需要多条语句才能完成。
- 例
1.处理订单，核对库存中有相应的物品
2.如果库存有物品，物品需要预定以便不卖给别人，库存数量减少
3.库存中没有物品则需要于供应商进行交互
4.物品入库和退订需要通知相应客户
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然其作用不限于此。
为什么要使用存储过程
主要好处：简单、安全、高效
- 通过把处理分装在容易使用的单元中，简化复杂操作
- 不要求反复建立一系列处理步骤，保证了数据的完整性，防止错误
- 简化对变动的管理。如果表名、列名、业务逻辑有变化只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
- 提高性能，使用存储过程比使用单独的SQL语句要快
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
注意
- 存储过程编写比SQL语句更复杂
- 编写存储过程需要安全访问权限
- MySQL将编写存储过程和使用存储过程的权限分开使用
使用存储过程
存储过程的执行远比其定义更经常遇到
执行存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。
CALL productpricing ( @pricelow, @pricehigh, @priceaverage );
执行名为productpricing的存储过程，计算并返回产品最低、最高和平均价格。
存储过程可以显示加过，也可以不显示结果
创建存储过程
CREATE PROCEDURE productpricing () BEGIN
SELECT
AVG( prod_price ) AS priceaverage
FROM
products;
END;
注意其有2个分隔符;
创建名为productpricing的存储过程，此存储过程没有参数，但是()仍然需要。BEGIN和END语句用来限定存储过程体，过程本身是一个SELECT语句
MySQL命令行客户机的分隔符;
如果使用的是命令行，因为MySQL默认语句分隔为;
而存储过程需要使用自身内部的;字符，会导致SQL出现句法错误。
可以临时更改命令行实用程序的语句分隔符
SELIMITER //
CREATE PROCEDURE productpricing () BEGIN
SELECT
AVG( prod_price ) AS priceaverage
FROM
products;
END//
DELIMITER ;
SELIMITER //告诉命令行使用//作为新的语句结束分隔符。最后DELIMITER ; 恢复为原来的语句分隔符。
除了\ 转义字符外，任何字符都可以用作语句分隔符
- 执行该存储过程
CALL productpricing ();
因为存储过程实际上是一种函数，所以存储过程名后需要有()符号，即使不传参数也需要
删除存储过程
DROP PROCEDURE productpricing;
注意其没有()，只需要给出存储过程名即可
- 仅当存在时删除
如果指定过程不存在将会产生一个错误，可使用DROP PROCEDURE IF EXISTS...
使用参数
一般，存储过程并不显示结果，而是把结果返回指定的便利(用来临时存储数据)
- 创建存储过程
CREATE PROCEDURE productpricing (
OUT p1 DECIMAL ( 8, 2 ),
OUT ph DECIMAL ( 8, 2 ),
OUT pa DECIMAL ( 8, 2 )) BEGIN
SELECT
MIN( prod_price ) INTO p1
FROM
products;
SELECT
MAX( prod_price ) INTO ph
FROM
products;
SELECT
AVG( prod_price ) INTO pa
FROM
products;
END;
此存储过程接受3个参数：p1最低、ph最高、pa平均。使用十进制。
OUT指出相应的参数用来从存储过程传出一个值返回给调用者。MySQL支持IN、OUT和INOUT类型的参数
- 调用
CALL productpricing ( @1, @2, @3 );
MySQL变量必须以@开始
其不返回任何数据，但是可以检索出其变量
检索存储的变量
SELECT
@1,
@2,
@3;
使用IN和OUT参数
ordertotal存储过程接受订单号，并返回该订单的合计价格。
CREATE PROCEDURE ordertotal (
IN onumber INT,
OUT ototal DECIMAL ( 8, 2 )) BEGIN
SELECT
SUM( item_price * quantity )
FROM
orderitems
WHERE
order_num = onumber INTO ototal;
END;
调用存储过程，并查询变量值
CALL ordertotal ( 20005, @total );
SELECT
@total;
建立智能存储过程
只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。
- 例
需要订单合计，针对某些顾客对合计增加营业税
1.获得合计
2.把营业税有条件地添加到合计
3.返回合计（带税或不带税）
-- Name:ordertotal
-- 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 adding tax" BEGIN
-- DECLARE variable for total
DECLARE
total DECIMAL ( 8, 2 );
-- get the order total
DECLARE
taxrate INT DEFAULT 6;
SELECT
SUM( item_price * quantity )
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 out variable
SELECT
total INTO ototal;
END;
- 关于该SQL的解释
1.增加注释--
2.添加了宁外一个参数taxable，为boolean值是否增税
3.declare定义了2个局部变量，支持可选的默认值
4.SELECT语句的值存储到total
5.检查taxable是否为真，如果为真则用宁一个SELECT语句增加营业税
6.将total保存到ototal
7.COMMENT关键字，不是必须的，如果给出将在SHOW PROCEDURE STATUS的结果中显示
- 调用存储过程
CALL ordertotal ( 20005, 0, @total );
SELECT
@total;
CALL ordertotal ( 20005, 1, @total );
SELECT
@total;
IF语句
IF语句还是吃ELSEIF(还可使用THEN子句)和ELSE子句
检查存储过程
显出用来创建一个存储过程的CREATE语句，使用
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE "ordertotal";
SHOW PROCEDURE STATUS可获得包括合适、由谁创建等详细信息的存储过程列表，由于会列出所有的存储过程，故可以使用LIKE过滤限制输出
