使用存储过程——创建、执行、删除、检查

1. 存储过程

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

---

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

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

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。
不过，在将SQL代码转换为存储过程前，也有一些缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

---

3. 使用存储过程

3.1 执行存储过程

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

存储过程可以显示结果，也可以不显示结果。

---

3.2 创建存储过程

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

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

最后，为恢复为原来的语句分隔符，可使用delimiter;。

此存储过程名为productpricing，用create procedure productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的select语句。

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

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

---

3.3 删除存储过程

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

注意删除存储过程没有使用后面的()，只给出存储过程名。

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

---

3.4 使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。变量指的是内存中一个特定的位置，用来临时存储数据。

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

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

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

这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。所有MySQL变量都必须以@开始。

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

另一个例子：使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计。

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

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

---

3.5 建立智能存储过程

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

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

---

3.6 检查存储过程

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

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用show procedure status。show procedure status列出所有存储过程。为限制其输出，可使用like指定一个过滤模式：

---

-- 执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。
CALL productpricing (@pricelow, 
                     @pricehigh,
                     @priceaverage);
                     

-- 创建一个返回产品平均价格的存储过程。DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
  SELECT AVG(prod_price) AS priceaverage
  FROM products;
 END //
 
 -- 为恢复为原来的语句分隔符;
 DELIMITER ;
 
 -- 使用存储过程
 CALL productpricing();
 
 -- 删除存储过程
 DROP PROCEDURE productpricing;
 
 -- 存储过程使用参数
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 ;
 
 -- 为调用此修改过的存储过程，必须指定3个变量名
 CALL productpricing(@pricelow,
                     @pricehigh,
                     @priceaverage);
                     
-- 检索出产品平均价格
SELECT @priceaverage;

-- 检索3个变量的值
SELECT @pricelow, @pricehigh, @priceaverage;

-- 使用in和out参数。。ordertotal接受订单号并返回该订单的合计
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 ;

-- 调用存储过程,必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名
CALL ordertotal(20005,@total);

-- 显示合计
SELECT @total;

-- 显示另一个订单的合计，需要再次调用存储过程，然后重新显示变量：
CALL  (20009,@total);

-- 显示合计
SELECT @total;

-- 2020-9-27
USE lu_test;
DROP PROCEDURE ordertotal;

-- 获得订单合计，但需要对合计增加营业税，不过只针对某些顾客
DELIMITER //
-- Name:ordertotal
-- Parameters:onumber = order number
--            taxable = 0 if not taxable, 1 if taxable 
--            ototal = order total variable

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//
DELIMITER ;

-- 检索结果
CALL ordertotal(20005,0,@total);
SELECT @total;

-- 检索结果
CALL ordertotal(20005,1,@total);
SELECT @total;

-- 显示创建存储过程
SHOW CREATE PROCEDURE ordertotal;
 
 -- 列出详细的存储过程
 SHOW PROCEDURE STATUS LIKE 'ordertotal';