存储过程

当一个完整的操作需要多条语句才能完成，如

为处理订单，需要核对以保证库存中有相应的物品
如果库存里有物品，则将物品预订以边不将其卖给他人，并且要减少可用的物品数量以反映正确的库存量
库存中没有的物品需要订购，与供货商进行交互
关于哪些物品入库并可以立即发货的，和那些物品退订，要通知相应的客户

处理多表多MySQL语句

单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时都要重复做
创建存储过程，即为之后的使用而保存的一条或多条语句的集合。可以视为批文件，但作用不仅限于批处理

存储过程的优势

简单
- 通过把处理封装在容易使用的单元中，简化复杂操作
- 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。防止错误
安全
- 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。其他人员甚至不需要知道这些变化。安全性
高性能
- 提高性能。因为使用存储过程比使用单独的SQL语句要快
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码

存储过程的缺陷

存储过程的编写比基本SQL语句复杂，编写存储过程需要更高技能，更丰富经验
创建存储过程权限限制，未必有权限

存储过程使用

执行存储过程

调用存储过程CALL，接收存储过程名字已经所需传参

1 CALL productpricing(@pricelow,
2 　　　　　　　　　　　　@pricehigh,
3 　　　　　　　　　　　　@priceaverage);

创建存储过程

例：返回产品平均价格的存储过程

创建存储过程：

1 CREATE PROCEDURE productpricing()
2 BEGIN
3 　　SELECT Avg(prod_price) AS priceaverage
4 　　FROM products;
5 END
6 #存储过程名productpricing，若存储过程要接收参数，在()中列举
7 #BEGIN ENG 限定存储过程体，过程体本身只是简单的SELECT语句

当MySQL处理这段代码将创建一个新的存储过程productpricing。没有调用存储过程，故没有返回数据。

使用存储过程：

1 CALL productpricing()

删除存储过程

1 DROP PROCEDURE productpricing;

注意删除存储过程时，没有使用后面的()，只用给出存储过程名。

仅当存在时删除：

如果指定的过程不存在，删除会报错，可以用

DROP PROCEDURE IF EXISTS productpricing;

传递参数

一般存储过程并不会显示结果，而是把结果返回给指定的变量

变量variable：用来临时存储数据

--例1、创建存储过程：

 1 CREATE PROCEDURE productpricing(
 2 OUT pl DECIMAL(8,2),
 3 OUT ph DECIMAL(8,2),
 4 OUT pa DECIMAL(8,2)
 5 )
 6 BEGIN
 7 　　SELECT Min(prod_price)
 8 　　INTO pl
 9 　　FROM products;
10 　　SELECT Max(prod_price)
11 　　INTO ph
12 　　FROM products;
13 　　SELECT Avg(prod_price)
14 　　INTO pa
15 　　FROM products;
16 END;

接收3个参数：pl最低价格，ph最高价格，pa平均价格
OUT 指出相应的参数用来从存储过程传出一个值返回给调用者
IN(传递给存储过程)，OUT(从存储过程传出)，INOUT(对存储过程传入和传出)
通过指定INTO将检索结果保存到相应变量
不能通过一个参数返回多个行和列

--例1、调用存储过程：

1 CALL productpricing(@pricelow,
2 　　　　　　　　　　　　@pricehigh,
3 　　　　　　　　　　　　@priceaverage);

存储过程要求3个参数，则必须传参3个。
所有MySQL变量都必须@开始

--例1、显示检索结果：

1 SELECT @priceaverage;
2 ############
3 SELECT @pricelow,@pricehigh,@priceaverage;

----------------------------------------------------------------------------

--例2、创建存储过程：

 1 # 接收订单号并返回该订单的合计
 2 CREATE PROCEDURE ordertotal(
 3 IN onumber INT,
 4 OUT ototal DECIMAL(8,2)
 5 )
 6 BEGIN
 7 　　SELECT Sum(item_price*quantity)
 8 　　FROM orderitems
 9 　　WHERE order_num = onumber
10 　　INTO ototal;
11 END;

订单号onumber定义为IN，传入存储过程

合计ototal定义为OUT，从存储过程返回合计

INTO使用otoal存储计算出来的合计

--例2、调用存储过程：

1 CALL ordertotal(20005,@total);

--例2、显示检索结果：

1 SELECT @total

建立智能存储过程

发挥存储过程真正的实例，在存储过程中包含业务规则和智能处理，如：

获得订单的合计
把营业税有条件的添加到合计
返回合计

 1 ‐‐ 方法名：ordertotal
 2 ‐‐ 规则：onumber = order number
 3 ‐‐ 　　　taxable = 0 if not taxable,1 if taxable
 4 ‐‐ 　　　ototal = order total variable
 5 CREATE PROCEDURE ordertotal(
 6 　　IN onumber INT,
 7 　　IN taxable BOOLEAN,
 8 　　OUT ototal DECIMAL(8,2)
 9 ) COMMENT 'Obtain order total, optionally adding tax'
10 BEGIN
11 　　‐‐ Declare variable for total
12 　　DECLARE total DECIMAL(8,2);
13 　　‐‐ Declare tax percentage
14 　　DECLARE taxrate INT DEFAULT 6;
15 
16 　　‐‐ Get the order total
17 　　SELECT Sum(item_price*quantity)
18 　　FROM orderitems
19 　　WHERE order_num = onumber
20 　　INTO total;
21 
22 　　‐‐ Is this taxable?
23 　　IF taxable THEN
24 　　　　‐‐ add taxrate to the total
25 　　　　SELECT total+(total/100*taxrate) INTO total;
26 　　END IF;
27 
28 　　‐‐ And finally,save to out variable
29 　　SELECT total INTO ototal;
30 END;

taxable布尔值，加税为真，否则为假

存储过程体中，DECLARE定义了两个局部变量并指定变量名和数据类型

在CREATE PROCEDURE语句中包含一个comment值，即将在SHOW PROCEDURE STATUS的结果中显示

调用

1  CALL ordertotal(20005,0,@total);
2 SELECT @total;
3 ##############
4 CALL ordertotal(20005,1,@total);
5 SELECT @total;

检查存储过程

为显示用来创建一个存储过程的CREATE语句：

1 SHOW CREATE PROCEDURE ordertotal;

为获取何时由谁创建的详细信息的存储过程列表使用：

1 SHOW PROCEDURE STATUS LIKE 'ordertotal';