MySQL 存储过程详解：概念、创建与删除全教程

📑 目录

• 1 什么是MySQL存储过程
  • 1.1 存储过程核心定义
  • 1.2 存储过程优缺点
  • 1.3 适用业务场景
• 2 存储过程的创建语法实操
  • 2.1 无参存储过程创建
  • 2.2 带入参存储过程创建
  • 2.3 存储过程调用方式

1. 什么是MySQL存储过程]

存储过程:一组完成特定功能的语句集,经过编译后储存在数据库中,用户可以指定存储过程的名字和参数来进行执行,执行完毕得到相应结果;
用大白话来说就是:存储过程就是提前把一堆 SQL 语句打包写好，存在 MySQL 数据库里，给这段打包代码起个名字。后续不用重复写一堆 SQL，直接调用名字就能一次性执行全部语句
我们可以类比与Java中的方法,或者C语言中的函数

正如,SQL语句我们可以理解为:方法体里面的语句
当我们调用这个方法的时候,就会完成指定的任务之后,就会得到对应的结果;
特点:

1. 存在数据库端：代码保存在 MySQL 服务器，不是 Java、本地文件里；
2. 一次编译，多次运行：第一次调用编译，后续直接执行，大批量 SQL 效率更高；
3. 支持入参、出参，可以外部传值控制 SQL 逻辑；
4. 可以写流程控制：if 判断、while 循环，让 SQL 拥有简单编程能力。

1.1 存储过程核心定义

正常情况下,我们的数据库主要负责数据的存储和检索工作
Java 服务负责流程控制（if 判断、循环、事务、业务计算）
而使用存储过程模式:
数据库包揽流程控制、判断、循环、运算、事务；Java 只充当 “调用方”，只负责传参、拿返回结果，不再参与业务判断。
例如:

• 普通写法：Java 查余额、if 判断钱够不够、try 控制事务、加减金额；
• 存储过程：Java 只传转账双方 ID 和金额，剩下查余额、判断、扣钱加钱、出错回滚全由 MySQL 跑完。

1.2 存储过程优缺点

优点:

1. 性能优化
   存储过程,是在创建时编译放在数据库中的,执行存储过程时,执行速度会比执行单个SQL语句集快;
2. 代码重用
   创建的存储过程,可以重复被使用,避免重复代码;
3. 安全性
   存储过程可以限制直接访问数据库,通过间接访问;
4. 降低耦合性
   当创建的表结构发生变化时,我们只需要修改对应的存储过程即可
5. 事物管理
   可以在存储过程中实现比较复杂的事物逻辑

缺点:

1. 可移植性差
   存储过程不能夸数据库进行使用,更换数据库时,需要重新编写;
2. 调试困难
   只有少数情况下数据库管理系统支持存储过程调试,我们在使用命令行窗口时,调试非常困难,找bug很艰难;
3. 不适合高并发的场景
   在高并发场景下,如果使用存储过程来管理数据库,可能会增加数据库压力,本来我们数据库就是代码执行过程中最慢的时候,此时数据库就很难以维护;

1.3 适用业务场景

适合场景: 类似于一下场景
批量数据处理：批量修改、批量统计、定时归档；
复杂多SQL组合：多表查询、多步骤计算、报表统计；
闭环事务操作：下单、支付、库存扣减等强一致性业务；
逻辑分支繁多：大量if/判断依赖数据库字段做分支。

不推荐场景
.需要频繁迭代改动的业务；
需要跨库操作、依赖Java中间件逻辑；
追求数据库横向分库分表的大型分布式系统。

主要使用场景日常开发中按照业务需求即可;

2. 存储过程的创建语法实操

语法:

-- 修改sql语句结束标识符为 //delimiter//-- 创建存储过程createprocedure存储过程名(参数列表)begin-- sql 语句end//-- 修改sql语句结束标识符为 ;delimiter;

为什么我们此时需要修改结束标识符?
我们知道我们在命令行客户端进行增删改查的时候,我们是使用;来表示这条语句结束
如果我们此时不加;此时,编译器就不知道你到底结束没;
例如:

此时就会出现这种情况;

2.1 无参存储过程创建

我们先创建一个学生表;

createtablestudent(idintprimarykeyauto\_increment,namevarchar(40)default'匿名',ageintnotnull);

默认数据就是好久之前写博客创建的数据:

此时表中数据是这样的4条数据,只有名字,id,和年龄;
举个简单的例子:我们需要查询年龄大于40岁的人,使用存储过程来创建

delimiter//createprocedurep_test()beginselectid,name,agefromstudentwhereage>40;end//delimiter;

此时我们存储过程就已经创建好了:

2.2 带入参存储过程创建

还是以刚才的数据为例子:
创建存储过程:我们需要手动指定,年龄大于***岁的存储过程

delimiter//createprocedurequery_student_by_age(inint)beginselectid,name,agefromstudentwhereage>param_age;end//delimiter;

in：参数方向关键字
param_age：自定义参数名
int：参数的数据类型

例如:

delimiter//createprocedurep_test(invalint)beginselectid.name,agefromstudentwhereval>40;end//delimiter;

此时我们带参数的存储过程就已经创建好了;
我们也可以使用inout num int的方式来创建

set@num:=10;delimiter//createprocedurep_test(invalint)beginset@num=@num+val;end//delimiter;select@num;

2.3 存储过程调用方式

语法:

call存储过程名字(参数);

作用：存储过程向外输出结果，必须用@自定义变量承接，不能传常量。
例如调用我们上诉创建的查看:

delimiter//createprocedurep_test(invalint)beginselectid,name,agefromstudentwhereage>val;end//delimiter;callp_test(40);

结果: