十一、存储过程

1. 为什么需要使用存储过程。

存储过程可以理解成函数，调用存储过程就是调用函数。 所以使用存储过程的理由有下：

• 封装性。 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 完整性。 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
• 防止错误。 需要执行的步骤越多，出错的可能
  性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。 如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

实际上就是函数的作用。

但是语句复杂，且我们需要有创建存储过程的安全访问权限才可以。

2. 创建存储过程。

先看：

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

这就是创建一个存储过程。首先，我们使用关键词

CREATE PROCEDURE 函数名()

括号里面可以有参数，现在先写一个没有的。随后再BEGIN和END中间写下我们的执行体(函数体)。

这里要注意：分号结尾问题。我们在函数体里用分号结尾，整条命令就结束了，无法跳到END。所以我们在创建存储过程之前要先改变结尾的命令符。

delimiter //

使用delimiter关键字来改变，上句是改成双斜杆。创建完存储过程之后，我们要使用

delimiter ;

来改回分号做结束符。

3. 执行存储过程。

CALL productpricing();

4. 删除存储过程。

DROP PROCEDURE productpricing;

5. 使用参数。

(1) 创建。

mysql> CREATE PROCEDURE productpricing(
-> OUT pl DECIMAL(8,3),
-> OUT ph DECIMAL(8,3),
-> OUT pa DECIMAL(8,3)
-> )
-> 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： 参数中的out是表示传出参数。对应还有IN是传入参数，INOUT是传入传出参数。
• DECIMAL(8,3)： 返回一个总数最多8位，小数占3位。
• INTO : 可看做赋值。

(2) 使用。

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

现在三个数都传上来了，执行：

select @pricelow;

皆可以得到第一个参数值：

+-----------+
| @pricelow |
+-----------+
|     2.500 |
+-----------+
1 row in set (0.00 sec)

同理可得其它两个参数。

现在举个有IN关键字的例子：

mysql> CREATE PROCEDURE ordertotal(
-> IN onumber INT,
-> OUT total DECIMAL(8,2)
-> )
-> BEGIN
->     SELECT Sum(item_price*quantity)
->     FROM orderitems
->     WHERE order_num = onumber
->     INTO total;
-> END;
-> //

执行：

CALL ordertotal(20005,@total);

再检索结果：

mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

6. 检查存储过程。

查看我们创建的存储过程的信息：

SHOW CREATE PROCEDURE 名字;

如果是想要获得何时、谁等详细信息，使用：

SHOW PROCEDURE STATUS;