一、创建存储过程

1、返回产品平均价格的存储过程

CREATE PROCEDURE productpricing() 
BEGIN
    SELECT
        AVG( prod_price ) AS priceaverage 
    FROM
        products; 
END;

• 此存储过程名为productpricing，用 CREATE PROCEDURE productpricing() 语句定义
• 如果存储过程接受参数，它们将在()中列举出来
• BEGIN 和 END语句用来限定存储过程体，过程体本身仅是一个简单的 SELECT 语句

2、MySQL命令行客户机的分隔符

• 默认的MySQL语句分隔符为;，如果在存储过程自身内的有;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现语法错误
• 解决办法是临时更改命令行实用程序的语句分隔符，DELIMITER //告诉命令行实用程序实用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END定义为 END// 而不是 END；
• 最后，为恢复为原来的语句分隔符，可使用 DELIMITER ;。
• 除 \ 符号外，任何字符都可以用作语句分隔符

DELIMITER //
CREATE PROCEDURE productpricing() 
BEGIN
    SELECT
        AVG( prod_price ) AS priceaverage 
    FROM
        products ;
END //
DELIMITER ;

二、使用存储过程

CALL productpricing();

三、删除存储过程

DROP PROCEDURE productpricing;

四、存储过程使用参数

1、变量（variable）

• 内存中一个特定的位置，用来临时存储数据

2、创建带有参数的存储过程

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：存储产品平均价格

3、调用带有参数的存储过程

CALL productpricing(
@priceLow,
@priceHigh,
@priceAvg
)

• 调用时，这条语句并不显示任何数据。为了显示检索出的产品的3个值，可进行如下操作

SELECT @priceLow, @priceHigh, @priceAvg

五、使用 IN 和 OUT 参数的存储过程

1、创建同时带有 IN 和 OUT 参数的存储过程

CREATE PROCEDURE orderTotal(
    IN number INT,
    OUT total DECIMAL(8, 2)
)
BEGIN
    SELECT SUM(item_price * quantity)
    FROM orderitems
    WHERE order_num = number
    INTO total;
END;

• number定义为 IN，因为订单号被传入存储过程
• total 定义为 OUT，因为要从存储过程返回合计
• WHERE子句使用number选择正确的行
• INTO 使用 total 存储计算出来的合计

2、调用同时带有 IN 和 OUT 参数的存储过程

CALL orderTotal(20005, @total)

3、显示同时带有 IN 和 OUT 参数的存储过程

SELECT @total

六、建立智能存储过程

• 在存储过程内包含业务规则和智能处理时，才能显示出存储过程的威力

1、获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（条件），那么需要做下面几件事情

• 获得合计（与以前一样）
• 把营业税有条件地添加到合计
• 返回合计（带或不带税）

2、创建存储过程

CREATE PROCEDURE orderTotalAuto(
    IN number 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 = number
    INTO total;
    
    -- 判断是否需要税率 
    IF taxable THEN
        SELECT total + (total / 100 *taxrate) INTO total;
    END IF;
    
    SELECT total INTO ototal;
END;

3、调用没有添加税率

CALL orderTotalAuto(20005, 0, @total)
SELECT @total

4、调用添加税率

CALL orderTotalAuto(20005, 1, @total)
SELECT @total