上一節(jié)我介紹了視圖，它是 SQL 中的一個重要應用，使用視圖對 SQL 查詢進行封裝，可以讓 SQL 的代碼結構更清晰，讓用戶權限管理更安全。
今天我來講一下 SQL 的存儲過程，它是 SQL 中另一個重要應用，和視圖一樣，都是對 SQL 代碼進行封裝，可以反復利用。它和視圖有著同樣的優(yōu)點，清晰、安全，還可以減少網絡傳輸量。不過它和視圖不同存儲過程out結果集，視圖是虛擬表，通常不對底層數(shù)據表直接操作，而存儲過程是程序化的 SQL，可以直接操作底層數(shù)據表，相比于面向集合的操作方式，能夠實現(xiàn)一些更復雜的數(shù)據處理。存儲過程可以說是由 SQL 語句和流控制語句構成的語句集合，它和我們之前學到的函數(shù)一樣，可以接收輸入參數(shù)，也可以返回輸出參數(shù)給調用者，返回計算結果。
今天有關存儲過程的內容，你將重點掌握以下幾個部分：
什么是存儲過程，如何創(chuàng)建一個存儲過程？流控制語句都有哪些，如何使用它們？各大公司是如何看待存儲過程的？在實際工作中，我們該如何使用存儲過程？什么是存儲過程，如何創(chuàng)建一個存儲過程
存儲過程的英文是 。它的思想很簡單，就是 SQL 語句的封裝。一旦存儲過程被創(chuàng)建出來，使用它就像使用函數(shù)一樣簡單，我們直接通過調用存儲過程名即可。我在前面講過，存儲過程實際上由 SQL 語句和流控制語句共同組成。流控制語句都有哪些呢？這個我稍后講解。
我們先來看下如何定義一個存儲過程：
CREATE PROCEDURE 存儲過程名稱 ([參數(shù)列表])BEGIN 需要執(zhí)行的語句END 復制代碼在這里，我們使用 創(chuàng)建一個存儲過程，后面是存儲過程的名稱，以及過程所帶的參數(shù)，可以包括輸入參數(shù)和輸出參數(shù)。最后由 BEGIN 和 END 來定義我們所要執(zhí)行的語句塊。
和視圖一樣，我們可以刪除已經創(chuàng)建的存儲過程，使用的是 DROP 。如果要更新存儲過程，我們需要使用 ALTER 。
講完了如何創(chuàng)建，更新和刪除一個存儲過程，下面我們來看下如何實現(xiàn)一個簡單的存儲過程。比如我想做一個累加運算，計算 1+2+…+n 等于多少，我們可以通過參數(shù) n 來表示想要累加的個數(shù)，那么如何用存儲過程實現(xiàn)這一目的呢？這里我做一個 的存儲過程，具體的代碼如下：
CREATE PROCEDURE `add_num`(IN n INT)BEGIN DECLARE i INT; DECLARE sum INT; SET i = 1; SET sum = 0; WHILE i <= n DO SET sum = sum + i; SET i = i +1; END WHILE; SELECT sum;END復制代碼當我們需要再次使用這個存儲過程的時候，直接使用 CALL (50);即可。這里我傳入的參數(shù)為 50，也就是統(tǒng)計 1+2+…+50 的積累之和，查詢結果為：
這就是一個簡單的存儲過程，除了理解 1+2+…+n 的實現(xiàn)過程，還有兩點你需要理解，一個是 定義語句的結束符，另一個是存儲過程的三種參數(shù)類型。
我們先來看下 的作用。如果你使用 這個工具來管理 MySQL 執(zhí)行存儲過程，那么直接執(zhí)行上面這段代碼就可以了。如果用的是 MySQL，你還需要用 來臨時定義新的結束符。因為默認情況下 SQL 采用（；）作為結束符，這樣當存儲過程中的每一句 SQL 結束之后，采用（；）作為結束符，就相當于告訴 SQL 可以執(zhí)行這一句了。但是存儲過程是一個整體，我們不希望 SQL 逐條執(zhí)行，而是采用存儲過程整段執(zhí)行的方式，因此我們就需要臨時定義新的 ，新的結束符可以用（//）或者（$）。如果你用的是 MySQL，那么上面這段代碼，應該寫成下面這樣：
DELIMITER //CREATE PROCEDURE `add_num`(IN n INT)BEGIN DECLARE i INT; DECLARE sum INT; SET i = 1; SET sum = 0; WHILE i <= n DO SET sum = sum + i; SET i = i +1; END WHILE; SELECT sum;END //DELIMITER ;復制代碼首先我用（//）作為結束符，又在整個存儲過程結束后采用了（//）作為結束符號，告訴 SQL 可以執(zhí)行了，然后再將結束符還原成默認的（;）。
需要注意的是，如果你用的是 工具，那么在編寫存儲過程的時候， 會自動設置 為其他符號，我們不需要再進行 的操作。
我們再來看下存儲過程的 3 種參數(shù)類型。在剛才的存儲過程中，我們使用了 IN 類型的參數(shù)，另外還有 OUT 類型和 INOUT 類型，作用如下：
IN 和 OUT 的結合，既用于存儲過程的傳入參數(shù)，同時又可以把計算結果放到參數(shù)中，調用者可以得到返回值。
你能看到，IN 參數(shù)必須在調用存儲過程時指定，而在存儲過程中修改該參數(shù)的值不能被返回。而 OUT 參數(shù)和 INOUT 參數(shù)可以在存儲過程中被改變，并可返回。
舉個例子，這里會用到我們之前講過的王者榮耀的英雄數(shù)據表 heros。假設我想創(chuàng)建一個存儲類型 ，用來查詢某一類型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻擊值，那么該怎么寫呢？
CREATE PROCEDURE `get_hero_scores`( OUT max_max_hp FLOAT, OUT min_max_mp FLOAT, OUT avg_max_attack FLOAT, s VARCHAR(255) )BEGIN SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;END復制代碼你能看到我定義了 4 個參數(shù)類型，其中 3 個為 OUT 類型，分別為 、 和 ，另一個參數(shù) s 為 IN 類型。
這里我們從 heros 數(shù)據表中篩選主要英雄定位為 s 的英雄數(shù)據，即篩選條件為 =s，提取這些數(shù)據中的最大的最大生命值，最小的最大魔法值，以及平均最大攻擊值，分別賦值給變量 、 和 。
然后我們就可以調用存儲過程，使用下面這段代碼即可：
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '戰(zhàn)士');SELECT @max_max_hp, @min_max_mp, @avg_max_attack;復制代碼運行結果：
流控制語句
流控制語句是用來做流程控制的，我剛才講了兩個簡單的存儲過程的例子，一個是 1+2+…+n 的結果計算，一個是王者榮耀的數(shù)據查詢，你能看到這兩個例子中，我用到了下面的流控制語句：
BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結束符。： 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變量的聲明。SET：賦值語句，用于對變量進行賦值。…INTO：把從數(shù)據表中查詢的結果存放到變量中，也就是為變量賦值。
除了上面這些用到的流控制語句以外，還有一些常用的流控制語句：
1.IF…THEN…ENDIF：條件判斷語句，我們還可以在 IF…THEN…ENDIF 中使用 ELSE 和 來進行條件判斷。2.CASE：CASE 語句用于多條件的分支判斷，使用的語法是下面這樣的。
CASE WHEN expression1 THEN ... WHEN expression2 THEN ... ... ELSE --ELSE 語句可以加，也可以不加。加的話代表的所有條件都不滿足時采用的方式。END復制代碼3.LOOP、LEAVE 和 ：LOOP 是循環(huán)語句，使用 LEAVE 可以跳出循環(huán)，使用 則可以進入下一次循環(huán)。如果你有面向過程的編程語言的使用經驗，你可以把 LEAVE 理解為 BREAK，把 理解為 。4.…UNTIL…END ：這是一個循環(huán)語句，首先會執(zhí)行一次循環(huán)，然后在 UNTIL 中進行表達式的判斷，如果滿足條件就退出，即 END ；如果條件不滿足，則會就繼續(xù)執(zhí)行循環(huán)，直到滿足退出條件為止。5.WHILE…DO…END WHILE：這也是循環(huán)語句，和 循環(huán)不同的是，這個語句需要先進行條件判斷，如果滿足條件就進行循環(huán)，如果不滿足條件就退出循環(huán)。
我們之前說過 SQL 是聲明型語言，使用 SQL 就像在使用英語，簡單直接。今天講的存儲過程，尤其是在存儲過程中使用到的流控制語句，屬于過程性語言，類似于 C++ 語言中函數(shù)，這些語句可以幫我們解決復雜的業(yè)務邏輯。
關于存儲過程使用的爭議
盡管存儲過程有諸多優(yōu)點，但是對于存儲過程的使用，一直都存在著很多爭議，比如有些公司對于大型項目要求使用存儲過程，而有些公司在手冊中明確禁止使用存儲過程，為什么這些公司對存儲過程的使用需求差別這么大呢？
我們得從存儲過程的特點來找答案。
你能看到存儲過程有很多好處。
首先存儲過程可以一次編譯多次使用。存儲過程只在創(chuàng)造時進行編譯，之后的使用都不需要重新編譯，這就提升了 SQL 的執(zhí)行效率。其次它可以減少開發(fā)工作量。將代碼封裝成模塊，實際上是編程的核心思想之一，這樣可以把復雜的問題拆解成不同的模塊，然后模塊之間可以重復使用，在減少開發(fā)工作量的同時，還能保證代碼的結構清晰。還有一點，存儲過程的安全性強，我們在設定存儲過程的時候可以設置對用戶的使用權限，這樣就和視圖一樣具有較強的安全性。最后它可以減少網絡傳輸量，因為代碼封裝到存儲過程中，每次使用只需要調用存儲過程即可，這樣就減少了網絡傳輸量。同時在進行相對復雜的數(shù)據庫操作時，原本需要使用一條一條的 SQL 語句，可能要連接多次數(shù)據庫才能完成的操作，現(xiàn)在變成了一次存儲過程，只需要連接一次即可。
基于上面這些優(yōu)點，不少大公司都要求大型項目使用存儲過程，比如微軟、IBM 等公司。但是國內的阿里并不推薦開發(fā)人員使用存儲過程，這是為什么呢？
存儲過程雖然有諸如上面的好處，但缺點也是很明顯的。
它的可移植性差，存儲過程不能跨數(shù)據庫移植，比如在 MySQL、 和 SQL 里編寫的存儲過程，在換成其他數(shù)據庫時都需要重新編寫。
其次調試困難，只有少數(shù) DBMS 支持存儲過程的調試。對于復雜的存儲過程來說，開發(fā)和維護都不容易。
此外，存儲過程的版本管理也很困難，比如數(shù)據表索引發(fā)生變化了，可能會導致存儲過程失效。我們在開發(fā)軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩。
最后它不適合高并發(fā)的場景，高并發(fā)的場景需要減少數(shù)據庫的壓力，有時數(shù)據庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護，增加數(shù)據庫的壓力存儲過程out結果集，顯然就不適用了。
了解了存儲過程的優(yōu)缺點之后，我想說的是，存儲過程既方便，又有局限性。盡管不同的公司對存儲過程的態(tài)度不一，但是對于我們開發(fā)人員來說，不論怎樣，掌握存儲過程都是必備的技能之一。
最后我們做一個小練習吧。針對王者榮耀的英雄數(shù)據表 heros 表，請編寫存儲過程 ，用來得到某一類型英雄（主要定位為某一類型即可）的最大生命值的總和。
歡迎你在評論區(qū)寫下你的答案，也歡迎把這篇文章分享給你的朋友或者同事，一起交流一下。