qidao123.com ToB IT社区-企服评测·应用市场»论坛 › 数据库 › Oracle › 数据库原理学习——存储过程详解

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

[复制链接]
发表于 2025-11-27 16:32:28 | 显示全部楼层 |阅读模式

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

×
目次
一、什么是存储过程？
二、MySQL 中的存储过程代码演示
（一）不带参数的存储过程
（二）带参数的存储过程
三、在SpringBoot+Mybatis项目中利用过程存储
完备代码示例

一、什么是存储过程？

        存储过程（Stored Procedure）是数据库中的一种可编程对象，它是一组为了完成特定功能的SQL语句聚集，存储在数据库中并以名称标识。存储过程可以吸收输入参数，返回输出参数，并在服务器端运行。这种方式进步了代码的复用性和性能，由于多次实行只必要发送调用下令，而不是多次传输SQL语句。
存储过程的长处包罗：

  • 性能优化：由于存储过程在数据库端运行，镌汰了网络通讯。
  • 代码复用：可以界说复杂的逻辑并重复利用。
  • 安全性：通过限定访问权限，可以防止直接访问底层表。
  • 简化复杂性：封装复杂的SQL逻辑，进步代码的可读性。
二、MySQL 中的存储过程代码演示

下面是一个完备的存储过程创建、调用和删除的示例：
（一）不带参数的存储过程

1. 创建示例数据库和表

  1. CREATE DATABASE IF NOT EXISTS example_db;
  2. USE example_db;
  4. CREATE TABLE employees (
  5.     id INT AUTO_INCREMENT PRIMARY KEY,
  6.     name VARCHAR(100),
  7.     salary DECIMAL(10, 2),
  8.     department VARCHAR(50)
  9. );
  11. -- 插入一些测试数据
  12. INSERT INTO employees (name, salary, department)
  13. VALUES
  14. ('Alice', 5000.00, 'HR'),
  15. ('Bob', 7000.00, 'IT'),
  16. ('Charlie', 6500.00, 'Finance');
复制代码
2. 创建存储过程

需求：创建一个存储过程，目的是，根据部门名称查询员工信息。
  1. DELIMITER $$
  3. CREATE PROCEDURE GetEmployeesByDepartment(
  4.     IN dept_name VARCHAR(50) -- 输入参数，部门名称
  5. )
  6. BEGIN
  7.     -- 查询指定部门的员工
  8.     SELECT id, name, salary, department
  9.     FROM employees
  10.     WHERE department = dept_name;
  11. END $$
  13. DELIMITER ;
复制代码
阐明：


  • DELIMITER $$：更改语句竣事符，防止存储过程界说中的分号辩论。
  • IN dept_name VARCHAR(50)：界说输入参数
    1. CALL GetEmployeesByDepartment('IT');
    复制代码
    ，表现调用时必要提供部门名称。
  • SELECT ...：存储过程的主体逻辑。
3. 调用存储过程

利用 CALL 关键字调用存储过程。
  1. CALL GetEmployeesByDepartment('IT');
复制代码
输出示例：
  1. +----+------+--------+------------+
  2. | id | name | salary | department |
  3. +----+------+--------+------------+
  4. |  2 | Bob  | 7000.00| IT         |
  5. +----+------+--------+------------+
复制代码
（二）带参数的存储过程

需求：盘算某个部门的员工匀称工资，并通过输出参数返回。
  1. DELIMITER $$
  3. CREATE PROCEDURE GetAverageSalaryByDepartment(
  4.     IN dept_name VARCHAR(50), -- 输入参数
  5.     OUT avg_salary DECIMAL(10, 2) -- 输出参数
  6. )
  7. BEGIN
  8.     -- 计算平均工资
  9.     SELECT AVG(salary) INTO avg_salary
  10.     FROM employees
  11.     WHERE department = dept_name;
  12. END $$
  14. DELIMITER ;
复制代码
调用存储过程并获取效果：
  1. SET @avg_salary = 0; -- 定义变量
  2. CALL GetAverageSalaryByDepartment('HR', @avg_salary);
  3. SELECT @avg_salary AS AverageSalary; -- 查看结果
复制代码
输出示例：
  1. +---------------+
  2. | AverageSalary |
  3. +---------------+
  4. |       5000.00 |
  5. +---------------+
