Mysql高级——存储过程和函数

一、什么是存储过程和函数

存储过程和函数是事先经过编译 并存储在数据库中的一段sql语句的集合，调用存储过程和函数可以简化开发人员的许多工作，减少数据在数据库和应用服务之间的传输，对于提高数据处理效率是有好处的。(现在已经不推荐使用，面试会问到。)

存储过程和函数的区别在于，一个又返回值，一个没有。

• 存储过程：是一个没有返回值的函数。

• 函数：是一个有返回值的过程。

二、创建存储过程

书写格式：

create procedure procedure_name(in/out param ...)
begin
		---sql语句
end;

• in/out定义是输入参数还是传出函数。不写的话默认in。

– 给变量重新赋值
select concat(“user表中的记录数num=”,num) from sys_user;
end

1.设置输入参数

例如：我们写一个简单的存储过程，要求是传入一个值 身高，根据身高大小返回 相应的身材类型。

create procedure test5(in height int )

BEGIN
		-- 声明一个变量  并给默认值 
		-- 
		DECLARE height int DEFAULT 180;
		DECLARE description VARCHAR(20) DEFAULT '';

		if 
		height > 180 then set description='高个子';
		ELSEIF 
		height <= 180 and height > 160 then set description='中等个子';
		else 
		set description='矮个子';
		end if;
-- 给变量重新赋值
	select concat("身高=",height,'是一个',description);
end;

-- 调用存储过程
call test01(155 )

1. 使用 in height int 定义一个输入参数。
2. 申明一个变量基本语法：DECLARE XXX 参数类型 （DEFAULT 默认值可有可无）
   如： DECLARE height int DEFAULT 180;
3. IF判断语法：IF 条件 THEN SQL语句 ELSEIF SQL语句 ELSE SQL语句 END IF；
   注意 每个SQL语句后面都得有;结束，最后END IF 后面也得有 ;.
4. 给变量赋值 使用 ：set XXX = ??;
5. 调用存储过程：call procedure_name();
   我们调用一下：

2.设置输出参数

将前面的存储过程改造一下，有两个参数，分别是 in 和 out 修饰，height代表输入参数身高，description 代表输出参数内容。

create PROCEDURE test6( in height int  ,out description VARCHAR(50))
BEGIN
		-- 声明一个变量  并给默认值 
		-- 

		if 
		height > 180 then set description='高个子';
		ELSEIF 
		height <= 180 and height > 160 then set description='中等个子';
		else 
		set description='矮个子';
		end if;
end;

call test6(188,@description);

select @description;

1. in height int 输入参数
2. out description VARCHAR(50) 删除函数。
3. call test6(188,@description);在调用存储过程时，输出函数，使用@变量名；
4. select @description; 获取输出参数，也是使用@变量名；
   
   传入188，返回高个子。

3.case when 语句

根据输入月份判断是第几季度。

create PROCEDURE test_7(in  mont int)
BEGIN
	-- 可以
	select count(*) into num from sys_user;

	DECLARE result VARCHAR(20);
	case  when mont>=1 and mont <4 
	then  set result ='第一季度' ;
	case when mont>=4 and mont <7 
	then  set result ='第二季度' ;
	case  when mont>=1 and mont <10 
	then  set result ='第三季度' ;

	else  set result ='第四季度' ;
	end case;
select  result；
end;

• 基本语法：case when 条件SQL then SQL语句 else SQL语句 end case;
• 可以使用select count(*) into num from sys_user;给 num赋值为sys_user表行数

4.while循环 语句

根据传入数字，将累加结果返回。

create PROCEDURE test8(n int)
BEGIN
	-- 定义初始累加值 为0
	DECLARE total int DEFAULT 0;
	-- 定义累加初始数为1 从1开始
	DECLARE num int default 1;
	-- 一直加到 n为止
	WHILE num<=n do 
	set total = total + num;
	set num = num+1;
	end WHILE;
	select  total;
end;

call test8(100);

• 基本语法：WHILE 结束条件SQL do SQL语句 end WHILE;
  

5.loop循环 语句

还是给定一个值，返回累加结果；

create PROCEDURE test11(n int)
BEGIN
DECLARE total int DEFAULT 0;
-- 定义loop 循环
c:LOOP
	set total = total+n;
	set n = n-1;
-- 设置跳出循环条件
	if n<=0 THEN
		LEAVE c;
	end if;

end loop;
select total;
end;


call test11(100);

• 语法：c:LOOP SQL语句 if 跳出循环条件 THEN LEAVE c; end if; end loop;

6.REPEAT循环 语句

给定一张sys_user表，需要往表中插入 20条记录。

CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名称',
  `password` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户密码',
  `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '邮箱',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别0 女 1男',
  `department_id` int(11) DEFAULT NULL COMMENT '部门id',
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

create PROCEDURE test10(n int)
BEGIN

REPEAT  
		INSERT INTO `sys_user` (`id`, `name`, `password`, `email`, `sex`, `department_id`, `role_id`) VALUES (n, 'admin', '123456', '952793966@qq.com', '1', '1', '5');
		set n = n-1;
UNTIL n=0
end REPEAT; 
end;

call test10(20);

• UNTIL n=0 为终止条件。
  

三、创建函数

函数Function的语法和存储过程的语法结构基本类似。
例如：我们现在有一张部门表：需要创建一个函数，去根据传入id获取，部门名称。

create FUNCTION fun1(inid int)
RETURNS VARCHAR(20)
BEGIN
	DECLARE depname VARCHAR(20);
	select department_name into depname from department where id = inid;

	RETURN depname;
END ;

• 存储过程是：PROCEDURE 函数是：FUNCTION
• 需要指定返回值类型：RETURNS VARCHAR(20)
• 需要return出sql处理结果: RETURN depname;
• 调用函数直接使用select fun1(5);