存儲過程與函數
就是巨集Macro啦，這部分我暫時用不到，先直接貼上筆記過眼完事了，以後有用到再回來補
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 新的結束標記
因為MySQL預設的語句結束符號為分號‘;’。為了避免與存儲過程中SQL語句結束符相衝突，需要使用DELIMITER改變存儲過程的結束符。
比如：“DELIMITER //“語句的作用是將MySQL的結束符設置為//，並以"END //“結束存儲過程。存儲程序定義完畢之後再使用"DELIMITER ;“恢復預設結束符。DELIMITER也可以指定其他符號作為結束符。
當使用DELIMITER命令時，應該避免使用反斜線（‘\’）字元，因為反斜線是MySQL的轉義字元。
示例：
DELIMITER $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名 參數類型,...)
[characteristics ...]
BEGIN
sql語句1;
sql語句2;
END $
2.2 代碼舉例
舉例1：創建存儲過程select_all_data()，查看 emps 表的所有資料
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;
舉例2：創建存儲過程avg_employee_salary()，返回所有員工的平均工資
DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;
舉例3：創建存儲過程show_max_salary()，用來查看"emps"表的最高薪資值。
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查看最高薪資'
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER ;
舉例4：創建存儲過程show_min_salary()，查看"emps"表的最低薪資值。並將最低薪資通過OUT參數"ms"輸出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT 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))
BEGIN
SELECT 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)
BEGIN
SELECT 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))
BEGIN
SELECT ename INTO empname FROM emps
WHERE 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)
BEGIN
SELECT 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)
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 ;
如果你用的是 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
BEGIN
RETURN (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
BEGIN
RETURN (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 INT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT '查詢部門平均工資'
BEGIN
RETURN (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_db
Name: SelectAllData
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-16 15:55:07
Created: 2021-10-16 15:55:07
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database 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、它不適合高併發的場景。**高併發的場景需要減少資料庫的壓力，有時資料庫會採用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護，增加資料庫的壓力，顯然就不適用了。
小結：
存儲過程既方便，又有局限性。儘管不同的公司對存儲過程的態度不一，但是對於我們開發人員來說，不論怎樣，掌握存儲過程都是必備的技能之一。
上次修改於 2021-12-26