复制代码
5. 删除存储过程

如果必要删除存储过程，可以利用 DROP PROCEDURE 语句。
  1. DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
  2. DROP PROCEDURE IF EXISTS GetAverageSalaryByDepartment;
复制代码
总结

存储过程是数据库中一个强大的工具，它封装了复杂的业务逻辑，进步了性能和代码复用性。MySQL 的存储过程支持输入参数、输出参数和逻辑控制语句，可以满意多种业务需求。在计划存储过程时必要注意性能优化和安全性，克制过分复杂化导致难以维护。
三、在SpringBoot+Mybatis项目中利用过程存储

 具体步调如下：

  • 存储过程的界说 存储过程在 MySQL 数据库中界说并创建。发起利用 SQL 脚本直接在数据库中实行，而不是通过 MyBatis 的 <sql> 标签动态创建。
  • Mapper XML 设置 在 Mapper XML 中，通过 CALLABLE 范例调用存储过程，并为输入和输出参数明确指定 jdbcType。
  • DAO 层 利用 Map 或单独参数的方式转达输入和输出参数。Map 是一种更机动的方式。
  • Service 层 在 Service 层封装调用逻辑，并处置处罚业务逻辑或错误环境。
  • Controller 层 吸收前端哀求并调用 Service 层，将效果返回给前端。
完备代码示例

1. 存储过程界说

存储过程必要直接在数据库中创建：
  1. DELIMITER $$
  3. CREATE PROCEDURE GetFaceCollectedCount(
  4.     IN input_community_id INT,       -- 输入参数：社区ID
  5.     OUT face_collected_count INT,   -- 输出参数：已采集人脸的居民人数
  6.     OUT total_count INT             -- 输出参数：社区居民人数
  7. )
  8. BEGIN
  9.     -- 查询社区居民总人数
  10.     SELECT COUNT(*) INTO total_count
  11.     FROM person
  12.     WHERE community_id = input_community_id;
  14.     -- 查询已采集人脸的居民人数
  15.     SELECT COUNT(*) INTO face_collected_count
  16.     FROM person
  17.     WHERE community_id = input_community_id AND state = 2;
  18. END $$
  20. DELIMITER ;
复制代码
代码寄义：


  • 输入参数 input_community_id：指定目的社区的 ID。
  • 输出参数 total_count：存储该社区中住民的总人数。
  • 输出参数 face_collected_count：存储该社区中已缴收罗人脸信息的住民人数。
  • 逻辑：

    • 第一部门统计符合条件的总人数，并赋值给 total_count。
    • 第二部门统计符合条件且 state = 2 的人数（表现人脸收罗已完成的住民）。

作用：这是在数据库层界说的逻辑，用于处置处罚社区干体系计。
2. Mapper XML 设置

设置用于调用‘存储过程’的sql语句：
注意 statementType="CALLABLE" 是必须的，否则 MyBatis 无法准确调用存储过程。
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.example.mapper.CommunityMapper">
  7.     <!-- 调用存储过程 -->
  8.     <select id="getFaceCollectedCount" statementType="CALLABLE">
  9.         CALL GetFaceCollectedCount(
  10.             #{input_community_id, mode=IN, jdbcType=INTEGER},
  11.             #{face_collected_count, mode=OUT, jdbcType=INTEGER},
  12.             #{total_count, mode=OUT, jdbcType=INTEGER}
  13.         )
  14.     </select>
  16. </mapper>
复制代码
具体阐明


  • jdbcType 的须要性：

    • MyBatis 在处置处罚存储过程时，对于 OUT 参数，必要知道参数的 JDBC 范例。
    • 常用的 JDBC 范例包罗 INTEGER、VARCHAR、DECIMAL 等，具体范例必要与你的存储过程输出参数的现实范例同等。

  • mode 属性：

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

  • 完备参数阐明：

    • #{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 接口：
  1. package com.example.mapper;
  3. import java.util.Map;
  5. public interface CommunityMapper {
  6.     /**
  7.      * 调用存储过程获取人脸采集和总人数
  8.      *
  9.      * @param resultMap 包含输入参数和输出参数的 Map
  10.      */
  11.     void getFaceCollectedCount(Map<String, Integer> resultMap);
  12. }
