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

server/2024/12/28 3:47:51/

目录

一、什么是存储过程?

二、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 类型包括 INTEGERVARCHARDECIMAL 等,具体类型需要与你的存储过程输出参数的实际类型一致。
  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_counttotal_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_counttotal_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_counttotal_count)必须与 MyBatis XML 文件中的参数名称一致。

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

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

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

 


http://www.ppmy.cn/server/153798.html

相关文章

【C++多重类循环依赖问题】基于class前向声明与类定义和实现分离的解决方案与分析

前言 在前几节中&#xff0c;我们讲述了如何借助CMake和CMakeLists去创建自定义C库&#xff0c;如何链接到.so并包含hpp去运行自定义库 # 【动态库.so | 头文件.hpp】基于CMake与CMakeList编写C自定义库 在C类编程中&#xff0c;我们常常会遇到多个类相互包含的工程文件&#…

Java前端基础—HTML

Java前端基础—HTML 目录 Java前端基础—HTML1.简介2.基础语法2.1HTML页面固定结构2.2标题标签2.3段落标签2.4换行标签2.5水平线标签2.6文本标签2.7图片标签2.8音频标签2.9视频标签2.10链接标签2.11列表标签2.12表格标签2.13表单标签2.14语义标签 1.简介 1.网页组成&#xff1…

Elasticsearch检索方案之一:使用from+size实现分页

前面两篇文章介绍了elasticsearch以及Kibana的安装&#xff0c;检索引擎以及可视化工具都已经安装完成&#xff0c;接下来介绍下如何使用golang的sdk实现简单的分页查询。 1、下载Elastic官方golang sdk 在讲解elasticsearch检索之前&#xff0c;需要先把golang的环境安装好&…

微服务分布式(二、注册中心Consul)

首先我们需要安装consul,到官网下载Consul Install | Consul | HashiCorp Developer 在解压的consul目录下 执行启动命令 consul agent -dev 启动服务 -dev表示开发模式 -server表示服务模式 启动后在浏览器访问8500端口http://localhost:8500/可以看到服务管理界面 项目…

YOLOv9-0.1部分代码阅读笔记-torch_utils.py

torch_utils.py utils\torch_utils.py 目录 torch_utils.py 1.所需的库和模块 2.def smart_inference_mode(torch_1_9check_version(torch.__version__, 1.9.0)): 3.def smartCrossEntropyLoss(label_smoothing0.0): 4.def smart_DDP(model): 5.def reshape_classif…

Charles安装证书过程(手机)

背景&#xff1a;使用模拟器抓包时&#xff0c;发现https请求无法抓取&#xff0c;需要安装相应证书。我自己是因为模拟器升级了安卓7&#xff0c;发现之前安装的证书无效了&#xff0c;需要重新安装。 参考博客&#xff1a;夜神模拟器12Charles进行Https抓包_模拟器抓包ssl-C…

十二月第22讲:巧用mask属性创建一个纯CSS图标库

&#xff08;Scalable Vector Graphics&#xff0c;可缩放矢量图形&#xff09;是一种基于 XML 的图像格式&#xff0c;用于定义二维图形。与传统的位图图像&#xff08;如 PNG 和 JPG&#xff09;不同&#xff0c;SVG 图像是矢量图形&#xff0c;可以在任何尺寸下保持清晰度&a…

C#关键字volatile

文章目录 一、 基本概念二、可见性问题没有 volatile 关键字的情况使用 volatile 关键字后的可见性保证 三、防止指令重排序指令重排序的概念volatile 防止指令重排序的原理 四、使用场景示例生产者 - 消费者模式示例 五、注意事项性能影响不能替代锁机制 一、 基本概念 在 C# …