MySQL存储过程

使用存储过程三个好处简单，安全，高性能。、

@练习数据下载链接create.sql and populate.sql

 

1 获得合计

2 把营业税加到合计

3 返回合计（带或不带税） 

 

--Parameters:onumber =order number
            taxable =0 if not taxable, 1 if taxable
            ototal =order totalvariable 
--计算带税率得收入                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
CREATE PROCEDURE ordertotal(
       IN onumber INT,
       IN taxable BOOLEAN,
       OUT ototal DECIMAL(8,2))     
BEGIN
--定义变量 
       DECLARE total DECIMAL(8,2);
--默认税率是6
       DECLARE taxrate INT DEFAULT 6;
       
--计算所有收入
       SELECT Sum(item_price*quantity)
       FROM orderitems
       WHERE order_num = onumber
       INTO total;
       
--计算收入加税率
       IF taxable THEN
          SELECT total+(total/100*taxrate) INTO total;
       END IF;
       
       SELECT total INTO ototal;
     END;  
--执行存储过程
CALL ordertoal(20005, 1, @total);
SELECT @total;