您好,登錄后才能下訂單哦!
本篇文章給大家分享的是有關(guān)如何在MySQL中自定義函數(shù)和存儲(chǔ)過(guò)程,小編覺(jué)得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說(shuō),跟著小編一起來(lái)看看吧。
1、前置條件
MySQL數(shù)據(jù)庫(kù)中存在表user_info,其結(jié)構(gòu)和數(shù)據(jù)如下:
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、自定義函數(shù)
函數(shù):可以完成特定功能的一段SQL集合。MySQL支持自定義函數(shù)來(lái)完成特定的業(yè)務(wù)功能。
創(chuàng)建自定義函數(shù)(User Defined Function 簡(jiǎn)稱UDF)的語(yǔ)法如下:
create function <函數(shù)名稱> ([參數(shù)1] [類型1], [參數(shù)N] [類型N])
returns <類型>
return
<函數(shù)主體>
調(diào)用UDF的語(yǔ)法如下:
select <函數(shù)名稱> ([參數(shù)])
創(chuàng)建無(wú)參的UDF
示例1:查詢user_info表中有多少條記錄
#定義函數(shù) mysql> create function user_info_count() -> returns int(10) -> return -> (select count(*) from user_info);
調(diào)用函數(shù)user_info_count()
mysql> select user_info_count(); +-------------------+ | user_info_count() | +-------------------+ | 2 | +-------------------+ 1 row in set (0.00 sec)
創(chuàng)建有參UDF
示例2:根據(jù)id查詢用戶name。
#定義函數(shù) 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)
調(diào)用函數(shù),查詢id為1的用戶名稱。
mysql> select queryNameById(1); +------------------+ | queryNameById(1) | +------------------+ | StephenWang7 | +------------------+ 1 row in set (0.00 sec)
查看UDF
查詢系統(tǒng)中所有的UDF
show function status;
查詢指定的UDF
# show create function 函數(shù)名稱; 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
如果想要修改函數(shù)的內(nèi)容,先刪除后再重新創(chuàng)建。
刪除UDF
刪除UDF語(yǔ)法如下:
drop function <函數(shù)名稱>;
示例3:刪除函數(shù)queryNameId后再次調(diào)用并觀察現(xiàn)象。
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、存儲(chǔ)過(guò)程
存儲(chǔ)功能和自定義函數(shù)相似,也是一組完成特定功能的SQL語(yǔ)句集合。把復(fù)雜或頻繁調(diào)用的SQL提前寫好并指定一個(gè)名稱。待到要使用時(shí),直接調(diào)用即可。
定義存儲(chǔ)過(guò)程的語(yǔ)法如下:
CREATE PROCEDURE <過(guò)程名> ( [過(guò)程參數(shù)[,…] ] ) <過(guò)程體>
[過(guò)程參數(shù)[,…] ] 格式
[ IN | OUT | INOUT ] <參數(shù)名> <類型>
#語(yǔ)法定義來(lái)自:http://c.biancheng.net/view/2593.html
創(chuàng)建無(wú)參的存儲(chǔ)過(guò)程
示例4:查詢用戶name。
mysql> DELIMITER // mysql> craete procedure queryName() -> begin -> select name from user_info; -> end //
關(guān)于DELIMITER命令,修改MySQL結(jié)束命令的字符。默認(rèn)的結(jié)束命令字符為分號(hào),當(dāng)存儲(chǔ)過(guò)程中包含多條語(yǔ)句時(shí),遇到第一個(gè)分號(hào)會(huì)作為存儲(chǔ)過(guò)程結(jié)束的標(biāo)志。這樣不符合預(yù)期,因此需要修改默認(rèn)結(jié)束命令字符。 DELIMITER //就是將結(jié)束命令字符修改為//。調(diào)用存儲(chǔ)過(guò)程的命令為:call 存儲(chǔ)過(guò)程名稱。
#此時(shí)的命令的結(jié)束符號(hào)為// 不是; mysql> call queryName()// +--------------+ | name | +--------------+ | StephenWang7 | | StephenWang8 | +--------------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程
示例5:根據(jù)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)
調(diào)用存儲(chǔ)過(guò)程queryNameById
mysql> call queryNameById(1); -> // +--------------+ | name | +--------------+ | StephenWang7 | +--------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.04 sec)
修改存儲(chǔ)過(guò)程
如果想創(chuàng)建存儲(chǔ)過(guò)程的內(nèi)容可以先刪除再重新創(chuàng)建存儲(chǔ)過(guò)程。
查看存儲(chǔ)過(guò)程
show create procedure <過(guò)程名稱>
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)
刪除存儲(chǔ)過(guò)程
drop procedure <過(guò)程名稱>
刪除存儲(chǔ)過(guò)程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、總結(jié)
自定義函數(shù)和存儲(chǔ)過(guò)程都是完成特定功能的SQL集合,那么他們有什么不同呢?
a、調(diào)用方式不同
#自定義函數(shù)
select <函數(shù)名>
#存儲(chǔ)過(guò)程
call <存儲(chǔ)過(guò)程名>
b、自定義函數(shù)不能有輸出參數(shù),而存儲(chǔ)過(guò)程可以。
c、自定義函數(shù)必須要包含return語(yǔ)句,而存儲(chǔ)過(guò)程則不需要。
以上就是如何在MySQL中自定義函數(shù)和存儲(chǔ)過(guò)程,小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘9ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。