前面我們介紹了數據自帶的函數，此處提到的函數，指的是用戶自定義函數。存儲函數是一定會有返回值的，存儲過程是可以沒有返回值的。

MySQL從5.0版本開始支持存儲過程和函數。存儲過程和函數能夠將復雜的SQL邏輯封裝在一起，應用程序無須關注存儲過程和函數內部復雜的SQL邏輯，而只需要簡單地調用存儲過程和函數即可。

1. 存儲過程概述

1.1 理解

含義：存儲過程的英文是 Stored Procedure。它的思想很簡單，就是一組經過預先編譯的 SQL 語句的封裝。

執行過程：存儲過程預先存儲在 MySQL 服務器上，需要執行的時候，客戶端只需要向服務器端發出調用存儲過程的命令，服務器端就可以把預先存儲好的這一系列 SQL 語句全部執行。

好處：

1、簡化操作，提高了sql語句的重用性，減少了開發程序員的壓力
2、減少操作過程中的失誤，提高效率
3、減少網絡傳輸量（客戶端不需要把所有的 SQL 語句通過網絡發給服務器）
4、減少了 SQL 語句暴露在網上的風險，也提高了數據查詢的安全性

和視圖、函數的對比：

它和視圖有著同樣的優點，清晰、安全，還可以減少網絡傳輸量。不過它和視圖不同，視圖是虛擬表，通常不對底層數據表直接操作，而存儲過程是程序化的 SQL，可以直接操作底層數據表，相比于面向集合的操作方式，能夠實現一些更復雜的數據處理。

一旦存儲過程被創建出來，使用它就像使用函數一樣簡單，我們直接通過調用存儲過程名即可。相較于函數，存儲過程是沒有返回值的。

1.2 分類

存儲過程的參數類型可以是IN、OUT和INOUT。根據這點分類如下：

1、沒有參數（無參數無返回）
2、僅僅帶 IN 類型（有參數無返回）
3、僅僅帶 OUT 類型（無參數有返回）
4、既帶 IN 又帶 OUT（有參數有返回）
5、帶 INOUT（有參數有返回）

注意：IN、OUT、INOUT 都可以在一個存儲過程中帶多個。

2. 創建存儲過程

2.1 語法分析

語法：

CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名 參數類型,...)
[characteristics ...]
BEGIN存儲過程體END

類似于Java中的方法：

修飾符 返回類型 方法名(參數類型 參數名,...){方法體;
}

說明：

1、參數前面的符號的意思

• IN：當前參數為輸入參數，也就是表示入參；

  存儲過程只是讀取這個參數的值。如果沒有定義參數種類，默認就是 IN，表示輸入參數。

• OUT：當前參數為輸出參數，也就是表示出參；

  執行完成之后，調用這個存儲過程的客戶端或者應用程序就可以讀取這個參數返回的值了。

• INOUT：當前參數既可以為輸入參數，也可以為輸出參數。

2、形參類型可以是 MySQL數據庫中的任意類型。

3、characteristics 表示創建存儲過程時指定的對存儲過程的約束條件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• LANGUAGE SQL：說明存儲過程執行體是由SQL語句組成的，當前系統支持的語言為SQL。
• [NOT] DETERMINISTIC：指明存儲過程執行的結果是否確定。DETERMINISTIC表示結果是確定的。每次執行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語句的限制。
  • CONTAINS SQL表示當前存儲過程的子程序包含SQL語句，但是并不包含讀寫數據的SQL語句；
  • NO SQL表示當前存儲過程的子程序中不包含任何SQL語句；
  • READS SQL DATA表示當前存儲過程的子程序中包含讀數據的SQL語句；
  • MODIFIES SQL DATA表示當前存儲過程的子程序中包含寫數據的SQL語句。
  • 默認情況下，系統會指定為CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：執行當前存儲過程的權限，即指明哪些用戶能夠執行當前存儲過程。
  • DEFINER表示只有當前存儲過程的創建者或者定義者才能執行當前存儲過程；
  • INVOKER表示擁有當前存儲過程的訪問權限的用戶能夠執行當前存儲過程。
  • 如果沒有設置相關的值，則MySQL默認指定值為DEFINER。
