MySQL Crash Course #15# Chapter 23. Working with Stored Procedures

转载

mb5ff981d806017 2018-04-22 12:10:00

文章标签 存储过程 mysql 分隔符自动生成命令行 文章分类 代码人生

以前写过类似的东西，用来自动生成数据。

你可以将 Stored Procedure 理解为可以重复使用的批处理文件。

Stored Procedure 非常有用，我们应该尽可能地去使用它。

那么，应用 Stored Procedure 有什么好处呢？

封装过程，简化复杂的操作
代码重用、共用，所有人都用同一个存储过程，减少出错的可能
简化变更管理，如果业务逻辑发生改变，修改存储过程就可以了，上层软件甚至不需要知道发生了什么改变。
提高性能，存储过程比单条执行要快
MySQL 语言让存储过程更加强大和灵活

查看已存在的存储过程（这个结果是可以过滤的！）：

SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE procedure_name;

调用一个已经存在的存储过程非常简单：

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

但是写起来就没那么简单了，首先看一个简单的例子：

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

这个示例创建了一个叫 productpricing() 的存储过程，丢到命令行跑一下：

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 4

由于分隔符原因，创建失败了，解决方案是临时地改变分隔符：

DELIMITER //

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

DELIMITER ;

再次丢进去跑，然后调用新创建的存储过程：

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

删除这个存储过程：

DROP PROCEDURE IF EXISTS productpricing;

创建并调用一个升级版本的存储过程：

DELIMITER //

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

DELIMITER ;

mysql> CALL productpricing(@pricelow,
    ->                     @pricehigh,
    ->                     @priceaverage);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

就像上面看到的，MySQL 的变量名必须以 @ 开头。MySQL 所允许的变量类型和表中允许的字段类型是一致的，对于存储过程参数而言，可以再分成三大类：IN参数 OUT参数以及 INOUT参数，再次看一个简单的例子，创建如下存储过程：

DELIMITER //

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

DELIMITER ;

查看 id 为 20006 的订单总价：

mysql> CALL ordertotal(20006, @total);
Query OK, 1 row affected (0.00 sec)

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

我一直觉得存储过程这个翻译有歧义，可能改成“被存储的过程”会比较好懂。。。

下面我们来创建一个更智能的存储过程，这才是存储过程真正的用途：

-- 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);
   -- 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;