MySQL存储过程
一、存储过程的概念
存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可以把它理解为一种函数。使用存储过程有3个主要的好处,即简单、安全、高性能。
二、使用存储过程
1. 无参数的存储过程
-- 创建一个返回产品平均价格的存储过程
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
-- 调用该存储过程
call productpricing();
2. 有参数的存储过程
例1:
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关键字用来将参数的结果返回给调用者。
*/
-- 调用该存储过程,用pricelow,pricehigh,priceaverage三个参数保存存储过程的三个返回值。
call productpricing(@pricelow,
@pricehigh,
@priceaverage);
-- 显示pricelow,pricehigh,priceaverage的值
select @pricehigh, @pricelow, @priceaverage;
例2:
-- onumber定义为in,需要将订单号传入该存储过程。ototal定义为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;
3. 智能存储过程
-- 该存储过程实现根据传入参数的不同,对合计增加或不增加营业税。
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)
begin
-- 用declare语句定义两个局部变量。declare要求指定变量名和数据类型。
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity)
from orderitems
where order_num = onumber
into total;
-- if语句检查taxable是否为真(非零为真,零为假),如果为真,则增加营业税到局部变量total中。
if taxable then
select total+(total*taxrate/100) into total;
end if;
select total into ototal;
end;
-- 调用该存储过程,参数为0表示不增加营业税
call ordertotal(20005, 0, @total)
-- 调用该存储过程,参数为1表示增加营业税
call ordertotal(20005, 1, @total)
三、删除存储过程
-- 这条语句删除已有的存储过程productpricing。注意存储过程名后面没有()。
drop procedure productpricing;