这篇文章将为大家详细讲解有关MySQL之存储过程和函数的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

一、变量

系统变量

系统变量分为全局变量和会话变量，是由系统提供的。

全局变量作用域：服务器每次启动将为所有的全局变量初始化,不能跨重启。

会话变量作用域：仅针对当前会话有效。

【全局变量】作用域:服务器每次启动将为所有的全局变量初始化,不能跨重启#1.查看所有的全局变量SHOWGLOBALVARIABLES;#2.查看部分全局变量SHOWGLOBALVARIABLESLIKE'%char%';#3.查看某个全局变量的值SELECT@@global.autocommit;#查看是否自动提交SELECT@@global.tx_isolation;#查看隔离级别#4.为某个指定的全局变量赋值SET@@global.autocommit=0;【会话变量】作用域:仅针对当前会话有效.#1.查看所有的会话变量SHOWSESSIONVARIABLES;SHOWVARIABLES;#默认省略session#2.查看部分会话变量SHOWSESSIONVARIABLESLIKE'%char%';#3.查看某个会话变量的值SELECT@@tx_isolation;SELECT@@session.tx_isolation;#4.为某个指定的会话变量赋值SET@@session.autocommit=0;

自定义变量

自定义变量分为用户变量和局部变量，是用户自定义的。

【用户变量】作用域:针对当前会话有效,和会话变量的作用域相同#声明并初始化#下面三种方式都可以SET@count=1;#set@count:=1;#select@count:=1;#赋值SELECTCOUNT(*)INTO@countFROMemployees;#查看用户变量SELECT@count;【局部变量】作用域:仅在定义的beginend中有效,应用在beginend中的第一句话#声明DECLARE变量名类型;#declare变量名类型default值;#赋值SET局部变量名=值;#或set局部变量名:=值;#select@局部变量名:=值#使用SELECT局部变量名;【案例】#案例:声明两个变量并赋初值,求和,打印#用户变量SET@m=1;SET@n=2;SET@sum=@m+@n;SELECT@sum;#局部变量[只能在begin...end中运行]DECLAREmINTDEFAULT1;DECLAREnINTDEFAULT2;DECLARESUMINT;SETSUM=m+n;SELECTSUM;

二、存储过程

定义：事先经过编译并存储在数据库中的一组sql语句的集合。

存储过程的优点：①提高代码重用性 ②减少编译与连接次数 ③提高效率。

创建语法：

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的sql语句);
END 结束符

调用语法：
CALL 存储过程名(实参列表)

删除语法:
DROP PROCEDURE 存储过程名;

查看语法:
SHOW CREATE PROCEDURE 存储过程名;

存储过程的参数别聊提供了3种参数：

IN：需要输入，需要调用方传入值。

OUT：可以输出，可以作为返回值。

INOUT：可以输入和输出，既需要入参又需要返回值。

注意：

如果存储过程只有一句话，可以省略BEGIN END。

存储过程体中的每条sql语句的结尾要求必须加分号。

存储过程的结尾可以使用delimiter重写结束标志，DELIMITER 结束标记。

【空参存储过程】#案例:向admin表中插入5条记录#重置结尾符为$DELIMITER$#创建存储过程CREATEPROCEDUREmyp1()BEGININSERTINTOadmin(username,PASSWORD)VALUES('join1','000'),('join2','000'),('join3','000'),('join4','000'),('join5','000');END$#调用存储过程CALLmyp1()$【带in模式的存储过程】#案例:创建存储过程,实现根据女神名查询对应的男朋友信息CREATEPROCEDUREmyp2(INbeautyNameVARCHAR(20))BEGINSELECTbo.*FROMboysboRIGHTJOINbeautybONbo.id=b.boyfriend_idWHEREb.name=beautyName;END$CALLmyp2('柳岩')$#案例:创建存储过程,查看用户是否登录成功CREATEPROCEDUREmyp3(INusernameVARCHAR(10),INPASSWORDVARCHAR(10))BEGINDECLAREresultINTDEFAULT0;#声明并初始化SELECTCOUNT(*)INTOresult#赋值FROMadminWHEREadmin.username=usernameANDPASSWORD=PASSWORD;SELECTIF(result,'成功','失败');#打印变量END$#调用CALLmyp3('john','8888')$【带out模式的存储过程】#案例:根据女神名,返回对应的男神名CREATEPROCEDUREmyp4(INbeautyNameVARCHAR(20),OUTboyNameVARCHAR(20))BEGINSELECTbo.boyNameINTOboyName#赋值FROMboysboINNERJOINbeautybONb.boyfriend_id=bo.idWHEREb.name=beautyName;END$CALLmyp4('热巴',@bName)$#不定义,直接使用用户变量填充#调用SELECT@bName$#案例:根据女神名,返回对应的男神名和男神魅力值CREATEPROCEDUREmyp5(INbeautyNameVARCHAR(20),OUTboyNameVARCHAR(20),OUTuserCPINT)BEGINSELECTbo.boyName,bo.userCPINTOboyName,userCP#赋值FROMboysboINNERJOINbeautybONb.boyfriend_id=bo.idWHEREb.name=beautyName;END$CALLmyp5('热巴',@bName,@usercp)$#不定义,直接使用用户变量填充#调用SELECT@bName,@userCP$【带inout模式的存储过程】#案例：传入a和b两个值，最终a和b都翻倍并返回CREATEPROCEDUREmyp6(INOUTaINT,INOUTbINT)BEGINSETa=a*2;SETb=b*2;END$SET@m=10$SET@n=20$CALLmyp6(@m,@n)$SELECT@m,@n$

