当前位置： 首页 > news >正文

MySQL存储过程

news 2025/8/8 6:00:31

存储过程是一组预定义的SQL语句集合，存储在数据库中，可以像调用函数一样重复使用。它可以封装复杂的业务逻辑，简化开发。

作用

  • 提高开发效率：避免重复写复杂SQL
  • 封装业务逻辑：让调用者只需调用存储过程
  • 增强安全性：限制直接操作底层表
  • 性能提升：存储过程预编译，执行快

语法

delimiter //   临时设置新的语句结束符（因为存储过程中的;会被认为是结束符）
#创建存储过程
create procedure 存储过程名()
begin-- 存储过程的逻辑代码-- 可以包含SQL语句、控制结构和变量操作等
end;delimiter ;
#执行存储过程
call 存储过程名();#删除存储过程
drop procedure [if exists] 存储过程名;

示例

delimiter //
create procedure mypro()
beginselect * from emp;select * from dept;
end;
delimiter ;
call mypro();
drop procedure if exists mypro;

使用参数

create procedure 存储过程名([in|out|inout] 参数名1 参数的数据类型,[in|out|inout] 参数名2 参数的数据类型,...
)
begin-- 存储过程的逻辑代码-- 可以包含SQL语句、控制结构和变量操作等
end;
  • 参数类型：
  • in（默认）：输入参数，存储过程的输入值，从外部传递给存储过程，存储过程内部是只读的，不能修改它的值
  • out：输出参数，存储过程的返回值，存储过程可以修改它的值并将其返回
  • inout：输入和输出参数既可以作为输入值传递给存储过程，也可以由存储过程修改并返回
drop procedure if exists mypro;
# 参数：in/out/inout
create procedure mypro(in i int)
beginselect i;set i=2;#尝试修改参数 i的值（由于是in参数，修改不会影响外部变量，且有些数据库配置下会报错或被忽略）  # 只是修改存储过程中的局部变量，不会影响外部变量@varselect i;
end;
#存储过程外部定义变量 set @varName 用来传递给存储过程
set @var=1;# in    1    2 1  可读不可修改
# out   null 2 2  不可读可修改
# inout 1    2 2  可读可修改
call mypro(@var);#传入： @var的值（当前是1）作为参数i
select @var;
  • 总结：
类型描述程序内行为外部变量影响示例调用后值
in输入，只读可以读取参数值，但不能修改它以影响外部变量无影响select @var仍是1
out输出存储过程赋值给参数，调用后可以取到新值影响外部变量需要在调用时定义变量，可用@var接收
inout输入输出既可以读参数值，也可以修改，影响外部变量改变外部变量值传入变量，存储过程中修改会反映到外部变量

使用变量

  • declare是MySQL存储过程（以及函数）中用来定义局部变量的关键字。它的作用是声明一个变量，并指定这个变量的名字和数据类型。

  • 作用：

    • 声明局部变量，只能在存储过程、函数或游标内使用，作用范围限定在定义的代码块内。
    • 可以指定默认值，必须在declare后面用set赋值。
#定义变量
declare 变量名 变量的数据类型 [default 默认值];
#变量赋值
set 变量名=要赋的值;
  • 例：

decimal(10,2)是MySQL中用于定义数字类型的一种，用于存储带有小数点的精确数字。

  • decimal：表示十进制数（高精度数值）。
  • (10,2)：括号内两个数字的含义是：
    • 10：总有效数字的位数（即该数字的总长度，包括整数部分和小数部分）。
    • 2：小数点后的位数（即小数部分的位数）。
delimiter //create procedure example_proc()
begindeclare emp_id int; -- 声明变量declare total_salary decimal(10,2);declare message varchar(100);-- 赋值set emp_id=101;set total_salary=5000.50;set message='操作成功';-- 使用变量select emp_id, total_salary, message;
end //delimiter ;

逻辑语句

  • 条件语句（if、case）
if condition then逻辑代码;
[elseif condition then 逻辑代码;]
[else 逻辑代码;]
end if;casewhen condition1 then逻辑代码when condition2 then逻辑代码else逻辑代码
end case;
  • 例：
delimiter //create procedure check_grade(in score int)
beginif score >= 90 thenselect 'A';elseif score >= 80 thenselect 'B';elseif score >= 70 thenselect 'C';elseselect 'D或低于70';end if;
end //delimiter ;
  • 调用示例
call check_grade(85);  -- 输出：B
call check_grade(65);  -- 输出：D或低于70
  • 循环语句（while、repeat）
