mysql存儲(chǔ)過(guò)程的案例分析

這篇文章主要介紹mysql存儲(chǔ)過(guò)程的案例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

1、什么是存儲(chǔ)過(guò)程

為以后的使用而保存的一條或多條MySQL語(yǔ)句的集合。

存儲(chǔ)過(guò)程思想上就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用。

（相關(guān)推薦：MySQL教程）

2、為什么要使用存儲(chǔ)過(guò)程

1. 把處理封裝在容易使用的單元中，簡(jiǎn)化復(fù)雜的操作

2. 防止錯(cuò)誤保證了數(shù)據(jù)的一致性

3. 簡(jiǎn)化對(duì)變動(dòng)的管理。(修改對(duì)應(yīng)表名、列名等修改對(duì)應(yīng)存儲(chǔ)過(guò)程的代碼，對(duì)于使用的人不需要知道變化)

4. 提高性能

5. 靈活

總的來(lái)說(shuō)是簡(jiǎn)單、安全、高性能
缺點(diǎn)：

1. 編寫(xiě)比SQL語(yǔ)句復(fù)雜

2. 權(quán)限問(wèn)題（可能無(wú)權(quán)、一般都是使用存儲(chǔ)過(guò)程、沒(méi)有創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限）

3、創(chuàng)建存儲(chǔ)過(guò)程

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

注意：在命令行中輸入的問(wèn)題

mysql> delimiter //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT Avg(prod_price) AS priceaverage
    -> FROM products;
    -> END //

4、使用存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程實(shí)際上是一種函數(shù)

CALL productpricing();

4、刪除存儲(chǔ)過(guò)程

    drop procedure productpricing；
    drop procedure if EXISTS productpricing；

5、使用參數(shù)

一般，存儲(chǔ)過(guò)程并不顯示結(jié)果，而是把結(jié)果返回給你指定的變量  
變量(variable)內(nèi)存中一個(gè)特定的位置，用來(lái)臨時(shí)存儲(chǔ)數(shù)據(jù)。

CREATE PROCEDURE productpricing(
    OUT p1 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;

關(guān)鍵字OUT指出相應(yīng)的參數(shù)用來(lái)從存儲(chǔ)過(guò)程傳出 一個(gè)值(返回給調(diào)用者)。
MySQL支持IN(傳遞給存儲(chǔ)過(guò)程)、
OUT(從存 儲(chǔ)過(guò)程傳出，如這里所用)
INOUT(對(duì)存儲(chǔ)過(guò)程傳入和傳出)類型的參 數(shù)。

變量名 所有MySQL變量都必須以@開(kāi)始。
調(diào)用存儲(chǔ)過(guò)程

call productpricing(@pricelow,@pricehign,@priceaverage);

查詢

SELECT @priceaverage;

SELECT @priceaverage,@pricehign,@pricelow;

使用in和out
創(chuàng)建

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;

調(diào)用

call ordertotal(20005,@total);

查詢

select @total;

6、建立智能存儲(chǔ)過(guò)程

迄今為止使用的所有存儲(chǔ)過(guò)程基本上都是封裝 MySQL簡(jiǎn)單的SELECT語(yǔ)句。雖然它們?nèi)际怯行У拇鎯?chǔ)過(guò)程例子，但它們所能完成的工作你直接用這些被封裝的語(yǔ)句就能完成（如果說(shuō)它們還能帶來(lái)更多的東西。那就是使事情更復(fù)雜）。只有在存儲(chǔ)過(guò)程內(nèi)包含業(yè)務(wù)規(guī)則和智能處理時(shí)，它們的威力才真正顯現(xiàn)出來(lái)。

   考慮這個(gè)場(chǎng)景。你需要獲得與以前一樣的訂單合計(jì)，但需要對(duì)合計(jì)增加營(yíng)業(yè)稅，不過(guò)只針對(duì)某些顧客（或許是你所在州中那些顧客）。那么，你需要做下面幾件事情：
   1、獲得合計(jì)（和以前一樣）
   2、把營(yíng)業(yè)稅有條件的添加到合計(jì)
   3、返回合計(jì)（帶或不帶稅的）

我們輸入如下代碼：

-- Name: ordertotal        //   添加注釋
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxtable
--             ototal = order total variable

CREATE     PROCEDURE ordertotal (
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT '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 total
        SELECT Sum(item_price*quantity)
        FROM orderitems
        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 out variable
        SELECT total INTO ototal;
END;

此存儲(chǔ)過(guò)程有很大的變動(dòng)。首先，增加了注釋（前面放置 --）。在存儲(chǔ)過(guò)程復(fù)雜性增加時(shí)，這樣做特別重要。  
添加了另外一個(gè)參數(shù) taxable，它是一個(gè)布爾值（如果要增加稅則為真，否則為假）。  
在存儲(chǔ)過(guò)程體中，用 DECLARE語(yǔ)句定義了兩個(gè)局部變量。 DECLARE要求指定變量名和數(shù)據(jù)類型，
它也支持可選的默認(rèn)值（這個(gè)例子中的 taxrate的默認(rèn)被設(shè)置為 6%）。SELECT 語(yǔ)句變，因此其結(jié)果存儲(chǔ)到 total（局部變量）而不是 ototal。  
IF 語(yǔ)句檢查taxable是否為真，如果為真，則用另一SELECT語(yǔ)句增加營(yíng)業(yè)稅到局部變量 total。

最后，用另一SELECT語(yǔ)句將total（它增加或許不增加營(yíng)業(yè)稅）保存到 ototal。  
注意：COMMENT關(guān)鍵字 ，本例子中的存儲(chǔ)過(guò)程在 CREATE PROCEDURE語(yǔ)句中包含了一個(gè) COMMENT值。  
它不是必需的，但如果給出，將在SHOW PROCEDURE STATUS的結(jié)果中顯示。

這顯然是一個(gè)更高級(jí)，功能更強(qiáng)的存儲(chǔ)過(guò)程。為試驗(yàn)它，請(qǐng)用以下兩條語(yǔ)句：  
第一條：

call ordertotal(20005, 0, @total);
SELECT @total;

輸出：

+--------+
| @total |
+--------+
|  38.47 |
+--------+

第二條：

call ordertotal(20009, 1，@total);
SELECT @total;

輸出：

+--------+
| @total |
+--------+
|  36.21 |
+--------+

BOOLEAN值指定為1 表示真，指定為 0表示假（實(shí)際上，非零值都考慮為真，只有 0被視為假）。通過(guò)給中間的參數(shù)指定 0或1 ，可以有條件地將營(yíng)業(yè)稅加到訂單合計(jì)上。

這個(gè)例子給出了 MySQL的IF 語(yǔ)句的基本用法。 IF語(yǔ)句還支持 ELSEIF和ELSE 子句（前者還使用 THEN子句，后者不使用）。在以后章節(jié)中我們將會(huì)看到 IF的其他用法（以及其他流控制語(yǔ)句）。

7、檢查存儲(chǔ)過(guò)程

為顯示用來(lái)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的CREATE語(yǔ)句

show create PROCEDURE ordertotal;

為了獲得包括何時(shí)、由誰(shuí)創(chuàng)建等詳細(xì)信息的存儲(chǔ)過(guò)程列表

show procedure status;

表比較多，用like過(guò)濾

show procedure status like 'ordertotal';

以上是“mysql存儲(chǔ)過(guò)程的案例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！