MySQL数据库——存储对象之存储过程

最新推荐文章于 2025-07-16 16:35:04 发布
最新推荐文章于 2025-07-16 16:35:04 发布
阅读量2k 收藏 30
点赞数 62
CC 4.0 BY-SA版权
文章标签： 数据库 mysql 存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/BRJ_123456789/article/details/148639676

一、介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点

  • 封装，复用
  • 可以接收参数，也可以返回数据
  • 减少网络交互，效率提升

二、基本语法

2.1 创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    -- SQL语句
END;

2.2 调用

CALL 名称 ([ 参数 ]);

2.3 查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

2.4 删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称;

注意：在命令行中创建存储过程可能会出现问题

我们刚刚已经把p1这个存储过程删掉了，此时我们来命令行中创建一个相同的存储过程p1

为什么会报错？

因为在命令行中执行SQL语句的时候，一旦遇到了分号(;)，就会认为当前SQL语句已经结束了，显然不符合创建存储过程的语法。

在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

三、变量

3.1 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量 (GLOBAL)、会话变量 (SESSION)

  • 全局变量：针对所有的会话有效。创建的一个查询控制台是一个会话。
  • 会话变量：仅在当前会话内有效。

3.1.1 查看系统变量

-- 查看所有系统变量
SHQW [ SESSION | GLOBAL ] VARIABLES; -- 没有加级别, 默认是session

--可以通过LIKE模糊匹配方式查找变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......';

-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;

3.1.2 设置系统变量

SET [SESSION | GLOBAL] 系统变量名 = 值;

SET @@[SESSION | GLOBAL] 系统变量名 = 值;

这里是把自动提交的开关给关了，测试过后记得重新设置为1，否则执行SQL语句（如插入）时，不会自动提交，需要手动 commit;

注意：

  • 如果没有指定SESSION / GLOBAL，默认是 SESSION 会话变量。
  • mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

3.2 用户定义变量

用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

3.2.1 赋值

SET @var name = expr [, @var_name = expr] ...;
-- 或
SET @var_name := expr [, @var_name := expr] ...;

SELECT @var_name := expr [, @var_name := expr] ...;
-- 或
SELECT 字段名 INTO @var_name FROM 表名;

3.2.2 使用

SELECT @var_name;

3.3 局部变量

局部变量 是根据需要定义的在局部生效的变量，访问之前，需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN ... END 块。

3.3.1 声明

DECLARE 变量名 变量类型 [DEFAULT ...];

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

3.3.2 赋值

SET 变量名 = 值;

SET 变量名 := 值;

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

四、if判断

4.1 语法

IF 条件1 THEN
    ......
ELSEIF 条件2 THEN    --可选
    ......
ELSE                 --可选
    ......
END IF;

4.2 案例

定义存储过程，完成如下需求

根据定义的分数score变量，判定当前分数对应的分数等级。

  1. score >= 85分，等级为优秀。
  2. score >=60分目score<85分，等级为及格。
  3. score<60分，等级为不及格。
create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);

    if score >= 85 then
        set result := '优秀';
    elseif  score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;

    select result;
end;


call p3();

五、参数

类型含义备注
IN该类参数作为输入，也就是需要调用时传入值默认
OUT该类参数作为输出，也就是该参数可以作为返回值
INOUT既可以作为输入参数，也可以作为输出参数

5.1 用法

