我们常用的操作数据库的SQL语句在执行的时候需要先编译，然后执行；而存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果有的话）来调用它。
1.什么是存储过程
简单的来说，存储过程就是为了以后的使用而保存的一条或多条SQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
2.为什么要使用存储过程
（1）通过把处理封装在容易使用的单元中，简化复杂的操作。
（2）简化对变动的管理。如果表名、列名等有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化，通过存储过程限制对基础数据的访问减少数据讹误的机会。
（3）提高性能。因为使用存储过程比使用单独的SQL语句要快。
总的来说，存储过程有三个主要的好处：简单、安全、高性能。
3.使用存储过程
（1）执行存储过程
MySQL使用关键字CALL来调用存储过程，CALL接受存储过程的名字以及需要传递给它的参数，参考如下示例：
|
1 |
CALL productpricing(@pricelow, @pricehigh, @priceaverage); |
其中，存储过程名为productpricing，它计算并返回产品的最低、最高和平均价格；存储过程可以显示结果，也可以不显示结果，稍后介绍。
（2）创建存储过程
创建一个名为productpricing的存储过程：
|
1 2 3 4 5 6 7 8 9 |
mysql> DELIMITER // mysql> CREATE PROCEDURE productpricing() -> BEGIN -> SELECT AVG(prod_price) AS priceaverage -> FROM products; -> END // Query OK, 0 rows affected (0.50 sec) mysql> DELIMITER ; |
其中：
1）语句CREATE PROCEDURE用来定义存储过程，如果存储过程接受参数，它们将在（）内列举出来。
2）关键字BEGIN和END用来限定存储过程体，该示例存储过程体只是一个简单的select语句。
3）如果你使用的是mysql命令行实用程序（跟本示例一样），那么默认的MySQL语句分隔符为分号（;），巧了的是，mysql命令行实用程序也使用分号作为语句分隔符。这样导致存储过程体在输入的过程中会输入不完全而终止，出现语法错误。
4）针对第三点的解决办法是临时更改命令行实用程序的语句分隔符，使用关键字DELIMITER。
创建完之后，就可以使用CALL来调用了：
|
1 2 3 4 5 6 |
mysql> CALL productpricing(); +--------------+ | priceaverage | +--------------+ | 16.133571 | +--------------+ |
（3）删除存储过程
关键字DROP可以用来删除一个存储过程：
|
1 |
DROP PROCEDURE productpricing; |
（4）使用参数
productpricing只是一个简单的存储过程，它简单地显示select语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
以下是productpricing的修改版本（如果不限删除此存储过程，则不能再次创建它）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> DELIMITER // mysql> CREATE PROCEDURE productpricing( -> OUT pl DECIMAL(8,2), -> OUT ph DECIMAL(8,2), -> OUT pa DECIMAL(8,2) -> ) -> BEGIN -> SELECT MIN(prod_price) -> INTO pl -> FROM products; -> SELECT MAX(prod_price) -> INTO ph -> FROM products; -> SELECT AVG(prod_price) -> INTO pa -> FROM products; -> END; -> // Query OK, 0 rows affected (0.07 sec) |
此存储过程接受3个参数，其中：
1）关键字OUT指出相应的参数用来从存储过程传出一个值给调用者，MySQL支持IN(传递给存储过程)、OUT（从存储过程传出）、INOUT（对存储过程传入和传出）类型的参数。
2）该存储过程是一系列SELECT语句，用来检索值，然后保存到相应的变量——通过指定INTO关键字。
为了获得3个值，可使用以下语句：
|
1 2 3 4 5 6 7 8 |
mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage); mysql> SELECT @pricelow, @pricehigh, @priceaverage; +-----------+------------+---------------+ | @pricelow | @pricehigh | @priceaverage | +-----------+------------+---------------+ | 2.50 | 55.00 | 16.13 | +-----------+------------+---------------+ 1 row in set (0.00 sec) |
注意：所有的MySQL变量都必须以@开始。
再看一个使用关键字IN的示例：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> 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; |
其中，
1）onumber定义为IN，因为订单号被传入存储过程。
2）ototal定义为OUT，因为要从存储过程返回合计值。
3）SELECT语句使用这两个参数，WHERE子句使用ONUMBER选择正确的行，INTO使用ototal存储计算出来的合计。
为调用这个存储过程，使用如下语句：
|
1 |
mysql> CALL ordertotal(20005, @total); |
必须给其传递两个参数，第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。
然后，为了显示此合计，可使用如下语句：
|
1 2 3 4 5 6 |
mysql> SELECT @total; +--------+ | @total | +--------+ | 149.87 | +--------+ |
为了得到另一个订单的合计显示，可以再次调用存储过程：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CALL ordertotal(20009, @total); Query OK, 1 row affected (0.00 sec) mysql> SELECT @total; +--------+ | @total | +--------+ | 38.47 | +--------+ 1 row in set (0.00 sec) |
4.建立智能存储存过
通过上面的几个简单的示例，我们已经对存储过程有了一个直观的感受。然而，上面的示例都是对select语句的封装，现实应用中我们不会去为几个简单的select语句创建存储过程。
只有在存储过程内包含业务规则和智能处理时，它们的威力才真正的显现出来。
考虑如下场景：你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客。那么，你需要做下面几件事情：
- 获得合计（和前面的示例一样）
- 把营业税有条件添加到合计
- 返回合计（带或不带税）
对此，有如下存储过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> -- Name: ordertotal mysql> -- Parameters: onumber = order number mysql> -- taxable = 0 if not taxable, 1 if taxable mysql> -- ototal = order total variable mysql> 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); -> -- Declare tax percentage -> DECLARE taxrate INT DEFAULT 6; -> --Get the order total -> 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; |
此存储过程引入了一些新的特性：
1）增加了注释（前面的–符号）
2）添加了另一个参数taxable，它是一个布尔值
3）DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（示例中taxrate的默认值设置为了6%）
4）SELECT语句较前面的示例也有所改变——其结果存储到total（局部变量）而不是ototal
5）IF语句检查taxable是否为真。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。
6）最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。
7）COMMENT关键字：它不是必需的，但如果给出，将在show procedure status的结果中显示。
对该存储过程进行调用：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CALL ordertotal(20005, 0, @total); SELECT @total; +---------+ | @total | +---------+ | 149.87 | +---------+ CALL ordertotal(20005, 1, @total); SELECT @total +---------+ | @total | +---------+ | 158.86 | +---------+ |
另外，为了显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句；例如：
SHOW CREATE PROCEDURE ordertotal;
为了获得包括合适、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS语句，也可使用LIKE指定一个过滤模式，例如：
SHOW PROCEDURE STATUS LIKE ‘ordertotal’;