本文介紹什么是存儲過程?為什么要使用存儲過程?如何使用存儲過程?如何去使用存儲過程以及怎么執行存儲過程。

[[275019]]

系列存儲集錦

• 細致入微：Oracle中執行計劃在Shared Pool中的存儲位置探秘
• 故障診斷 | 存儲Cache丟失導致數據庫無法open的案例分享
• 【12.2新特性】在Oracle Active Data Guard上部署列式存儲
• DBA必備技能：RAC 如何安裝新主機識別老存儲恢復數據庫
• 守得云開見月明：一次ASM存儲高可用故障解決過程分析
• 基于超大規模集群的本地存儲系統優化
• 深入解析：DB2 V10.5新特性列式存儲表的優點與缺點
• WiredTiger存儲引擎知多少?

什么是存儲過程

迄今為止，使用的大多數SQL語句都是針對一個或多個表的單條語句。并非所有操作都這么簡單，經常會有一個完整的操作需要多條語句才能完成。例如，考慮以下的情形。

1. 為了處理訂單，需要核對以保證庫存中有相應的物品。

2. 如果庫存有物品，這些物品需要預定以便不將它們再賣給別的人，并且要減少可用的物品數量以反映正確的庫存量。

3. 庫存中沒有的物品需要訂購，這需要與供應商進行某種交互。

4. 關于哪些物品入庫(并且可以立即發貨)和哪些物品退訂，需要通知相應的客戶。

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

那么，怎樣編寫此代碼?一種是我們可以單獨編寫每條語句，并根據結果有條件地執行另外的語句。在每次需要這個處理時(以及每個需要它的應用中)都必須做這些工作。而另一種可以創建存儲過程。

其實簡單來說：存儲過程，就是為以后的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件，雖然它們的作用不僅限于批處理。

為什么要使用存儲過程

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

1. 通過把處理封裝在容易使用的單元中，簡化復雜的操作(正如前面例子所述)

2. 由于不要求反復建立一系列處理步驟，這保證了數據的完整性。如果所有開發人員和應用程序都使用同一(試驗和測試)存儲過程，則所使用的代碼都是相同的。這一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大。防止錯誤保證了數據的一致性。

3. 簡化對變動的管理。如果表名、列名或業務邏輯(或別的內容)有變化，只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。這一點的延伸就是安全性。通過存儲過程限制對基礎數據的訪問減少了數據訛誤(無意識的或別的原因所導致的數據訛誤)的機會。

4. 提高性能。因為使用存儲過程比使用單獨的 SQL語句要快。

5. 存在一些只能用在單個請求中的MySQL元素和特性，存儲過程可以使用它們來編寫功能更強更靈活的代碼(在下一章的例子中可以看到。)

換句話說，使用存儲過程有 3個主要的好處，即簡單、安全、高性能。顯然，它們都很重要。不過，在將 SQL代碼轉換為存儲過程前，也必須知道它的一些缺陷。

1. 一般來說，存儲過程的編寫比基本 SQL語句復雜，編寫存儲過程需要更高的技能，更豐富的經驗。

2. 你可能沒有創建存儲過程的安全訪問權限。許多數據庫管理員限制存儲過程的創建權限，允許用戶使用存儲過程，但不允許他們創建存儲過程。

盡管有這些缺陷，存儲過程還是非常有用的，并且應該盡可能地使用。

不能編寫存儲過程?你依然可以使用：MySQL將編寫存儲過程的安全和訪問與執行存儲過程的安全和訪問區分開來。這是好事情。即使你不能(或不想)編寫自己的存儲過程，也仍然可以在適當的時候執行別的存儲過程。

如何去使用存儲過程

使用存儲過程需要知道如何執行(運行)它們。存儲過程的執行遠比其定義更經常遇到，因此，我們將從執行存儲過程開始介紹。然后再介紹創建和使用存儲過程。

執行存儲過程

MySQL稱存儲過程的執行為調用，因此MySQL執行存儲過程的語句為CALL。 CALL接受存儲過程的名字以及需要傳遞給它的任意參數。請看以下例子：

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

其中執行productpricing的存儲過程，他計算并返回產品的最低價格，最高價格，均價。存儲過程可以顯示結果，也可以不顯示結果，接下來會提到。

創建存儲過程

正如所述，編寫存儲過程并不是微不足道的事情。為讓你了解這個過程，請看一個例子——一個返回產品平均價格的存儲過程。以下是其代碼：

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

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

在MySQL處理這段代碼時，它創建一個新的存儲過程 productpricing。沒有返回數據，因為這段代碼并未調用存儲過程，這里只是為以后使用而創建它。

這里有一個需要注意的就是：MySQL命令行客戶機的分隔符

如果你使用的是MySQL命令行實用程序，應該仔細閱讀此說明。

默認的 MySQL語句分隔符為;(正如你已經在迄今為止所使用的MySQL語句中所看到的那樣)。MySQL命令行實用程序也使用;作為語句分隔符。如果命令行實用程序要解釋存儲過程自身內的 ;字符，則它們最終不會成為存儲過程的成分，這會使存儲過程中的 SQL出現句法錯誤。解決辦法是臨時更改命令行實用程序的語句分隔符，如下所示：

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

其中， DELIMITER //告訴命令行實用程序使用 //作為新的語句結束分隔符，可以看到標志存儲過程結束的 END定義為END//而不是END; 。這樣，存儲過程體內的 ;仍然保持不動，并且正確地傳遞給數據庫引擎。最后，為恢復為原來的語句分隔符，可使用 DELIMITER ;。除符號外，任何字符都可以用作語句分隔符。如果你使用的是 MySQL命令行實用程序，在閱讀本文時請記住這里的內容。

那么，如何使用這個存儲過程?如下所示：

CALL productpricing(); 

結果是：

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

CALL productpricing();執行剛創建的存儲過程并顯示返回的結果。因為存儲過程實際上是一種函數，所以存儲過程名后需要有()符號(即使不傳遞參數也需要)。