MySQL從5.0版本開始支持存儲過程和函數(shù)
存儲過程和函數(shù)能夠?qū)?fù)雜的SQL邏輯封裝在一起，應(yīng)用程序無須關(guān)注存儲過程和函數(shù)內(nèi)部復(fù)雜的SQL邏輯，而只需要簡單地調(diào)用存儲過程和函數(shù)即可

函數(shù)：自定義函數(shù)
存儲過程：函數(shù)有返回值，存儲過程沒有返回值

1. 存儲過程概述

1.1 理解

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

執(zhí)行過程：存儲過程預(yù)先存儲在 MySQL 服務(wù)器上，需要執(zhí)行的時候，客戶端只需要向服務(wù)器端發(fā)出調(diào)用存儲過程的命令，服務(wù)器端就可以把預(yù)先存儲好的這一系列 SQL 語句全部執(zhí)行

好處：

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

和視圖、函數(shù)的對比：
相同點：清晰、安全，還可以減少網(wǎng)絡(luò)傳輸量
不同點：視圖是虛擬表 ， 通常不對底層數(shù)據(jù)表直接操作，而存儲過程是程序化的 SQL，可以直接操作底層數(shù)據(jù)表 ，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復(fù)雜的數(shù)據(jù)處理

存儲過程與函數(shù)的區(qū)別：存儲過程沒有返回值

1.2 分類

存儲過程的參數(shù)類型可以是IN、OUT和INOUT

根據(jù)這點分類如下：

1. 沒有參數(shù)（無參數(shù)無返回）
2. 僅僅帶 IN 類型（有參數(shù)無返回）
3. 僅僅帶 OUT 類型（無參數(shù)有返回）
4. 既帶 IN 又帶 OUT（有參數(shù)有返回）
5. 帶 INOUT（有參數(shù)有返回）

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

2. 創(chuàng)建存儲過程

2.1 語法分析

語法：

CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名參數(shù)類型,...) [characteristics ...] 
BEGIN 
	存儲過程體 
END
# 調(diào)用
CALL 存儲過程名();

說明：

1. 參數(shù)前面的符號的意思
   • IN ：當前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)ⅲ?存儲過程只是讀取這個參數(shù)的值
     注意：如果沒有定義參數(shù)種類， 默認就是 IN ，表示輸入?yún)?shù)
   • OUT ：當前參數(shù)為輸出參數(shù)，也就是表示出參； 執(zhí)行完成之后，調(diào)用這個存儲過程的客戶端或者應(yīng)用程序就可以讀取這個參數(shù)返回的值了
   • INOUT ：當前參數(shù)既可以為輸入?yún)?shù)，也可以為輸出參數(shù)
2. 形參類型可以是 MySQL數(shù)據(jù)庫中的任意類型
3. characteristics 表示創(chuàng)建存儲過程時指定的對存儲過程的約束條件，其取值信息如下：
   • LANGUAGE SQL ：說明存儲過程執(zhí)行體是由SQL語句組成的，當前系統(tǒng)支持的語言為SQL
   • [NOT] DETERMINISTIC ：指明存儲過程執(zhí)行的結(jié)果是否確定
     • DETERMINISTIC表示結(jié)果是確定的
       • 每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出
     • NOT DETERMINISTIC表示結(jié)果是不確定的
       • 每次執(zhí)行存儲過程時，相同的輸入可能得到不同的輸出
     • 如果沒有指定任意一個值，默認為NOT DETERMINISTIC
   • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL語句的限制
     • CONTAINS SQL表示當前存儲過程的子程序包含SQL語句，但是并不包含讀寫數(shù)據(jù)的SQL語句
     • NO SQL表示當前存儲過程的子程序中不包含任何SQL語句
     • READS SQL DATA表示當前存儲過程的子程序中包含讀數(shù)據(jù)的SQL語句
     • MODIFIES SQL DATA表示當前存儲過程的子程序中包含寫數(shù)據(jù)的SQL語句
     • 默認情況下，系統(tǒng)會指定為==CONTAINS SQL==
   • SQL SECURITY { DEFINER | INVOKER } ：執(zhí)行當前存儲過程的權(quán)限，即指明哪些用戶能夠執(zhí)行當前存儲過程
     • DEFINER 表示只有當前存儲過程的創(chuàng)建者或者定義者才能執(zhí)行當前存儲過程
     • INVOKER 表示擁有當前存儲過程的訪問權(quán)限的用戶能夠執(zhí)行當前存儲過程
     • 如果沒有設(shè)置相關(guān)的值，則MySQL默認指定值為DEFINER
   • COMMENT 'string' ：注釋信息，可以用來描述存儲過程

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

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

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

