mysql存储过程区别 mysql存储过程和存储函数

转载

梦断蓝桥魂 2023-05-26 17:04:42

文章标签 存储过程赋值 sql语句 文章分类 MySQL 数据库

文章目录

一、MySQL存储过程、存储函数的概念
二、存储过程、存储函数的好处
三、存储过程存储函数的区别
四、存储过程的创建

1.数据准备
2.存储过程的创建

五、存储过程的调用
六、存储过程的查看
七、存储过程的删除
八、存储过程的语法

1.变量

1.1 定义变量、查询变量
1.2 赋值变量

2.if语句
3.参数传递
4.while语句
5.存储函数的创建、调用、删除

一、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这个表，执行会报错如下。

这是我从谷歌上搜的结果：

mysql存储过程区别 mysql存储过程和存储函数_sql语句

七、存储过程的删除

-- 删除存储过程
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;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：android init 进程 android instantrun

下一篇：redis加载配置启动 redis启动带配置文件

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql存储过程区别 mysql存储过程和存储函数

mysql存储过程区别 mysql存储过程和存储函数

文章目录

一、MySQL存储过程、存储函数的概念

二、存储过程、存储函数的好处

三、存储过程存储函数的区别

四、存储过程的创建

1.数据准备

2.存储过程的创建

五、存储过程的调用

六、存储过程的查看

七、存储过程的删除

八、存储过程的语法

1.变量

1.1 定义变量、查询变量

1.2 赋值变量

2.if语句

3.参数传递

4.while语句

5.存储函数的创建、调用、删除

51CTO博客