-- show tables;
-- code demonstarte the learning of procedure
-- example ch23.3.2 try to simple procedure
delimiter //
create procedure productpricing1()
begin
select avg(prod_price)as priceaverage
from products;
end //
delimiter ;
call productpricing1();
drop procedure productpricing1;
-- example ch23.3.4 procedure with parameter
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(@plowprice,@pHigh,@pAverage);
select @pAverage,@plowprice,@pHigh;
-- final ch23.3.5 最终作业
delimiter //
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)comment 'get order total'
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity)
from orderitems
where order_num = onumber
into total;
-- is this taxable
if taxable then
select total +(total/100*taxrate)
into total;
end if;
-- finally
select total into ototal;
end //
delimiter ;
-- call main
call ordertotal(20005,0,@total);
select @total;
call ordertotal(20005,1,@total);
select @total;
show create procedure ordertotal;
drop procedure ordertotal;