5. 需要設(shè)置新的結(jié)束標記
   DELIMITER 新的結(jié)束標記
   • MySQL默認的語句結(jié)束符號為分號;，為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用 DELIMITER改變存儲過程的結(jié)束符
   • 比如：
     • “DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲過程
     • 存儲過程定義完畢之后再使用“DELIMITER ;”恢復(fù)默認結(jié)束符
   • 當使用DELIMITER命令時，應(yīng)該避免使用反斜杠（‘\’）字符，因為反斜線是MySQL的轉(zhuǎn)義字符
   • 注意：如果用的是 Navicat 工具，那么在編寫存儲過程的時候，Navicat 會自動設(shè)置 DELIMITER 為其他符號， 不需要再進行 DELIMITER 的操作
     eg：

DELIMITER $ 
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名參數(shù)類型,...)
[characteristics ...]
BEGIN 
	sql語句1; 
	sql語句2; 
END $
DELIMITER ;

2.2 代碼舉例

2.2.1 無參數(shù)（無參無返回）

存儲過程體就是普通的查詢語句
使用CALL 調(diào)用

舉例：創(chuàng)建存儲過程select_all_data()，查看 emps 表的所有數(shù)據(jù)

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
END $
DELIMITER ;
# 調(diào)用
CALL select_all_data();

2.2.2 帶OUT（無參有返回）

SELECT 的結(jié)果給了變量
存儲過程體SELECT 后要輸出的字段后寫INTO 變量名
使用CALL 調(diào)用，要傳入 @變量名
使用SELECT 查看變量值

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

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN 
	SELECT MIN(salary) INTO ms 
	FROM emps; 
END //
DELIMITER ;
# 調(diào)用
CALL show_min_salary(@ms);
# 查看變量值
SELECT @ms;

2.2.3 帶IN（有參無返回）

就是傳了個參數(shù)，供篩選等使用
調(diào)用時直接傳入?yún)?shù)的值，或者傳入@ 變量名

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

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN 
	SELECT salary 
	FROM emps 
	WHERE ename = empname; 
END //
DELIMITER ;
# 調(diào)用方式1
CALL show_someone_salary('Abel');
# 調(diào)用方式2
SET @empname = 'Abel';
CALL show_someone_salary(@empname);
# 賦值用 = 或者 := 都可以

2.2.4 帶IN和OUT（有參有返回）

前面兩種的集合

舉例：創(chuàng)建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用IN參數(shù)empname 輸入員工姓名，用OUT參數(shù)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 ;

2.2.5 帶INOUT（有參數(shù)又返回）

參數(shù)、返回值給的參數(shù)是同一個，相當于修改了參數(shù)

舉例：創(chuàng)建存儲過程show_mgr_name()，查詢某個員工領(lǐng)導(dǎo)的姓名，并用INOUT參數(shù)“empname”輸入員工姓名，輸出領(lǐng)導(dǎo)的姓名

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. 調(diào)用存儲過程

3.1 調(diào)用格式

存儲過程有多種調(diào)用方法
存儲過程必須使用CALL語句調(diào)用，并且存儲過程和數(shù)據(jù)庫相關(guān)，如果要執(zhí)行其他數(shù)據(jù)庫中的存儲過程，需要指定數(shù)據(jù)庫名稱
例如CALL dbname.procname

格式：

• 調(diào)用in模式的參數(shù)：

CALL sp1('值');

• 調(diào)用out模式的參數(shù)：

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

• 調(diào)用inout模式的參數(shù)：

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

3.2 代碼舉例

創(chuàng)建存儲過程，實現(xiàn)累加運算，計算 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 ;

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

3.3 如何調(diào)試

在 MySQL 中，存儲過程不像普通的編程語言（比如 VC++、Java 等）那樣有專門的集成開發(fā)環(huán)境

因此，你可以通過 SELECT 語句，把程序執(zhí)行的中間結(jié)果查詢出來，來調(diào)試一個 SQL 語句的正確性（print大法）
調(diào)試成功之后，把 SELECT 語句后移到下一個 SQL 語句之后，再調(diào)試下一個 SQL 語句