while 循环条件 do逻辑代码
end while;repeat逻辑代码
until condition end repeat;
  • 例：计算n项和
drop procedure if exists mypro2;
create procedure mypro2(in n int)
begindeclare sum int default 0;while n > 0 doset sum=sum+n;set n=n-1;end while;select sum;
end;call mypro2(100);drop procedure if exists mypro3;
create procedure mypro3(in n int)
begindeclare sum int default 0;repeatset sum=sum+n;set n=n-1;until n = 0 end repeat;select sum;
endcall mypro3(100);
  • 区别：
    • mypro2用while，在条件满足时运行
    • mypro3用repeat，先执行一次再判断条件

特点

  • 优点：
    • 代码复用：存储过程可以被多个应用程序或脚本调用，实现了代码的复用。
    • 提高性能：MySQL 将编译后的存储过程放入缓存中。如果应用程序在单个连接中多次使用存储过程，直接使用编译版本。
    • 减少网络流量：存储过程可以一次执行多条 SQL 语句，减少了与数据库的交互次数。
    • 安全控制：存储过程可以对数据库中的数据进行严格的访问控制和权限管理。
    • 数据一致性：存储过程可以实现复杂的数据操作和事务处理，确保数据的一致性和完整性。
  • 缺点：
    • 创建和维护成本高：SQL 是一种结构化查询语言，难以处理复杂的业务逻辑。
    • 开发调试复杂：需要通过特定的工具和技术进行，不方便调式。
    • 可移植性差：存储过程通常依赖于特定的数据库平台和版本，不同的数据库系统之间存储过程的语法和特性可能有差异，导致存储过程的可移植性较差。

自定义函数

这个咱们在之前博客中讲过，如果想要了解，请点击——>自定义函数

游标

cursor，使用游标可以对存储过程或函数中的查询结果进行逐行处理。

  • 创建游标后，可以使用 open 语句打开游标，开始执行游标指定的查询语句并生成结果集。在游标打开得到结果集后，可以使用 fetch 语句访问它的每一行。
  • 游标处理完成后，应关闭游标，释放游标使用的内存和资源。

游标读取到符合条件的结果会放在游标变量中。

  • 游标变量（存放从“结果集”中取出的数据）：

    • 用来存放“逐行”取出的数据
    • 每次取出一行，变量就装入一行数据
    • 可以理解为“临时存储每一行数据的容器”

语法

#创建游标
declare 游标名 cursor for 查询语句;
#打开游标
open 游标名;
#读取游标数据到变量中
fetch 游标名 into 变量名1[,变量名2...];
#关闭游标
close 游标名;

例：检索单行数据

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#打开游标open mycursor;#多次读取游标数据fetch mycursor into emp_name;select emp_name;#关闭游标close mycursor;
end;
call testCursor;
  • 例:循环检索数据
#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);declare i int default 0;#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#打开游标open mycursor;#多次读取游标数据while i<5 dofetch mycursor into emp_name;set i=i+1;select emp_name;end while;#关闭游标close mycursor;
end;
call testCursor;

循环检索数据需要设置结束条件：声明一个结束标志位的变量，声明一个句柄，当 not found（sqlstate ‘02000’）出现时，修改结束标志位

declare done int default 0;
#这里声明游标
declare continue handler for not found set done=1;
  • 例：
#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);declare done int default 0;#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#声明句柄：not found 结束declare continue handler for not found set done=1;#打开游标open mycursor;#循环读取游标数据while done=0dofetch mycursor into emp_name;if done=0 then select emp_name;end if;end while;#关闭游标close mycursor;
end;
call testCursor;
  • 注意：
    • declare 的顺序为：局部变量、游标、句柄

举个生活中的例子

你有一份学生名单和他们的成绩单，你要逐个检查每个学生的成绩，

  • 如果成绩低于60分，就给他们打个警告，通知他们需要补习。

（你不能只用一句话批量操作——你需要逐个检查每个学生，然后发出通知。）

  • 用游标：

    • 查询出所有学生
    • 一次读出一位学生的成绩
    • 根据成绩决定是否通知
    • 直到所有学生都处理完

异常处理机制

  • 声明处理程序 (declare … handler)：定义错误或条件发生时要执行的操作。
  • 捕获类型：
    • CONTINUE：捕获后，继续执行后续的存储过程（错误不会终止存储过程）
    • EXIT：捕获后，立即退出存储过程
  • 处理条件：
    • SQLEXCEPTION：所有SQL异常（错误）
    • SQLWARNING：所有SQL警告
    • 也可以捕获特定的异常（比如指定错误代码或错误信息）

