這是Mysql系列第17篇。

環(huán)境：mysql5.7.25，cmd命令中進(jìn)行演示。

代碼中被[]包含的表示可選，|符號(hào)分開的表示可選其一。

需求背景介紹

線上程序有時(shí)候出現(xiàn)問題導(dǎo)致數(shù)據(jù)錯(cuò)誤的時(shí)候，如果比較緊急，我們可以寫一個(gè)存儲(chǔ)來快速修復(fù)這塊的數(shù)據(jù)，然后再去修復(fù)程序，這種方式我們用到過不少。

存儲(chǔ)過程相對(duì)于java程序?qū)τ趈ava開發(fā)來說，可能并不是太好維護(hù)以及閱讀，所以不建議在程序中去調(diào)用存儲(chǔ)過程做一些業(yè)務(wù)操作。

關(guān)于自定義函數(shù)這塊，若mysql內(nèi)部自帶的一些函數(shù)無法滿足我們的需求的時(shí)候，我們可以自己開發(fā)一些自定義函數(shù)來使用。

所以建議大家掌握mysql中存儲(chǔ)過程和自定義函數(shù)這塊的內(nèi)容。

本文內(nèi)容

• 詳解存儲(chǔ)過程的使用

• 詳解自定義函數(shù)的使用

準(zhǔn)備數(shù)據(jù)

/*建庫javacode2018*/
drop?database?if?exists?javacode2018;
create?database?javacode2018;/*切換到j(luò)avacode2018庫*/
use?javacode2018;/*建表test1*/
DROP?TABLE?IF?EXISTS?t_user;
CREATE?TABLE?t_user?(id???INT?NOT?NULL?PRIMARY?KEY?COMMENT?'編號(hào)',age??SMALLINT?UNSIGNED?NOT?NULL?COMMENT?'年齡',name?VARCHAR(16)?NOT?NULL?COMMENT?'姓名'
)?COMMENT?'用戶表';

存儲(chǔ)過程

概念

一組預(yù)編譯好的sql語句集合，理解成批處理語句。

好處：

• 提高代碼的重用性

• 簡化操作

• 減少編譯次數(shù)并且減少和數(shù)據(jù)庫服務(wù)器連接的次數(shù)，提高了效率。

創(chuàng)建存儲(chǔ)過程

create?procedure?存儲(chǔ)過程名([參數(shù)模式]?參數(shù)名?參數(shù)類型)
begin存儲(chǔ)過程體
end

參數(shù)模式有3種：

in：該參數(shù)可以作為輸入，也就是該參數(shù)需要調(diào)用方傳入值。

out：該參數(shù)可以作為輸出，也就是說該參數(shù)可以作為返回值。

inout：該參數(shù)既可以作為輸入也可以作為輸出，也就是說該參數(shù)需要在調(diào)用的時(shí)候傳入值，又可以作為返回值。

參數(shù)模式默認(rèn)為IN。

一個(gè)存儲(chǔ)過程可以有多個(gè)輸入、多個(gè)輸出、多個(gè)輸入輸出參數(shù)。

調(diào)用存儲(chǔ)過程

call?存儲(chǔ)過程名稱(參數(shù)列表);

注意：調(diào)用存儲(chǔ)過程關(guān)鍵字是call。

刪除存儲(chǔ)過程

drop?procedure?[if?exists]?存儲(chǔ)過程名稱;

存儲(chǔ)過程只能一個(gè)個(gè)刪除，不能批量刪除。

if exists：表示存儲(chǔ)過程存在的情況下刪除。

修改存儲(chǔ)過程

存儲(chǔ)過程不能修改，若涉及到修改的，可以先刪除，然后重建。

查看存儲(chǔ)過程

show?create?procedure?存儲(chǔ)過程名稱;

可以查看存儲(chǔ)過程詳細(xì)創(chuàng)建語句。

示例

示例1：空參列表

創(chuàng)建存儲(chǔ)過程