• COMMENT 'string'：注釋信息，可以用來描述存儲過程。

4、存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END

編寫存儲過程并不是一件簡單的事情，可能存儲過程中需要復雜的 SQL 語句。

1. BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結束符。
2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變量的聲明。
3. SET：賦值語句，用于對變量進行賦值。
4. SELECT… INTO：把從數據表中查詢的結果存放到變量中，也就是為變量賦值。

5、需要設置新的結束標記DELIMITER

DELIMITER 新的結束標記

因為MySQL默認的語句結束符號為分號‘;’。為了避免與存儲過程中SQL語句結束符相沖突，需要使用DELIMITER改變存儲過程的結束符。

比如：“DELIMITER //”語句的作用是將MySQL的結束符設置為//，并以“END //”結束存儲過程。存儲過程定義完畢之后再使用“DELIMITER ;”恢復默認結束符。DELIMITER也可以指定其他符號作為結束符。

當使用DELIMITER命令時，應該避免使用反斜杠（‘\’）字符，因為反斜線是MySQL的轉義字符。

示例：

DELIMITER $CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名  參數類型,...)
[characteristics ...]
BEGINsql語句1;sql語句2; #如果不使用DELIMITER $，此處;會被看做結束，使用后遇到$代表結束END $

2.2 代碼舉例

舉例1：創建存儲過程select_all_data()，查看 emps 表的所有數據

DELIMITER $CREATE PROCEDURE select_all_data()
BEGINSELECT * FROM emps;END $DELIMITER ;

舉例2：創建存儲過程avg_employee_salary()，返回所有員工的平均工資

DELIMITER //CREATE PROCEDURE avg_employee_salary ()
BEGINSELECT AVG(salary) AS avg_salary FROM emps;
END //DELIMITER ;

舉例3：創建存儲過程show_max_salary()，用來查看“emps”表的最高薪資值。

CREATE PROCEDURE show_max_salary()LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT '查看最高薪資'BEGINSELECT MAX(salary) FROM emps;END //DELIMITER ;

舉例4：創建存儲過程show_min_salary()，查看“emps”表的最低薪資值。并將最低薪資通過OUT參數“ms”輸出

DELIMITER //CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)BEGINSELECT MIN(salary) INTO ms FROM emps;END //DELIMITER ;

舉例5：創建存儲過程show_someone_salary()，查看“emps”表的某個員工的薪資，并用IN參數empname輸入員工姓名。

DELIMITER //CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))BEGINSELECT salary FROM emps WHERE ename = empname;END //DELIMITER ;

舉例6：創建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用IN參數empname輸入員工姓名，用OUT參數empsalary輸出員工薪資。

DELIMITER //CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)BEGINSELECT salary INTO empsalary FROM emps WHERE ename = empname;END //DELIMITER ;

舉例7：創建存儲過程show_mgr_name()，查詢某個員工領導的姓名，并用INOUT參數“empname”輸入員工姓名，輸出領導的姓名。

DELIMITER //CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))BEGINSELECT ename INTO empname FROM empsWHERE eid = (SELECT MID FROM emps WHERE ename=empname);END //DELIMITER ;

3. 調用存儲過程

3.1 調用格式

存儲過程有多種調用方法。存儲過程必須使用CALL語句調用，并且存儲過程和數據庫相關，如果要執行其他數據庫中的存儲過程，需要指定數據庫名稱，例如CALL dbname.procname。

CALL 存儲過程名(實參列表)

格式：

1、調用in模式的參數：

CALL sp1('值');

2、調用out模式的參數：

SET @name;
CALL sp1(@name);
SELECT @name;

3、調用inout模式的參數：

