MySQL自定义函数和存储过程示例详解
前言
本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧
1、前置条件
MySQL数据库中存在表user_info，其结构和数据如下：
mysql>descuser_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|| +-----------+----------+------+-----+---------+-------+ 12rowsinset(0.10sec) mysql>select*fromuser_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-1220:11:30|2019-04-1920:11:30|30|NULL| |2|StephenWang8|123456|123@qq.com|15103887470|viewer|male|200|2019-04-1220:11:30|2019-04-1920:11:30|30|NULL| +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+ 2rowsinset(0.00sec)
2、自定义函数
函数：可以完成特定功能的一段SQL集合。MySQL支持自定义函数来完成特定的业务功能。
创建自定义函数（UserDefinedFunction简称UDF）的语法如下：
createfunction<函数名称>（[参数1][类型1],[参数N][类型N]）
returns<类型>
return
<函数主体>
调用UDF的语法如下：
select<函数名称>([参数])
创建无参的UDF
示例1：查询user_info表中有多少条记录
#定义函数 mysql>createfunctionuser_info_count() ->returnsint(10) ->return ->(selectcount(*)fromuser_info);
调用函数user_info_count()
mysql>selectuser_info_count(); +-------------------+ |user_info_count()| +-------------------+ |2| +-------------------+ 1rowinset(0.00sec)
创建有参UDF
示例2：根据id查询用户name。
#定义函数 mysql>createfunctionqueryNameById(uidint(10)) ->returnschar(20) ->return ->(selectnamefromuser_infowhereid=uid); QueryOK,0rowsaffected(0.01sec)
调用函数，查询id为1的用户名称。
mysql>selectqueryNameById(1); +------------------+ |queryNameById(1)| +------------------+ |StephenWang7| +------------------+ 1rowinset(0.00sec)
查看UDF
查询系统中所有的UDF
showfunctionstatus;
查询指定的UDF
# showcreatefunction函数名称; mysql>showfunctionqueryNameById; ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'queryNameById'atline1 mysql>showfunctionqueryNameById(); ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'queryNameById()'atline1 mysql>showcreatefunctionqueryNameById(); ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'()'atline1 mysql>showcreatefunctionqueryNameById; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |Function|sql_mode|CreateFunction|character_set_client|collation_connection|DatabaseCollation| +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |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|CREATEDEFINER=`root`@`localhost`FUNCTION`queryNameById`(uidint(10))RETURNSchar(20)CHARSETlatin1 return(selectnamefromuser_infowhereid=uid)|utf8|utf8_general_ci|latin1_swedish_ci| +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1rowinset(0.00sec
修改UDF
如果想要修改函数的内容，先删除后再重新创建。
删除UDF
删除UDF语法如下：
dropfunction <函数名称>;
示例3：删除函数queryNameId后再次调用并观察现象。
mysql>dropfunctionqueryNameById; QueryOK,0rowsaffected(0.45sec) mysql>selectqueryNameById(1); ERROR1305(42000):FUNCTIONrms.queryNameByIddoesnotexist mysql>
3、存储过程
存储功能和自定义函数相似，也是一组完成特定功能的SQL语句集合。把复杂或频繁调用的SQL提前写好并指定一个名称。待到要使用时，直接调用即可。
定义存储过程的语法如下：
CREATEPROCEDURE<过程名>([过程参数[,…]])<过程体>
[过程参数[,…]]格式
[IN|OUT|INOUT]<参数名><类型>
#语法定义来自：http://c.biancheng.net/view/2593.html
创建无参的存储过程
示例4：查询用户name。
mysql>DELIMITER// mysql>craeteprocedurequeryName() ->begin ->selectnamefromuser_info; ->end//
关于DELIMITER命令，修改MySQL结束命令的字符。默认的结束命令字符为分号，当存储过程中包含多条语句时，遇到第一个分号会作为存储过程结束的标志。这样不符合预期，因此需要修改默认结束命令字符。DELIMITER//就是将结束命令字符修改为//。调用存储过程的命令为：call存储过程名称。
#此时的命令的结束符号为//不是; mysql>callqueryName()// +--------------+ |name| +--------------+ |StephenWang7| |StephenWang8| +--------------+ 2rowsinset(0.00sec) QueryOK,0rowsaffected(0.00sec)
创建带参数的存储过程
示例5：根据id查询name。
mysql>createprocedurequeryNameById ->(Inuidint(15)) ->begin ->selectnamefromuser_infowhereid=uid; ->end ->// QueryOK,0rowsaffected(0.03sec)
调用存储过程queryNameById
mysql>callqueryNameById(1); ->// +--------------+ |name| +--------------+ |StephenWang7| +--------------+ 1rowinset(0.03sec) QueryOK,0rowsaffected(0.04sec)
修改存储过程
如果想创建存储过程的内容可以先删除再重新创建存储过程。
查看存储过程
showcreateprocedure<过程名称>
mysql>showcreateprocedurequeryNameById;->//+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+|Procedure|sql_mode|CreateProcedure|character_set_client|collation_connection|DatabaseCollation|+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+|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|CREATEDEFINER=`root`@`localhost`PROCEDURE`queryNameById`(Inuidint(15))beginselectnamefromuser_infowhereid=uid;end|utf8|utf8_general_ci|latin1_swedish_ci|+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1rowinset(0.04sec)
删除存储过程
dropprocedure<过程名称>
删除存储过程queryNameById
mysql>dropprocedurequeryNameById// QueryOK,0rowsaffected(0.02sec) mysql>callqueryNameById(1)// ERROR1305(42000):PROCEDURErms.queryNameByIddoesnotexist
4、总结
自定义函数和存储过程都是完成特定功能的SQL集合，那么他们有什么不同呢？
a、调用方式不同
#自定义函数
select <函数名>
#存储过程
call<存储过程名>
b、自定义函数不能有输出参数，而存储过程可以。
c、自定义函数必须要包含return语句，而存储过程则不需要。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。