MySQL数据库之存储过程揭秘-mysql 存储过程

[[268532]]

什么是存储过程

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

1、为了处理订单，需要核对以保证库存中有相应的物品。

2、如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。

3、库存中没有的物品需要订购，这需要与供应商进行某种交互。

4、关于哪些物品入库(并且可以立即发货)和哪些物品退订，需要通知相应的客户。

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

那么，怎样编写此代码?一种是我们可以单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时(以及每个需要它的应用中)都必须做这些工作。而另一种可以创建存储过程。

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

为什么要使用存储过程

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

1、通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。

2、由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

3、简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。

4、提高性能。因为使用存储过程比使用单独的 SQL语句要快。

5、存在一些只能用在单个请求中的 MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码(在下一章的例子中可以看到。)

换句话说，使用存储过程有 3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将 SQL代码转换为存储过程前，也必须知道它的一些缺陷。

1、一般来说，存储过程的编写比基本 SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

2、你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

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

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

如何去使用存储过程

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

执行存储过程

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

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

其中执行productpricing 的存储过程，他计算并返回产品的最低价格，最高价格，均价。存储过程可以显示结果，也可以不显示结果，接下来会提到。

创建存储过程

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

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

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

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

这里有一个需要注意的就是：mysql命令行客户机的分隔符

如果你使用的是 mysql命令行实用程序，应该仔细阅读此说明。

默认的 MySQL语句分隔符为;(正如你已经在迄今为止所使用的MySQL语句中所看到的那样)。 mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的 ;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的 SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

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

其中， DELIMITER //告诉命令行实用程序使用 //作为新的语句结束分隔符，可以看到标志存储过程结束的 END定义为END//而不是END; 。这样，存储过程体内的 ;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用 DELIMITER ;。除符号外，任何字符都可以用作语句分隔符。如果你使用的是 mysql命令行实用程序，在阅读本文时请记住这里的内容。

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

CALL productpricing();

结果是：

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

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