簡介

存儲過程Stored Procedure，SQL中的另一個重要應用。

前面說的視圖，只能勉強跟編程中的函數相似，存儲過程更進一步逼近了函數這一概念。 視圖仍然局限在SQL語句這個范疇，但是存儲過程已經可以進一步使用控制語句了。或許，存儲過程可以視為是視圖的更進一步

存儲過程是由SQL語句和流控制語句組成的語句集合，和函數一樣，它可以接收輸入參數，也可以把結果返回。一旦存儲過程被創建出來，使用它就像使用函數一樣簡單，直接調用存儲過程名就可以。

先簡單看了一下存儲過程究竟是什么，該怎么用，給我的感覺是：存儲過程就是遵循模塊化編程的指導思想下的一個比較簡陋的產品。

按教程的內容，本節將主要介紹以下部分：

• 存儲過程的形式；
• 存儲過程中各組成部分的介紹；
• 存儲過程的優缺點。

存儲過程的形式

定義一個存儲過程

create procedure 存儲過程名稱([參數列表])
begin需要執行的語句
end

和視圖一樣，刪除存儲過程是drop procedure，更新存儲過程是alter procedure。

舉一個例子，寫一個簡單的存儲過程，計算1+2+3+…+n等于多少。

delimiter //
create procedure `add_num`(IN n INT)
begindeclare i int;declare sum int;set i=1;set sum=0;while i <= n doset sum = sum + i;set i = i+1;end while;select sum;
end //
delimiter ;

使用delimiter將’//‘作為整個存儲過程的結束符號，并在最后將結束符重新定義回默認的’;’

然后我們需要計算1到50的累加之和時，只需要調用call add_num(50);即可。

使用delimiter定義語句結束符

以MySQL舉例，如果使用Navicat這種圖形化工具來編寫存儲過程的話，是不需要手動定義delimiter的，navicat會自己加。

但是如果你使用的工具沒有提供這個功能，或者是你直接在后臺手敲的，那么你需要顯式用delimiter來定義結束符。

那么，為什么要定義語句結束符呢？

這是因為默認情況下MySQL使用分號，即;來作為結束符。

這樣的話，在存儲過程中的每一行SQL語句之后加分號，就相當于告訴SQL解釋器，這一行已經結束了，可以執行這一句了。

但是有時候我們不希望SQL這樣做，存儲過程是一個整體，我們更希望存儲過程整段一起執行，所以我們需要臨時定義新的delimiter，比如說’//'或者’$$‘。

存儲過程中的三種參數類型

分別是IN、OUT、和INOUT型。

IN在存儲過程中不能返回，即存儲過程之外無法調用到in類型的參數，但是out和inout是可以調到的。

create procedure `func`(out max_hp float,out min_hp float,s varchar(255)
)
beginselect max(hp), min(hp)from heroswhere role_category=sinto max_hp, min_hp;
end

可以看到定義了兩個out類型的參數用來接收返回值，定義了一個參數s用來接收輸入，缺省情況下是IN參數。

那怎么讀取到存儲過程返回的結果呢？

call func(@max_hp, @min_hp, '戰士');
select @max_hp, @min_hp;

就可以把結果打印出來了。

流控制語句

常用的流控制語句有：

1. begin…end：表示存儲過程的范圍，有點像編程里的花括號；

2. declare：聲明變量用，變量在使用前必須提前聲明，聲明方式declare var_name var_type;

3. set: 賦值語句，用于變量賦值，如set var_name = value;

4. select…into：把查詢結果存到out類變量中，就是利用select來為變量賦值；

5. if…then…elseif…then…else…endif;

6. case:

   casewhen 表達式1 then...when 表達式2 then...else...
   end
   

7. LOOP、leave、iterate：LOOP是循環語句，類似for循環。leave中止本層循環，類似break。iterate中止本次循環，類似continue；

8. repeat…until…end repeat：有點類似編程里的do while語句。repeat是先執行一次循環，然后until做表達式判斷，如果滿足條件就退出(這里跟while是不同的)，即走end repeat；若條件不滿足，則繼續執行循環，直到滿足條件；

9. while…do…end while：這個跟while沒有區別，滿足條件就循環，不滿足就退出；

存儲過程的優缺點

當前對存儲過程的使用一直都有爭議。有些公司對大型項目要求使用存儲過程，但有些公司卻明令禁止使用（如阿里）。

優點：

1. 一次編譯多次使用。存儲過程只在創建時執行編譯，之后都不需要再進行編譯；
2. 減少開發工作量。將代碼封裝成模塊，實際上是模塊化編程思想。這樣，多個模塊之間可以重復使用，而且也方便復雜查詢的拆解。
3. 安全。可以設定哪些用戶可以使用存儲過程。
4. 減少網絡傳輸量。連接一次數據庫，執行整個存儲過程即可，不需要多次連接數據庫，一行一行執行代碼。

缺點：

1. 可移植性差。無法跨數據庫移植。
2. 調試困難。多數DBMS不支持存儲過程的調試，所以對于復雜的存儲過程，其開發和維護都相當困難。
3. 版本管理困難。存儲過程本身沒有版本控制，在迭代更新時會比較麻煩。另外，存儲過程很容易失效，比如說數據表索引發生變化時，可能會導致失效；
4. 不適合高并發。對于分庫分表的并發查詢，很難維護。

參考文獻

1. 13丨什么是存儲過程，在實際項目中用得多么？