---

---

1.概述

目前为止，我们使用的大多数 SQL 语句都是针对一个或多个表的单条语句。但在实际开发中，你所面对的复杂的业务，可能需要你做更多的尝试，例如下列的情况：

• 确认订单时，必须保证库存中有相应的物品；
• 如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量；
• 如果库存中没有的物品需要订购，这需要与供应商进行某种交互；
• 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客；

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

那么，该怎么实现这个功能呢？我们可以单独编写每条 SQL 语句，并根据结果有条件地执行其他SQL语句。在每次需要执行这个处理时（以及每个需要它的应用中）去执行后续的SQL语句。

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

当然，存储过程很复杂的一项技术，本小节不可能将其涉及的内容完全介绍完，所以只做基础的介绍，在实际工作中，你需要沉淀很久，称为资深的开发人员，才会需要使用到存储过程的所有内容，这里我们只需要了解其基础用法，遇到的时候可以看懂即可。

2.为什么要使用存储过程

前面我们介绍了什么是存储过程，虽然存储过程是一项很复杂的技术，但是使用它的优点，足以支撑我们去学习这项技术：

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）；
• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的；
• 上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。这也是数据库管理中最重要的部分；
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化；
• 上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会；
• 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能；
• 存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码；

也就是说，使用存储过程有三个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，其同时也存在一些缺点，我们在使用存储过程时，需要尽量避免这些缺点：

• 不同 DBMS 中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，至少客户端应用代码不需要变动；
• 一般来说，编写存储过程比编写基本 SQL 语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）；

3.执行存储过程

大多数 DBMS 将编写存储过程所需的安全和访问权限与执行存储过程所需的安全和访问权限区分开来。所以即使我们不会编写自己的存储过程，也仍然可以在适当的时候使用其他的存储过程。

存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。

执行存储过程的 SQL 语句很简单，即 使用CALL 关键字。CALL 关键字接受存储过程名和需要传递给它的任何参数。请看下面的例子（你无法运行这个例子，因为 productpricing 这个存储过程还不存在）：

输入：

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

分析▼
这里执行一个名为 productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

4.创建存储过程

正如所述，存储过程的编写很重要。为了更加明确的了解存储过程，我们来实现一个简单的存储过程例子，创建一个返回产品平均价格的存储过程。

输入：

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

上面的例子中，我们创建了一个名为productpricing的存储过程，与创建表不同，创建存储过程时将table关键字替换为了procedure。这个存储过程中并未传入任何值，所以存储过程名后的小括号中没有任何内容。

存储过程的代码括在 BEGIN 和 END 语句中，这里执行一条简单的 SELECT 语句，它查询并返回产品的平均价格。

5. MySql客户机中的命令行分隔符

分号（；）是MySql默认的分隔符，我们大多数情况都直接使用这个分隔符，来表示一条SQL语句的正确结束。但是MySql命令行也使用分号（；）作为默认的语句分隔符，如果命令行要解析存储过程自身内的分号（；）字符，会导致它们最终奴能成为存储过程的城根，这会使存储过程中的SQL出现语法错误。

我们可以使用DELIMITER关键字临时修改实用程序的语句分隔符：

输入：

DELIMITER //

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

DELIMITER ;

上面的存储过程中，在第一行我们使用DELIMITER关键字临时将实用程序的语句分隔符设置为了 “//”，在后续存储过程结束的END字符后我们使用了临时设置的分隔符，当然你也可以将分隔符设置为除 “\” 外的任意字符，如：“==”，但是一定要注意避免歧义。

最后，我们再次使用DELIMITER关键字将临时设置的分隔符改回 “;” 。

运行这段命令，既可以创建这个存储过程了，此时并不会有任何数据输出，因为你只是创建了一段可以执行的命令，那么我们如何执行这段命令呢？

正如上面介绍的一样，使用CALL关键字后跟存储过程名即可：

输入：

CALL productpricing();

输出：

此时已正确获取运行存储过程后的结果，我们创建的存储过程因为不需要传入参数，所以我们执行存储过程的语句后的小括号中不用传入任何参数。

6.删除存储过程

存储过程在创建之后被保存在服务器上以供使用，直至被删除。

删除存储过程的命令十分简单，使用DROP命令，后跟PROCEDURE 关键字表明删除的是存储过程，最后跟存储过程名即可，如：

输入：

DROP PROCEDURE productpricing;

注意存储过程名后不跟小括号，只给出存储过程名即可。执行此命令时，如果要删除的存储过程不存在，那么则会产生一个错误，如果想避免在删除时因存储过程不存在导致的错误，我们可以使用下列命令：

