MySQL 学习笔记 —— 19、使用存储过程

存储过程

之前讲述的大多数 SQL 语句都是针对一个或多个表的单条语句；但是并非所有的操作都这么简单，经常会有一些复杂的操作需要多条语句才能完成

例如以下情形：

1. 为了处理订单，必须核对以保证库存中有对应物品

2. 如果物品有库存，需要预定，不再出售给别人，并且减少物品数据以反映正确的库存量

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

4. 关于哪些物品入库（并且可以立即发货）和 哪些物品退订，需要通知相应的顾客

显然，为了解决上述四个问题，需要针对许多表的多条 SQL 语句；
并且需要执行的具体 SQL 语句及其次序也不是固定的，它们可能会根据是否在库存中而发生变化

编写代码的选择：

可以单独编写每条 SQL 语句，并根据结果有条件地执行其他语句，在每次需要这个处理时（以及每个需要它地应用中），都必须做这些工作

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

---

为什么使用存储过程

存储过程的优点：

1. 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如上述例子）

2. 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性；如果所有的开发人员和应用程序都使用同一存储过程，则使用的代码都是相同的——防止错误，需要执行的步骤越多，出错的可能性就越大

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

4. 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命名所需的工作量少，提高了性能

5. 存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

简单、安全、高性能

存储过程的缺点：

1. 不同的 DBMS 中的存储过程语法不同，事实上，编写真正的可移植存储过程几乎是不可能的；不过，存储过程的自我调节（名字以及数据如何传递）可以相对保持可移植；因此，如果需要移植到别的 DBMS ，至少客户端应用代码不需要变动

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

3. 创建存储过程需要比较高的安全访问权限

---

执行存储过程

存储过程的执行远比编写要频繁得多

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 语句

没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它

mysql 命令行客户机的分隔符：
默认的 MySQL 语句分隔符为 ;；mysql 命令行实用程序也使用 ; 作为语句分隔符
如果命令行实用程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，反而出现语法错误

解决办法：
临时更改命令行实用程序的语句分隔符
DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为 END // 而不是 END;
存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎
为恢复为原来的语句分隔符，可以使用 DELIMITER ;

除 \ 符号外，任何字符都可以用作语句分隔符

mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT Avg(prod_price) AS priceaverage
    -> FROM products;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

使用存储过程

CALL productpricing();

存储过程实际上是一种函数，所以存储过程名后需要有 () 符号（即使不传递参数也需要）

---

删除存储过程

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

删除命令从服务器中删除存储过程

DROP PROCEDURE productpricing;

仅当存在时删除：
如果指定的过程不存在，则 DROP PROCEDURE 将产生一个错误，当过程存在想删除时（如果过程不存在也不产生错误）可以使用 DROP PROCEDURE IF EXISTS

---

使用参数

productpricing 只是一个简单的存储过程，它简单地显示 SELECT 语句的结果

一般，存储过程并不显示结果，而是把结果返回给指定的变量

变量（variable）:
内存中一个特定的位置，用来临时存储数据

示例：
(如果还是在 mysql 命令行中，需要和刚才同样操作，临时改变语句分隔符)

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 个变量，它们是存储过程将保存结果的 3 个变量的名字

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

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

SELECT @priceaverage;//

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

SELECT @pricehigh, @pricelow, @priceaverage;//

下面时使用 IN 和 OUT 参数的例子

ordertotal 接受订单号并返回订单的合计

mysql> 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 存储计算出来的合计

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

mysql> CALL ordertotal(20005, @total);//

必须给 ordertotal 传递两个参数

第一个参数为订单号，第二个参数为包含计算出来的合计的变量名

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

mysql> SELECT @total;//

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

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

mysql> CALL ordertotal(20009, @total);//
mysql> SELECT @total;//

---

建立智能存储过程

迄今为止使用的所有存储过程基本上都是封装 MySQL 简单的 SELECT 语句

虽然它们全都是有效的存储过程例子，但它们所能完成的工作，直接用这些被封装的语句就能完成

只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来

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

• 获得合计（和之前一样）
• 把营业税有条件地添加到合计
• 返回合计（带或者不带税）

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

mysql> -- Name: ordertotal
mysql> -- Parameters: onumber = order number
mysql> --             taxable = 0 if not taxable, 1 if taxable
mysql> --             ototal  = order total variable
mysql> 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 要求指定变量名和数据类型，它也支持可选的默认值，（例如在此例子中，taxrate 的默认值设置为6%）

IF 语句检查 taxrate 是否为真，如果为真，则使用一个 SELECT 语句增加营业税到局部变量 total

最后，用另一个 SELECT 语句将 total ( 不增加营业税 ) 保存到 total

COMMENT 关键字 :
在上述存储过程 CREATE PROCEDURE 语句中包含了一个 COMMENT 值，它不是必需的，但是如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示

测试存储过程

mysql> CALL ordertotal(20005, 0, @ototal);//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @ototal;//

mysql> CALL ordertotal(20005, 1, @ototal);//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @ototal;//

BOOLEAN 值指定为 1 表示真，指定为 0 表示假（实际上，非零值都会，考虑为真，只有 0 会被视为假）

通过给中间的参数指定 0 或者 1，可以有条件地将营业税加到订单合计上

IF 语句
IF 语句还支持 ELSEIF 和 ELSE 子句（前者还使用 THEN 子句，后者不使用）

---

检查存储过程

为显示用来创建一个存储过程的 CREATE 语句，使用 SHOW CREATE PROCEDURE 语句

SHOW CREATE PROCEDURE ordertotal;//

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

SHOW PROCEDURE STATUS 会列出所有存储过程，为了限制其输出，可以使用 LIKE 指定一个过滤模式
SHOW PROCEDURE STATUS LIKE 'ordertotal';//