《mysql必知必会》笔记（四）

二十三：使用存储过程：

         1：mysql 5添加了对存储过程的支持。很多时候，一个完整的操作需要多条语句才能完成。存储过程简单来说，就是为以后的使用而保存的一条或多条mysql语句的集合，可将其视为批文件、函数。

         2：使用存储过程有3个主要的好处：简单、安全和高性能。

 

         3：一个返回产品平均价格的存储过程，代码如下：

delimiter //

create procedure productpricing()

begin

 select avg(prod_price) as priceaverage

 from products;

end//

         这样就创建了名为productpricing的存储过程，begin和end用来定义存储过程体，过程体本身是一个简单的select语句。

         因为默认的mysql语句分隔符为“;”，但是定义存储过程时，需要在内部使用”;”，所以需要使用“delimiter //”，临时将语句分隔符改为”//”，如果需要恢复默认的语句分隔符”;”，再次使用delimiter即可：”delimiter ;”。注意，除了”\”符号之外，任何字符都可以用作语句分隔符。

 

         调用这个存储过程的语句为：call productpricing();结果如下：

 

         4：存储过程在创建之后，被保存在服务器上以供使用，直到被删除。删除存储过程的命令如下：drop procedure productpricing;

         如果指定的存储过程名不存在，则drop procedure将产生一个错误，可以使用下面的语句：drop procedure if exists productpricing;

 

         5：一般来说，存储过程并不显示结果，而是把结果返回给你指定的参数。下面是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（传入和传出）。

 

         调用该存储过程的语句是：call productpricing(@pricelow, @pricehigh, @priceaverage)//

         注意，所有的变量都必须以@开始。在调用时，并不显示任何数据，要想显示结果，则：

select @pricelow,@pricehigh, @priceaverage，结果如下：

 

         6：使用in和out参数的例子如下：

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

 

call ordertotal(20005,@total)//

select @total//  结果如下：

 

         7：为了显示创建一个存储过程的create语句，可以使用show create procedure name，比如：

show create procedure ordertotal//  结果如下：

 

二十四：使用游标

         1：mysql 5添加了对游标的支持。使用简单的select语句，没有办法得到第一行、下一行或前10行，也不存在每次一行的处理所有行的简单方法。

         2：有时，需要在检索出来的行中前进或后退一行或多行。这时就需要使用游标。游标是一个存储在mysql服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

         3：mysql游标只能用于存储过程。

 

         4：在能够使用游标之前，必须声明定义它。这个过程实际上没有检索数据，它只是定义要使用的select语句。一旦声明之后，必须打开游标以供使用。这个过程，会用前面定义的select语句把数据实际检索出来。

         对于填有数据的游标，根据需要取出各行。在结束游标使用时，必须关闭游标。

 

         5：游标用declare语句创建。declare命名游标，并定义相应的select语句，比如：

create procedure processorders()

begin

 declare ordernumbers cursor

 for

 select order_num from orders;

end//

         这个存储过程并没有做很多事，declare语句用来定义和命名游标，这里游标名为ordernumbers。存储过程处理完成后，游标就消失，因为它局限于存储过程。这里，即使调用该存储过程，也没有任何效果，因为该存储过程只是简单的定义了游标。

 

         6：下面以一个全面的例子来说明：

 CREATE PROCEDURE processorders()

        BEGIN

 

        -- declarelocal variable

        DECLARE done BOOLEAN  DEFAULT  0;

        DECLARE o INT;

        DECLARE t DECIMAL(8,2);

 

        -- declare the cursor

        DECLARE  ordernumbers  CURSOR

        FOR

        SELECT order_num  FROM orders ;