MySQL中存储过程的简单使用案例

JoyousHorse

已于 2022-03-25 16:02:38 修改

阅读量2.4k

点赞数

分类专栏： MySQL 文章标签： mysql springboot

于 2022-03-25 15:17:17 首次发布

本文链接：https://blog.csdn.net/ONEMOOC/article/details/123736479

版权

MySQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了MySQL存储过程的基础知识，包括定义、优点和缺点，并通过SpringBoot项目展示了如何调用存储过程，实现数据复制。此外，还简单讲解了存储过程的语法和参数类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、存储过程简介

1.1 什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的一组SQL语句集合，包含数据类型、流程控制、输入和输出和它自己的函数库，编译后保存在数据库中，用户可随时调用它。

1.2 为什么要用存储过程

存储过程将sql处理操作封装在一个简单的处理单元中，存储数据库中，便于反复调用，在一定程度上能增加数据的处理效率，同时能防止表结构暴露，预防sql注入。在银行等金融行业，数据安全性高，程序逻辑变动少，常常使用存储过程来进行一部分业务逻辑处理；但在互联网行业，需求变化频繁，可扩展性要求高，系统移植概率高，往往少使用存储过程。

1.2.1 存储过程的优点

存储过程一次编译后，存储在数据库中，后续调用不需要再编译，执行速度快。
存储过程是在数据库内部进行数据处理，相较于外部程序调用接口（如Java 程序的 JDBC 接口）访问数据库内部数据，性能更优。

1.2.2 存储过程的缺点

存储过程依赖于数据库，例如Oracle的存储过程和MySQL的存储过程语法存在差异。若需要从Oracle迁移到MySQL，带来的改造量将是巨大的，这也是部分架构人员不推荐使用存储过程的原因。
存储过程将处理逻辑和数据库绑定，不利于系统解耦。
存储过程代码管理困难，不利于团队协作开发；当数据库表变化时，可能会影响存储过程。

二、Spring+MySQL调用存储过程

本节将使用SpringBoot+MySQL项目，演示存储过程的使用方法。

2.1 项目目标

搭建一个基于SpringBoot的web系统，在浏览器发送请求后，java代码通过mybatis执行存储过程，将表MYTEST中的数据拷贝到MYTEST_TEMP。

2.2 创建表及测试数据

(一)、建表MYTEST ：

CREATE TABLE IF NOT EXISTS `MYTEST`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

（二）、创建表MYTEST_TEMP

CREATE TABLE IF NOT EXISTS `MYTEST_TEMP`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
    `execute_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

（三）、在表MYTEST中插入数据

INSERT INTO MYTEST(title,author,submission_date)
VALUES ('任务-1','展示','2017-01-02'),
       ('任务-2','张二','2017-01-02'),
       ('任务-3','张三','2022-03-24'),
       ('任务-4','张四','2022-03-24'),
       ('任务-5','张五','2022-03-24')；

（四）、编写存储过程

在Navicat中创建存储过程的方式请参考：Navicat创建存储过程方法

创建名为TEST的存储过程，入参为execDate。

CREATE DEFINER=`root`@`%` PROCEDURE `TEST`(
  IN execDate  VARCHAR(8)
)
BEGIN

# 清空表
TRUNCATE TABLE MYTEST_TEMP; 

INSERT INTO MYTEST_TEMP 
(
id,
author,
submission_date,
execute_date
)
SELECT 
id,author,submission_date,DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS execute_date
FROM MYTEST
WHERE submission_date = execDate;
COMMIT;

END

2.3 编写Springboot代码

（一）、Controller层

创建HelloController类

@RestController
public class HelloController {

    @Autowired
    private ProcedureService procedureService;


    @RequestMapping("hello")
    public String hello(){

        procedureService.copyData("20220324");

        return  "hello world!";
    }    
}

（二）、Service层

创建ProcedureService接口

public interface ProcedureService {

    public void copyData(String date);
}

创建实现类

@Service
public class ProcedureServiceImpl  implements ProcedureService{


    @Autowired
    private ProcedureDao procedureDao;

    @Override
    public void copyData(String date){
        procedureDao.copyData(date);
    }
}

（三）、Dao层

创建ProcedureDao接口

public interface ProcedureDao {

    public void copyData(String date);
}

（四）、mapper.xml

创建mapper映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.dao.ProcedureDao">
    
    <update id="copyData" parameterType="java.lang.String"   statementType="CALLABLE">
      CALL TEST(#{execDate,mode=IN,jdbcType=VARCHAR})
    </update>
</mapper>

2.4 查看执行结果

在浏览器中请求http://localhost:8080/hello，查看运行结果，最后可以看到，MYTEST表中数据已导入到MYTEST_TEMP中。

在这里插入图片描述

三、存储过程语法介绍

存储过程分为存储过程声明、执行过程、异常处理部分（可选）。

3.1 MySQL存储过程参数介绍

MySQL的存储过程的参数分为三类:IN、OUT、INOUT。

IN 默认参数类型，代表入参
OUT 出参，可将结果传会给调用的程序
INOUT 即是入参也是出参

常用实例

声明无参的存储过程

CREATE DEFINER = CURRENT_USER PROCEDURE `test`()  # 声明名称为test的存储过程
BEGIN
   # 存储过程执行部分
	#Routine body goes here...

END;

声明入参为字符的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `TEST`(
  IN execDate  VARCHAR(8)
)
BEGIN
# 执行过程，它是业务逻辑处理部分
END

调用存储过程

# call 存储过程名称
call procedure_name()

删除存储过程

drop procedure name

四、参考文档