/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*如果存儲(chǔ)過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc1;
/*創(chuàng)建存儲(chǔ)過程proc1*/
CREATE?PROCEDURE?proc1()BEGININSERT?INTO?t_user?VALUES?(1,30,'路人甲Java');INSERT?INTO?t_user?VALUES?(2,50,'劉德華');END?$/*將結(jié)束符置為;*/
DELIMITER?;

delimiter用來設(shè)置結(jié)束符，當(dāng)mysql執(zhí)行腳本的時(shí)候，遇到結(jié)束符的時(shí)候，會(huì)把結(jié)束符前面的所有語句作為一個(gè)整體運(yùn)行，存儲(chǔ)過程中的腳本有多個(gè)sql，但是需要作為一個(gè)整體運(yùn)行，所以此處用到了delimiter。

mysql默認(rèn)結(jié)束符是分號(hào)。

上面存儲(chǔ)過程中向t_user表中插入了2條數(shù)據(jù)。

調(diào)用存儲(chǔ)過程：

CALL?proc1();

驗(yàn)證效果：

mysql>?select?*?from?t_user;
+----+-----+---------------+
|?id?|?age?|?name??????????|
+----+-----+---------------+
|??1?|??30?|?路人甲Java????|
|??2?|??50?|?劉德華????????|
+----+-----+---------------+
2?rows?in?set?(0.00?sec)

存儲(chǔ)過程調(diào)用成功，test1表成功插入了2條數(shù)據(jù)。

示例2：帶in參數(shù)的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程：

/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*如果存儲(chǔ)過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc2;
/*創(chuàng)建存儲(chǔ)過程proc2*/
CREATE?PROCEDURE?proc2(id?int,age?int,in?name?varchar(16))BEGININSERT?INTO?t_user?VALUES?(id,age,name);END?$/*將結(jié)束符置為;*/
DELIMITER?;

調(diào)用存儲(chǔ)過程：

/*創(chuàng)建了3個(gè)自定義變量*/
SELECT?@id:=3,@age:=56,@name:='張學(xué)友';
/*調(diào)用存儲(chǔ)過程*/
CALL?proc2(@id,@age,@name);

驗(yàn)證效果：

mysql>?select?*?from?t_user;
+----+-----+---------------+
|?id?|?age?|?name??????????|
+----+-----+---------------+
|??1?|??30?|?路人甲Java????|
|??2?|??50?|?劉德華????????|
|??3?|??56?|?張學(xué)友????????|
+----+-----+---------------+
3?rows?in?set?(0.00?sec)

張學(xué)友插入成功。

示例3：帶out參數(shù)的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程：

delete?a?from?t_user?a?where?a.id?=?4;
/*如果存儲(chǔ)過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc3;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過程proc3*/
CREATE?PROCEDURE?proc3(id?int,age?int,in?name?varchar(16),out?user_count?int,out?max_id?INT)BEGININSERT?INTO?t_user?VALUES?(id,age,name);/*查詢出t_user表的記錄，放入user_count中,max_id用來存儲(chǔ)t_user中最小的id*/SELECT?COUNT(*),max(id)?into?user_count,max_id?from?t_user;END?$/*將結(jié)束符置為;*/
DELIMITER?;

proc3中前2個(gè)參數(shù)，沒有指定參數(shù)模式，默認(rèn)為in。

調(diào)用存儲(chǔ)過程：

/*創(chuàng)建了3個(gè)自定義變量*/
SELECT?@id:=4,@age:=55,@name:='郭富城';
/*調(diào)用存儲(chǔ)過程*/
CALL?proc3(@id,@age,@name,@user_count,@max_id);

驗(yàn)證效果：

mysql>?select?@user_count,@max_id;
+-------------+---------+
|?@user_count?|?@max_id?|
+-------------+---------+
|???????????4?|???????4?|
+-------------+---------+
1?row?in?set?(0.00?sec)

示例4：帶inout參數(shù)的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程：

/*如果存儲(chǔ)過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc4;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過程proc4*/
CREATE?PROCEDURE?proc4(INOUT?a?int,INOUT?b?int)BEGINSET?a?=?a*2;select?b*2?into?b;END?$/*將結(jié)束符置為;*/
DELIMITER?;

調(diào)用存儲(chǔ)過程：