這樣逐步推進 ，就可以完成對存儲過程中所有操作的調(diào)試了

也可以把存儲過程中的 SQL 語句復(fù)制出來，逐段單獨調(diào)試

調(diào)試很麻煩，意味著很難維護，所以生產(chǎn)實踐中通常不推薦使用存儲過程

4. 存儲函數(shù)的使用

之前學習過很多函數(shù)，使用這些函數(shù)可以對數(shù)據(jù)進行的各種處理操作，極大地提高用戶對數(shù)據(jù)庫的管理效率
MySQL支持自定義函數(shù)，定義好之后，調(diào)用方式與調(diào)用MySQL預(yù)定義的系統(tǒng)函數(shù)一樣

4.1 語法分析

語法格式：

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

說明：

1. 參數(shù)列表：
   指定參數(shù)為IN、OUT或INOUT只對PROCEDURE是合法的，F(xiàn)UNCTION中總是默認為IN參數(shù)
2. RETURNS type 語句表示函數(shù)返回數(shù)據(jù)的類型
   RETURNS子句只能對FUNCTION做指定，對函數(shù)而言這是強制的
   它用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個 RETURN value 語句
3. characteristic 創(chuàng)建函數(shù)時指定的對函數(shù)的約束
   取值與創(chuàng)建存儲過程時相同
4. 函數(shù)體也可以用BEGIN…END來表示SQL代碼的開始和結(jié)束
   如果函數(shù)體只有一條語句，也可以省略 BEGIN…END

4.2 調(diào)用存儲函數(shù)

與MySQL內(nèi)部函數(shù)的使用方法是一致

區(qū)別在于，存儲函數(shù)是用戶自己定義的，而內(nèi)部函數(shù)是MySQL 的開發(fā)者定義的

SELECT 函數(shù)名(實參列表)

4.3 代碼舉例

舉例1： 創(chuàng)建存儲函數(shù)，名稱為email_by_name()，參數(shù)定義為空，該函數(shù)查詢Abel的email，并返回，數(shù)據(jù)類型為字符串型

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

調(diào)用：

SELECT email_by_name();

注意：

• 聲明返回值類型時，用RETURNS 注意有S，而函數(shù)體里的沒有
• return 語句中不能存在;，分好應(yīng)該寫在return的括號外
• 函數(shù)的參數(shù)前不用寫IN、OUT這些
• 若在創(chuàng)建存儲函數(shù)中報錯“ you might want to use the less safe log_bin_trust_function_creators variable ”
• 有兩種處理方法：
• 方式1：加上必要的函數(shù)特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
• 方式2：mysql> SET GLOBAL log_bin_trust_function_creators = 1;

4.4 對比存儲函數(shù)和存儲過程

存儲過程不是返回值，是改的變量的值

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

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

5.1 查看

創(chuàng)建完之后，怎么知道我們創(chuàng)建的存儲過程、存儲函數(shù)是否成功了呢？

MySQL存儲了存儲過程和函數(shù)的狀態(tài)信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看，也可直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢

這里介紹3種方法：

1. 使用SHOW CREATE語句查看存儲過程和函數(shù)的創(chuàng)建信息
   基本語法結(jié)構(gòu)：
   SHOW CREATE {PROCEDURE | FUNCTION} 存儲過程名或函數(shù)名
   舉例：
   SHOW CREATE FUNCTION test_db.CountProc \G
2. 使用SHOW STATUS語句查看存儲過程和函數(shù)的狀態(tài)信息
   基本語法結(jié)構(gòu):
   SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
   這個語句返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期
   [LIKE 'pattern']：匹配存儲過程或函數(shù)的名稱，可以省略，當省略不寫時，會列出MySQL數(shù)據(jù)庫中存在的所有存儲過程或函數(shù)的信息
3. 從information_schema.Routines表中查看存儲過程和函數(shù)的信息
   MySQL中存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中，因此可以通過查詢該表的記錄來查詢存儲過程和函數(shù)的信息
   基本語法形式：
   SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存儲過程或函數(shù)的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
   說明：如果在MySQL數(shù)據(jù)庫中存在存儲過程和函數(shù)名稱相同的情況，最好指定ROUTINE_TYPE查詢條件來指明查詢的是存儲過程還是函數(shù)

5.2 修改

