考虑有这样一个情景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些客户。可以使用存储过程来实现这个功能
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
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
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;
-- finally, save to our variable
select total into ototal;
end;
//使用
//所有mysql变量都必须以@开始
call ordertotal(20005, 0 @total);
select @total;
//结果
+--------+
| @total |
+--------+
| 149.87 |
+--------+
call ordertotal(20005, 1 @total);
select @total;
//结果
+--------+
| @total |
+--------+
| 158.86 |
+--------+
----
//注意，以上代码如果直接敲入到命令行会有问题，因为存储过程里面有";"结束标识，会导致存储过程无法正确运行
//因此，在创建存储过程前，可以临时更改结束标识
delimiter //
//注意除"\"外，其它字符都可以作为结束标识
//当创建存储过程后，再改结束标识为";"即可
---
使用drop procedure ordertotal;删除存储过程
