本篇文章給大家帶來的內容是關于mysql自定義函數與存儲過程的詳細介紹（附代碼），有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。
1、前置條件
mysql數據庫中存在表user_info，其結構和數據如下：
mysql>?desc??user_info; +-----------+----------+------+-----+---------+-------+ |?Field?????|?Type?????|?Null?|?Key?|?Default?|?Extra?| +-----------+----------+------+-----+---------+-------+ |?id????????|?int(10)??|?NO???|?PRI?|?NULL????|???????| |?name??????|?char(20)?|?NO???|?????|?NULL????|???????| |?passwd????|?char(40)?|?NO???|?????|?NULL????|???????| |?email?????|?char(20)?|?NO???|?????|?NULL????|???????| |?phone?????|?char(20)?|?NO???|?????|?NULL????|???????| |?role??????|?char(10)?|?NO???|?????|?NULL????|???????| |?sex???????|?char(10)?|?NO???|?????|?NULL????|???????| |?status????|?int(10)??|?NO???|?????|?NULL????|???????| |?createAt??|?datetime?|?NO???|?????|?NULL????|???????| |?exprAt????|?datetime?|?NO???|?????|?NULL????|???????| |?validDays?|?int(10)??|?NO???|?????|?NULL????|???????| |?delAt?????|?datetime?|?YES??|?????|?NULL????|???????| +-----------+----------+------+-----+---------+-------+ 12?rows?in?set?(0.10?sec) mysql>?select??*?from??user_info; +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+ |?id?|?name?????????|?passwd???|?email??????|?phone???????|?role???|?sex??|?status?|?createAt????????????|?exprAt??????????????|?validDays?|?delAt?| +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+ |??1?|?StephenWang7?|?py123456?|?123@qq.com?|?15103887470?|?admin??|?male?|????200?|?2019-04-12?20:11:30?|?2019-04-19?20:11:30?|????????30?|?NULL??| |??2?|?StephenWang8?|?123456???|?123@qq.com?|?15103887470?|?viewer?|?male?|????200?|?2019-04-12?20:11:30?|?2019-04-19?20:11:30?|????????30?|?NULL??| +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+ 2?rows?in?set?(0.00?sec)
2、自定義函數
函數：可以完成特定功能的一段SQL集合。MySQL支持自定義函數來完成特定的業務功能。
創建自定義函數（User Defined Function 簡稱UDF）的語法如下：
create?function??（[參數1]?[類型1],?[參數N]?[類型N]） returns? return?
調用UDF的語法如下：
select???([參數])
創建無參的UDF
示例1：查詢user_info表中有多少條記錄
#定義函數 mysql>?create?function??user_info_count() ????->?returns?int(10) ????->?return ????->?(select??count(*)?from?user_info);
調用函數user_info_count()
mysql>?select??user_info_count(); +-------------------+ |?user_info_count()?| +-------------------+ |?????????????????2?| +-------------------+ 1?row?in?set?(0.00?sec)
創建有參UDF
示例2：根據id查詢用戶name。
#定義函數 mysql>?create?function??queryNameById(uid?int(10)) ????->?returns?char(20) ????->?return ????->?(select??name??from???user_info??where?id=uid); Query?OK,?0?rows?affected?(0.01?sec)
調用函數，查詢id為1的用戶名稱。
mysql>?select??queryNameById(1); +------------------+ |?queryNameById(1)?| +------------------+ |?StephenWang7?????| +------------------+ 1?row?in?set?(0.00?sec)
查看UDF
查詢系統中所有的UDF
show?function?status;
查詢指定的UDF
# show???create?function???函數名稱; mysql>?show??function???queryNameById; ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'queryNameById'?at?line?1 mysql>?show??function???queryNameById(); ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'queryNameById()'?at?line?1 mysql>?show???create?function???queryNameById(); ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'()'?at?line?1 mysql>?show???create?function???queryNameById; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |?Function??????|?sql_mode??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|?Create?Function???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|?character_set_client?|?collation_connection?|?Database?Collation?| +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |?queryNameById?|?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`?FUNCTION?`queryNameById`(uid?int(10))?RETURNS?char(20)?CHARSET?latin1 return?(select??name??from???user_info??where?id=uid)?|?utf8?????????????????|?utf8_general_ci??????|?latin1_swedish_ci??| +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1?row?in?set?(0.00?sec
修改UDF
如果想要修改函數的內容，先刪除后再重新創建。
刪除UDF
刪除UDF語法如下：
drop?function??;
示例3：刪除函數queryNameId后再次調用并觀察現象。
mysql>?drop??function?queryNameById; Query?OK,?0?rows?affected?(0.45?sec) mysql>?select?queryNameById(1); ERROR?1305?(42000):?FUNCTION?rms.queryNameById?does?not?exist mysql>
3、存儲過程
存儲功能和自定義函數相似，也是一組完成特定功能的SQL語句集合。把復雜或頻繁調用的SQL提前寫好并指定一個名稱。待到要使用時，直接調用即可。
定義存儲過程的語法如下：
CREATE?PROCEDURE??(?[過程參數[,…]?]?)? [過程參數[,…]?]?格式 [?IN?|?OUT?|?INOUT?]?? #語法定義來自：http://c.biancheng.net/view/2593.html
創建無參的存儲過程
示例4：查詢用戶name。
mysql>?DELIMITER?// mysql>?craete?procedure?queryName() ????->?begin ????->?select??name?from?user_info; ????->?end?//
關于DELIMITER命令，修改MySQL結束命令的字符。默認的結束命令字符為分號，當存儲過程中包含多條語句時，遇到第一個分號會作為存儲過程結束的標志。這樣不符合預期，因此需要修改默認結束命令字符。 DELIMITER //就是將結束命令字符修改為//。調用存儲過程的命令為：call 存儲過程名稱。
#此時的命令的結束符號為//?不是; mysql>?call??queryName()// +--------------+ |?name?????????| +--------------+ |?StephenWang7?| |?StephenWang8?| +--------------+ 2?rows?in?set?(0.00?sec) Query?OK,?0?rows?affected?(0.00?sec)
創建帶參數的存儲過程
示例5：根據id查詢name。
mysql>?create?procedure??queryNameById ????->?(In?uid?int(15)) ????->?begin ????->?select??name??from??user_info?where?id=uid; ????->?end ????->?// Query?OK,?0?rows?affected?(0.03?sec)
調用存儲過程queryNameById
mysql>?call?queryNameById(1); ????->?// +--------------+ |?name?????????| +--------------+ |?StephenWang7?| +--------------+ 1?row?in?set?(0.03?sec) Query?OK,?0?rows?affected?(0.04?sec)
修改存儲過程
如果想創建存儲過程的內容可以先刪除再重新創建存儲過程。
查看存儲過程
show?create?procedure?
mysql>?show?create?procedure?queryNameById;?->?//?+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+?|?Procedure?|?sql_mode?|?Create?Procedure?|?character_set_client?|?collation_connection?|?Database?Collation?|?+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+?|?queryNameById?|?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?`queryNameById`(In?uid?int(15))?begin?select?name?from?user_info?where?id=uid;?end?|?utf8?|?utf8_general_ci?|?latin1_swedish_ci?|?+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+?1?row?in?set?(0.04?sec)
刪除存儲過程
drop?procedure?
刪除存儲過程queryNameById
mysql>?drop?procedure??queryNameById// Query?OK,?0?rows?affected?(0.02?sec) mysql>?call??queryNameById(1)// ERROR?1305?(42000):?PROCEDURE?rms.queryNameById?does?not?exist
4、總結
自定義函數和存儲過程都是完成特定功能的SQL集合，那么他們有什么不同呢？
a、調用方式不同
#自定義函數 select?? #存儲過程 call?
b、自定義函數不能有輸出參數，而存儲過程可以。
c、自定義函數必須要包含return語句，而存儲過程則不需要。
【相關推薦：MySQL教程】
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
