MySQL使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。在我看来和编程语言中的函数是一样一样的。 也就是需要定义函数，和调用函数。

创建存储过程

创建存储过程之前需要注意的是，默认的MySQL语句的分割符为";"，而存储过程是一些SQL语句，每个SQL语句的结尾需要使用分隔符，而定义存储过程也要使用分隔符，这样就会造成语法错误。

mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT AVG(prod_price) AS priceaverage FROM products;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

在输入第一个";"的时候，就已经提示错误。无法定义完成存储过程。

解决的办法是临时改变命令行的语句分隔符。

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage FROM products;
END//
DELIMITER ;

DELIMITER //表示暂时使用"//"当作分隔符。除"\"以外，任何字符都可用作语句分隔符。

需要注意的是，DELIMITER后面要加一个空格，再写"//"。一开始没加空格，结果是总是显示错误，害的我一个字母一个字母的瞄，还是没找到错在哪里。后来我一拍脑袋，DELIMITER后面可能是有问题的。就找到错误的原因了。

执行存储过程

这个简单，使用CALL加上存储过程的名字即可。

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+

删除存储过程

DROP PROCEDURE productpricing;

注意，删除存储过程的时候，后面不需要加上()。这个跟删除表的操作一样，要这个存储过程之前存在，如果不存在就会出现错误。

而想如果过程存在就删除，过程不存在也不出现错误，这时可以使用如下语句

DROP PROCEDURE IF EXISTS productpricing;

使用参数

productpricing只是一个简单的存储过程，它简单的显示SELECT语句的效果（这个显示SELECT语句的效果是不是只是在控制台才显示？）。一般，存储过程并不显示结果，而是把结果返回为你指定的变量。

变量：内存中特定的位置，用来存放临时数据。

以下是productpricing的修改版本

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//

关键字OUT支出相应的参数用来从存储过程中传出一个值（返回给调用者，和 INTO 配合使用）

定义参数的时候形式为：方式+变量名+变量类型，多个变量就用","分割。

MySQL支持三种方式的参数

1. IN：传递给存储过程
2. OUT：从存储过程传出
3. INOUT：对存储过程传入和传出

需要注意的是，记录集是不允许传出的，也就是不能通过一个参数返回多个行或列。

为调用这个修改过的存储过程，必须指定3个变量名,如下：

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

变量名：所有的MySQL变量名都必须以@开始

为了获取三个值，可以使用如下语句

SELECT @pricelow,@pricehigh,@priceaverage;

综上的简单示例

需要使用的表的结构

mysql> SELECT * FROM orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
|     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 |          1 | FC      |       50 |       2.50 |
|     20009 |          1 | FB      |        1 |      10.00 |
|     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+------------+---------+----------+------------+

在此表上定义的存储结构如下

mysql> CREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,OUT ototal DECIMAL(8,2))
    -> BEGIN
    -> DECLARE taxrate INT DEFAULT 6;
    -> DECLARE total DECIMAL(8,2);
    -> SELECT SUM(item_price*quantity)
    -> FROM orderitems
    -> WHERE order_num=onumber
    -> INTO total;
    -> IF taxable THEN
    -> SELECT total+(total/100*taxrate) INTO total;
    -> END IF;
    -> SELECT total INTO ototal;
    -> END//

作用为：计算特定订单的销售额，如果需要加上税的话，可以加上税。

调用结果如下：

mysql> CALL ordertotal(20005,0,@total);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+