MySQL必知應會-第23章-使用預存程序

標籤：位置   布爾值   介紹   資料庫管理員   列表   訂單號   完成   轉換   商務邏輯   

第23章-使用預存程序

本章介紹什麼是預存程序，為什麼要使用預存程序以及如何使用預存程序，並且介紹建立和使用預存程序的基本文法。

23.1 預存程序

需要MySQL 5 MySQL 5添加了對預存程序的支援，因此，本章內容適用於MySQL 5及以後的版本。迄今為止，使用的大多數SQL語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單，經常會有一個完整的操作需要多條語句才能完成。例如，考慮以下的情形。

• 為了處理訂單，需要核對以保證庫存中有相應的物品。
• 如果庫存有物品，這些物品需要預定以便不將它們再賣給別的人，並且要減少可用的物品數量以反映正確的庫存量。
• 庫存中沒有的物品需要訂購，這需要與供應商進行某種互動。
• 關於哪些物品入庫（並且可以立即發貨）和哪些物品退訂，需要通知相應的客戶。這顯然不是一個完整的例子，它甚至超出了本書中所用範例表的範圍，但足以協助表達我們的意思了。執行這個處理需要針對許多表的多條MySQL語句。此外，需要執行的具體語句及其次序也不是固定的，它們可能會（和將）根據哪些物品在庫存中哪些不在而變化。那麼，怎樣編寫此代碼？可以單獨編寫每條語句，並根據結果有條217164 使用預存程序件地執行另外的語句。在每次需要這個處理時（以及每個需要它的應用中）都必須做這些工作。可以建立預存程序。預存程序簡單來說，就是為以後的使用而儲存的一條或多條MySQL語句的集合。可將其視為批檔案，雖然它們的作用不僅限於批處理。

23.2 為什麼要使用預存程序

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

• 通過把處理封裝在容易使用的單元中，簡化複雜的操作（正如前面例子所述）。
• 由於不要求反覆建立一系列處理步驟，這保證了資料的完整性。如果所有開發人員和應用程式都使用同一（實驗和測試）預存程序，則所使用的代碼都是相同的。這一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大。防止錯誤保證了資料的一致性。
• 簡化對變動的管理。如果表名、列名或商務邏輯（或別的內容）有變化，只需要更改預存程序的代碼。使用它的人員甚至不需要知道這些變化。這一點的延伸就是安全性。通過預存程序限制對基礎資料的訪問減少了資料訛誤（無意識的或別的原因所導致的資料訛誤）的機會。
• 提高效能。因為使用預存程序比使用單獨的SQL語句要快。
• 存在一些只能用在單個請求中的MySQL元素和特性，預存程序可以使用它們來編寫功能更強更靈活的代碼（在下一章的例子中可以看到。）換句話說，使用預存程序有3個主要的好處，即簡單、安全、高效能。顯然，它們都很重要。不過，在將SQL代碼轉換為預存程序前，也必須知道它的一些缺陷。
• 一般來說，預存程序的編寫比基本SQL語句複雜，編寫預存程序需要更高的技能，更豐富的經驗。218
• 你可能沒有建立預存程序的安全存取權限。許多資料庫管理員限制預存程序的建立許可權，允許使用者使用預存程序，但不允許他們建立預存程序。儘管有這些缺陷，預存程序還是非常有用的，並且應該儘可能地使用。不能編寫預存程序？你依然可以使用 MySQL將編寫預存程序的安全和訪問與執行預存程序的安全和訪問區分開來。這是好事情。即使你不能（或不想）編寫自己的預存程序，也仍然可以在適當的時候執行別的預存程序。

23.3 使用預存程序

使用預存程序需要知道如何執行（運行）它們。預存程序的執行遠比其定義更經常遇到，因此，我們將從執行預存程序開始介紹。然後再介紹建立和使用預存程序。

23.3.1 執行預存程序

MySQL稱預存程序的執行為調用，因此MySQL執行預存程序的語句為CALL。 CALL接受預存程序的名字以及需要傳遞給它的任意參數。請看以下例子：
call productpricing(@pricelow,@pricehigh,@priceaverage);
其中，執行名為productpricing的預存程序，它計算並返回產品的最低、最高和平均價格。預存程序可以顯示結果，也可以不顯示結果，如稍後所述。

