MYSQL必知必会读书笔记 第二十三章 使用存储过程

存储过程也是MySQL5.0 添加的。

大多数SQL语句都是针对一个或者多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

为什么使用存储过程？

1.通过把处理封装在容易使用的单元中，简化复杂的操作

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

3.简化对变动的管理。

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

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

MySQL将编写存储过程的安全和执行存储过程的安全区分开来。

执行存储过程

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

执行一个计算并返回产品的最低、最高和平均价格。

创建存储过程

CREATE  PEOCEDURE  productpricing()

BEGIN

         SELECT  AVG(prod_price)  AS  priceaverage

         FROM  products;

END;

mysql命令行客户机的分隔符   ：如果你使用的是mysql命令行实用程序；默认的MySQL语句使用分号；作为分隔符。所以在使用存储过程的时候需要临时更改分隔符。解决办法：

DELIMITER  //

CREATE PROCEDURE  productpricing()

BEGIN

          SELECT  AVG(prod_price)  AS  priceaverage  FROM  products;

END  //

DELIMITER ;

除\号外任何符号都能作为分隔符；

删除存储过程

  DROP  PROCEDURE  存储过程名称；//注意不需要括号。

仅当存在时删除。 

DROP PROCEDURE  IF  EXISTS.

存储过程中使用参数：

CREATE PRODUCEDURE  productpricing(

OUT  p1  DECIMAL(8,2),

OUT p2   DECIMAL(8,2),

OUT p3  DECIMAL(8,2)

)

BEGIN

       SELECT  MIN(prod_price)

       INTO  p1   FROM  products;

       SELECT  MAX(prod_price)

       INTO   p2   FROM  products;

       SELECT  AVG(prod_price)

       INTO   p3   FROM  products;

END;

参数中的数据类型  ：存储过程的参数允许的数据类型与表中使用的数据类型相同。注意的是，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。为调用此存储过程，必须指定三个变量名。

CALL productpricing(@pricelow,@pricehigh,@priceaverage);//所有的Mysql变量都必须以@开始。

在调用时，这条语句并不显示任何数据。它返回以后可以显示的变量。

可以使用 SELECT @priceaverage;显示平均价格。

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

CREATE  PROCEDURE  ordertotal (

IN   onumber   INT,

OUT  ototal  DECIMAL(8,2)

)

BEGIN

  SELECT  SUM(iterm_price* quantity)  FROM  orderitems WHERE  order_num=onumber   INTO  ototal;

END ;

CALL  ordertotal(2005,@total);

SELECT  @total;

建立智能存储过程：

迄今为止所使用的都是SELECT语句。但是SQL语句也需要使用复杂的逻辑语句，这样它的威力才真正的显现出来。

考虑一个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些客户。

CREATE  PROCEDURE  ordertotal(

IN  onumber  INT,

IN teaxable  BOOLEAN,

OUT  ototal DECIMAL(8,2)

) COMMENT  'Obtain  order  toatal ,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  ;

COMMENT关键字   本例子中的存储过程有注释可以通过SHOW  PROCEDURE  STATUS 的结果中显示。同时可以使用lLIKE关键字指定一个过滤模式。

例如：SHOW PROCEDURE STATUS LIKE 'ordertotal';

CALL  ordertotal(2005,0,@total);

SELECT @total;