mysql存儲(chǔ)過(guò)程的詳細(xì)介紹(代碼示例)

本篇文章給大家?guī)?lái)的內(nèi)容是關(guān)于mysql存儲(chǔ)過(guò)程的詳細(xì)介紹(代碼示例),有一定的參考價(jià)值,有需要的朋友可以參考一下,希望對(duì)你有所幫助。

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. 編寫比SQL語(yǔ)句復(fù)雜

  2. 權(quán)限問(wèn)題(可能無(wú)權(quán)、一般都是使用存儲(chǔ)過(guò)程、沒有創(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變量都必須以@開始。
調(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';

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊11 分享