MySQL—变量、存储过程和函数（十一）_mysql存储过程变量-CSDN博客

本文链接：https://blog.csdn.net/qq_21370419/article/details/131780545

一、变量

1 变量的种类

1.1 系统变量

系统变量一共分为两种：
1）全局变量
2）会话变量

系统变量：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1> 查看所有系统变量
show global|【session】variables;
2> 查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3> 查看指定的系统变量的值
select @@global|【session】系统变量名;
4> 为某个系统变量赋值
方式一：
set global|【session】系统变量名=值;
方式二：
set @@global|【session】系统变量名=值;

1.1.1 全局变量

全局变量：针对于所有会话（连接）有效，但不能跨重启

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=1;

1.1.2 会话变量

会话变量：针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

1.2 自定义变量

自定义变量一共分为两种：

1）用户变量

2）局部变量

自定义变量：变量由用户自定义，而不是系统提供的
使用步骤：
1> 声明
2> 赋值
3> 使用（查看、比较、运算等）

1.2.1 用户变量

用户变量：针对于当前会话（连接）有效，作用域同于会话变量

#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
#③使用（查看变量的值）
SELECT @变量名;

1.2.2 局部变量

局部变量：仅仅在定义它的begin end块中有效。应用在 begin end中的第一句话

#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;


#②赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;

1.2.3 用户变量和局部变量对比

	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

案例：

1>用户变量

#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

运行结果：

2>局部变量

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

运行结果：

局部变量的正确使用语法：

-- #######局部变量正确用法#######
-- 创建一个存储过程
DELIMITER $
CREATE PROCEDURE myp()
BEGIN
		DECLARE m INT DEFAULT 1;
		DECLARE n INT DEFAULT 1;
		DECLARE SUM INT;
		SET SUM=m+n;
		SELECT SUM;
END $
-- 调用存储过程
CALL myp();

运行结果：

二、存储过程

1 存储过程介绍

存储过程和函数：类似于java中的方法
好处：
1>提高代码的重用性
2>简化操作

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1> 提高代码的重用性
2> 简化操作
3> 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法：

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

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

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

调用语法:

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

注意：
1>如果存储过程体仅仅只有一句话，begin end可以省略
2>存储过程体中的每条sql语句的结尾要求必须加分号。
3>存储过程的结尾可以使用 delimiter 重新设置。因为MySQL默认的语句结束符号为分号是" ;" 。为了避免与存储过程中SQL语句结束符相冲突，需要使用delimiter 改变存储过程的结束符。
语法：

delimiter 结束标记

案例：

delimiter $ -- 将结束符号从“;”改成了“$”

2 案例演示

2.1 数据准备

-- 数据准备
-- 1 创建admin表
CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- 插入数据
insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');

-- 2 创建beauty表
CREATE TABLE beauty (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

-- 插入数据
insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) 
values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),
			 (2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),
			 (3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),
			 (4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),
		   (5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),
			 (6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),
			 (7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),
			 (8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),
			 (9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),
			 (10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),
			 (11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),
			 (12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);

