爱站程序员基地根据尚硅谷的视频教程学习MySQL，学习记录-15 – 存储过程和函数。
存储过程和函数：一组预先编译好的SQL语句的集合，理解成批处理语句。可以提高代码的重用性，简化操作，提高效率
一、创建语法
CREATE PROCEDURE 存储过程名（参数列表）BEGIN存储过程体（一组合法的SQL语句）END
注意：
1、参数列表包含三部分：参数模式，参数名，参数类型，举例 IN stuname VARCHAR(20)
参数模式：
IN: 参数可以作为输入，要求调用方传入值
OUT: 参数可以作为输出，也就是该参数可以作为返回值
INOUT: 参数既可以作为输入，又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，BEGIN END可以省略。
3、存储过程体中的每条SQL语句的结果要求必须加分号。存储过程的结果可以使用DELIMITER重新设置
DELIMITER 结束标记
二、调用语法
CALL 存储过程名(实参列表);
- 空参列表
案例：插入到admin表中五条记录
先查看目前admin表的信息
CREATE PROCEDURE myp1()BEGINinsert into admin values(null, \'john1\',\'0000\'),(null,\'john2\',\'1111\'),(null,\'lyt1\',\'3333\'),(null,\'tia\',\'4545\'),(null,\'nina\',\'6666\');END # 创建空参列表CALL myp1(); #调用该控制体select * from admin; #查看最终结果
- 创建带 in 模式参数的存储过程
案例1：创建存储过程，实现根据女神名 查询对应的男神信息
CREATE PROCEDURE mypb(IN beautyname VARCHAR(20))BEGINSELECT bo.*FROM boys bo RIGHT JOIN beauty bON b.boyfriend_id = bo.idWHERE b.name = beautyname;END #创建存储过程CALL mypb(\'赵敏\'); #调用存储过程
结果如下：
案例2： 创建存储过程实现，用户是否登录成功
CREATE PROCEDURE mypa(IN username VARCHAR(20), IN password VARCHAR(20))BEGINDECLARE result INT DEFAULT 0; #声明变量并初始化SELECT COUNT(*) INTO result #将count(*)的结果赋值给resultFROM adminWHERE admin.username = usernameAND admin.password = password;SELECT IF( result > 0,\'成功\',\'失败\'); #使用result进行判断END #创建存储过程，有2个变量需要输入CALL mypa(\'张飞\',\'8888\'); #输入对应信息，查看是否在admin表中CALL mypa(\'john\',\'8888\'); #结果分别如下
- 创建带out 模式的存储过程
案例1：根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20))BEGINSELECT bo.boyname INTO boyname #将查询结果直接赋值给out参数FROM boys bo RIGHT JOIN beauty bON b.boyfriend_id = bo.idWHERE b.name = beautyname;ENDCALL myp5(\'小昭\',@bname) #将out数据赋值给新的参数 bnameSELECT @bname #查询最终的结果
案例2：根据女神名，返回对应的男神名和cp值
CREATE PROCEDURE myp7(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20), OUT usercp INT)BEGINSELECT bo.boyname, bo.userCP INTO boyname, usercp #2个out参数，需要将结果对应赋值FROM boys bo RIGHT JOIN beauty bON b.boyfriend_id = bo.idWHERE b.name = beautyname;ENDCALL myp7(\'小昭\',@bname,@usercp); #调用存储过程SELECT @bname, @usercp; #查看最终结果
- 创建带 inout 模式参数的存储过程
案例1： 传入a 和 b 两个值，最终a 和 b 都翻倍并返回
CREATE PROCEDURE myp8(INOUT a int, inout b int)BEGINSET a = a*2;SET b = b*2;END #创建存储过程set @m = 20; #定义变量并赋值set @n = 30;CALL myp8(@m,@n);#调用存储过程，使用定义的变量SELECT @m, @n; # 查看最终执行后的结果
三、删除存储过程
DROP PROCEDURE 存储过程名称; #一次只能删除一个存储过程
四、查看存储过程的信息
SHOW CREATE PROCEDURE 存储过程名;
五、存储过程练习
- 创建存储过程实现 传入一个日期，格式化成XX年XX月XX日并返回
CREATE PROCEDURE myp01(IN mydate DATETIME, out strdate VARCHAR(50))BEGINSELECT DATE_FORMAT(mydate,\'%Y年%m月%d日\') INTO strdate;END#调用并显示结果CALL myp01(NOW(),@strdate);SELECT @strdate;
- 创建存储过程或函数实现传入女神名，返回：女神 and 男神
CREATE PROCEDURE testp2( IN beautyname VARCHAR(20), out beautyandboy VARCHAR(20))BEGINSELECT CONCAT(beautyname, \' AND \', IFNULL(bo.boyname, \'NA\')) INTO beautyandboyFROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.idWHERE b.name = beautyname;END #创建存储过程#调用存储过程并检查结果CALL testp2(\'小昭\',@beautyandboy);SELECT @beautyandboy;#查看没有对应boy数据的情况CALL testp2(\'岳灵珊\',@beautyandboy1);SELECT @beautyandboy1;
- 创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE testp4(IN startindex int, in size int)BEGINselect * from beauty LIMIT startindex, size;END#调用存储过程CALL testp4(3,5)
六、函数的创建、调用
含义：一组先编译好的SQL语句的集合，批处理语句，与存储过程一致。
差异如下：
存储过程：可以有0个返回，也可以有多个返回，适合批量插入，批量更新
函数：有且仅有一个返回，适合做处理数据后返回一个结果
- 创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型BEGIN函数体END
注意：
1 参数列表包含两部分：参数名、参数类型
2 函数体：肯定会有return语句，如果没有会报错。
3 函数体中仅有一句话，则可以省略BEGIN END
4 使用 delimiter 语句设置结束标记
- 调用语法
SELECT 函数名（参数列表）
- 案例 – 无参有返回
案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INTBEGINDECLARE c INT DEFAULT 0; #定义一个变量SELECT COUNT(*) INTO c #对变量进行赋值FROM employees;RETURN c;END#调用函数：SELECT myf1();
案例 – 有参返回
案例：根据员工名，返回它的工资
CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLEBEGINSET @sal = 0; #定义用户变量SELECT salary INTO @sal #赋值FROM employeesWHERE last_name = empname;RETURN @sal;END#调用函数SELECT myf2(\'Kochhar\');
案例 – 根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(depname VARCHAR(20)) RETURNS DOUBLEBEGINDECLARE sal DOUBLE; #定义变量SELECT AVG(salary) INTO sal #为变量赋值FROM employees eJOIN departments d ON e.department_id = d.department_idWHERE d.department_name = depname; #因为直接查看指定部门的平均薪资，不需要再GROUP BYRETURN sal;END#调用函数SELECT myf3(\'IT\');
七、查看与删除函数
SHOW CREATE FUNCTION 函数名; #查看函数DROP FUNCTION 函数名; #删除函数
八、练习
- 创建函数，实现传入两个float返回二者之和
CREATE FUNCTION myf4(a float, b float) RETURNS FLOATBEGINDECLARE sum FLOAT;SELECT a + b INTO sum;RETURN sum;END#调用函数SELECT myf4(12.12,32);
结果为：