SET @name=值; #以變量的方式
CALL sp1(@name);
SELECT @name;

3.2 代碼舉例

舉例1：

DELIMITER //CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGINSELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //DELIMITER ;

調用存儲過程：

mysql> CALL CountProc (101, @num);
Query OK, 1 row affected (0.00 sec)

查看返回結果：

mysql> SELECT @num;

該存儲過程返回了指定 s_id=101 的水果商提供的水果種類，返回值存儲在num變量中，使用SELECT查看，返回結果為3。

舉例2： 創建存儲過程，實現累加運算，計算 1+2+…+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 ;

如果你用的是 Navicat 工具，那么在編寫存儲過程的時候，Navicat 會自動設置 DELIMITER 為其他符號，我們不需要再進行 DELIMITER 的操作。

直接使用 CALL add_num(50);即可。這里我傳入的參數為 50，也就是統計 1+2+…+50 的積累之和。

3.3 如何調試

在 MySQL 中，存儲過程不像普通的編程語言（比如 VC++、Java 等）那樣有專門的集成開發環境。因此，你可以通過 SELECT 語句，把程序執行的中間結果查詢出來，來調試一個 SQL 語句的正確性。調試成功之后，把 SELECT 語句后移到下一個 SQL 語句之后，再調試下一個 SQL 語句。這樣逐步推進，就可以完成對存儲過程中所有操作的調試了。當然，你也可以把存儲過程中的 SQL 語句復制出來，逐段單獨調試。

4. 存儲函數的使用

前面學習了很多函數，使用這些函數可以對數據進行的各種處理操作，極大地提高用戶對數據庫的管理效率。MySQL支持自定義函數，定義好之后，調用方式與調用MySQL預定義的系統函數一樣。

4.1 語法分析

學過的函數：LENGTH、SUBSTR、CONCAT等

語法格式：

CREATE FUNCTION 函數名(參數名 參數類型,...) 
RETURNS 返回值類型
[characteristics ...]
BEGIN函數體   #函數體中肯定有 RETURN 語句END

說明：

1、參數列表：指定參數為IN、OUT或INOUT只對PROCEDURE是合法的，FUNCTION中總是默認為IN參數。

2、RETURNS type 語句表示函數返回數據的類型；

RETURNS子句只能對FUNCTION做指定，對函數而言這是強制的。它用來指定函數的返回類型，而且函數體必須包含一個RETURN value語句。

3、characteristic 創建函數時指定的對函數的約束。取值與創建存儲過程時相同，這里不再贅述。

4、函數體也可以用BEGIN…END來表示SQL代碼的開始和結束。如果函數體只有一條語句，也可以省略BEGIN…END。

4.2 調用存儲函數

在MySQL中，存儲函數的使用方法與MySQL內部函數的使用方法是一樣的。換言之，用戶自己定義的存儲函數與MySQL內部函數是一個性質的。區別在于，存儲函數是用戶自己定義的，而內部函數是MySQL的開發者定義的。

SELECT 函數名(實參列表)

4.3 代碼舉例

舉例1：

創建存儲函數，名稱為email_by_name()，參數定義為空，該函數查詢Abel的email，并返回，數據類型為字符串型。

DELIMITER //CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGINRETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //DELIMITER ;

調用：

SELECT email_by_name();

舉例2：

創建存儲函數，名稱為email_by_id()，參數傳入emp_id，該函數查詢emp_id的email，并返回，數據類型為字符串型。

DELIMITER //CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGINRETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //DELIMITER ;

調用：

SET @emp_id = 102;
SELECT email_by_id(102);

舉例3：

創建存儲函數count_by_id()，參數傳入dept_id，該函數查詢dept_id部門的員工人數，并返回，數據類型為整型。

DELIMITER //CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INTLANGUAGE SQLNOT DETERMINISTICREADS SQL DATASQL SECURITY DEFINERCOMMENT '查詢部門平均工資'
BEGINRETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);END //DELIMITER ;

