【轉載】MySQL之存儲過程（PROCEDURE） - 菜菜成長記 - 部落格園
存儲過程是一組為了完成特定功能的 SQL 語句集合。
| 在資料庫的實際操作中，并非所有操作都是針對一個表或幾個表的單條 SQL 語句那麼簡單，經常會有一個完整的操作需要多條 SQL 語句處理多個表才能完成。 例如，為了确認學生能否畢業，需要同時查詢學生檔案表、成績表和綜合表，此時就需要使用多條 SQL 語句來針對幾個資料表完成這個處理要求。 存儲過程可以有效地完成這個資料庫操作。 |
使用存儲過程的目的是将常用或複雜的工作預先用 SQL 語句寫好并用一個指定名稱存儲起來，這個過程經編譯和優化後存儲在資料庫伺服器中，是以稱為存儲過程。當以後需要資料庫提供與已定義好的存儲過程的功能相同的服務時，隻需調用“CALL存儲過程名字”即可自動完成。
常用操作資料庫的 SQL 語句在執行的時候需要先編譯，然後執行。存儲過程則不一樣。
一個存儲過程是一個可程式設計的函數，它在資料庫中建立并儲存，一般由 SQL 語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的特定功能時，存儲過程尤為合适。
存儲過程通常有如下優點：
1) 封裝性
存儲過程被建立後，可以在程式中被多次調用，而不必重新編寫該存儲過程的 SQL 語句，并且資料庫專業人員可以随時對存儲過程進行修改，而不會影響到調用它的應用程式源代碼。
2) 可增強 SQL 語句的功能和靈活性
存儲過程可以用流程控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
3) 可減少網絡流量
由于存儲過程是在伺服器端運作的，且執行速度快，是以當客戶計算機上調用該存儲過程時，網絡中傳送的隻是該調用語句，進而可降低網絡負載。
4) 高性能
存儲過程執行一次後，産生的二進制代碼就駐留在緩沖區，在以後的調用中，隻需要從緩沖區中執行二進制代碼即可，進而提高了系統的效率和性能。
5) 提高資料庫的安全性和資料的完整性
使用存儲過程可以完成所有資料庫操作，并且可以通過程式設計的方式控制資料庫資訊通路的權限。
二、MySQL建立存儲過程（CREATE PROCEDURE）
MySQL存儲過程是一些 SQL 語句的集合，比如有的時候我們可能需要一大串的 SQL 語句，或者說在編寫 SQL 語句的過程中還需要設定一些變量的值，這個時候我們就完全有必要編寫一個存儲過程。
基本文法
可以使用 CREATE PROCEDURE 語句建立存儲過程。
文法格式如下：
CREATE PROCEDURE <過程名> ( [過程參數[,…] ] ) <過程體>
[過程參數[,…] ] 格式
[ IN | OUT | INOUT ] <參數名> <類型>
文法說明如下：
1) 過程名
存儲過程的名稱，預設在目前資料庫中建立。若需要在特定資料庫中建立存儲過程，則要在名稱前面加上資料庫的名稱，即 db_name.sp_name。需要注意的是，名稱應當盡量避免選取與 MySQL 内置函數相同的名稱，否則會發生錯誤。
2) 過程參數
存儲過程的參數清單。其中，
<參數名>
為參數名，
<類型>
為參數的類型（可以是任何有效的 MySQL 資料類型）。當有多個參數時，參數清單中彼此間用逗号分隔。存儲過程可以沒有參數（此時存儲過程的名稱後仍需加上一對括号），也可以有 1 個或多個參數。
MySQL 存儲過程支援三種類型的參數，即輸入參數、輸出參數和輸入/輸出參數，分别用 IN、OUT 和 INOUT 三個關鍵字辨別。其中，輸入參數可以傳遞給一個存儲過程，輸出參數用于存儲過程需要傳回一個操作結果的情形，而輸入/輸出參數既可以充當輸入參數也可以充當輸出參數。需要注意的是，參數的取名不要與資料表的列名相同，否則盡管不會傳回出錯資訊，但是存儲過程的 SQL 語句會将參數名看作列名，進而引發不可預知的結果。
3) 過程體
存儲過程的主體部分，也稱為存儲過程體，包含在過程調用的時候必須執行的 SQL 語句。這個部分以關鍵字 BEGIN 開始，以關鍵字 END 結束。若存儲過程體中隻有一條 SQL 語句，則可以省略 BEGIN-END 标志。
在存儲過程的建立中，經常會用到一個十分重要的 MySQL 指令，即 DELIMITER 指令，特别是對于通過指令行的方式來操作 MySQL 資料庫的使用者，更是要學會使用該指令。
在 MySQL 中，伺服器處理 SQL 語句預設是以分号作為語句結束标志的。然而，在建立存儲過程時，存儲過程體可能包含有多條 SQL 語句，這些 SQL 語句如果仍以分号作為語句結束符，那麼 MySQL 伺服器在處理時會以遇到的第一條 SQL 語句結尾處的分号作為整個程式的結束符，而不再去處理存儲過程體中後面的 SQL 語句，這樣顯然不行。為解決這個問題，通常可使用 DELIMITER 指令将結束指令修改為其他字元。
文法格式如下：
DELIMITER $$
文法說明如下：
- $$ 是使用者定義的結束符，通常這個符号可以是一些特殊的符号，如兩個“?”或兩個“￥”等。
- 當使用 DELIMITER 指令時，應該避免使用反斜杠“\”字元，因為它是 MySQL 的轉義字元。
在 MySQL 指令行用戶端輸入如下SQL語句。
mysql > DELIMITER ??
成功執行這條 SQL 語句後，任何指令、語句或程式的結束标志就換為兩個問号“??”了。
若希望換回預設的分号“;”作為結束标志，則在 MySQL 指令行用戶端輸入下列語句即可：
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之間一定要有一個空格。在建立存儲過程時，必須具有 CREATE ROUTINE 權限。可以使用 SHOW PROCEDURE STATUS 指令檢視資料庫中存在哪些存儲過程，若要檢視某個存儲過程的具體資訊，則可以使用 SHOW CREATE PROCEDURE <存儲過程名>。
建立不帶參數的存儲過程
【執行個體 1】建立名稱為 ShowStuScore 的存儲過程，存儲過程的作用是從學生成績資訊表中查詢學生的成績資訊，輸入的 SQL 語句和執行過程如下所示。
建立存儲過程 ShowStuScore 後，通過 CALL 語句調用該存儲過程的 SQL 語句和執行結果如下所示。
建立帶參數的存儲過程
【執行個體 2】建立名稱為 GetScoreByStu 的存儲過程，輸入參數是學生姓名。存儲過程的作用是通過輸入的學生姓名從學生成績資訊表中查詢指定學生的成績資訊，輸入的 SQL 語句和執行過程如下所示。
建立存儲過程 GetScoreByStu 後，通過 CALL 語句調用該存儲過程的 SQL 語句和執行結果如下所示。
三、MySQL修改存儲過程（ALTER PROCEDURE）
在實際開發過程中，業務需求修改的情況時有發生，這樣，不可避免的需要修改 MySQL 中存儲過程的特征 。
基本文法
可以使用 ALTER PROCEDURE 語句修改存儲過程的某些特征。
文法格式如下：
ALTER PROCEDURE <過程名> [ <特征> … ]
提示：這個文法用于修改存儲過程的某些特征，如要修改存儲過程的内容，可以先删除該存儲過程，再重新建立。
修改存儲過程的内容和名稱
修改存儲過程的内容可以通過删除原存儲過程，再以相同的命名建立新的存儲過程。
修改存儲過程的名稱可以通過删除原存儲過程，再以不同的命名建立新的存儲過程。
四、MySQL删除存儲過程（DROP PROCEDURE）
當MySQL資料庫中存在廢棄的存儲過程時，我們需要将它從資料庫中删除。
基本文法
存儲過程被建立後，儲存在資料庫伺服器上，直至被删除。可以使用 DROP PROCEDURE 語句删除資料庫中已建立的存儲過程。
文法格式如下：
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <過程名>
文法說明如下：
1) 過程名
指定要删除的存儲過程的名稱。
2) IF EXISTS
指定這個關鍵字，用于防止因删除不存在的存儲過程而引發的錯誤。
注意：存儲過程名稱後面沒有參數清單，也沒有括号，在删除之前，必須确認該存儲過程沒有任何依賴關系，否則會導緻其他與之關聯的存儲過程無法運作。
删除存儲過程
删除存儲過程 GetScoreByStu，檢視存儲過程的運作結果如下所示。