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

数据库原理学习——存储过程详解

news 2024/12/23 23:08:07

目录

一、什么是存储过程？

二、MySQL 中的存储过程代码演示

（一）不带参数的存储过程

（二）带参数的存储过程

三、在SpringBoot+Mybatis项目中使用过程存储

完整代码示例

一、什么是存储过程？

        存储过程（Stored Procedure）是数据库中的一种可编程对象，它是一组为了完成特定功能的SQL语句集合，存储在数据库中并以名称标识。存储过程可以接收输入参数，返回输出参数，并在服务器端运行。这种方式提高了代码的复用性和性能，因为多次执行只需要发送调用命令，而不是多次传输SQL语句。

存储过程的优点包括：

  1. 性能优化：由于存储过程在数据库端运行，减少了网络通信。
  2. 代码复用：可以定义复杂的逻辑并重复使用。
  3. 安全性：通过限制访问权限，可以防止直接访问底层表。
  4. 简化复杂性：封装复杂的SQL逻辑，提高代码的可读性。

二、MySQL 中的存储过程代码演示

下面是一个完整的存储过程创建、调用和删除的示例：

（一）不带参数的存储过程

1. 创建示例数据库和表
CREATE DATABASE IF NOT EXISTS example_db;
USE example_db;CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2),department VARCHAR(50)
);-- 插入一些测试数据
INSERT INTO employees (name, salary, department)
VALUES 
('Alice', 5000.00, 'HR'),
('Bob', 7000.00, 'IT'),
('Charlie', 6500.00, 'Finance');
2. 创建存储过程

需求：创建一个存储过程，目的是，根据部门名称查询员工信息。

DELIMITER $$CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50) -- 输入参数，部门名称
)
BEGIN-- 查询指定部门的员工SELECT id, name, salary, departmentFROM employeesWHERE department = dept_name;
END $$DELIMITER ;

说明：

  • DELIMITER $$：更改语句结束符，防止存储过程定义中的分号冲突。
  • IN dept_name VARCHAR(50)：定义输入参数 
    CALL GetEmployeesByDepartment('IT');
    ，表示调用时需要提供部门名称。
  • SELECT ...：存储过程的主体逻辑。
3. 调用存储过程

使用 CALL 关键字调用存储过程。

CALL GetEmployeesByDepartment('IT');

输出示例：

+----+------+--------+------------+
| id | name | salary | department |
+----+------+--------+------------+
|  2 | Bob  | 7000.00| IT         |
+----+------+--------+------------+

（二）带参数的存储过程

需求：计算某个部门的员工平均工资，并通过输出参数返回。

DELIMITER $$CREATE PROCEDURE GetAverageSalaryByDepartment(IN dept_name VARCHAR(50), -- 输入参数OUT avg_salary DECIMAL(10, 2) -- 输出参数
)
BEGIN-- 计算平均工资SELECT AVG(salary) INTO avg_salaryFROM employeesWHERE department = dept_name;
END $$DELIMITER ;

调用存储过程并获取结果：

SET @avg_salary = 0; -- 定义变量
CALL GetAverageSalaryByDepartment('HR', @avg_salary);
SELECT @avg_salary AS AverageSalary; -- 查看结果

输出示例：

+---------------+
| AverageSalary |
+---------------+
|       5000.00 |
+---------------+
5. 删除存储过程

如果需要删除存储过程，可以使用 DROP PROCEDURE 语句。

DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
DROP PROCEDURE IF EXISTS GetAverageSalaryByDepartment;

总结

存储过程是数据库中一个强大的工具，它封装了复杂的业务逻辑，提高了性能和代码复用性。MySQL 的存储过程支持输入参数、输出参数和逻辑控制语句，可以满足多种业务需求。在设计存储过程时需要注意性能优化和安全性，避免过度复杂化导致难以维护。

