MySQL 基础知识6 (存储过程)

一、存储过程说明

1. 基本定义：为后续使用保存的单条或多条MySQL语句的集合

2. 主要作用：

(1). 通过把处理封装在容易使用的单元中, 简化复杂的操作

(2). 由于不要求反复建立一系列处理步骤, 这保证了数据的完整性

(3). 简化对变动的管理 (如果表名、列名或业务逻辑等有所改变, 只需改变存储过程的代码)

(4). 提高性能 (因为使用存储过程比使用单独的SQL语句要快)

(5). 存在一些只能用在单个请求中的MySQL元素和特性, 存储过程可以使用它们来编写功能更强更灵活的代码

二、使用存储过程

1. 创建存储过程：

存储过程名称 (参数)，即使存储过程名后的()无参数也需要()；BEGIN 和 END 用于限定存储过程体

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

2. 创建存储过程 (使用MySQL命令行实用程序)

说明：若使用MySQL命令行实用程序，也将使用到分号； 作为语句分隔符，如果需要解释存储过程自身内的分号；字符，则会出现句法错误

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

3. 创建存储过程 (使用参数)

说明：这里以三个参数为例 (OUT 参数名 数据类型 表示从存储过程传出；IN 参数名 数据类型 表示传入存储过程)

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;

4. 调用存储过程：CALL

说明：调用上述形参的存储过程：存储过程用于保存结果的变量名称必须以@开始

-- 调用无参的存储过程
CAL productpricing();
-- 调用有参的存储过程
CALL productpricing(
    @pricelow, @pricehigh, @priceaverage
);

5. 检索存储过程变量的数据

SELECT @pricelow AS 最低价, @pricehigh AS 最高价, @priceaverage AS 平均价;

6. 创建存储过程：使用IN 和 OUT 参数

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

-- 调用上述存储过程
CALL ordertotal(20005, @ototal);
SELECT @ototal AS 订单条数;

7. 删除存储过程：DROP

DROP PROCEDURE productpricing;

8. 查看存储过程信息

SHOW CREATE PROCEDURE ordertotal;

三、建立智能存储过程

1. 说明：这里以需要获取与以前一样的订单合计为例, 但需要对合计增加营业税

2. 步骤：先获取合计条数，再把营业税有条件地添加到合计，最后返回总计(带或不带税)

-- 存储过程名称：ordertotal
-- 参数名称：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, optionall adding tax'
BEGIN
-- 定义局部变量：总数
DECLARE total DECIMAL(8,2);
-- 定义局部变量：税率百分比
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;

3. 调用存储过程，检索数据信息

CALL ordertotal(20005, 0, @total);
SELECT @total AS 无税总价;

CALL ordertotal(20005, 1, @total);
SELECT @total AS 交税总价;