CREATE PROCEDURE 存储过程名称([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
    -- SQL语句
END;

5.2 案例

定义存储过程，完成如下需求

1. 根据传入参数score，判定当前分数对应的分数等级，并返回。

  1. score >= 85分，等级为优秀。
  2. score >=60分目score<85分，等级为及格。
  3. score<60分，等级为不及格。
create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif  score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

call p4(68, @result);

select @result;

2. 将传入的 200分制的分数，进行换算，换算成百分制，然后返回。

create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @score := 78;

call p5(@score);

select @score;

六、case

6.1 语法

  • 语法一
CASE case_value
    WHEN when_value1 THEN statement_list1
    [ WHEN when_value2 THEN statement_list2] ...
    [ ELSE statement_list ]
END CASE;
  • 语法二
CASE
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition2 THEN statement_list2] ...
    [ELSE statement_list]
END CASE;

6.2 案例

定义存储过程，完成如下需求

根据传入的月份，判定月份所属的季节（要求采用case结构）

  1. 1-3月份，为第一季度
  2. 4-6月份，为第二季度
  3. 7-9月份，为第三季度
  4. 10-12月份，为第四季度
create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when month >= 1 and month <= 3 then
            set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else
            set result := '无效的月份';
    end case;

    select concat('您输入的月份为：',  month, '，所属的季度为：', result);
end;

call p6(4);

七、循环

7.1 while循环

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL逻辑...
END WHILE;

定义存储过程，完成如下需求

计算从 1 累加到 n 的值，n 为传入的参数值。

create procedure p7(in num int)
begin
    declare result int default 0;

    while num > 0 do
        set result := result + num;
        set num := num - 1;
    end while;

    select result;
end;

call p7(100);

7.2 repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
    SQL逻辑...
    UNTIL 条件
END REPEAT;

定义存储过程，完成如下需求

计算从 1 累加到 n 的值，n 为传入的参数值。

create procedure p8(in num int)
begin
    declare result int default 0;

    repeat
        set result := result + num;
        set num := num - 1;
    until num <= 0
    end repeat;

    select result;
end;

call p8(100);

7.3 loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：

  • LEAVE：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:] LOOP
    SQL逻辑...
END LOOP [end_label];
LEAVE label;    --退出指定标记的循环体
ITERATE label;  --直接进入下一次循环

定义存储过程，完成如下需求

1. 计算从 1 累加到 n 的值，n 为传入的参数值。

create procedure p9(in num int)
begin
    declare result int default 0;

    sum: loop
        if num <= 0 then
            leave sum;
        end if;

        set result := result + num;
        set num := num - 1;
    end loop sum;

    select result;
end;

call p9(100);

2. 计算从 1 到 n 之间的偶数累加的值，n 为传入的参数值。

create procedure p10(in num int)
begin
    declare result int default 0;

    sum: loop
        if num <= 0 then
            leave sum;
        end if;

        if num % 2 = 1 then
            set num := num - 1;
            iterate sum;
        end if;

        set result := result + num;
        set num := num - 1;
    end loop sum;

    select result;
end;

call p10(10);

八、游标

游标（CURSOR）是用来存储询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

8.1 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

8.2 打开游标

OPEN 游标名称;

8.3 获取游标记录

FETCH 游标名称 INTO 变量[, 变量 ];

8.4 关闭游标

CLOSE 游标名称;

8.5 案例

定义存储过程，完成如下需求

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

分析：

-- 逻辑:
-- A. 声明游标，存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare uprofession varchar(100);

    -- A. 声明游标，存储查询结果集
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;

    -- B. 准备: 创建表结构
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );

    -- C. 打开游标
    open u_cursor;

    while true do
        -- D. 获取游标中的数据
        fetch u_cursor into uname, uprofession;
        -- E. 插入数据到新表中
        insert into tb_user_pro values(null, uname, uprofession);
    end while;

    -- F. 关闭游标
    close u_cursor;
end;

call p11(40);

创建存储过程是没有问题的，执行 call p11(40); 时报错

但是我们可以看到，新建的表tb_user_pro是有的，且表结构中是存在数据的。有多少条？17条！且年龄都是 <=40 的

也就是说我们的逻辑是正常的，那为什么执行还要报错呢？

原因就出现在一个地方，这个地方就是循环体

我们简单分析一下当前的问题：

当前出现的问题，主要就是在这里！

while true！！！

也就意味着我们有没有退出循环？