23.3.2 建立預存程序

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

使用預存程序我們稍後介紹第一條和最後一條語句。此預存程序名為productpricing，用CREATE PROCEDURE productpricing()語句定義。如果預存程序接受參數，它們將在()中列舉出來。此預存程序沒有參數，但後跟的()仍然需要。 BEGIN和END語句用來限定預存程序體，過程體本身僅是一個簡單的SELECT語句（使用第12章介紹的Avg()函數）。在MySQL處理這段代碼時，它建立一個新的預存程序productpricing。沒有返回資料，因為這段代碼並未調用預存程序，這裡只是為以後使用而建立它。mysql命令列客戶機的分隔字元 如果你使用的是mysql命令列公用程式，應該仔細閱讀此說明。預設的MySQL語句分隔字元為;（正如你已經在迄今為止所使用的MySQL語句中所看到的那樣）。 mysql命令列公用程式也使用;作為語句分隔字元。如果命令列公用程式要解釋預存程序自身內的;字元，則它們最終不會成為預存程序的成分，這會使預存程序中的SQL出現句法錯誤。解決辦法是臨時更改命令列公用程式的語句分隔字元，如下所示：

其中， DELIMITER //告訴命令列公用程式使用//作為新的語句結束分隔字元，可以看到標誌預存程序結束的END定義為END//而不是END;。這樣，預存程序體內的;仍然保持不動，並且正確地傳遞給資料庫引擎。最後，為恢複為原來的語句分隔字元，22022123.3 使用預存程序 167可使用DELIMITER ;。除\符號外，任何字元都可以用作語句分隔字元。如果你使用的是mysql命令列公用程式，在閱讀本章時請記住這裡的內容。那麼，如何使用這個預存程序？如下所示：

CALL productpricing();執行剛建立的預存程序並顯示返回的結果。因為預存程序實際上是一種函數，所以預存程序名後需要有()符號（即使不傳遞參數也需要）。

23.3.3 刪除預存程序

預存程序在建立之後，被儲存在伺服器上以供使用，直至被刪除。刪除命令（類似於第21章所介紹的語句）從伺服器中刪除預存程序。為刪除剛建立的預存程序，可使用以下語句：

這條語句刪除剛建立的預存程序。請注意沒有使用後面的()，只給出預存程序名。僅當存在時刪除 如果指定的過程不存在，則DROP PROCEDURE將產生一個錯誤。當過程存在想刪除它時（如果過程不存在也不產生錯誤）可使用DROP PROCEDURE IF EXISTS。

23.3.4 使用參數

productpricing只是一個簡單的預存程序，它簡單地顯示SELECT語句的結果。一般，預存程序並不顯示結果，而是把結果返回給你指定的輸入輸出分析222168 使用預存程序變數。變數（variable） 記憶體中一個特定的位置，用來臨時儲存資料。以下是productpricing的修改版本（如果不先刪除此預存程序，則不能再次建立它）：

此預存程序接受3個參數：

pl儲存產品最低價格， ph儲存產品最高價格， pa儲存產品平均價格。每個參數必須具有指定的類型，這裡使用十進位值。關鍵字OUT指出相應的參數用來從預存程序傳出一個值（返回給調用者）。 MySQL支援IN（傳遞給預存程序）、 OUT（從預存程序傳出，如這裡所用）和INOUT（對預存程序傳入和傳出）類型的參數。預存程序的代碼位於BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然後儲存到相應的變數（通過指定INTO關鍵字）。參數的資料類型 預存程序的參數允許的資料類型與表中使用的資料類型相同。附錄D列出了這些類型。注意，記錄集不是允許的類型，因此，不能通過一個參數返回多個行和列。這就是前面的例子為什麼要使用3個參數（和3條SELECT語句）的原因。為調用此修改過的預存程序，必須指定3個變數名，如下所示：