输入：

DROP PROCEDURE IF EXISTS productpricing;

7.使用参数

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

7.1 使用OUT关键字传递参数

下面我们删除之前的存储过程，然后对其进行改进：

输入：

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 ;

此存储过程接受三个参数，pl存储产品的最低价格，ph存储产品的最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值，返回给调用者。

MySql支持IN（传递给存储过程），OUT（从存储过程传出值）和INOUT（对存储过程传入和传出值）类型的参数。它们是一系列的SELECT语句，用来检索值，然后保存到相应的变量。

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

为了调用修改后的存储过程，我们必须在调用时指定3个变量名用来接收返回的值：

输入：

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

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

在执行后，这条SQL语句只会返回执行成功的结果，并不显示任何数据，它返回后可以显示的变量。为了显示查询到的结果，我们可以进行下列的操作：

分别输入：

SELECT @pricelow;

SELECT @pricehigh;

SELECT @priceaverage;

分别执行这三条SQL用来查询变量保存的结果，如果想同时获取这三个结果，可以使用下列的语句：

输入：

SELECT @pricelow,@pricehigh,@priceaverage;

输出：

7.2 使用IN关键字传递参数

下面我们再来创建另一个存储过程，这里我们将同时使用OUT和IN关键字：

输入：

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 ;

onumber被定义为IN，因为我们这里的查询，需要传入一个订单号。ototal定义为OUT，因为这个参数是要从存储过程中返回的合计的值。SELECT语句使用这俩个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计值。

为了调用这个存储过程，我们可以下列的语句：

CALL ordertotal(20005,@total);

这条语句共传递了俩个参数，第一个为订单号，第二个参数为包含计算出来的合计的变量名。

为了显示查询结果，我们可以使用下列的语句：

输入：

SELECT @total;

输出：

8.建立智能存储过程

之前我们介绍的存储过程都是简单的查询语句，虽然它们确实是存储过程的语句，但是其结果都可以直接通过查询语句实现，只有在存储过程包含业务规则和智能处理时，它们的存在才真正有用武之地。

假设我们需要获得和之前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客。那么，我们需要做这几件事情：

1. 获得合计值；
2. 把营业税有条件的添加到合计值；
3. 返回添加或者没有添加营业税的合计值；

那此时，我们的存储过程语句将变为这样：

输入：

-- name：ordertotal
-- parameters:onumber = order number
-- 	      taxable = 0 if not taxable,1 if taxable
--            ototal = order total variable

DELIMITER //

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
) COMMENT '获取订单总金额，有条件地增加订单税'
BEGIN
	-- 声明总计变量
	DECLARE total DECIMAL(8,2);
	-- 声明税率的百分比
	DECLARE taxrate INT DEFAULT 6;
	
	-- 获取订单总金额
	SELECT SUM(item_price * quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	-- 判断是否要增加营业税
	IF taxable THEN
		-- 要增加营业税，那么重新计算增加后的订单总金额
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	-- 最后，保存要输出的订单总金额
	SELECT total INTO ototal;
END//

DELIMITER ;

这才是一个比较完整的存储过程，首先，我们在语句中假如了注释，注释规则与SQL的规则一致，在复杂的SQL等任何语句中，增加注释是很有必要的。

我们添加了参数taxable，其数据类型为布尔值，如果要增加营业税，那么值为真，不增加，则值为假。

在存储过程中，用DECLARE关键字定义了俩个局部变量，DECLARE关键字定义变量时，要求指定变量名和数据类型，其也可以支持默认值，例如taxable的默认值被设置为6；

因为SELECT语句已经改变，所以其结果存储到total，而不是ototal，执行完后续的语句后，确认其输出的值后再将其存储到ototal中。

IF语句检查taxable是否为真，如果为真，则THEN关键字执行MySql执行后续的语句，使用SELECT增加营业税到局部变量TOTAL中，如果为假，那么则表明不增加营业税，那么此时的查询结果不用做任何变动。

最后，使用另一个SELECT语句将增加或者没有增加营业税的total的值保存到ototal中。

接下来，我们验证其是否可以正常使用：

输入：

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

输出：

输入：

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

输出：

执行存储过程时传入的第二个值即为taxable的值，其1表示真，0表示假，实际上，非零的值都考虑为真，只有0被视为假。我们看到增加营业税或者不增加营业税获取订单总金额完全不一样。

9.检查存储过程

最后，为了显示用来创建存储过程的create语句，使用SHOW CREATE PROCEDURE语句：

输入：

SHOW CREATE PROCEDURE ordertotal;

输出：

为了获取何时，由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS语句。

输入：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

输出：