/*創(chuàng)建了2個(gè)自定義變量*/
set?@a=10,@b:=20;
/*調(diào)用存儲(chǔ)過程*/
CALL?proc4(@a,@b);

驗(yàn)證效果：

mysql>?SELECT?@a,@b;
+------+------+
|?@a???|?@b???|
+------+------+
|???20?|???40?|
+------+------+
1?row?in?set?(0.00?sec)

上面的兩個(gè)自定義變量@a、@b作為入?yún)?#xff0c;然后在存儲(chǔ)過程內(nèi)部進(jìn)行了修改，又作為了返回值。

示例5：查看存儲(chǔ)過程

mysql>?show?create?procedure?proc4;
+-------+-------+-------+-------+-------+-------+
|?Procedure?|?sql_mode?|?Create?Procedure?|?character_set_client?|?collation_connection?|?Database?Collation?|
+-------+-------+-------+-------+-------+-------+
|?proc4?????|?ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION?|?CREATE?DEFINER=`root`@`localhost`?PROCEDURE?`proc4`(INOUT?a?int,INOUT?b?int)
BEGINSET?a?=?a*2;select?b*2?into?b;END?|?utf8?????????????????|?utf8_general_ci??????|?utf8_general_ci????|
+-------+-------+-------+-------+-------+-------+
1?row?in?set?(0.00?sec)

函數(shù)

概念

一組預(yù)編譯好的sql語句集合，理解成批處理語句。類似于java中的方法，但是必須有返回值。

創(chuàng)建函數(shù)

create?function?函數(shù)名(參數(shù)名稱?參數(shù)類型)
returns?返回值類型
begin函數(shù)體
end

參數(shù)是可選的。

返回值是必須的。

調(diào)用函數(shù)

select?函數(shù)名(實(shí)參列表);

刪除函數(shù)

drop?function?[if?exists]?函數(shù)名;

查看函數(shù)詳細(xì)

show?create?function?函數(shù)名;

示例

示例1：無參函數(shù)

創(chuàng)建函數(shù)：

/*刪除fun1*/
DROP?FUNCTION?IF?EXISTS?fun1;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建函數(shù)*/
CREATE?FUNCTION?fun1()returns?INTBEGINDECLARE?max_id?int?DEFAULT?0;SELECT?max(id)?INTO?max_id?FROM?t_user;return?max_id;END?$
/*設(shè)置結(jié)束符為;*/
DELIMITER?;

調(diào)用看效果：

mysql>?SELECT?fun1();
+--------+
|?fun1()?|
+--------+
|??????4?|
+--------+
1?row?in?set?(0.00?sec)

示例2：有參函數(shù)

創(chuàng)建函數(shù)：

/*刪除函數(shù)*/
DROP?FUNCTION?IF?EXISTS?get_user_id;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建函數(shù)*/
CREATE?FUNCTION?get_user_id(v_name?VARCHAR(16))returns?INTBEGINDECLARE?r_id?int;SELECT?id?INTO?r_id?FROM?t_user?WHERE?name?=?v_name;return?r_id;END?$
/*設(shè)置結(jié)束符為;*/
DELIMITER?;

運(yùn)行看效果：

mysql>?SELECT?get_user_id(name)?from?t_user;
+-------------------+
|?get_user_id(name)?|
+-------------------+
|?????????????????1?|
|?????????????????2?|
|?????????????????3?|
|?????????????????4?|
+-------------------+
4?rows?in?set?(0.00?sec)

存儲(chǔ)過程和函數(shù)的區(qū)別

存儲(chǔ)過程的關(guān)鍵字為procedure，返回值可以有多個(gè)，調(diào)用時(shí)用call，一般用于執(zhí)行比較復(fù)雜的的過程體、更新、創(chuàng)建等語句。

函數(shù)的關(guān)鍵字為function，返回值必須有一個(gè)，調(diào)用用select，一般用于查詢單個(gè)值并返回。

	存儲(chǔ)過程	函數(shù)
返回值	可以有0個(gè)或者多個(gè)	必須有一個(gè)
關(guān)鍵字	procedure	function
調(diào)用方式	call	select