文章目录
一、MySQL存储过程、存储函数的概念
- 存储过程、存储函数是预先编译好并存储在数据库中的一段SQL的集合,类似于用java或cpp写的函数。
二、存储过程、存储函数的好处
- 类似于编程时写好的函数,所以可以重复调用,提高代码复用性
- 减少与数据库的数据交互,减少流量提高效率
- 减少编写代码时的劳动
三、存储过程存储函数的区别
- 存储过程没有返回值,存储函数必须有返回值。
四、存储过程的创建
1.数据准备
-- 创建db8数据库
CREATE DATABASE db8;
-- 使用db8数据库
USE db8;
-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(5), -- 学生性别
score INT -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张大彪',23,'男',95),
(NULL,'李云龙',24,'男',98),
(NULL,'王翠花',25,'女',100),
(NULL,'赵小花',26,'女',90);
- 有如下需求:按照性别进行分组,查询每组学生的总成绩。按照总成绩的升序排序
-- 有如下需求:按照性别进行分组,查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum
FROM student
GROUP BY gender
ORDER BY getSum ASC;
- 怎样将此需求封装到存储过程中?
2.存储过程的创建
-- 创建存储过程的标准语法如下:
-- 修改结束分隔符为$
DELIMITER $
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
sql语句1;
sql语句2;
sql语句3;
END$
-- 修改结束分隔符为分号
DELIMITER ;
其中 DELIMITER关键字的作用:
-- 使用 DELIMITER 关键字可以指定分隔符。因为sql语句默认以分号结束,但是有时候执行的语句里包含分号,此时并不想结束执行,所以可以设定另外的分隔符
DELIMITER 分隔符
-- 创建stu_group()存储过程,封装 分组查询总成绩并按照总成绩升序排列的功能
DELIMITER $
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getSum
FROM student
GROUP BY gender
ORDER BY getSum ASC;
end $
DELIMITER ;
五、存储过程的调用
-- 标准语法
CALL 存储过程名称(实际参数);
-- 调用stu_group存储过程
CALL stu_group();
六、存储过程的查看
-- 查看存储过程
SELECT * FROM mysql.proc WHERE db = 'db8';
- 注意:旧版本可以执行此语句,但从MySQL8.0版本开始已经抛弃了mysql.proc这个表,执行会报错如下。
这是我从谷歌上搜的结果:
七、存储过程的删除
-- 删除存储过程
DROP PROCEDURE stu_group;
DROP PROCEDURE IF EXISTS stu_group;
八、存储过程的语法
1.变量
1.1 定义变量、查询变量
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意: DECLARE定义的是局部变量,只能用在BEGIN END范围之内
-- 定义一个int类型变量、并赋默认值为10
DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
DECLARE num INT DEFAULT 10; -- 定义变量
SELECT num; -- 查询变量
END$
DELIMITER ;
-- 调用pro_test1存储过程
CALL pro_test1();
1.2 赋值变量
- 变量赋值第1种方式
-- 标准语法
SET 变量名 = 变量值;
-- 变量赋值第1种:
DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
DECLARE name VARCHAR(20); -- 定义变量
SET name = '存储过程'; -- 赋值
SELECT name;
END $
DELIMITER ;
CALL pro_test2();
- 变量赋值第2种方式
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 变量赋值第2种:将查询结果作为值赋值给变量
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE menScore,womenScore INT; -- 定义2个int变量保存男女的总分
SELECT SUM(score) INTO menScore FROM student WHERE gender='男';
SELECT SUM(score) INTO womenScore FROM student WHERE gender='女';
SELECT menScore,womenScore;
END $
DELIMITER ;
CALL pro_test3();
2.if语句
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
- 查询所有人加起来的总成绩并根据成绩赋值给另一变量“优秀”、“良好”、“合格”。
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
DECLARE total INT; -- 所有人总分数
DECLARE info VARCHAR(10); -- 分数描述
SELECT SUM(score) INTO total FROM student;
-- if语句进行判断
IF total > 380 THEN
SET info = '优秀';
ELSEIF total >= 320 AND total <= 380 THEN
SET info = '良好';
ELSE
SET info = '合格';
END IF;
SELECT total, info;
END $
DELIMITER ;
CALL pro_test4();
3.参数传递
DELIMITER $
-- 标准语法
/*
IN:代表输入参数,需要由调用者传递实际数据。默认的
OUT:代表输出参数,该参数可以作为返回值
INOUT:代表既可以作为输入参数,也可以作为输出参数
*/
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
执行的sql语句;
END$
DELIMITER ;
- 创建带参数的存储过程
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE pro_test5(IN total INT,OUT info VARCHAR(20))
BEGIN
IF total > 380 THEN
SET info = '优秀';
ELSEIF total >= 320 AND total <= 380 THEN
SET info = '良好';
ELSE
SET info = '合格';
END IF;
end $
DELIMITER ;
CALL pro_test5(330,@info);
-- 或者
CALL pro_test5((SELECT SUM(score) FROM student), @info);
SELECT @info;
- 注意:
- @变量名: 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
- @@变量名: 这种在变量前加上 “@@” 符号, 叫做系统变量
4.while语句
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
- 使用存储过程封装功能:计算1~100之间的偶数和。
DELIMITER $
CREATE PROCEDURE pro_test6()
BEGIN
DECLARE res iNT DEFAULT 0;
DECLARE num INT DEFAULT 1;
WHILE num <= 100 DO
IF num % 2 = 0 THEN
SET res = res + num;
END IF;
SET num = num + 1;
END WHILE;
SELECT res;
end $
DELIMITER ;
CALL pro_test6();
5.存储函数的创建、调用、删除
- 创建存储函数
DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
执行的sql语句;
RETURN 结果;
END$
DELIMITER ;
- 定义存储函数,获取学生表中成绩 >95的学生数量,返回结果
-- 创建存储函数
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
DECLARE res INT;
SELECT COUNT(*) INTO res FROM student WHERE score > 95;
RETURN res;
END $
DELIMITER ;
/*
报错:[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or......
原因:
因为我们在MySQL中开启了bin-log日志,所以创建函数或者存储过程,必须声明其为确定性的(DETERMINISTIC),或者声明为不修改数据(READS SQL DATA) 。
在MySQL中执行以下脚本,对参数进行设置即可。
set global log_bin_trust_function_creators=TRUE;
*/
set global log_bin_trust_function_creators=TRUE;
-- 调用存储函数
SELECT fun_test1();
- 删除存储函数
DROP FUNCTION fun_test1;