MySQL 存储过程的使用

发表于 阅读次数： 评论数： 本文字数： 1.5k 阅读时长 ≈ 1 分钟

大纲

存储过程的介绍

  • 存储过程是在数据库中存储的一组预编译的 SQL 语句，可以在需要时多次调用。
  • 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般的 SQl 语句每执行一次就编译一次，因此使用存储过程可以大大提高数据库的执行速度。
  • 通常，复杂的业务逻辑需要多条 SQL 语句。这些语句要分别地从客户端发送到服务器，当客户端和服务器之间的操作有很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户端和服务器之间的网络传输就会大大减少，从而降低了网络负载。

存储过程的优缺点

  • 存储过程的优点

    • 性能优势：存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，提高了数据库操作的性能。
    • 安全性：存储过程可以实现数据访问控制和权限管理，可以限制用户对数据库的操作。
    • 代码重用：存储过程可以被多个应用程序调用，提高代码的重用性和维护性。
    • 减少数据传输：通过存储过程可以将多个 SQL 语句打包执行，减少了数据在客户端和服务器之间的传输量。
    • 事务管理：存储过程可以支持事务控制，确保了多个 SQL 语句的原子性操作，保证数据的一致性和完整性。

  • 存储过程的缺点

    • 学习难度：编写存储过程需要特定的语法和知识，可能需要更多的学习成本。
    • 维护困难：当存储过程逻辑复杂时，维护和调试会变得更加困难。
    • 数据库平台依赖：存储过程的语法和功能有可能会受到数据库平台的限制，降低了代码的可移植性。
    • 性能问题：存储过程的使用不当可能会导致数据库性能下降，例如存储过程中的循环操作会增加数据库服务器的负担。
    • 开发环境限制：有些开发工具对存储过程的支持可能不够友好，开发过程可能会受到限制。

存储过程的使用场景

  • 代码重用：将常用的数据操作逻辑封装到一个存储过程中，方便重复调用，提高代码复用性和减少代码量。
  • 数据清洗和转换：存储过程可以对数据进行清洗、转换、规范化等操作，使数据更具有一致性，提高数据质量。
  • 报表生成：存储过程可以实现复杂的数据查询和计算，生成报表并输出所需的结果。
  • 安全性和权限控制：存储过程可以实现对数据的访问控制，只暴露必要的接口给用户，同时隐藏具体的数据结构和实现细节。
  • 事务管理：存储过程可以包含事务处理逻辑，确保了多个 SQL 语句的原子性操作，保证数据的一致性和完整性。

使用 JDBC 调用存储过程

存储过程的调用步骤

  • (1) 加载数据库驱动：加载 MySQL 驱动，如 Class.forName(com.mysql.jdbc.Driver)
  • (2) 建立数据库连接：使用驱动管理器获取数据库连接，如 DriverManager.getConnection(url, user, password)
  • (3) 准备调用存储过程的 SQL 语句：编写调用存储过程的 SQL 语句，如 {call 存储过程名称(参数1, 参数2, ...)}
  • (4) 创建 CallableStatement 对象：使用连接对象创建 CallableStatement 对象，并将 SQL 语句传递给它
  • (5) 设置输入参数：如果存储过程有输入参数，可以使用 setXxx(index, value) 方法设置参数的值
  • (6) 执行存储过程：调用 execute() 方法执行存储过程
  • (7) 处理输出参数：如果存储过程有输出参数，可以使用 getXxx(index) 方法获取输出参数的值
  • (8) 关闭连接：在使用完数据库连接后，需要关闭连接和相关的资源，可以调用 close() 方法关闭 Statement 对象和连接

存储过程的调用代码

  • 在 MySQL 中，使用存储过程插入新数据，并返回新数据的 ID
1
2
3
4
5
6
7
8
9
10
-- 创建存储过程
create procedure insert_Student(_name varchar(50), _age int, out _id int)
begin
    insert into student value(null, _name, _age);
    select max(stuId) into _id from student;
end;

-- 调用存储过程
call insert_Student('Jim', 23, @id);
select @id;
  • 在 Java 中，使用 JDBC 调用存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;


public class JdbcTest {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    static final String USER = "your_username";
    static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection cn = null;
        CallableStatement cstmt = null;

        try {
            // 加载数据库驱动
            Class.forName(JDBC_DRIVER);
            // 建立数据库连接
            cn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            // 创建 CallableStatement 对象
            cstmt = cn.prepareCall("{call insert_Student(?, ?, ?)}");
            // 设置输入参数
            cstmt.setString(1, "Jim");
            cstmt.setInt(2, 23);
            // 注册输出参数的类型
            cstmt.registerOutParameter(3, Types.INTEGER);
            // 执行存储过程
            cstmt.execute();
            // 获取输出参数的值
            System.out.println(cstmt.getString(3));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 释放资源
                if (cstmt != null) {
                    cstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                // 关闭连接
                if (cn != null) {
                    cn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}