MySQL-变量、存储过程、函数、流程控制-CSDN博客

本文链接：https://blog.csdn.net/qq_42861526/article/details/119324435

一、变量

1.1 系统变量

系统变量分为全局global以及会话session两种

1.查看系统变量 非指定
show global | session variables 【like '%xxx%'】

2.查看系统变量 指定
select @@global.tx_isolation; 
select @@tx_isolation; 

3.修改系统变量
set @@global.autocommit=0 针对全局
set@@autocommit=0 针对当前连接

1.2 自定义变量

自定义变量分为用户变量与局部变量

一、用户变量
作用域：针对于当前会话（连接）有效，作用域同于会话变量
1.创建用户变量
set @user = 'wang';
select @v1:=1;
select count(*) into @v2 from employees;

2.修改用户变量
同上，不再赘述

3.查看用户变量
select @v1;

注意：可以看到用户变量是一种若类型的变量，不需要指定变量类型，并且可以随时转变。

二、局部变量
1.声明并初始化局部变量
declare m1 int default 0
2.修改局部变量
set m1 = 2
3.查看局部变量
select m1

注意：局部变量的作用域只限于begin-end块中，而用户变量的作用域是任何地方

二、存储过程

存储过程类似于Java中的函数，其作用是将复杂的业务逻辑封装成一个过程，并且编译好，之后执行此业务就更加方便，且不用再次编译，提高了效率。

1.创建语法
create procedure p1(参数列表)
begin
	代码块....
end

2.参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

注意：
1.如果存储过程体仅仅只有一句话，begin end可以省略
2.存储过程体中的每条sql语句的结尾要求必须加分号。
3.存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter $

案例

#1.空参列表
#案例：插入到admin表中五条记录
delimiter $ #设置语句结束标识
CREATE PROCEDURE myp1()
BEGIN
	DECLARE i int DEFAULT 1;
	WHILE i<=5 DO
		INSERT INTO admin(uName,pwd) VALUES('fff','fffff');
		set i = i+1;
	end while;
END$
call myp1()$

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
create PROCEDURE myp2(in gName VARCHAR(20))
BEGIN
	select bo.* from beauty b left join boys bo on b.boyfriend_id = bo.id
	where b.name = gName;
end$
call myp2()$

#案例2 ：创建存储过程实现，用户是否登录成功
create PROCEDURE myp3(in name VARCHAR(20),in pwd VARCHAR(20))
BEGIN
	DECLARE res int DEFAULT 0;
	select count(*) into res from admin
	where admin.uName = name and admin.pwd = pwd;
	select if(res>0,'登录成功','登录失败');
END$
call myp3('fff','fffff')$

#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
create PROCEDURE myp4(in gName varchar(20),out bName VARCHAR(20))
BEGIN
	select bo.boyName into bName 
	from beauty b left join boys bo on b.boyfriend_id=bo.id
	where b.name = gName;
END$
call myp4('王语嫣',@bf)$
select @bf $

#案例2：根据输入的女神名，返回对应的男神名和魅力值
create procedure myp5(in gName varchar(20),out bName VARCHAR(20),out cp int)
BEGIN
	select bo.boyName,bo.userCP into bName,cp 
	from beauty b left join boys bo on b.boyfriend_id=bo.id
	where b.name = gName;
END$
call myp5('王语嫣',@bf,@cp)$
select @bf,@cp$

#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=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;错误，一次只能删一个

#四、查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE  myp2;

2.2存储过程与函数的区别

存储过程是将一组操作封装起来，起目的是为了执行那一系列操作，可以有返回值，也可以没有返回值。
函数的目的是为了得到一个返回值，必须有返回值
语法：create function fun1(参数列表) returns 返回类型
begin
函数体…
return 返回值
end$

三、流程控制

3.1 case

case分两种情况讨论
情况一：
	1.类似于switch
	case 变量或表达式
		when v1 then _v1
		when v2 then _v2
		...
		else _vn
	end
	
	2.类似if-else
	case
		when 表达式1 then v1
		when 表达式1 then v2
		...
		else vn
	end

情况二：
	1.类似于switch
	case 变量或表达式
		when v1 then 表达式1;
		when v2 then 表达式2;
		...
		else 表达式n;
	end
	
	2.类似if-else
	case
		when 表达式1 then 表达式1;
		when 表达式1 then 表达式2;
		...
		else 表达式n;
	end
	
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

位置：
可以放在任何位置，
如果放在begin end 外面，作为表达式结合着其他语句使用（也就是结束不带;）
如果放在begin end 里面，一般作为独立的语句使用(结束带;)

3.2 if-else

语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
else 语句3;
end if;

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
	
	
END $

SELECT test_if(87)$

3.3循环结构

分类：
while、loop、repeat

循环控制：

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环

*/

#1.while
/*

语法：

【标签:】while 循环条件 do
	循环体;
end while【 标签】;

联想：

while(循环条件){

	循环体;
}
*/

#2.loop
/*
语法：
【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环
*/

#3.repeat
/*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;
*/

#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
END $

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $