第23章 使用存储过程

  1.     存储过程
  2.     为什么要使用存储过程
  3.     使用存储过程
  4.         执行存储过程
  5.         创建存储过程
  6.         删除存储过程
  7.         使用参数
  8.         建立智能存储过程
  9.         检查存储过程

  

一、存储过程

  迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如，考虑以下的情形。

  为了处理订单，需要核对以保证库存中有相应的物品。  如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。  库存中没有的物品需要订购，这需要与供应商进行某种交互。  关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

  这显然不是一个完整的例子，它甚至超出了本书中所用样例表的范围，但足以帮助表达我们的意思了。执行这个处理需要针对许多表的多条MySQL语句。此外，需要执行的具体语句及其次序也不是固定的，它们可能会（和将）根据哪些物品在库存中哪些不在而变化。

  那么，怎样编写此代码？可以单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。

  可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

  

二、为什么要使用存储过程

  既然我们知道了什么是存储过程，那么为什么要使用它们呢？有许多理由，下面列出一些主要的理由。

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

  尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。

不能编写存储过程？你依然可以使用MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。这是好事情。即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

  

三、使用存储过程

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

1、执行存储过程

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

  1.     call productpricing(@pricelow,
  2.                         @pricehigh,
  3.                         @priceaverage);

  其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

  存储过程可以显示结果，也可以不显示结果，如稍后所述。

2、创建存储过程

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

  1.     create procedure productpricing()
  2.     begin
  3.         select avg(prod_price) as priceaverage
  4.         from products;
  5.     end;

  我们稍后介绍第一条和最后一条语句。此存储过程名为productpricing，用create procedure productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。begin和end语句用来限定存储过程体，过程体本身仅是一个简单的select语句（使用第12章介绍的avg()函数）。

  在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。

  那么，如何使用这个存储过程？如下所示：

  1.     call productpricing();

  call productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）。

3、删除存储过程

  存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令（类似于第21章所介绍的语句）从服务器中删除存储过程。

  为删除刚创建的存储过程，可使用以下语句：

  1.     drop procedure productpricing;

  这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。

仅当存在时删除如果指定的过程不存在，则drop procedure将产生一个错误。当过程存在想删除它时（如果过程不存在也不产生错误）可使用drop procedure if exists。

4、使用参数

  productpricing只是一个简单的存储过程，它简单地显示select语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

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

  以下是productpricing的修改版本（如果不先删除此存储过程，则不能再次创建它）：

  1.     create procedure productpricing(
  2.         out p1 decimal(8,2),
  3.         out ph decimal(8,2),
  4.         out pa decimal(8,2)
  5.     )
  6.     begin
  7.         select min(prod_price)
  8.         into p1
  9.         from products;
  10.         select max(prod_price)
  11.         into ph
  12.         from products;
  13.         select avg(prod_price)
  14.         into pa
  15.         from products;
  16.     end;

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

参数的数据类型存储过程的参数允许的数据类型与表中使用的数据类型相同。附录D列出了这些类型。注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是前面的例子为什么要使用3个参数（和3条select语句）的原因。

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

  1.     call productpricing(@pricelow,
  2.                         @pricehigh,
  3.                         @priceaverage);

  由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。所以，这条call语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

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

  在调用时，这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量。

  为了显示检索出的产品平均价格，可如下进行：

  1.     select @priceaverage;

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

  1.     select @pricehigh, @pricelow, @priceaverage;

  下面的另外一个列子，这次使用in和out参数。ordertotal接受订单号并返回该订单的合计：

  1.     create procedure ordertotal(
  2.         in onumber int,
  3.         out ototal decimal(8, 2)
  4.     )
  5.     begin
  6.         select sum(item_price*quantity)
  7.         from orderitems
  8.         where order_num = onumber
  9.         into ototal;
  10.     end;

  onumber定义为in，因为订单号被传入存储过程。ototal定义为out，因为要从存储过程返回合计。select语句使用这两个参数，where子句使用onumber选择正确的行，into使用ototal存储计算出来的合计。

  为调用这个新存储过程，可使用以下语句：

  1.     call ordertotal(20005, @total);

  必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

  为了显示此合计，可如下进行：

  1.     select @total;

  @total已由ordertotal的call语句添加，select显示它包含的值。

  为了得到另一个订单的合计显示，需要再次调用存储过程，然后重新显示变量：

  1.     call ordertotal(20009, @total);
  2.     select @total;

5、建立智能存储过程

  迄今为止使用的所有存储过程基本上都是封装MySQL简单的select语句。虽然它们全都是有效的存储过程例子，但它们所能完成的工作你直接用这些被封装的语句就能完成（如果说它们还能带来更多的东西，那就是使事情更复杂）。只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。

  考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：

  获得合计（与以前一样）；  把营业税有条件地添加到合计；  返回合计（带或不带税）。

  存储过程的完整工作如下：

  1.     -- Name : ordertotal
  2.     -- Parameters : onumber = order number
  3.     --              taxable = 0 if not taxable, 1 if taxable
  4.     --              ototal = order total variable
  6.     create procedure ordertotal(
  7.         in onumber int,
  8.         in taxable boolean,
  9.         out ototal decimal(8,2)
  10.     ) comment 'Obtain order total, optionally adding tax'
  11.     begin
  13.         -- Declare variable for total
  14.         declare total decimal(8,2);
  15.         -- Declare tax percentage
  16.         declare taxrate int default 6;
  18.         -- Get the order total
  19.         select sum(item_price*quantity)
  20.         from orderitems
  21.         where order_num = onumber
  22.         into total;
  24.         -- Is this taxable?
  25.         if taxable then
  26.             -- Yes, so add taxrate to the total
  27.             select total+(total/100*taxrate) into total;
  28.         end if;
  30.         -- And finally, save to out variable
  31.         select total into ototal;
  32.     end;

  此存储过程有很大的变动。首先，增加了注释（前面放置—）。  在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用declare语句定义了两个局部变量。declare要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。select语句已经改变，因此其结果存储到total（局部变量）而不是ototal。if语句检查taxable是否为真，如果为真，则用另一select语句增加营业税到局部变量total。最后，用另一select语句将total（它增加或许不增加营业税）保存ototal。

comment关键字本例子中的存储过程在create procedure语句中包含了一个comment值。它不是必需的，但如果给出，将在show procedure status的结果中显示。

  这显然是一个更高级，功能更强的存储过程。为试验它，请用以下两条语句：

  1.     call ordertotal(20005, 0, @total);
  2.     select @total;
  1.     call ordertotal(20005, 1, @total);
  2.     select @total;

  boolean值指定为1表示真，指定为0表示假（实际上，非零值都考虑为真，只有0被视为假）。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。

IF语句这个例子给出了MySQL的if语句的基本用法。if语句还支持elseif和else子句（前者还使用then子句，后者不使用）。在以后章节中我们将会看到if的其他用法（以及其他流控制语句）。

6、检查存储过程

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

  1.     show create procedure ordertotal;

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

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

  1.     show procedure status like 'ordertotal';


  

?