持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第4天，点击查看活动详情

使用存储过程

存储过程

存储过程简单来说，就是为了以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。但他的作用不仅限于批处理。

为什么要处理存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 使用统一存储过程，可防止错误，因为执行的步骤越多，出错的可能性就越大。
• 简化对变动的管理。如果变动了，我们只需修改存储过程就好。
• 提高性能，使用存储过程比单独的SQL语句要快
• 存在一些只能在单个请求中的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码。

总而言之就是：简单，安全，高性能。

使用存储过程

执行存储过程

mysql称存储过程的执行为调用。

CALL productpricing (@pricelow
                    @pricehigh
                    @priceaverage);

执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格。

创建存储过程

例：返回产品平均价格的存储过程

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

这个存储过程叫productpriceing，用CREATE PROCEDURE productpricing()语句定义。

如果，存储过程接受参数，他们将在（）中列举出来。此存储过程没有参数，但后面的（）依旧需要你跟着。BEGIN和END用来限定存储过程体。

那么如何使用这个存储过程呢？

CALL productpricing();

删除存储过程

DROP  PROCEDURE productpricing;

使用参数

一般来说，存储过程是不显示结果的，而是把结果返回给你指定的变量

变量：内存中一个特定的位置，用来临时存储数据。

那么我们修改一手productpricing()：

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
    )
BEGIN 
    SELECT Min(prod_price)
    INTO p1
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT AVg(prod_price)
    INTO pa
    FROM products;
END;

🍨 pl存储产品的最低价格。每个参数必须具有指定的类型，这里使用十进制值。关键字out指出这个参数是用来从存储过程传出一个值（返回给调用者）。

IN：传递给存储过程

OUT： 从存储过程传出

INOUT： 对存储过程传入和传出

CALL productpricing (@pricelow
                    @pricehigh
                    @priceaverage);

mysql的变量名必须以@开始

我们想要的结果就存储到了上面的三个变量里面了

SELECT @pricelow;

用上面的语句，就可以看到这个值了。

我们再写一个例子，用用IN和OUT。ordertotal接受订单号并返回该订单的合计

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,@total);

建立智能存储过程

--NAME:ordertatal
--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)
        )COMMIT '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 tatal
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO tatal;
    
    --IS this taxble?
    IF taxable THEN 
            --Yes,so add taxrate to the total
            SELECT total+(total/100*taxrate) INTO total;
    END IF;
    
    --AND finally,save to out variable
    SELECT total INTO ototal;
    
 END;

• — 这玩意后面跟的是注释
• DECLARE语句定义了两个局部变量，还支持设定默认值
• SELECT语句的结果存储到了total
• IF呢就是IF用法呗，真就执行。

检查存储过程

SHOW PROCEDURE STATUS LIKE '过程名';

会获得何时，谁创建等详细信息的存储过程列表。