存儲過程是程式化的 SQL,可以直接操作底層資料表,相比于面向集合的操作方式,能夠實作一些更複雜的資料處理。存儲過程可以說是由 SQL 語句和流控制語句構成的語句集合,它和我們之前學到的函數一樣,可以接收輸入參數,也可以傳回輸出參數給調用者,傳回計算結果。
存儲過程像是函數.
什麼是存儲過程,如何建立一個存儲過程
定義一個存儲過程:
CREATE PROCEDURE 存儲過程名稱([參數清單])
BEGIN
需要執行的語句
END
删除已經建立的存儲過程:
DROP PROCEDURE
更新存儲過程:
ALTER PROCEDURE
實作一個簡單的存儲過程:
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
mysql> call study_stored_procedure(50);
+------+
| sum |
+------+
| 1275 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
使用這個存儲過程:
CALL add_num(50);
DELIMITER
如果你使用 Navicat 這個工具來管理 MySQL 執行存儲過程,那麼直接執行上面這段代碼就可以了。如果用的是 MySQL,你還需要用 DELIMITER 來臨時定義新的結束符。因為預設情況下 SQL 采用(;)作為結束符,這樣當存儲過程中的每一句 SQL 結束之後,采用(;)作為結束符,就相當于告訴 SQL 可以執行這一句了。但是存儲過程是一個整體,我們不希望 SQL 逐條執行,而是采用存儲過程整段執行的方式,是以我們就需要臨時定義新的 DELIMITER,新的結束符可以用(//)或者($$)。如果你用的是 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 ;
存儲過程的 3 種參數類型
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yNiFGMkJjYiRWYzUmYzMGOmN2M0YjNlFDOkhDZhVTOj9CX5d2bs92Yl1iclB3bsVmdlR2LcNWaw9CXt92Yu4GZjlGbh5yYjV3Lc9CX6MHc0RHaiojIsJye.png)
IN 參數必須在調用存儲過程時指定,而在存儲過程中修改該參數的值不能被傳回。而 OUT 參數和 INOUT 參數可以在存儲過程中被改變,并可傳回。
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
調用:
- 調用的時候需要在變量前面加 @ , 否則報錯
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '戰士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;
流控制語句
- BEGIN…END:BEGIN…END 中間包含了多個語句,每個語句都以(;)号為結束符。
- DECLARE:DECLARE 用來聲明變量,使用的位置在于 BEGIN…END 語句中間,而且需要在其他語句使用之前進行變量的聲明。
- SET:指派語句,用于對變量進行指派。
- SELECT…INTO:把從資料表中查詢的結果存放到變量中,也就是為變量指派。
- IF…THEN…ENDIF:條件判斷語句,我們還可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 來進行條件判斷。
- CASE:CASE 語句用于多條件的分支判斷,使用的文法是下面這樣的。
- LOOP、LEAVE 和 ITERATE:LOOP 是循環語句,使用 LEAVE 可以跳出循環,使用 ITERATE 則可以進入下一次循環。如果你有面向過程的程式設計語言的使用經驗,你可以把 LEAVE 了解為 BREAK,把 ITERATE 了解為 CONTINUE。
- REPEAT…UNTIL…END REPEAT:這是一個循環語句,首先會執行一次循環,然後在 UNTIL 中進行表達式的判斷,如果滿足條件就退出,即 END REPEAT;如果條件不滿足,則會就繼續執行循環,直到滿足退出條件為止。
- WHILE…DO…END WHILE:這也是循環語句,和 REPEAT 循環不同的是,這個語句需要先進行條件判斷,如果滿足條件就進行循環,如果不滿足條件就退出循環。
CASE
WHEN expression1 THEN ...
WHEN expression2 THEN ...
...
ELSE
--ELSE語句可以加,也可以不加。加的話代表的所有條件都不滿足時采用的方式。
END
關于存儲過程使用的争議
存儲過程有很多好處:
• 存儲過程可以一次編譯多次使用
• 存儲過程的安全性強
• 可以減少網絡傳輸量
缺點也是很明顯的:
• 可移植性差
• 調試困難
• 版本管理也很困難
• 不适合高并發的場景