調用：

SET @dept_id = 50;
SELECT count_by_id(@dept_id);

注意：

若在創建存儲函數中報錯“you might want to use the less safe log_bin_trust_function_creators variable”，有兩種處理方法：

• 方式1：加上必要的函數特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

• 方式2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

4.4 對比存儲函數和存儲過程

	關鍵字	調用語法	返回值	應用場景
存儲過程	PROCEDURE	CALL 存儲過程()	理解為有0個或多個	一般用于更新
存儲函數	FUNCTION	SELECT 函數()	只能是一個	一般用于查詢結果為一個值并返回時

此外，存儲函數可以放在查詢語句中使用，存儲過程不行。反之，存儲過程的功能更加強大，包括能夠執行對表的操作（比如創建表，刪除表等）和事務操作，這些功能是存儲函數不具備的。

5. 存儲過程和函數的查看、修改、刪除

5.1 查看

創建完之后，怎么知道我們創建的存儲過程、存儲函數是否成功了呢？

MySQL存儲了存儲過程和函數的狀態信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看，也可直接從系統的information_schema數據庫中查詢。這里介紹3種方法。

1. 使用SHOW CREATE語句查看存儲過程和函數的創建信息

基本語法結構如下：

SHOW CREATE {PROCEDURE | FUNCTION} 存儲過程名或函數名

舉例：

SHOW CREATE FUNCTION test_db.CountProc \G

2. 使用SHOW STATUS語句查看存儲過程和函數的狀態信息

基本語法結構如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

這個語句返回子程序的特征，如數據庫、名字、類型、創建者及創建和修改日期。

[LIKE ‘pattern’]：匹配存儲過程或函數的名稱，可以省略。當省略不寫時，會列出MySQL數據庫中存在的所有存儲過程或函數的信息。
舉例：SHOW STATUS語句示例，代碼如下：

mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%' \G 
*************************** 1. row ***************************Db: test_dbName: SelectAllDataType: PROCEDUREDefiner: root@localhostModified: 2021-10-16 15:55:07Created: 2021-10-16 15:55:07Security_type: DEFINERComment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ciDatabase Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

3. 從information_schema.Routines表中查看存儲過程和函數的信息

MySQL中存儲過程和函數的信息存儲在information_schema數據庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲過程和函數的信息。其基本語法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存儲過程或函數的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

說明：如果在MySQL數據庫中存在存儲過程和函數名稱相同的情況，最好指定ROUTINE_TYPE查詢條件來指明查詢的是存儲過程還是函數。

舉例：從Routines表中查詢名稱為CountProc的存儲函數的信息，代碼如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id'　AND　ROUTINE_TYPE = 'FUNCTION' \G

5.2 修改

修改存儲過程或函數，不影響存儲過程或函數功能，只是修改相關特性。使用ALTER語句實現。當有需要修改內容的時候，可以刪除再造一個。

ALTER {PROCEDURE | FUNCTION} 存儲過程或函數的名 [characteristic ...]

其中，characteristic指定存儲過程或函數的特性，其取值信息與創建存儲過程、函數時的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL，表示子程序包含SQL語句，但不包含讀或寫數據的語句。
• NO SQL，表示子程序中不包含SQL語句。
• READS SQL DATA，表示子程序中包含讀數據的語句。
• MODIFIES SQL DATA，表示子程序中包含寫數據的語句。
• SQL SECURITY { DEFINER | INVOKER }，指明誰有權限來執行。
  • DEFINER，表示只有定義者自己才能夠執行。
  • INVOKER，表示調用者可以執行。
• COMMENT 'string'，表示注釋信息。

修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數使用ALTER FUNCTION語句。但是，這兩個語句的結構是一樣的，語句中的所有參數也是一樣的。

舉例1：

修改存儲過程CountProc的定義。將讀寫權限改為MODIFIES SQL DATA，并指明調用者可以執行，代碼如下：

