mysql 学习记录 存储过程

第23章 使用存储过程
存储优点
提高性能，比使用单独的sql语句快
存在一些只能用在单个请求中的mysql元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
存储缺点
存储编写复杂
需要权限

执行存储

call productpricing(@pricelow,
								@pricehigh,
								@priceaverage) #有些存储需要变量名

创建存储

delimiter //
create procedure productpricing()
begin
	select avg(prod_price) as priceaverage from products;
end //
delimiter ; delimiter 告诉程序使用//作为新的语句来结束分隔符，

如果存储过程接受参数，他们将在（）中列举出来。若没有也需要列出（）
delimiter 告诉程序使用//作为新的语句来结束分隔符，可以看到标志存储过程结束的end定义为end//而不是end； 。 这样，存储过程体内的；仍然保持不动，并且正确地传递给数据库引擎。最后恢复为原来的语句分隔符；。除符号\外任何字符都可以用作语句分隔符。

删除存储

drop procedure productpricing if exists;

使用参数

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 ;

#为调用上述存储过程，必须指定3个变量名
call productpricing(@pricelow,@pricehigh,@priceaverage);
#显示检索出的产品平均价格
select @priceaverage;
#获得3个值
select @pricehigh,@pricelow,@priceaverage;

mysql支持in(传递给存储过程)、out（从传递过程传出）和intout（对存储过程传入和传入。）

所有变量都必须以@开始

delimiter //
create procedure ordertotal(
	in onumber int,   				# onumber定义为IN，因为订单号被传入存储过程
    out ototal decimal(8,2)			# ototal为OUT，因为要从存储过程返回合计
)
begin
	select sum(item_price*quantity) from orderitems 
    where order_num = onumber
    into ototal;
end //
delimiter ;
#给ordertotal传递两个参数；
#第一个参数为订单号，第二个参数为包含计算出来的合计的变量名
call ordertotal(20005,@total);
#显示此合计
select @total;
#得到另一个订单的合计显示
call ordertotal(20009,@total);
select @total;

智能存储过程

-- 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, optionally adding tax'
begin
	-- declare variable for total 定义局部变量total
    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 taxable then
		-- Yes,so add taxrate to the total 给订单合计增加税率
		select total+(total/100*taxrate) into total;
	end if;
	-- and finally,save to out variable 最后，传递给输出变量 
	SELECT total INTO ototal;
END //
delimiter ;
# 调用上述存储过程，不加税 
call ordertotal(20005,0,@total);
select @total;
# 调用上述存储过程，加税 
call ordertotal(20005,1,@total);
select @total;

comment 表示注释，具体用法见上，也可comment（）

检查存储过程

show create procedure ordertotal;
#检查存储过程的语句
show procedure status
#检查所有何时、谁等详细信息的存储过程列表
show procedure status like 'ordertotal'