使用存储过程

1.存储过程

​ 需要MySQL5---->> MySQL5添加了对存储过程的支持。

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

• 为了处理订单，需要核对以保证库存中有相应的物品。
• 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
• 库存中没有的物品需要订购，这需要与供应商进行某种交互。
• 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

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

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

---

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

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

• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。

  如果所有开发和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。

  这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

  这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

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

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

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

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

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

---

3.使用存储过程

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

3.1 执行存储过程

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

输入

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

分析

其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

3.2创建存储过程

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

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

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

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

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

输入

CALL productpricing();

输出

priceaverage
16.133571

分析

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

3.3删除存储过程

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

​ 为删除刚创建的存储过程，可使用以下语句：

输入：DROP PROCEDURE productpricing

分析：这条语句删除刚创建的存储过程。请注意没有使用后面的（），只给出存储过程名。

3.4使用参数

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

​ 以下是productpricing的修改版本（如果不限删除此存储过程，则不能再次创建它）；

输入：

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个参数，不多也不少，所以，这个CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

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

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

​ 为了显示检索出的产品平均价格，可如下进行：

输入：SELECT @priceaverage;

输出：

@priceaverage
16.133571428

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

输入：SELECT @pricehigh,@pricelow,@priceaverage;

输出：

@pricehigh	@pricelow	@priceaverage
55.00	2.50	16.133571428

​ 下面是另外一个例子，这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

输入：

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

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

输入：CALL ordertotal(20005,@total);

分析：必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

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

输入：SELECT @total;

输出：

@total
149.87

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

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

输入：

CALL ordertotal(20009,@total);
SELECT @total;

3.5建立智能存储过程

​ 迄今为止使用的所有存储过程基本上都是封装MySQL简单的SELECT语句。虽然它们全都是有效的存储过程例子，但它们所能完成的工作你直接用这些被封装的语句就能完成（如果说它们还能带来更多的东西，那就是使事情更复杂）。只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。

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

• 获得合计（与以前一样）；
• 把营业税有条件地添加到合计；
• 返回合计（带或不带税）。

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

输入：

--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 toal
    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要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置成6%）。SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一个SELECT语句将total（它增加或许不增加营业税）保存到ototal。

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必须的，但如果给出，将在SHWO PROCEDURE STATUS的结果中显示。

​ 这显然是一个更高级，功能更强的存储过程。为试验它，请用一下两条语句：

输入：

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

输出：

@total
149.87

输入：

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

输出：

@total
158.862200000

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

IF语句 这个例子给出了MySQL的IF语句的基本用法。IF语句还支持ELSEFI和ELSE子句（前者还使用THEN子句，后者不使用）。

3.6检查存储过程

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

输入： SHOW CREATE PROCEDURE ordertotal;

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