[[268532]]

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

迄今為止，使用的大多數(shù) SQL語(yǔ)句都是針對(duì)一個(gè)或多個(gè)表的單條語(yǔ)句。并非所有操作都這么簡(jiǎn)單，經(jīng)常會(huì)有一個(gè)完整的操作需要多條語(yǔ)句才能完成。例如，考慮以下的情形。

1、 為了處理訂單，需要核對(duì)以保證庫(kù)存中有相應(yīng)的物品。

2、 如果庫(kù)存有物品，這些物品需要預(yù)定以便不將它們?cè)儋u給別的人，并且要減少可用的物品數(shù)量以反映正確的庫(kù)存量。

3、庫(kù)存中沒(méi)有的物品需要訂購(gòu)，這需要與供應(yīng)商進(jìn)行某種交互。

4、 關(guān)于哪些物品入庫(kù)(并且可以立即發(fā)貨)和哪些物品退訂，需要通知相應(yīng)的客戶。

這顯然不是一個(gè)完整的例子，它甚至超出了本書(shū)中所用樣例表的范圍，但足以幫助表達(dá)我們的意思了。執(zhí)行這個(gè)處理需要針對(duì)許多表的多條MySQL語(yǔ)句。此外，需要執(zhí)行的具體語(yǔ)句及其次序也不是固定的，它們可能會(huì)(和將)根據(jù)哪些物品在庫(kù)存中哪些不在而變化。

那么，怎樣編寫(xiě)此代碼?一種是我們可以單獨(dú)編寫(xiě)每條語(yǔ)句，并根據(jù)結(jié)果有條件地執(zhí)行另外的語(yǔ)句。在每次需要這個(gè)處理時(shí)(以及每個(gè)需要它的應(yīng)用中)都必須做這些工作。而另一種可以創(chuàng)建存儲(chǔ)過(guò)程。

其實(shí)簡(jiǎn)單來(lái)說(shuō)：存儲(chǔ)過(guò)程，就是為以后的使用而保存的一條或多條 MySQL語(yǔ)句的集合。可將其視為批文件，雖然它們的作用不僅限于批處理。

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

既然我們知道了什么是存儲(chǔ)過(guò)程，那么為什么要使用它們呢?有許多理由，下面列出一些主要的理由。

1、通過(guò)把處理封裝在容易使用的單元中，簡(jiǎn)化復(fù)雜的操作(正如前面例子所述)。

2、 由于不要求反復(fù)建立一系列處理步驟，這保證了數(shù)據(jù)的完整性。如果所有開(kāi)發(fā)人員和應(yīng)用程序都使用同一(試驗(yàn)和測(cè)試)存儲(chǔ)過(guò)程，則所使用的代碼都是相同的。這一點(diǎn)的延伸就是防止錯(cuò)誤。需要執(zhí)行的步驟越多，出錯(cuò)的可能性就越大。防止錯(cuò)誤保證了數(shù)據(jù)的一致性。

3、簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名或業(yè)務(wù)邏輯(或別的內(nèi)容)有變化，只需要更改存儲(chǔ)過(guò)程的代碼。使用它的人員甚至不需要知道這些變化。這一點(diǎn)的延伸就是安全性。通過(guò)存儲(chǔ)過(guò)程限制對(duì)基礎(chǔ)數(shù)據(jù)的訪問(wèn)減少了數(shù)據(jù)訛誤(無(wú)意識(shí)的或別的原因所導(dǎo)致的數(shù)據(jù)訛誤)的機(jī)會(huì)。

4、提高性能。因?yàn)槭褂么鎯?chǔ)過(guò)程比使用單獨(dú)的 SQL語(yǔ)句要快。

5、存在一些只能用在單個(gè)請(qǐng)求中的 MySQL元素和特性，存儲(chǔ)過(guò)程可以使用它們來(lái)編寫(xiě)功能更強(qiáng)更靈活的代碼(在下一章的例子中可以看到。)

換句話說(shuō)，使用存儲(chǔ)過(guò)程有 3個(gè)主要的好處，即簡(jiǎn)單、安全、高性能。顯然，它們都很重要。不過(guò)，在將 SQL代碼轉(zhuǎn)換為存儲(chǔ)過(guò)程前，也必須知道它的一些缺陷。

1、一般來(lái)說(shuō)，存儲(chǔ)過(guò)程的編寫(xiě)比基本 SQL語(yǔ)句復(fù)雜，編寫(xiě)存儲(chǔ)過(guò)程需要更高的技能，更豐富的經(jīng)驗(yàn)。

2、你可能沒(méi)有創(chuàng)建存儲(chǔ)過(guò)程的安全訪問(wèn)權(quán)限。許多數(shù)據(jù)庫(kù)管理員限制存儲(chǔ)過(guò)程的創(chuàng)建權(quán)限，允許用戶使用存儲(chǔ)過(guò)程，但不允許他們創(chuàng)建存儲(chǔ)過(guò)程。

盡管有這些缺陷，存儲(chǔ)過(guò)程還是非常有用的，并且應(yīng)該盡可能地使用。