三、在SpringBoot+Mybatis项目中使用过程存储

 详细步骤如下：

  1. 存储过程的定义 存储过程在 MySQL 数据库中定义并创建。建议使用 SQL 脚本直接在数据库中执行，而不是通过 MyBatis 的 <sql> 标签动态创建。

  2. Mapper XML 配置 在 Mapper XML 中，通过 CALLABLE 类型调用存储过程，并为输入和输出参数明确指定 jdbcType。

  3. DAO 层 使用 Map 或单独参数的方式传递输入和输出参数。Map 是一种更灵活的方式。

  4. Service 层 在 Service 层封装调用逻辑，并处理业务逻辑或错误情况。

  5. Controller 层 接收前端请求并调用 Service 层，将结果返回给前端。

完整代码示例

1. 存储过程定义

存储过程需要直接在数据库中创建：

DELIMITER $$CREATE PROCEDURE GetFaceCollectedCount(IN input_community_id INT,       -- 输入参数：社区IDOUT face_collected_count INT,   -- 输出参数：已采集人脸的居民人数OUT total_count INT             -- 输出参数：社区居民人数
)
BEGIN-- 查询社区居民总人数SELECT COUNT(*) INTO total_countFROM personWHERE community_id = input_community_id;-- 查询已采集人脸的居民人数SELECT COUNT(*) INTO face_collected_countFROM personWHERE community_id = input_community_id AND state = 2;
END $$DELIMITER ;

代码含义：

  • 输入参数 input_community_id：指定目标社区的 ID。
  • 输出参数 total_count：存储该社区中居民的总人数。
  • 输出参数 face_collected_count：存储该社区中已缴采集人脸信息的居民人数。
  • 逻辑：
    • 第一部分统计符合条件的总人数，并赋值给 total_count。
    • 第二部分统计符合条件且 state = 2 的人数（表示人脸采集已完成的居民）。

作用：这是在数据库层定义的逻辑，用于处理社区相关统计。

2. Mapper XML 配置

配置用于调用‘存储过程’的sql语句：

注意 statementType="CALLABLE" 是必须的，否则 MyBatis 无法正确调用存储过程。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.CommunityMapper"><!-- 调用存储过程 --><select id="getFaceCollectedCount" statementType="CALLABLE">CALL GetFaceCollectedCount(#{input_community_id, mode=IN, jdbcType=INTEGER},#{face_collected_count, mode=OUT, jdbcType=INTEGER},#{total_count, mode=OUT, jdbcType=INTEGER})</select></mapper>

具体说明

  1. jdbcType 的必要性：

    • MyBatis 在处理存储过程时，对于 OUT 参数，需要知道参数的 JDBC 类型。
    • 常用的 JDBC 类型包括 INTEGER、VARCHAR、DECIMAL 等，具体类型需要与你的存储过程输出参数的实际类型一致。

  2. mode 属性：

    • IN 表示输入参数。
    • OUT 表示输出参数。

  3. 完整参数说明：

    • #{input_community_id, mode=IN, jdbcType=INTEGER}：指定输入参数的 JDBC 类型。
    • #{face_collected_count, mode=OUT, jdbcType=INTEGER}：指定输出参数 face_collected_count 的 JDBC 类型。
    • #{total_count, mode=OUT, jdbcType=INTEGER}：指定输出参数 total_count 的 JDBC 类型。
3. DAO 接口

定义 Mapper 接口：

package com.example.mapper;import java.util.Map;public interface CommunityMapper {/*** 调用存储过程获取人脸采集和总人数** @param resultMap 包含输入参数和输出参数的 Map*/void getFaceCollectedCount(Map<String, Integer> resultMap);
}

代码含义： 

  • 参数：
    • 使用 Map<String, Integer> 作为输入和输出容器。
    • resultMap 中包含输入参数（如 input_community_id）和输出参数（如 face_collected_count 和 total_count）。
  • Map 的作用：
    • MyBatis 会通过 Map 的键名找到与存储过程占位符相对应的参数。
    • 输入参数会从 Map 中读取，存储过程的输出参数会写回到 Map 中。

作用：定义存储过程的调用方法，供 Service 层使用。

4. Service 层

封装存储过程的调用逻辑：

