MySQL存储过程

创建存储过程

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
	from products;
end;

执行存储过程

call productpricing();

删除存储过程

drop procedure 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;

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;

检查存储过程

show create procedure ordertotal;