没有，会一直循环下去。那么在循环的过程当中，每循环一次，那么我们就需要从游标当中去获取它的下一条记录，通过 fetch u_cursor into uname, uprofession; 赋值给uname和uprofession这两个变量。

那当我们循环到最后一条之后，接下来再进行下一次循环，游标里边儿还有数据吗？

就没有了，所以此时再去获取游标当中的数据就会报错了，告诉你No date已经没有数据可以去获取了。

所以就报错！报错之后就退出循环了。

问题解决：条件处理程序！！！

九、条件处理程序——handler

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement;

handler_action

    CONTINUE: 继续执行当前程序

    EXIT: 终止执行当前程序

condition_value

    SQLSTATE_sqlstate_value: 状态码，如 02000

    SQLWARNING: 所有以01开头的SQLSTATE代码的简写

    NOT FOUND: 所有以02开头的SQLSTATE代码的简写

    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

每一种状态码的含义，官方文档：

https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

现在我们就通过条件处理程序，解决游标模块的案例中留下的问题：

-- 逻辑:
-- A. 声明游标，存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare uprofession varchar(100);

    -- A. 声明游标，存储查询结果集
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;

    -- 声明一个条件处理程序，当满足SQL状态码为02000时触发，触发退出操作，退出后关闭游标
    declare exit handler for sqlstate '02000' close u_cursor;

    -- B. 准备: 创建表结构
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );

    -- C. 打开游标
    open u_cursor;

    while true do
        -- D. 获取游标中的数据
        fetch u_cursor into uname, uprofession;
        -- E. 插入数据到新表中
        insert into tb_user_pro values(null, uname, uprofession);
    end while;

    -- F. 关闭游标
    close u_cursor;
end;

call p11(30);

​

​ 执行成功！！！

​

