mySQL 23章存储过程

###########################
#第23章 使用存储过程 
###########################

##执行存储过程 
call productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

##创建存储过程 
delimiter //
create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end//
delimiter ;

#必须改变delimiter要不然下面的会出错因为有两个;

#create procedure productpricing()
#begin
#    select avg(prod_price) as priceaverage
#    from products;
#end;
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 ;

call productpricing(@pricelow,
					@pricehigh,
                    @priceaverage);#调用存储过程 不显示数据 
select @priceaverage,@pricehigh,@pricelow;     #显示数据 



##使用参数 例子二 
delimiter //
create procedure ordertotal(
    in number int,
    out ototal decimal(8,2)
						   )
begin
    select sum(item_price*quantity)
    from orderitems
    where order_num=onumber
    into ototal;
end//
delimiter ;

call ordertotal(20005,@total);
select @total;

##in是需要传入的参数 out是需要传出的参数 过程内部into要传给out参数 


##建立智能存储过程 

-- Name:ordertotal
-- Parameters: onumber=order number
--             taxable=0 if not taxable,1 if taxable
--             ototal=order total variable
delimiter //
create procedure ordertotal(
  in onumber int,
  in taxable boolean,
  out ototal decimal(8,2)
  )comment 'obtain order total,optimally adding tax'
begin
  
   -- Declare variable for total 创建局部变量 total,taxrate
   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 taxtable then
      select total+(total/100*taxrate) into total;
   end if;
   -- finally,save to out variable
   select total into ototal;
end //
delimiter ;
call ordertotal(20005,0,@total);
select @total;

-- delcare用来定义局部变量 select将结果存储到局部变量而不是全局变量 最后再把total保存ototal
-- comment 关键字不是必须的 

##检查存储过程
show create procedure ordertotal;