之前介绍的SQL语句都是针对一个或多个表的单条语句。而存储过程则是用于执行针对多张表的多条MySQL语句(执行的语句与次序也可能不固定)。

1 特点

• 简单

• 简化复杂的操作。将处理封装到容易使用的单元中，简化复杂的操作

• 简化变动的管理。如果表、列名或者业务逻辑有变化，只需要更改存储过程，不会影响上层应用的使用。

• 安全

• 防止出错。将需要进行的一系列操作封装起来，供所有应用使用。避免由于执行步骤多导致的出错，从而保证数据的一致性。

• 高性能

• 提高性能。使用存储过程比使用单独的SQL语句要快

• 更强更灵活。存储过程能够使用一些只能用在单个请求的MySQL元素和特性

2 操作

2.1 创建存储过程

 CREATE PROCEDURE productpricing ()
 BEGIN
     SELECT
                 AVG(prod_price) AS priceaverage
     FROM
                 product;
 END;
 #创建执行过程productpricing,括号中用于保存参数
 #BEGIN和END语句用来保存存储过程体，本案例中为查询平均值

2.2 执行存储过程

 CALL productpricing()
 #括号中为参数，若不需要传入参数也要保留括号

2.3 删除存储过程

 DROP PROCEDURE productpricing;
 #删除不需要带参数及括号
 #若存储过程不存在则报错，可使用DROP PROCEDURE IF EXISTS确保只有过程存在时才执行删除操作

2.4 检查存储过程

 SHOW CREATE PROCEDURE productpricing;

2.5 带参数的存储过程（OUT）

 CREATE PROCEDURE productpricing2 (
     OUT Pl DECIMAL (8, 2),
     OUT ph DECIMAL (8, 2),
     OUT pa DECIMAL (8, 2)
 )
 BEGIN
     SELECT
                 MIN(prod_price) INTO pl
     FROM
                 product;
 
     SELECT
         MAX(prod_price) INTO ph
     FROM
         product;
 
     SELECT
         AVG(prod_price) INTO pa
     FROM
         product;
 END;
 #指定三个参数pl、ph、pa。参数类型为十进制(DECIMAL),支持的参数类型与表中的一致；关键字OUT表示从存储过程传出，此外还支持IN(传递给存储过程)、INOUT(对存储过程传入和传出)
 #通过INTO将查询语句中的值与参数绑定
 
 CALL productpricing2 (
     @pricelow ,@pricehigh ,@priceaverage
 );
 
 SELECT
     @pricelow ,@pricehigh ,@priceaverage
 #执行存储过程时需要指定变量(变量必须以@开头)用于保存返回的值
 #要展示需要使用SELECT语句进行展示

2.6 带参数存储过程（IN）

 CREATE PROCEDURE productpricing3 (
     IN onumber INT,
     OUT ototal DECIMAL (8, 2)
 )
 BEGIN
     SELECT
                 SUM(item_price * quantity)
     FROM
                 order_item
     WHERE
                 order_num = onumber INTO ototal;
 END;
 #创建存储过程
 
 CALL productpricing3 (
     20005 ,@total
 );
 
 SELECT
     @total
 #执行并展示

2.7 智能存储过程

目标，需要进行如下操作

• 获得合计

• 把营业税有条件地添加到合计

• 返回合计(带或不带税)

 delimiter //
 -- name:odertotal 存储过程名：订单总价
 -- 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 add 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(quantity * item_price)
     FROM
         order_item
     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 our variable
     SELECT
                 total INTO ototal ;
 END//
 delimiter ;
 #创建存储过程
 #使用关键字delimiter来切换分隔符，最后需要恢复回去
 #--用来进行注释
 #用来设置局部变量，并能设置默认值(可选)
 
 CALL ordertotal (20005, 0 ,@total);
 
 SELECT
     @total
 #执行并展示