学习了存储过程，尝试完成下列习题吧

习题答案如下↓

【习题答案】#习题1：创建存储过程实现传入用户名和密码,插入到admin表中DELIMITER$CREATEPROCEDUREtest_1(INusernameVARCHAR(10),INloginPwdVARCHAR(10))BEGININSERTINTOadmin(admin.username,PASSWORD)VALUES(username,loginPwd);END$CALLtest_1('admin','111')$#习题2：创建存储过程或函数实现传入女神编号,返回女神名称和电话CREATEPROCEDUREtest_2(INidINT,OUTNAMEVARCHAR(20),OUTphoneVARCHAR(20))BEGINSELECTb.name,b.phoneINTONAME,phoneFROMbeautybWHEREb.id=id;END$CALLtest_2(1,@n,@p)$SELECT@n,@p;#习题3：创建存储过程或函数实现传入两个女神的生日,返回大小CREATEPROCEDUREtest_3(INbirth2DATETIME,INbirth3DATETIME,OUTresultINT)BEGINSELECTDATEDIFF(birth2,birth3)INTOresult;END$CALLtest_3('1998-1-1',NOW(),@result)$SELECT@result$#习题4：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回CREATEPROCEDUREtest_4(INmydateDATETIME,OUTstrdateVARCHAR(50))BEGINSELECTDATE_FORMAT(mydate,'%y年%m月%d日')INTOstrdate;END$CALLtest_4(NOW(),@str)$SELECT@str$#习题5：创建存储过程或函数实现传入女神名称，返回：女神and男神格式的字符串CREATEPROCEDUREtest_5(INbeautyNameVARCHAR(20),OUTstrVARCHAR(50))BEGINSELECTCONCAT(beautyName,'and',IFNULL(boyName,'null'))INTOstrFROMboysboRIGHTJOINbeautybONb.boyfriend_id=bo.idWHEREb.name=beautyName;END$CALLtest_5('热巴',@str)$SELECT@str$#习题6：创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录CREATEPROCEDUREtest_6(INstartIndexINT,INsizeINT)BEGINSELECT*FROMbeautyLIMITstartIndex,size;END$CALLtest_6(3,5)$

三、函数

存储过程和函数的区别？

存储过程可以有0个或多个返回；函数有且只有一个返回。

存储过程适合做批量插入、批量更新；函数适合做处理数据后返回一个结果。

创建语法：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体(一定有return语句);
END 结束符

调用语法：
SELECT 函数名(参数列表)

查看函数：
SHOW CREATE FUNCTION my_f3;

删除函数：
DROP FUNCTION my_f3;

【无参有返回】#返回公司的员工个数CREATEFUNCTIONmy_f1()RETURNSINTBEGINDECLAREnINTDEFAULT0;#定义变量SELECTCOUNT(*)INTOn#赋值FROMemployees;RETURNn;END$SELECTmy_f1()$【有参有返回】#根据员工名,返回工资CREATEFUNCTIONmy_f2(empNameVARCHAR(20))RETURNSDOUBLEBEGINSET@sal=0;#定义用户变量SELECTsalaryINTO@sal#赋值FROMemployeesWHERElast_name=empName;RETURN@sal;END$SELECTmy_f2('Kochhar')$#3.根据部门名,返回该部门平均工资CREATEFUNCTIONmy_f3(deptNameVARCHAR(20))RETURNSDOUBLEBEGINDECLAREsalDOUBLE;SELECTAVG(Salary)INTOsalFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREd.department_name=deptName;RETURNsal;END$SELECTmy_f3('IT')$

学习了mysql的函数，尝试完成下列习题

答案：
1、
CREATE FUNCTION test_1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_1(1,2)$
2、
CREATE FUNCTION test_2(jobName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE number INT DEFAULT 0; # 定义变量
SELECT COUNT(employee_id) INTO number # 赋值
FROM employees e
JOIN jobs j ON e.job_id=j.job_id
WHERE j.job_title=jobName;
RETURN number;
END $
SELECT test_2(‘President’)$
3、
CREATE FUNCTION test_3(empName VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
DECLARE managerName VARCHAR(20) DEFAULT ‘’; # 定义变量
SELECT e1.last_name AS managerName INTO managerName # 赋值
FROM employees e1
WHERE e1.employee_id=(SELECT e2.manager_id FROM employees e2 WHERE e2.last_name=empName);
RETURN managerName;
END $
SELECT test_3(‘Kochhar’)$

关于“MySQL之存储过程和函数的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。