修改存儲過程或函數(shù)，不影響存儲過程或函數(shù)功能，只是修改相關(guān)特性

使用ALTER語句實現(xiàn)

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

其中，characteristic指定存儲過程或函數(shù)的特性，其取值信息與創(chuàng)建存儲過程、函數(shù)時的取值信息略有不同

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

• CONTAINS SQL ，表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句
• NO SQL ，表示子程序中不包含SQL語句
• READS SQL DATA ，表示子程序中包含讀數(shù)據(jù)的語句
• MODIFIES SQL DATA ，表示子程序中包含寫數(shù)據(jù)的語句
• SQL SECURITY { DEFINER | INVOKER } ，指明誰有權(quán)限來執(zhí)行
  • DEFINER ，表示只有定義者自己才能夠執(zhí)行
  • INVOKER ，表示調(diào)用者可以執(zhí)行
• COMMENT 'string' ，表示注釋信息

修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數(shù)使用ALTER FUNCTION語句
但是，這兩個語句的結(jié)構(gòu)是一樣的，語句中的所有參數(shù)也是一樣的

舉例：
修改存儲過程CountProc的定義。將讀寫權(quán)限改為MODIFIES SQL DATA，并指明調(diào)用者可以執(zhí)行，代碼如下：

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';

5.3 刪除

刪除存儲過程和函數(shù)

可以使用DROP語句

語法結(jié)構(gòu)：

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

IF EXISTS：如果程序或函數(shù)不存儲，它可以防止發(fā)生錯誤，產(chǎn)生一個用SHOW WARNINGS查看的警告

舉例：
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

6. 關(guān)于存儲過程使用的爭議

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

6.1 優(yōu)點

1. 存儲過程可以一次編譯多次使用
   存儲過程只在創(chuàng)建時進行編譯，之后的使用都不需要重新編譯， 這就提升了 SQL 的執(zhí)行效率
2. 可以減少開發(fā)工作量
   將代碼封裝成模塊，實際上是編程的核心思想之一，這樣可以把復(fù)雜的問題拆解成不同的模塊，然后模塊之間可以重復(fù)使用 ，在減少開發(fā)工作量的同時，還能保證代碼的結(jié)構(gòu)清晰
3. 存儲過程的安全性強
   我們在設(shè)定存儲過程的時候可以==設(shè)置對用戶的使用權(quán)限 ==，這樣就和視圖一樣具有較強的安全性
4. 可以減少網(wǎng)絡(luò)傳輸量
   因為代碼封裝到存儲過程中，每次使用只需要調(diào)用存儲過程即可，這樣就減少了網(wǎng)絡(luò)傳輸量
5. 良好的封裝性
   在進行相對復(fù)雜的數(shù)據(jù)庫操作時，原本需要使用一條一條的 SQL 語句，可能要連接多次數(shù)據(jù)庫才能完成的操作，現(xiàn)在變成了一次存儲過程，只需要連接一次即可

6.2 缺點

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

[!阿里開發(fā)規(guī)范]
【強制】禁止使用存儲過程，存儲過程難以調(diào)試和擴展，更沒有移植性

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

1. 可移植性差
   存儲過程不能跨數(shù)據(jù)庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲過程，在換成其他數(shù)據(jù)庫時都需要重新編寫
2. 調(diào)試困難
   只有少數(shù) DBMS 支持存儲過程的調(diào)試。對于復(fù)雜的存儲過程來說，開發(fā)和維護都不容易。雖然也有一些第三方工具可以對存儲過程進行調(diào)試，但要收費
3. 存儲過程的版本管理很困難
   比如數(shù)據(jù)表索引發(fā)生變化了，可能會導(dǎo)致存儲過程失效。我們在開發(fā)軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩
4. 它不適合高并發(fā)的場景
   高并發(fā)的場景需要減少數(shù)據(jù)庫的壓力，有時數(shù)據(jù)庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護， 增加數(shù)據(jù)庫的壓力 ，顯然就不適用了

小結(jié)： 存儲過程既方便，又有局限性
盡管不同的公司對存儲過程的態(tài)度不一，但是對于我們開發(fā)人員來說， 不論怎樣，掌握存儲過程都是必備的技能之一

到此這篇關(guān)于MySQL 存儲例程(過程與函數(shù))的文章就介紹到這了,更多相關(guān)mysql存儲過程與函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論