由於此預存程序要求3個參數，因此必須正好傳遞3個參數，不多也不少。所以，這條CALL語句給出3個參數。它們是預存程序將儲存結果的3個變數的名字。變數名 所有MySQL變數都必須以@開始。在調用時，這條語句並不顯示任何資料。它返回以後可以顯示（或在其他處理中使用）的變數。為了顯示檢索出的產品平均價格，可如下進行：

為了獲得3個值，可使用以下語句：

下面是另外一個例子，這次使用IN和OUT參數。 ordertotal接受訂單號並返回該訂單的合計：

輸入輸出輸入輸出輸入224225170 使用預存程序onumber定義為IN，因為訂單號被傳入預存程序。 ototal定義為OUT，因為要從預存程序返回合計。 SELECT語句使用這兩個參數， WHERE子句使用onumber選擇正確的行， INTO使用ototal儲存計算出來的合計。為調用這個新預存程序，可使用以下語句：

必須給ordertotal傳遞兩個參數；第一個參數為訂單號，第二個參數為包含計算出來的合計的變數名。為了顯示此合計，可如下進行：

@total已由ordertotal的CALL語句填寫， SELECT顯示它包含的值。為了得到另一個訂單的合計顯示，需要再次調用預存程序，然後重新顯示變數：

23.3.5 建立智能預存程序

迄今為止使用的所有預存程序基本上都是封裝MySQL簡單的SELECT語句。雖然它們全都是有效預存程序例子，但它們所能完成的工作你直接用這些被封裝的語句就能完成（如果說它們還能帶來更多的東西，分析輸入輸出分析輸入22623.3 使用預存程序 171那就是使事情更複雜）。只有在預存程序內包含商務規則和智能處理時，它們的威力才真正顯現出來。考慮這個情境。你需要獲得與以前一樣的訂單合計，但需要對合計增加營業稅，不過只針對某些顧客（或許是你所在州中那些顧客）。那麼，你需要做下面幾件事情：

• 獲得合計（與以前一樣）；
• 把營業稅有條件地添加到合計；
• 返回合計（帶或不帶稅）。預存程序的完整工作如下：
  

此預存程序有很大的變動。首先，增加了注釋（前面放置—）。在預存程序複雜性增加時，這樣做特別重要。添加了另外一個參數taxable，它是一個布爾值（如果要增加稅則為真，否則為假）。在預存程序體中，用DECLARE語句定義了兩個局部變數。 DECLARE要求指定變數名和資料類型，它也支援可選的預設值（這個例子中的taxrate的預設被設定為6%）。 SELECT語句已經改變，因此其結果儲存到total（局部變數）而不是ototal。 IF語句檢查taxable是否為真，如果為真，則用另一SELECT語句增加營業稅到局部變數total。最後，用另一SELECT語句將total（它增加或許不增加營業稅）儲存到ototal。COMMENT關鍵字 本例子中的預存程序在CREATE PROCEDURE語句中包含了一個COMMENT值。它不是必需的，但如果給出，將在SHOW PROCEDURE STATUS的結果中顯示。這顯然是一個更進階，功能更強的預存程序。為實驗它，請用以下兩條語句：


BOOLEAN值指定為1表示真，指定為0表示假（實際上，非零值都考慮為真，只有0被視為假）。通過給中間的參數指定0或1，可以有條件地將營業稅加到訂單合計上。IF語句 這個例子給出了MySQL的IF語句的基本用法。 IF語句還支援ELSEIF和ELSE子句（前者還使用THEN子句，後者不使用）。在以後章節中我們將會看到IF的其他用法（以及其他流量控制語句）。

23.3.6 檢查預存程序

為顯示用來建立一個預存程序的CREATE語句，使用SHOW CREATE PROCEDURE語句：


為了獲得包括何時、由誰建立等詳細資料的預存程序列表， 使用SHOWPROCEDURE STATUS。限制過程狀態結果 SHOW PROCEDURE STATUS列出所有預存程序。為限制其輸出，可使用LIKE指定一個過濾模式，例如：

23.4 小結

本章介紹了什麼是預存程序以及為什麼要使用預存程序。我們介紹了預存程序的執行和建立的文法以及使用預存程序的一些方法。下一章我們將繼續這個話題。

MySQL必知應會-第23章-使用預存程序