使用存储过程

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

执行存储过程

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

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

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

创建存储过程

例子

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

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

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

那么如何使用这个存储过程呢？

CALL productpricing();

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

删除存储过程

DROP PROCEDURE productpricing;

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

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

使用参数

productpricing 只是一个简单的存储过程，它简单地显示 SELECT语句的结果。
一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
变量：内存中一个特定的位置，用来临时存储数据。
带有参数的存储过程例子：

CREATE PRODUCEDURE productpricing(
  OUT pl DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN 
  SELECT Min(prod_price)
  INOT pl
  FROM products;
  SELECT Max(prod_price)
  INTO ph
  FROM products;
  SELECT Avg(prod_price)
  INTO pa
  FROM products;
END;

关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）
MySQL 支持 IN（传递给存储过程）、OUT（从存储过程传出）、INOUT（对存储过程传入和传出）类型的参数。

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

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

所有MySQL变量都必须以@开始
在调用时候，这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量。

SELECT @pricelow, @pricehigh, @priceaverage;

接着看同时使用 IN 和 OUT 参数的例子，ordertotal 接受订单号并返回该订单的合计：

CREATE PROCDURE ordertotal (
  IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN 
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO otoal;
 END;

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

CALL ordertotal(20005, @total);

建立智能存储过程

迄今为止使用的所有存储过程基本上是封装 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 total
  SEECT 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;

检查存储过程

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

SHOW CREATE PROCEDURE ordertotal;

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

SHOW PROCEDURE STATUS LIKE 'ordertotal';

MySQL 使用存储过程