MySQL学习记录 - 15 - 存储过程和函数

根据尚硅谷的视频教程学习MySQL，学习记录-15 - 存储过程和函数。

存储过程和函数：一组预先编译好的SQL语句的集合，理解成批处理语句。可以提高代码的重用性，简化操作，提高效率

一、创建语法

CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
	存储过程体（一组合法的SQL语句）
END

注意：
1、参数列表包含三部分：参数模式，参数名，参数类型，举例 IN stuname VARCHAR(20)
参数模式：
IN: 参数可以作为输入，要求调用方传入值
OUT: 参数可以作为输出，也就是该参数可以作为返回值
INOUT: 参数既可以作为输入，又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，BEGIN END可以省略。
3、存储过程体中的每条SQL语句的结果要求必须加分号。存储过程的结果可以使用DELIMITER重新设置DELIMITER 结束标记

二、调用语法

CALL 存储过程名(实参列表);

1. 空参列表
   案例：插入到admin表中五条记录
   先查看目前admin表的信息
   

CREATE PROCEDURE myp1()
BEGIN
    insert into admin values(null, 'john1','0000'),(null,'john2','1111'),
    (null,'lyt1','3333'),(null,'tia','4545'),(null,'nina','6666');
END   # 创建空参列表
CALL myp1(); #调用该控制体
select * from admin; #查看最终结果

2. 创建带 in 模式参数的存储过程
   案例1：创建存储过程，实现根据女神名 查询对应的男神信息

CREATE PROCEDURE mypb(IN beautyname VARCHAR(20))
BEGIN
  SELECT bo.*
  FROM boys bo RIGHT JOIN beauty b 
  ON b.boyfriend_id = bo.id
  WHERE b.name = beautyname;
END   #创建存储过程
CALL mypb('赵敏'); #调用存储过程

结果如下：

案例2： 创建存储过程实现，用户是否登录成功

CREATE PROCEDURE mypa(IN username VARCHAR(20), IN  password VARCHAR(20))
BEGIN
  DECLARE result INT DEFAULT 0; #声明变量并初始化
  SELECT COUNT(*) INTO result  #将count(*)的结果赋值给result
  FROM admin
  WHERE admin.username = username 
  AND admin.password = password;
  SELECT IF( result > 0,'成功','失败');  #使用result进行判断
END  #创建存储过程，有2个变量需要输入
CALL mypa('张飞','8888'); #输入对应信息，查看是否在admin表中
CALL mypa('john','8888'); #结果分别如下

3. 创建带out 模式的存储过程
   案例1：根据女神名，返回对应的男神名

CREATE PROCEDURE myp5(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20))
BEGIN
  SELECT bo.boyname INTO boyname  #将查询结果直接赋值给out参数
  FROM boys bo RIGHT JOIN beauty b 
  ON b.boyfriend_id = bo.id
  WHERE b.name = beautyname;
END
CALL myp5('小昭',@bname)  #将out数据赋值给新的参数 bname
SELECT @bname #查询最终的结果

案例2：根据女神名，返回对应的男神名和cp值

CREATE PROCEDURE myp7(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20), OUT usercp INT)
BEGIN
  SELECT bo.boyname, bo.userCP INTO boyname, usercp #2个out参数，需要将结果对应赋值
  FROM boys bo RIGHT JOIN beauty b 
  ON b.boyfriend_id = bo.id
  WHERE b.name = beautyname;
END
CALL myp7('小昭',@bname,@usercp); #调用存储过程
SELECT @bname, @usercp; #查看最终结果

4. 创建带 inout 模式参数的存储过程
   案例1： 传入a 和 b 两个值，最终a 和 b 都翻倍并返回

CREATE PROCEDURE myp8(INOUT a int, inout b int)
BEGIN
 SET 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 存储过程名;

五、存储过程练习

1. 创建存储过程实现 传入一个日期，格式化成XX年XX月XX日并返回

CREATE PROCEDURE myp01(IN mydate DATETIME, out strdate VARCHAR(50))
BEGIN
	 SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO strdate;
END
#调用并显示结果
CALL myp01(NOW(),@strdate);
SELECT @strdate;

2. 创建存储过程或函数实现传入女神名，返回：女神 and 男神

CREATE PROCEDURE testp2( IN beautyname VARCHAR(20), out beautyandboy VARCHAR(20))
BEGIN
  SELECT CONCAT(beautyname, ' AND ', IFNULL(bo.boyname, 'NA')) INTO beautyandboy
  FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id
  WHERE b.name = beautyname;
END  #创建存储过程
#调用存储过程并检查结果
CALL testp2('小昭',@beautyandboy);
SELECT @beautyandboy;
#查看没有对应boy数据的情况
CALL testp2('岳灵珊',@beautyandboy1);
SELECT @beautyandboy1;

3. 创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录

CREATE PROCEDURE testp4(IN startindex int, in size int)
BEGIN
 select * from beauty LIMIT startindex, size;
END
#调用存储过程
CALL testp4(3,5)

六、函数的创建、调用

含义：一组先编译好的SQL语句的集合，批处理语句，与存储过程一致。
差异如下：
存储过程：可以有0个返回，也可以有多个返回，适合批量插入，批量更新
函数：有且仅有一个返回，适合做处理数据后返回一个结果

1. 创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：
1 参数列表包含两部分：参数名、参数类型
2 函数体：肯定会有return语句，如果没有会报错。
3 函数体中仅有一句话，则可以省略BEGIN END
4 使用 delimiter 语句设置结束标记

2. 调用语法

SELECT 函数名（参数列表）

3. 案例 - 无参有返回
   案例：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN 
	DECLARE c INT DEFAULT 0; #定义一个变量
	SELECT COUNT(*) INTO c #对变量进行赋值
	FROM employees;
	RETURN c;
END
#调用函数：
SELECT myf1();

案例 - 有参返回
案例：根据员工名，返回它的工资

CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal = 0; #定义用户变量
	SELECT salary INTO @sal #赋值
	FROM employees
	WHERE last_name = empname;
	RETURN @sal;
END
#调用函数
SELECT myf2('Kochhar');

案例 - 根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(depname VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE; #定义变量
	SELECT AVG(salary) INTO sal #为变量赋值
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name = depname; #因为直接查看指定部门的平均薪资，不需要再GROUP BY
	RETURN sal;
	END
#调用函数
SELECT myf3('IT');

七、查看与删除函数

SHOW CREATE FUNCTION 函数名; #查看函数
DROP FUNCTION 函数名; #删除函数

八、练习

1. 创建函数，实现传入两个float返回二者之和

CREATE FUNCTION myf4(a float, b float)  RETURNS FLOAT
BEGIN
	DECLARE sum FLOAT;
	SELECT a + b INTO sum;
	RETURN sum;
END
#调用函数
SELECT myf4(12.12,32);

结果为：