不能編寫(xiě)存儲(chǔ)過(guò)程?你依然可以使用：MySQL將編寫(xiě)存儲(chǔ)過(guò)程的安全和訪問(wèn)與執(zhí)行存儲(chǔ)過(guò)程的安全和訪問(wèn)區(qū)分開(kāi)來(lái)。這是好事情。即使你不能(或不想)編寫(xiě)自己的存儲(chǔ)過(guò)程，也仍然可以在適當(dāng)?shù)臅r(shí)候執(zhí)行別的存儲(chǔ)過(guò)程。

如何去使用存儲(chǔ)過(guò)程

使用存儲(chǔ)過(guò)程需要知道如何執(zhí)行(運(yùn)行)它們。存儲(chǔ)過(guò)程的執(zhí)行遠(yuǎn)比其定義更經(jīng)常遇到，因此，我們將從執(zhí)行存儲(chǔ)過(guò)程開(kāi)始介紹。然后再介紹創(chuàng)建和使用存儲(chǔ)過(guò)程。

執(zhí)行存儲(chǔ)過(guò)程

MySQL稱存儲(chǔ)過(guò)程的執(zhí)行為調(diào)用，因此 MySQL執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句為CALL。 CALL接受存儲(chǔ)過(guò)程的名字以及需要傳遞給它的任意參數(shù)。請(qǐng)看以下例子：

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

其中執(zhí)行productpricing 的存儲(chǔ)過(guò)程，他計(jì)算并返回產(chǎn)品的最低價(jià)格，最高價(jià)格，均價(jià)。存儲(chǔ)過(guò)程可以顯示結(jié)果，也可以不顯示結(jié)果，接下來(lái)會(huì)提到。

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

正如所述，編寫(xiě)存儲(chǔ)過(guò)程并不是微不足道的事情。為讓你了解這個(gè)過(guò)程，請(qǐng)看一個(gè)例子——一個(gè)返回產(chǎn)品平均價(jià)格的存儲(chǔ)過(guò)程。以下是其代碼：

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

我們稍后介紹第一條和最后一條語(yǔ)句。此存儲(chǔ)過(guò)程名為productpricing，用CREATE PROCEDURE productpricing() 語(yǔ)句定義。如果存儲(chǔ)過(guò)程接受參數(shù)，它們將在 ()中列舉出來(lái)。此存儲(chǔ)過(guò)程沒(méi)有參數(shù)，但后跟的 ()仍然需要。BEGIN和 END語(yǔ)句用來(lái)限定存儲(chǔ)過(guò)程體，過(guò)程體本身僅是一個(gè)簡(jiǎn)單的 SELECT語(yǔ)句(使用第12章介紹的 Avg()函數(shù))。

在MySQL處理這段代碼時(shí)，它創(chuàng)建一個(gè)新的存儲(chǔ)過(guò)程 productpricing。沒(méi)有返回?cái)?shù)據(jù)，因?yàn)檫@段代碼并未調(diào)用存儲(chǔ)過(guò)程，這里只是為以后使用而創(chuàng)建它。

這里有一個(gè)需要注意的就是：mysql命令行客戶機(jī)的分隔符

如果你使用的是 mysql命令行實(shí)用程序，應(yīng)該仔細(xì)閱讀此說(shuō)明。

默認(rèn)的 MySQL語(yǔ)句分隔符為;(正如你已經(jīng)在迄今為止所使用的MySQL語(yǔ)句中所看到的那樣)。 mysql命令行實(shí)用程序也使用;作為語(yǔ)句分隔符。如果命令行實(shí)用程序要解釋存儲(chǔ)過(guò)程自身內(nèi)的 ;字符，則它們最終不會(huì)成為存儲(chǔ)過(guò)程的成分，這會(huì)使存儲(chǔ)過(guò)程中的 SQL出現(xiàn)句法錯(cuò)誤。解決辦法是臨時(shí)更改命令行實(shí)用程序的語(yǔ)句分隔符，如下所示：

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

其中， DELIMITER //告訴命令行實(shí)用程序使用 //作為新的語(yǔ)句結(jié)束分隔符，可以看到標(biāo)志存儲(chǔ)過(guò)程結(jié)束的 END定義為END//而不是END; 。這樣，存儲(chǔ)過(guò)程體內(nèi)的 ;仍然保持不動(dòng)，并且正確地傳遞給數(shù)據(jù)庫(kù)引擎。最后，為恢復(fù)為原來(lái)的語(yǔ)句分隔符，可使用 DELIMITER ;。除符號(hào)外，任何字符都可以用作語(yǔ)句分隔符。如果你使用的是 mysql命令行實(shí)用程序，在閱讀本文時(shí)請(qǐng)記住這里的內(nèi)容。

那么，如何使用這個(gè)存儲(chǔ)過(guò)程?如下所示：

CALL productpricing(); 

結(jié)果是：

+--------------+ 
| priceaverage | 
+--------------+ 
| 16.133571 | 
+--------------+ 

CALL productpricing();執(zhí)行剛創(chuàng)建的存儲(chǔ)過(guò)程并顯示返回的結(jié)果。因?yàn)榇鎯?chǔ)過(guò)程實(shí)際上是一種函數(shù)，所以存儲(chǔ)過(guò)程名后需要有()符號(hào)(即使不傳遞參數(shù)也需要)。