-- 3 创建boys表
CREATE TABLE boys (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- 插入数据
insert  into `boys`(`id`,`boyName`,`userCP`) 
values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

2.2 语句演示

2.2.1 创建不带参数的存储过程

-- 4 创建不带参数的存储过程
-- 4.1 案例演示：插入到admin表中五条记录
-- 4.1.1 创建mypl存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

-- 4.1.2 调用mypl存储过程
CALL myp1();

-- 4.1.3 查看存储过程是否正确执行
SELECT * FROM admin;

2.2.2 创建带in模式参数的存储过程

2.2.2.1 案例演示：根据女神名，查询对应的男神信息

-- 5 创建带in模式参数的存储过程
-- 5.1 案例演示：根据女神名，查询对应的男神信息
-- 5.1.1 创建myp2存储过程
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
END $

-- 5.1.2 调用myp2存储过程
CALL myp2('柳岩');

2.2.2.2 案例演示：输入用户名和密码，判断是否登录成功

-- 5.2 案例演示：输入用户名和密码，判断是否登录成功
-- 5.2.1 创建myp4存储过程
DELIMITER $
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

-- 5.2.2 调用myp4存储过程
CALL myp4('张飞','8888');
CALL myp4('john','8888');

2.2.3 创建out模式参数的存储过程

2.2.3.1 案例演示：根据输入的女神名，返回对应的男神名

-- 6.1 案例演示：根据输入的女神名，返回对应的男神名
-- 6.1.1 创建myp6存储过程
DELIMITER $
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
END $

-- 6.1.2 调用myp6存储过程
CALL myp6('周芷若',@boyName);
SELECT @boyName;

2.2.3.2 案例演示：根据输入的女神名，返回对应的男神名和魅力值

-- 6.2 案例演示：根据输入的女神名，返回对应的男神名和魅力值
-- 6.2.1 创建myp7存储过程
DELIMITER $
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $

-- 6.2.2 调用myp7存储过程
CALL myp7('小昭',@name,@cp);
SELECT @name,@cp;

2.2.4 创建带inout模式参数的存储过程

2.2.4.1 案例演示：传入a和b两个值，最终a和b都翻倍并返回

-- 7 创建带inout模式参数的存储过程
-- 7.1 案例演示：传入a和b两个值，最终a和b都翻倍并返回
-- 7.1.1 创建myp8存储过程
DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

-- 7.1.2 调用myp8存储过程
SET @m=10;
SET @n=20;
CALL myp8(@m,@n);
SELECT @m,@n;

3 查看、删除存储过程

-- 8 查看存储过程myp2的状态
SHOW PROCEDURE STATUS LIKE 'myp2';

-- 9 查产存储过程的定义
SHOW CREATE PROCEDURE myp2;

-- 10 删除存储过程
DROP PROCEDURE myp1;

4 练习题

4.1 案例演示：mypl——插入到teacher表中四条记录

-- 1 创建teacher表
CREATE TABLE teacher(
tea_num VARCHAR(10) PRIMARY KEY COMMENT "教师编号",-- 主键
name VARCHAR(30) COMMENT "姓名",
gender CHAR(2) COMMENT "性别",
age INT COMMENT "年龄",
department VARCHAR(30) COMMENT "部门",
institute VARCHAR(50) COMMENT "学院",
salary DECIMAL(10,2) COMMENT "薪水",
hire_date date COMMENT "入职时间"
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


-- 4 案例演示：mypl——插入到teacher表中四条记录：
-- 教师号为100001，姓名为旗木卡卡西，性别男，年龄30，科室为综合科，就职学院为火影学院，薪水2000，入职日期为1988-12-21
-- 教师号为100002，姓名为迈特凯，性别男，年龄31，科室为体术科，就职学院为火影学院，薪水2100，入职日期为1987-10-21
-- 教师号为100003，姓名为孙悟空，性别男，年龄100，科室为猴科，就职学院为西游学院，薪水1500，入职日期为1966-11-01
-- 教师号为100004，姓名为马基，性别男，年龄42，科室为风科，就职学院为风影学院，薪水3000，入职日期为1975-04-21

-- 4.1 创建mypl存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO teacher
	VALUES("100001","旗木卡卡西","男",30,"综合科","火影学院",2000.56,"1988-12-21"),
				("100002","迈特凯","男",31,"体术科","火影学院",2100.58,"1987-10-21"),
				("100003","孙悟空","男",100,"猴科","西游学院",1500.123,"1966-11-01"),
				("100004","马基","男",42,"风科","风影学院",3000.222,"1975-04-21");
END $

-- 4.2 调用mypl存储过程
CALL myp1();

-- 4.3 查看存储过程是否正确执行
SELECT * FROM teacher;

4.2 案例演示：根据教师名，查询对应的教师信息

-- 5 案例演示：根据教师名，查询对应的教师信息
-- 5.1 创建myp2存储过程
DELIMITER $
CREATE PROCEDURE myp2(IN teaName VARCHAR(20))
BEGIN
	SELECT *
	FROM teacher
	WHERE name=teaName;
END $

-- 5.2 调用myp2存储过程
CALL myp2('马基');

4.3 案例演示：根据teacher表，输入教师编号和教师名，判断是否有这个教师

-- 6 案例演示：根据teacher表，输入教师编号和教师名，判断是否有这个教师
-- 6.1 创建myp4存储过程
DELIMITER $
CREATE PROCEDURE myp4(IN teaNo VARCHAR(10),IN teaName VARCHAR(30))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM teacher
	WHERE tea_num = teaNo AND name = teaName;
	
	SELECT IF(result>0,'存在','不存在');#使用
END $

-- 6.2 调用myp4存储过程
CALL myp4('100001','旗木卡卡西');

4.4 案例演示：根据输入教师的tea_num，返回对应的姓名和薪水

-- 7 案例演示：根据输入教师的tea_num，返回对应的姓名和薪水
-- 7.1 创建myp7存储过程
DELIMITER $
CREATE PROCEDURE myp7(IN teaNo VARCHAR(10),OUT teaName VARCHAR(30),OUT teaSalary DECIMAL(10,2)) 
BEGIN
	SELECT name,salary INTO teaName,teaSalary
	FROM teacher 
	WHERE tea_num=teaNo;
END $

-- 7.2 调用myp7存储过程
CALL myp7('100004',@name,@salary);
SELECT @name,@salary;

4.5 案例演示：传入a和b两个值，最终a和b都翻倍并返回

-- 8 案例演示：传入a和b两个值，最终a和b都翻倍并返回
-- 8.1 创建myp8存储过程
DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

-- 8.2 调用myp8存储过程
SET @m=10;
SET @n=20;
CALL myp8(@m,@n);
SELECT @m,@n;

三、函数

1 函数概述

存储过程和函数的区别：
1>存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
2>函数：有且仅有1 个返回，适合做处理数据后返回一个结果

创建语法：

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

注意：
1> 参数列表包含两部分：参数名 参数类型

2> 函数体：

肯定会有return语句，如果没有会报错;如果return语句没有放在函数体的最后也不报错，但不建议。

return 值;
3> 函数体中仅有一句话，则可以省略begin end
4> 使用 delimiter语句设置结束标记

调用语法：

SELECT 函数名(参数列表)

2 案例演示

2.1 无参有返回

-- 1 无参有返回 
-- 1.1 案例演示：返回boys的男神个数
-- 1.1.1 创建myf1函数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM boys;
	RETURN c;
	
END $

-- 1.1.2 调用函数
SELECT myf1();

2.2 有参有返回

2.2.1 案例演示：根据姓名返回其对应的Cp值

-- 2 有参有返回
-- 2.1 案例演示：根据姓名返回其对应的Cp值
-- 2.1.1  创建myf2函数
DELIMITER $
CREATE FUNCTION myf2(name VARCHAR(20)) RETURNS INT
BEGIN
	SET @cp=0;#定义用户变量 
	SELECT userCP INTO @cp   #赋值
	FROM boys
	WHERE boyName = name;
	
	RETURN @cp;#返回值
END $

-- 2.1.2 调用函数
SELECT myf2('张无忌');

2.2.2 案例演示：查询旗木卡卡西学生中最高的学费

-- 2.2 案例演示：查询旗木卡卡西学生中最高的学费
-- 2.2.1  创建myf3函数
DELIMITER $
CREATE FUNCTION myf3(name VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE maxFee DOUBLE;
	SELECT MAX(fee) INTO maxFee
	FROM student s
	JOIN teacher t ON s.tea_num = t.tea_num
	WHERE t.name = name;
	RETURN maxFee;
END $

-- 2.2.2 调用函数
SELECT myf3('旗木卡卡西');

2.2.3 案例演示：查询旗木卡卡西学生中最高的学费

-- 2.3 案例演示：查询旗木卡卡西学生中最高的学费
-- 2.3.1  创建myf4函数
DELIMITER $
CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;#声明一个局部float类型变量
	SET SUM=num1+num2;
	RETURN SUM;
END $

-- 2.3.2 调用函数
SELECT myf4(1,2);

3 查看、删除函数

-- 3 查看函数myf3的状态
SHOW FUNCTION STATUS LIKE 'myf3';

-- 4 查产函数的定义
SHOW CREATE FUNCTION myf3;

-- 5 删除函数
DROP PROCEDURE myp3;

4 练习题

4.1 案例演示：返回teacher表中的教师个数

-- 1 案例演示：返回teacher表中的教师个数
-- 1.1 创建myf1函数

-- 出现报错解决方案
set global log_bin_trust_function_creators=TRUE;

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM teacher;
	RETURN c;
END $

-- 1.2 调用函数
SELECT myf1();

4.2 案例演示：根据教师姓名返回其对应的salary值

-- 2 案例演示：根据教师姓名返回其对应的salary值
-- 2.1  创建myf2函数
DELIMITER $
CREATE FUNCTION myf2(teaName VARCHAR(30)) RETURNS DECIMAL(10,2)
BEGIN
	SET @sal=0.0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM teacher
	WHERE name = teaName;
	
	RETURN @sal;#返回值
END $

-- 2.2 调用myf2函数
SELECT myf2('旗木卡卡西');

4.3 案例演示：查询旗木卡卡西学生中最高的学费

-- 3 案例演示：查询旗木卡卡西学生中最高的学费
-- 数据准备
CREATE TABLE student(
stu_num VARCHAR(10) PRIMARY KEY COMMENT "学号", -- 主键
name VARCHAR(30) COMMENT "姓名",
gender CHAR(2) COMMENT "性别",
age INT COMMENT "年龄",
birthday DATE COMMENT "出生日期",
major VARCHAR(30) COMMENT "专业",
institute VARCHAR(50) COMMENT "学院",
fee DECIMAL(8,2) COMMENT "学费",
tea_num VARCHAR(10) COMMENT "教师编号",
create_time datetime COMMENT "创建时间",
FOREIGN KEY (tea_num) REFERENCES teacher(tea_num) -- 外键
);

-- 2.1 插入数据
INSERT INTO student
VALUES("2023000001","鸣人","男",12, '2001-01-02',"影分身","火影学院",20000.23,"100001",NOW());
INSERT INTO student
VALUES("2023000000","宇智波佐助","男",13, '2001-07-12',"写轮眼","火影学院",456123.21,"100001",NOW());
INSERT INTO student
VALUES('2023000002','小樱', '女', 14, '2001-05-18', '治疗', '火影学院', 12345.21,"100001",'2023-01-12 20:04:11');
INSERT INTO student
VALUES("2023000003","我爱罗","男",16, "1999-12-31","砂之术","风影学院",40000,"100004",NOW());
INSERT INTO student(stu_num,name,gender,age,birthday,major,institute,fee,tea_num,create_time)
VALUES("2023000004","小李","男",NULL, NULL,"体术","火影学院",10000.88,"100002",NOW());
INSERT INTO student
VALUES('2023000005','手鞠', '女', 18, '1995-01-25', '三星扇', '风影学院',5652.12,"100004",NOW());
-- 3.1  创建myf3函数
DELIMITER $
CREATE FUNCTION myf3(name VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE maxFee DOUBLE;
	SELECT MAX(fee) INTO maxFee
	FROM student s
	JOIN teacher t ON s.tea_num = t.tea_num
	WHERE t.name = name;
	RETURN maxFee;
END $

-- 3.2 调用myf3函数
SELECT myf3('旗木卡卡西');