复制代码
代码寄义： 


  • 参数：

    • 利用 Map<String, Integer> 作为输入和输出容器。
    • resultMap 中包罗输入参数（如 input_community_id）和输出参数（如 face_collected_count 和 total_count）。

  • Map 的作用：

    • MyBatis 会通过 Map 的键名找到与存储过程占位符相对应的参数。
    • 输入参数会从 Map 中读取，存储过程的输出参数会写回到 Map 中。

作用：界说存储过程的调用方法，供 Service 层利用。
4. Service 层

封装存储过程的调用逻辑：
  1. package com.example.service;
  3. import com.example.mapper.CommunityMapper;
  4. import org.springframework.beans.factory.annotation.Autowired;
  5. import org.springframework.stereotype.Service;
  7. import java.util.HashMap;
  8. import java.util.Map;
  10. @Service
  11. public class CommunityService {
  13.     @Autowired
  14.     private CommunityMapper communityMapper;
  16.     /**
  17.      * 调用存储过程
  18.      *
  19.      * @param communityId 社区ID
  20.      * @return 存储过程返回的数据
  21.      */
  22.     public Map<String, Integer> getFaceCollectedCount(int communityId) {
  23.         // 创建 Map 用于存储输入和输出参数
  24.         Map<String, Integer> resultMap = new HashMap<>();
  25.         resultMap.put("input_community_id", communityId); // 输入参数
  26.         resultMap.put("face_collected_count", null);      // 初始化输出参数
  27.         resultMap.put("total_count", null);
  29.         // 调用存储过程
  30.         communityMapper.getFaceCollectedCount(resultMap);
  32.         return resultMap;
  33.     }
  34. }
复制代码
getFaceCollectedCount（）方法的寄义：


  • 输入：参数 communityId是前端传入的社区 ID。
  • 逻辑：

    • 创建 resultMap，初始化输入参数（input_community_id）和输出参数（face_collected_count、total_count）。
    • 调用 Mapper 的 getFaceCollectedCount 方法，实行存储过程，resultMap 会更新输出参数的值。

  • 输出：返回包罗存储过程效果的 Map。
作用：封装业务逻辑，为 Controller 层提供服务。
 
5. Controller 层

处置处罚前端哀求并返回效果：
  1. package com.example.controller;
  3. import com.example.service.CommunityService;
  4. import com.example.utils.Result;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.web.bind.annotation.*;
  8. import java.util.Map;
  10. @RestController
  11. @RequestMapping("/community")
  12. public class CommunityController {
  14.     @Autowired
  15.     private CommunityService communityService;
  17.     /**
  18.      * 调用存储过程
  19.      *
  20.      * @param id 前端传入的社区ID数组
  21.      * @return 存储过程的返回结果
  22.      */
  23.     @PostMapping("/procedure")
  24.     public Result procedure(@RequestBody Integer[] id) {
  25.         int communityId = id[0];
  26.         System.out.println("Community ID: " + communityId);
  28.         Map<String, Integer> resMap = communityService.getFaceCollectedCount(communityId);
  30.         if (resMap.get("face_collected_count") == null || resMap.get("total_count") == null) {
  31.             return Result.error("存储过程调用失败");
  32.         }
  34.         return Result.ok().put("data", resMap);
  35.     }
  36. }
复制代码
 
6. 前端哀求示例

返回示例：
  1. {
  2.     "code": 200,
  3.     "message": "成功",
  4.      "data":
  5.           {
  6.              "face_collected_count": 10,     
  7.              "total_count": 50   
  8.           }
  9. }
复制代码
关键点阐明


  • 参数名与 MyBatis XML 设置同等： resultMap 中的键名（如 face_collected_count 和 total_count）必须与 MyBatis XML 文件中的参数名称同等。
  • jdbcType 是必须的： 在 MyBatis 中调用存储过程时，jdbcType 是输出参数的必填项，必须与数据库字段范例匹配。
  • Map 的利用： Map 是一种机动的方式，用于转达输入参数和吸收输出参数，实用于存储过程的复杂调用。
  • Service 层封装逻辑： Service 层处置处罚存储过程调用的逻辑，并将非常和错误信息同一处置处罚，进步代码的可维护性。
 
回复

使用道具 举报

返回列表

鼠扑
+ 我要发帖

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表