【MySQL】数据库——存储引擎
2401_83330816的博客
06-25 964
死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。如何避免死锁？
MySQL数据库——存储过程
weixin_67300995的博客
06-28 1463
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）仅当存在时删除，不添加 IF EXISTS 时，如果指定的过程不存在，则产生一个错误。
MySQL数据库——存储过程练习
2302_81782153的博客
05-13 404
【代码】MySQL数据库——存储过程练习。
MySQL数据库——MySQL查看存储过程
Itmastergo的博客
05-08 2173
创建好存储过程后，用户可以通过 SHOW ATATUS 语句来查看存储过程的状态，也可以通过 SHOW CREATE 语句来查看存储过程的定义。这里主要讲解查看存储过程的状态和定义的方法。
MYSQL数据库进阶篇——存储过程
qq_74177011的博客
09-11 2317
用户定义变量指用户自己定义的变量，用户变量不用提前声明，直接用“@变量名”使用即可，作用域为当前连接。例如：用户定义的变量不需要声明或初始化，默认为null；例如：3.局部变量局部变量是根据·定义在局部生效的变量，通过declare声明，可用作存储过程内的局部变量和输入参数，范围在begin...end之间。
MySQL数据库——MySQL修改存储过程（ALTER PROCEDURE）
Itmastergo的博客
05-09 2330
在实际开发过程中，业务需求修改的情况时有发生，所以修改MySQL中的存储过程是不可避免的。MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。下面将详细讲解修改存储过程的方法。
MySQL数据库——MySQL存储过程是什么？
Itmastergo的博客
05-05 853
我们前面所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。
【MySQL】MySQL数据库 —— 简单认识
小黑屋的黑小子的博客
04-15 1256
什么是数据库存储数据用文件就可以了，为什么还要弄个数据库?文件的安全性问题文件不利于数据查询和管理文件不利于存储海量数据文件在程序中控制不方便磁盘内存为了解决上述问题，专家们设计出更加利于管理数据的软件 ———数据库，它能更有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。1.2 数据库和数据结构之间关系表示一门学科，研究的是如何设计实现一个数据库(数据库软件背后的思想方法，设计)表示一类软件，管理数据的软件；
MySQL数据库——MySQL存储函数详解
Itmastergo的博客
05-09 1356
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。
MySQL数据库——MySQL创建存储过程（CREATE PROCEDURE）
Itmastergo的博客
05-08 3244
MySQL 存储过程是一些 SQL 语句的集合，比如有时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。
MySQL数据库——存储过程-循环（while、repeat、loop）
li13437542099的博客
11-14 9500
repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。-- 如果不满足，则继续下一次循环REPEATSQL逻辑...UNTIL 条件END REPEAT;LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LEAVE ：配合循环使用，退出循环。ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。SQL逻辑...
mysql存储过程——用于数据库的备份与还原
03-28
MySQL存储过程是数据库管理系统中的一种重要功能，它允许程序员或数据库管理员预编译一系列SQL语句，形成一个可重用的程序单元。在本场景中，我们关注的是如何利用存储过程进行数据库的备份与还原，这在数据管理中至...
MySQL数据库：存储过程的创建1.pptx
05-29
1）了解 ——存储过程的优点； 2）理解 —— 存储过程的概念； 3）掌握 —— 存储过程的创建方法； 存储过程优点 使用存储过程的优点有： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程执行一次后，其...
《MySQL数据库项目式教程》项目七++存储过程与触发器.ppt
10-11
在《MySQL数据库项目式教程》的项目七中，我们将深入学习MySQL数据库中的高级编程技术——存储过程和触发器。首先，我们将掌握MySQL程序设计的基础知识，这包括常量、变量、流程控制语句等概念。MySQL中的常量主要...
java进阶（二）+学习笔记
weixin_41330897的博客
07-13 1592
面向对象设计以类和对象为核心，通过封装隐藏细节、继承实现复用、多态单一职责（一个类只做一件事）、开闭原则（扩展开放/修改关闭）、接口隔离（细化接口功能）、依赖倒置（面向抽象编程）、组合复用（优先组合而非继承）、里氏替换（子类可无缝替换父类）、迪米特法则（减少对象直接耦合），这些原则共同指导构建高内聚、低耦合、易维护的软件系统。
深入理解红锁
2403_87122583的博客
07-15 319
在构建高并发、高可用的分布式系统时，我们常常会遇到这样一个核心挑战：如何确保多个服务实例能够安全、有序地访问共享资源，避免竞态条件（Race Condition）和数据不一致？传统单机环境下的锁机制（如Java的或）在分布式场景下显得力不从心。于是，分布式锁应运而生，而基于Redis的分布式锁因其高性能和简单性而被广泛应用。
第五章 RAG知识库进阶
weixin_45284646的博客
07-12 349
本文介绍了基于Spring AI框架开发RAG（检索增强生成）知识库应用的核心技术与实践要点。主要内容包括：1）RAG工作流程的四个关键环节（文档收集切割、向量转换存储、文档过滤检索、查询增强关联）；2）Spring AI的ETL（抽取、转换、加载）流程实现，重点解析了DocumentReader组件对不同格式文档（JSON、PDF等）的读取方式；3）文档转换（切片）阶段的DocumentTransformer接口应用。文章通过代码示例展示了JsonReader的具体用法，为开发者提供了RAG应用开发的系统
SQL，在join中，on和where的区别
最新发布
【小石头的茅坑】
07-16 288
参考。
包管理工具 snap 和 apt
2301_78094384的博客
07-13 396
APT与Snap包管理工具对比摘要 APT是Debian/Ubuntu的传统包管理系统（.deb格式），深度集成系统，版本稳定但较旧，依赖管理完善。Snap是容器化包管理系统（.snap格式），具备沙箱隔离、自动更新和跨发行版特性，版本更新但占用更多资源。主要区别：APT适合系统组件，强调稳定性；Snap适合获取新版应用。两者可共存，建议关键组件用APT，桌面/开发工具可选用Snap。可通过apt list和snap list命令查看已安装软件来源。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值