ALTER　PROCEDURE　CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

查詢修改后的信息：

SELECT specific_name,sql_data_access,security_type
FROM information_schema.`ROUTINES`
WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';

結果顯示，存儲過程修改成功。從查詢的結果可以看出，訪問數據的權限（SQL_DATA_ ACCESS）已經變成MODIFIES SQL DATA，安全類型（SECURITY_TYPE）已經變成INVOKER。

舉例2：

修改存儲函數CountProc的定義。將讀寫權限改為READS SQL DATA，并加上注釋信息“FIND NAME”，代碼如下：

ALTER　FUNCTION　CountProc
READS SQL DATA
COMMENT 'FIND NAME' ;

存儲函數修改成功。從查詢的結果可以看出，訪問數據的權限（SQL_DATA_ACCESS）已經變成READS SQL DATA，函數注釋（ROUTINE_COMMENT）已經變成FIND NAME。

5.3 刪除

刪除存儲過程和函數，可以使用DROP語句，其語法結構如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存儲過程或函數的名

IF EXISTS：如果程序或函數不存儲，它可以防止發生錯誤，產生一個用SHOW WARNINGS查看的警告。

舉例：

DROP PROCEDURE CountProc;

DROP FUNCTION CountProc;

6. 關于存儲過程使用的爭議

盡管存儲過程有諸多優點，但是對于存儲過程的使用，一直都存在著很多爭議，比如有些公司對于大型項目要求使用存儲過程，而有些公司在手冊中明確禁止使用存儲過程，為什么這些公司對存儲過程的使用需求差別這么大呢？

6.1 優點

1、存儲過程可以一次編譯多次使用。 存儲過程只在創建時進行編譯，之后的使用都不需要重新編譯，這就提升了 SQL 的執行效率。

2、可以減少開發工作量。 將代碼封裝成模塊，實際上是編程的核心思想之一，這樣可以把復雜的問題拆解成不同的模塊，然后模塊之間可以重復使用，在減少開發工作量的同時，還能保證代碼的結構清晰。

3、存儲過程的安全性強。 我們在設定存儲過程的時候可以設置對用戶的使用權限，這樣就和視圖一樣具有較強的安全性。

4、可以減少網絡傳輸量。 因為代碼封裝到存儲過程中，每次使用只需要調用存儲過程即可，這樣就減少了網絡傳輸量。

5、良好的封裝性。 在進行相對復雜的數據庫操作時，原本需要使用一條一條的 SQL 語句，可能要連接多次數據庫才能完成的操作，現在變成了一次存儲過程，只需要連接一次即可。

6.2 缺點

基于上面這些優點，不少大公司都要求大型項目使用存儲過程，比如微軟、IBM 等公司。但是國內的阿里并不推薦開發人員使用存儲過程，這是為什么呢？

阿里開發規范

【強制】禁止使用存儲過程，存儲過程難以調試和擴展，更沒有移植性。

存儲過程雖然有諸如上面的好處，但缺點也是很明顯的。

1、可移植性差。 存儲過程不能跨數據庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲過程，在換成其他數據庫時都需要重新編寫。

2、調試困難。 只有少數 DBMS 支持存儲過程的調試。對于復雜的存儲過程來說，開發和維護都不容易。雖然也有一些第三方工具可以對存儲過程進行調試，但要收費。

3、存儲過程的版本管理很困難。 比如數據表索引發生變化了，可能會導致存儲過程失效。我們在開發軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩。

4、它不適合高并發的場景。 高并發的場景需要減少數據庫的壓力，有時數據庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護，增加數據庫的壓力，顯然就不適用了。

小結：

存儲過程既方便，又有局限性。盡管不同的公司對存儲過程的態度不一，但是對于我們開發人員來說，不論怎樣，掌握存儲過程都是必備的技能之一。

7. 第十五章練習

第十五章練習見“章節練習”文件夾，可作為知識點及面試題使用