调用存储过程的语句_MySQL数据库之存储过程揭秘

想学Java的关注小编头条号，私信【Hello World】即可免费领取全套Java从入门到放弃的学习视频文档资料，想要的请私信我。

什么是存储过程

到目前为止使用的大多数 SQL 语句都是针对一个或多个表的单个语句。并非所有操作都如此简单，通常一个完整的操作需要多个语句才能完成。例如，考虑以下场景。

1. 为了处理订单，您需要检查以确保相应的物品有库存。

2. 如果有库存，则需要保留这些物品，以免出售给其他人，并且需要减少物品的可用数量以反映正确的库存水平。

3.缺货的商品需要订购，这需要与供应商进行一些互动。

4. 需要通知相应的客户哪些商品有现货（可以立即发货）以及哪些商品被取消。

这显然不是一个完整的示例，甚至超出了本书中使用的示例表的范围，但足以帮助传达这个想法。执行此过程需要针对许多表执行多个 MySQL 语句。此外，需要执行的特定语句及其顺序并不固定，它们可以（并且将）根据哪些商品在库存中以及哪些不在库存中而改变。

那么，我们如何编写此代码？一种方法是，我们可以单独编写每个语句，并根据结果有条件地执行其他语句。每次需要此处理时（以及在每个需要它的应用程序中），都必须完成这项工作。另一种方法是创建存储过程。

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

为什么要使用存储过程？

现在我们知道了什么是存储过程，那么为什么要使用它们呢？原因有很多，以下是一些主要原因。

1. 通过将处理封装成易于使用的单元来简化复杂的操作（如前面的示例所述）。

2. 无需重复建立一系列处理步骤，从而确保数据完整性。如果所有开发人员和应用程序都使用相同的（经过尝试和测试的）存储过程，则使用的代码是相同的。错误预防是其延伸。需要执行的步骤越多，出错的可能性就越大。错误预防可确保数据一致性。

3. 简化变更管理。如果表名、列名或业务逻辑（或其他内容）发生变化，则只需更改存储过程的代码。使用它的人甚至不需要知道这些变化。安全性是这一点的延伸。通过存储过程限制对底层数据的访问可降低数据损坏（无意或其他）的可能性。

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

5. 一些 MySQL 元素和功能只能在单个请求中使用。存储过程可以使用它们来编写更强大、更灵活的代码（如下一章中的示例所示）。

也就是说，使用存储过程主要有三大好处，即简单、安全和高性能。显然，它们都很重要。但是在将 SQL 代码转换为存储过程之前，您还必须意识到它的缺点。

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

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

尽管存在这些缺点，存储过程仍然非常有用，应该尽可能地使用。

无法编写存储过程？您仍然可以使用它们：MySQL 将编写存储过程的安全性和访问与执行存储过程的安全性和访问分开。这是一件好事。即使您不能（或不想）编写自己的存储过程，您也可以

在适当的时候，其他存储过程仍可执行。

如何使用存储过程

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

执行存储过程

MySQL 将存储过程的执行称为调用，因此在 MySQL 中执行存储过程的语句是 CALL。CALL 接受存储过程的名称以及需要传递给它的任何参数。请参见以下示例：

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

这里执行的存储过程计算并返回产品的最低价、最高价、平均价，存储过程可以显示结果也可以不显示，后面会讲到。

创建存储过程

如上所述，编写存储过程并非易事。为了让你了解这个过程，让我们看一个例子——一个返回产品平均价格的存储过程。以下是代码：

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

我们稍后会查看第一个和最后一个语句。此存储过程名为，并使用 () 语句定义。如果存储过程接受参数，则它们列在 () 内。此存储过程没有参数，但仍然需要以下 ()。BEGIN 和 END 语句用于分隔存储过程的主体，存储过程本身只是一个简单的语句（使用第 12 章中介绍的 Avg() 函数）。

当 MySQL 处理此代码时，它会创建一个新的存储过程。由于此代码不调用存储过程，因此不会返回任何数据；它只是为了以后使用而创建的。

这里需要注意的是：mysql 命令行客户端的分隔符

如果您正在使用 mysql 命令行实用程序，则应该仔细阅读此说明。

默认的 MySQL 语句分隔符是 ;（正如您在迄今为止使用的 MySQL 语句中看到的那样）。mysql 命令行实用程序也使用 ; 作为语句分隔符。如果命令行实用程序在存储过程本身内解释 ; 字符，它们将不会成为存储过程的一部分，这将导致存储过程内的 SQL 出现语法错误。解决方案是暂时更改命令行实用程序的语句分隔符，如下所示：

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

这里，// 告诉命令行实用程序使用 // 作为新的语句结束分隔符。您可以看到，标记存储过程结束的 END 被定义为 END// 而不是 END;。这样，存储过程主体中的 ; 保持完整并正确传递给数据库引擎。最后，要恢复原始语句分隔符，请使用 ;。除 \ 符号外，任何字符都可以用作语句分隔符。如果您正在使用 mysql 命令行实用程序，请在阅读本文时记住这一点。

那么，如何使用此存储过程？方法如下：

CALL productpricing();

结果发现：

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

CALL(); 执行新创建的存储过程并显示返回结果。由于存储过程实际上是一个函数，因此存储过程名称后面必须有 () 符号（即使没有传递任何参数）。