21 使用存储过程

21.1 存储过程

• 迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如，考虑以下的情形。
  • 为了处理订单，需要核对以保证库存中有相应的物品。
  • 如果库存有物品，这些物品需要预定以便不将它们再卖给别人，并且要减少可用的物品数量以反映正确的库存量
  • 库存中没有物品需要订购，这需要与供应商进行某种交互
  • 关于哪些物品入库和哪些物品退订，需要通知相应的客户。
• 可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条SQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

21.2 为什么要使用存储过程

• 下面列出一些主要的理由：
  • 通过把处理封装在容易使用的单元中，简化复杂的操作
  • 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名，列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据错误（无意识的或别的原因所导致的数据错误）的机会。
  • 提高性能。因为使用存储过程比使用单独的SQL语句要快。
  • 存在一些只能在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
  • 换句话来说，使用存储过程由3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。
  • 一般来说，存储过程的编写比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
  • 你可能没有创建存储过程的安全访问权限。许多数据管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
• 尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。
• 不能编写存储过程？你依然可以使用 MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。这是好事情。即使你 不能编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

21.3 使用存储过程

• 使用存储过程需要知道如何执行它们。存储过程的执行远比其定义更经常遇到，因此，我们将从执行存储过程开始介绍。然后再介绍创建和使用存储过程。

21.3.1 执行存储过程

• MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。请看以下例子：

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

• 其中，执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格。
• 存储过程可以显示结果，也可以不显示结果。

21.3.2 创建存储过程

• 编写存储过程并不是微不足道的事情。为了让你了解这个过程，请看一个例子————一个返回产品平均价格的存储过程。以下是其代码：

create procedure productpricing()
begin
    select Avg(prod_price) as priceaverage
    from products;
end;

• 此存储过程名为productpricing，用create procedure productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。begin和end语句用来限定存储过程体，过程体本身仅是一个简单的select语句。
• 在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
• 使用这个存储过程？如下所示:

CALL productpricing();

+--------------+
| priceaverage |
+--------------+
| 16.133571    |
+--------------+

• CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号。

21.3.3 删除存储过程

• 存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。
• 为删除刚创建的存储过程，可使用以下语句：

drop procedure productpricing;

• 这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。
• 仅当存在时删除 如果指定的过程不存在，则drop procedure将产生一个错误。当过程存在想删除它时可使用drop procedure if exists。

21.3.4 使用参数

• productpricing只是一个简单的存储过程，它简单地显示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;

• 此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程 传出一个值。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于begin和end语句内，如前所见，它们是一系列select语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。
• 为调用此修改过的存储过程，必须指定3个变量名，如下所示：

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

• 由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。所以，这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。
• 变量名 所有MySQL变量都必须以@开始。
• 在调用时，这条语句并不显示任何数据。它返回以后可以显示的变量。
• 为了显示检索出的产品平均价格，可如下进行：

select @priceaverage;

+---------------+
| @priceaverage |
+---------------+
| 16.13         |
+---------------+

• 为了获得3个值，可使用以下语句：

select @pricehigh, @pricelow, @priceaverage;

+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00      | 2.50      | 16.13         |
+------------+-----------+---------------+

• 下面是另外一个例子，这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

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;

• onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。select语句使用这两个参数，where子句使用onumber选择正确的行，INTO使用ototal存储 计算出来的合计。
• 为调用这个新存储过程，可使用以下语句：

CALL ordertotal(20005, @total);

• 必须给ordertotal传递两个参数：第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。
• 为了显示此合计，可如下进行：

select @total;

+--------+
| @total |
+--------+
| 149.87 |
+--------+

• @total已由ordertotal的CALL语句填写，select显示它包含的值。

21.3.5 建立智能存储过程

• 只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。
• 考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客。那么，你需要做下面几件事情：
  • 获得合计
  • 把营业税有条件地添加到合计
  • 返回合计（带或不带税）
• 存储过程的完整工作如下：

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

• 首先，增加了注释(前面放置--)。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值（如果要增加税为真，否则为假）。在存储过程体中，用declare语句定义了两个局部变量。declare要求指定变量名和数据类型，它也支持可选的默认值。select语句已经改变，因此其结果存储到total而不是ototal。if语句检查taxable是否为真，如果为真，则用另一select语句增加营业税到局部变量taxable。最后，用另一select语句将total保存到ototal。
• COMMIT关键字 它不是必需的，但如果给出，将在show procedure status的结果中显示
• 这显然是一个更高级，功能更强的存储过程。为试验它，请用以下两条语句：

CALL ordertotal(20005, 0, @total);
select @total;

+--------+
| @total |
+--------+
| 149.87 |
+--------+

CALL ordertotal(20005, 1, @total);
select @total;

+--------+
| @total |
+--------+
| 158.86 |
+--------+

• BOOLEAN值指定为1表示真，指定为0表示假。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。
• IF语句 IF语句还支持ELSEIF和ELSE子句。

21.3.6 检查存储过程

• 为显示用来创建一个存储过程的create语句，使用show create procedure语句：

 show create procedure ordertotal;

• 为了获得包括何时、由谁创建等详细信息的存储过程列表，使用show procedure status。

• 限制过程状态结果 show procedure status列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

show procedure status like 'ordertotal';