package com.example.service;import com.example.mapper.CommunityMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.HashMap;
import java.util.Map;@Service
public class CommunityService {@Autowiredprivate CommunityMapper communityMapper;/*** 调用存储过程** @param communityId 社区ID* @return 存储过程返回的数据*/public Map<String, Integer> getFaceCollectedCount(int communityId) {// 创建 Map 用于存储输入和输出参数Map<String, Integer> resultMap = new HashMap<>();resultMap.put("input_community_id", communityId); // 输入参数resultMap.put("face_collected_count", null);      // 初始化输出参数resultMap.put("total_count", null);// 调用存储过程communityMapper.getFaceCollectedCount(resultMap);return resultMap;}
}

getFaceCollectedCount（）方法的含义：

  • 输入：参数 communityId是前端传入的社区 ID。
  • 逻辑：
    • 创建 resultMap，初始化输入参数（input_community_id）和输出参数（face_collected_count、total_count）。
    • 调用 Mapper 的 getFaceCollectedCount 方法，执行存储过程，resultMap 会更新输出参数的值。
  • 输出：返回包含存储过程结果的 Map。

作用：封装业务逻辑，为 Controller 层提供服务。

 

5. Controller 层

处理前端请求并返回结果：

package com.example.controller;import com.example.service.CommunityService;
import com.example.utils.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.Map;@RestController
@RequestMapping("/community")
public class CommunityController {@Autowiredprivate CommunityService communityService;/*** 调用存储过程** @param id 前端传入的社区ID数组* @return 存储过程的返回结果*/@PostMapping("/procedure")public Result procedure(@RequestBody Integer[] id) {int communityId = id[0];System.out.println("Community ID: " + communityId);Map<String, Integer> resMap = communityService.getFaceCollectedCount(communityId);if (resMap.get("face_collected_count") == null || resMap.get("total_count") == null) {return Result.error("存储过程调用失败");}return Result.ok().put("data", resMap);}
}

 

6. 前端请求示例

返回示例：

{ "code": 200, "message": "成功","data": { "face_collected_count": 10,     "total_count": 50    } 
}

关键点说明

  1. 参数名与 MyBatis XML 配置一致： resultMap 中的键名（如 face_collected_count 和 total_count）必须与 MyBatis XML 文件中的参数名称一致。

  2. jdbcType 是必需的： 在 MyBatis 中调用存储过程时，jdbcType 是输出参数的必填项，必须与数据库字段类型匹配。

  3. Map 的使用： Map 是一种灵活的方式，用于传递输入参数和接收输出参数，适用于存储过程的复杂调用。

  4. Service 层封装逻辑： Service 层处理存储过程调用的逻辑，并将异常和错误信息统一处理，提高代码的可维护性。

 


http://www.mrgr.cn/news/81092.html

相关文章：

  • 探索 JSON 数据在关系型数据库中的应用：MySQL 与 SQL Server 的对比
  • 免费开源！推荐一款网页版数据库管理工具！
  • OpenHarmony-6.IPC/RPC组件
  • 酷黑金色配色 影片素材不过时 色彩丰富 电影主题html
  • sql server一些冷知识
  • Linux下部署MySQL8.0集群 - 主从复制(一主两从)
  • AtCoder Beginner Contest 385（A~F）题解
  • 【微服务】SpringBoot 整合Redis实现延时任务处理使用详解
  • kafka理解记录
  • Java重要面试名词整理（二）：SpringMyBatis
  • SMMU软件指南SMMU编程之虚拟机结构和缓存
  • List接口
  • 机器学习（Machine Learning）的安全问题
  • 以太网详解（三）FPGA以太网IP配置（Quartus平台）
  • C++的封装(十四)：《设计模式》这本书
  • Kafka快速扫描
  • Redis存在安全漏洞
  • EasyPoi 使用$fe:模板语法生成Word动态行
  • [react 3种方法] 获取ant组件ref用ts如何定义?
  • 麒麟操作系统服务架构保姆级教程（三）ssh远程连接
  • en3d 部署笔记
  • 数据可视化echarts学习笔记
  • 【老白学 Java】HashSet 应用 - 卡拉 OK（五）
  • 第1章 命题逻辑
  • Android13下拉状态栏QS面板的加载流程解析
  • 搭建MPI/CUDA开发环境