這是Mysql系列第17篇。
環(huán)境：mysql5.7.25，cmd命令中進(jìn)行演示。
代碼中被[]包含的表示可選，|符號分開的表示可選其一。
需求背景介紹
線上程序有時候出現(xiàn)問題導(dǎo)致數(shù)據(jù)錯誤的時候，如果比較緊急，我們可以寫一個存儲來快速修復(fù)這塊的數(shù)據(jù)，然后再去修復(fù)程序，這種方式我們用到過不少。
存儲過程相對于java程序?qū)τ趈ava開發(fā)來說，可能并不是太好維護(hù)以及閱讀，所以不建議在程序中去調(diào)用存儲過程做一些業(yè)務(wù)操作。
關(guān)于自定義函數(shù)這塊，若mysql內(nèi)部自帶的一些函數(shù)無法滿足我們的需求的時候，我們可以自己開發(fā)一些自定義函數(shù)來使用。
所以建議大家掌握mysql中存儲過程和自定義函數(shù)這塊的內(nèi)容。
本文內(nèi)容
-
詳解存儲過程的使用
-
詳解自定義函數(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?'編號',
??age??SMALLINT?UNSIGNED?NOT?NULL?COMMENT?'年齡',
??name?VARCHAR(16)?NOT?NULL?COMMENT?'姓名'
)?COMMENT?'用戶表';
存儲過程
概念
一組預(yù)編譯好的sql語句集合，理解成批處理語句。
好處：
-
提高代碼的重用性
-
簡化操作
-
減少編譯次數(shù)并且減少和數(shù)據(jù)庫服務(wù)器連接的次數(shù)，提高了效率。
創(chuàng)建存儲過程
create?procedure?存儲過程名([參數(shù)模式]?參數(shù)名?參數(shù)類型)
begin
????存儲過程體
end
參數(shù)模式有3種：
in：該參數(shù)可以作為輸入，也就是該參數(shù)需要調(diào)用方傳入值。
out：該參數(shù)可以作為輸出，也就是說該參數(shù)可以作為返回值。
inout：該參數(shù)既可以作為輸入也可以作為輸出，也就是說該參數(shù)需要在調(diào)用的時候傳入值，又可以作為返回值。
參數(shù)模式默認(rèn)為IN。
一個存儲過程可以有多個輸入、多個輸出、多個輸入輸出參數(shù)。
調(diào)用存儲過程
call?存儲過程名稱(參數(shù)列表);
注意：調(diào)用存儲過程關(guān)鍵字是
call。
刪除存儲過程
drop?procedure?[if?exists]?存儲過程名稱;
存儲過程只能一個個刪除，不能批量刪除。
if exists：表示存儲過程存在的情況下刪除。
修改存儲過程
存儲過程不能修改，若涉及到修改的，可以先刪除，然后重建。
查看存儲過程
show?create?procedure?存儲過程名稱;
可以查看存儲過程詳細(xì)創(chuàng)建語句。
示例
示例1：空參列表
創(chuàng)建存儲過程
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*如果存儲過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc1;
/*創(chuàng)建存儲過程proc1*/
CREATE?PROCEDURE?proc1()
??BEGIN
????INSERT?INTO?t_user?VALUES?(1,30,'路人甲Java');
????INSERT?INTO?t_user?VALUES?(2,50,'劉德華');
??END?$
/*將結(jié)束符置為;*/
DELIMITER?;
delimiter用來設(shè)置結(jié)束符，當(dāng)mysql執(zhí)行腳本的時候，遇到結(jié)束符的時候，會把結(jié)束符前面的所有語句作為一個整體運(yùn)行，存儲過程中的腳本有多個sql，但是需要作為一個整體運(yùn)行，所以此處用到了delimiter。
mysql默認(rèn)結(jié)束符是分號。
上面存儲過程中向t_user表中插入了2條數(shù)據(jù)。
調(diào)用存儲過程：
CALL?proc1();
驗證效果：
mysql>?select?*?from?t_user;
+----+-----+---------------+
|?id?|?age?|?name??????????|
+----+-----+---------------+
|??1?|??30?|?路人甲Java????|
|??2?|??50?|?劉德華????????|
+----+-----+---------------+
2?rows?in?set?(0.00?sec)
存儲過程調(diào)用成功，test1表成功插入了2條數(shù)據(jù)。
示例2：帶in參數(shù)的存儲過程
創(chuàng)建存儲過程：
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*如果存儲過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc2;
/*創(chuàng)建存儲過程proc2*/
CREATE?PROCEDURE?proc2(id?int,age?int,in?name?varchar(16))
??BEGIN
????INSERT?INTO?t_user?VALUES?(id,age,name);
??END?$
/*將結(jié)束符置為;*/
DELIMITER?;
調(diào)用存儲過程：
/*創(chuàng)建了3個自定義變量*/
SELECT?@id:=3,@age:=56,@name:='張學(xué)友';
/*調(diào)用存儲過程*/
CALL?proc2(@id,@age,@name);
驗證效果：
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ù)的存儲過程
創(chuàng)建存儲過程：
delete?a?from?t_user?a?where?a.id?=?4;
/*如果存儲過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc3;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲過程proc3*/
CREATE?PROCEDURE?proc3(id?int,age?int,in?name?varchar(16),out?user_count?int,out?max_id?INT)
??BEGIN
????INSERT?INTO?t_user?VALUES?(id,age,name);
????/*查詢出t_user表的記錄，放入user_count中,max_id用來存儲t_user中最小的id*/
????SELECT?COUNT(*),max(id)?into?user_count,max_id?from?t_user;
??END?$
/*將結(jié)束符置為;*/
DELIMITER?;
proc3中前2個參數(shù)，沒有指定參數(shù)模式，默認(rèn)為in。
調(diào)用存儲過程：
/*創(chuàng)建了3個自定義變量*/
SELECT?@id:=4,@age:=55,@name:='郭富城';
/*調(diào)用存儲過程*/
CALL?proc3(@id,@age,@name,@user_count,@max_id);
驗證效果：
mysql>?select?@user_count,@max_id;
+-------------+---------+
|?@user_count?|?@max_id?|
+-------------+---------+
|???????????4?|???????4?|
+-------------+---------+
1?row?in?set?(0.00?sec)
示例4：帶inout參數(shù)的存儲過程
創(chuàng)建存儲過程：
/*如果存儲過程存在則刪除*/
DROP?PROCEDURE?IF?EXISTS?proc4;
/*設(shè)置結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲過程proc4*/
CREATE?PROCEDURE?proc4(INOUT?a?int,INOUT?b?int)
??BEGIN
????SET?a?=?a*2;
????select?b*2?into?b;
??END?$
/*將結(jié)束符置為;*/
DELIMITER?;
調(diào)用存儲過程：
/*創(chuàng)建了2個自定義變量*/
set?@a=10,@b:=20;
/*調(diào)用存儲過程*/
CALL?proc4(@a,@b);
驗證效果：
mysql>?SELECT?@a,@b;
+------+------+
|?@a???|?@b???|
+------+------+
|???20?|???40?|
+------+------+
1?row?in?set?(0.00?sec)
上面的兩個自定義變量@a、@b作為入?yún)?，然后在存儲過程內(nèi)部進(jìn)行了修改，又作為了返回值。
示例5：查看存儲過程
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)
BEGIN
????SET?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ù)
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?INT
??BEGIN
????DECLARE?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?INT
??BEGIN
????DECLARE?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)
存儲過程和函數(shù)的區(qū)別
存儲過程的關(guān)鍵字為procedure，返回值可以有多個，調(diào)用時用call，一般用于執(zhí)行比較復(fù)雜的的過程體、更新、創(chuàng)建等語句。文章來源：http://www.zghlxwxcb.cn/news/detail-705687.html
函數(shù)的關(guān)鍵字為function，返回值必須有一個，調(diào)用用select，一般用于查詢單個值并返回。文章來源地址http://www.zghlxwxcb.cn/news/detail-705687.html
| 存儲過程 | 函數(shù) | |
|---|---|---|
| 返回值 | 可以有0個或者多個 | 必須有一個 |
| 關(guān)鍵字 | procedure | function |
| 調(diào)用方式 | call | select |
到了這里，關(guān)于玩轉(zhuǎn)Mysql系列 - 第17篇：存儲過程&自定義函數(shù)詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