语法

declare handler_type handler_condition handler_action;

  • handler_type：CONTINUE或EXIT

  • handler_condition：捕获什么类型的条件（如SQLEXCEPTION、SQLWARNING，或自定义条件）

  • handler_action：执行的操作（通常是begin … end块，比如赋值、输出、退出等）

  • SQLEXCEPTION

    • 意思：捕获所有的SQL异常（错误）
    • 作用：当出现任何严重的SQL错误时（如数据类型错误、违反约束、除零错误等），会触发这个条件。
    • 特点：一旦发生错误，通常会中断当前操作，除非捕获后用CONTINUE处理，否则会导致存储过程终止。

  • SQLWARNING

    • 意思：捕获所有SQL警告
    • 作用：当某个操作出现警告（非严重错误，但值得注意的事情，比如截断、数据溢出、部分成功）时，会触发这个条件。
    • 特点：警告不会导致操作中断，但可以用来捕获并处理。
条件类型触发情境作用是否中断典型用途
SQLEXCEPTION发生任何SQL错误（异常）捕获严重错误，可以决定是否中断中断捕获错误，做异常处理
SQLWARNING发生警告（如数据截断、部分成功等非错误）捕获警告信息，可继续执行否警告处理，记录或通知
  • 当然 决定程序是否中断的是EXIT和CONTINUE，只不过是不同条件匹配不同的捕获类型。

单个异常处理

示例：捕获所有异常

delimiter //create procedure test_exception()
begindeclare exit handler for sqlexceptionbeginselect '发生了异常，已捕获并处理' as message;end;-- 这是有风险的操作（除以0会出错）select 1/0; -- 此时会出错select '这条语句不会执行到' as message;
end //delimiter ;
  • 调用
call test_exception();
  • 输出
发生了异常，已捕获并处理
  • 解释：
    • 发生除以0的错误，自动跳转到handler
    • handler内的select输出消息
    • 存储过程不会因为错误中断，继续执行之后的语句

多个异常处理

  • 你可以定义多个handler，用来捕获不同类型的异常：
declare continue handler for sqlwarning
begin-- 处理警告select '捕获到警告' as message;
end;declare exit handler for sqlexception
begin-- 处理错误select '捕获到异常' as message;
end;

  • 注意：

  • 必须放在存储过程一开始的部分。

  • declare语句必须在任何可执行语句之前。

http://www.lqws.cn/news/113365.html

相关文章：

  • MySQL 搜索特定桩号距离之间的数据
  • 传输层协议：网络通信的关键纽带
  • 12.7 LangChain实战：1.2秒响应！用LCEL构建高效RAG系统，准确率提升41%
  • 现代密码学介绍
  • 从0开始学linux韦东山教程第四章问题小结（3）
  • 【LUT技术专题】图像自适应3DLUT代码讲解
  • 蛋白质设计软件LigandMPNN介绍
  • 数据加密标准(DES)解析
  • 40、响应处理-【源码分析】-基于请求参数的内容协商原理
  • malloc 内存分配机制：brk 与 mmap
  • C#面试问题61-80
  • 时代星光推出战狼W60智能运载无人机，主要性能超市场同类产品一倍！
  • PDF.js无法显示数字签名
  • 基于MATLAB的FTN调制和硬判决的实现
  • 《仿盒马》app开发技术分享-- 个人中心关于逻辑完善（端云一体）
  • 关于线缆行业设备数据采集异构问题的解决
  • 实现对deepseek流式返回的json数据，进行逐字解析并实时渲染
  • 【计算机网络】第七章 运输层
  • 蛋白质结构预测软件openfold介绍
  • 永磁同步电机控制算法--基于PR电流环的矢量控制
  • HCIP(BGP基础)
  • 模型上下文协议（MCP）简介
  • 【HarmonyOS 5】鸿蒙mPaas详解
  • 【网络安全】SRC漏洞挖掘思路/手法分享
  • Python训练营打卡Day42
  • sqlite3 命令行工具详细介绍
  • 蓝桥杯_DS18B20温度传感器---新手入门级别超级详细解析
  • 【自动思考记忆系统】demo （Java版）
  • 50天50个小项目 (Vue3 + Tailwindcss V4) ✨ | Dad Jokes（冷笑话卡片）
  • LangChain学习系列之LangChain4j介绍