JDBC调用Oracle存储过程详解

蔚落 2022-02-17 16:11 396阅读 0赞

## 一、为什么要调用存储过程 ##

**1、概述**

存储过程说白了就是一堆 SQL 的合并，中间加了点逻辑控制，但是存储过程处理比较复杂的业务时比较实用。比如说：一个复杂的数据操作，如果你在前台处理的话，可能会涉及到多次数据库连接；但如果你用存储过程的话，就只有一次。从响应时间上来说有优势，也就是说存储过程可以给我们带来运行效率提高的好处。

**2、优势**

*  存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
 *  当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
 *  存储过程可以重复使用,可减少数据库开发人员的工作量。
 *  安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

**3、缺点**

*  调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
 *  移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
 *  重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
 *  如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的。维护起来更加麻烦！

## 二、使用JDBC调用存储过程 ##

**步骤如下：**

*  1、通过Connection对象的prepareCall()方法创建一个CallableStatement对象实例，在使用Connection对象的prepareCall() 方法时，需要传入一个String类型的字符串，该字符串用于指明如何调用存储过程。
 *  2、通过CallableStatement对象的registerOutParameter() 方法注册Out参数。
 *  3、通过CallableStatement对象的setXxx()方法设定IN或IN OUT 参数，若想将参数设为null，可以使用setNUll()。
 *  4、通过CallableStatement对象的execute()方法执行存储过程。
 *  5、如果所调用的是带返回参数的存储过程没还需要通过CallableStatement对象的getXxx()函数进行获取。

**说明：**

1、CallableStatement的所有超级接口为PreparedStatement、Statement、Wrapper。其中继承自PreparedStatement接口。CallableStatement主要是调用数据库中的存储过程。在使用CallableStatement时可以接收存储过程的返回值。CallableStatement对象为所有的DBMS提供了一种标准的形式去调用数据库中已存在的存储过程。对数据库中存储过程的调用是CallableStatement对象所含的内容。有两种形式：1：形式带结果参数；2：形式不带结果参数。结果参数是一种输出参数(存储过程中的输出OUT参数)，是存储过程的返回值。两种形式都有带有数量可变的输入、输出、输入和输出的参数。用问号做占位符。

*  形式带结果参数语法格式：`{ ? = call 存储过程名[(?, ?, ?, ...)]};`
 *  形式不带结果参数语法格式：`{ call 存储过程名[(?, ?, ?, ...)]};`
 *  方括号为可选内容

2、CallableStatement类中的方法如下，用法与PreparedStatement类似。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]

*  可以使用getXXX(int parameterIndex)根据参数下标获取（下标从1开始），或使用getXXX(String parameterName) 根据参数名获取。
 *  registerOutParameter(int parameterIndex, int sqlType):按顺序位置parameterIndex将OUT参数注册为JDBC类型sqlType。
 *  wasNull():查询最后一个读取的OUT参数是否为SQL Null。

**准备工作**

*  封装一个获取Connection的工具类BaseDao.java

package cn.demo;
    
    import java.sql.*;
    public class BaseDao {
    
        private static  String driver="oracle.jdbc.OracleDriver";   //数据库驱动
        private static String url="jdbc:oracle:thin:@localhost:1521:orcl";  //url路径
        private static String username="scott"; //用户名
        private static String password="123456";    //密码
    
        private static Connection connection;
    
        static {
            try {
                //加载驱动
                Class.forName(driver);
            }catch (ClassNotFoundException e){
                throw new ExceptionInInitializerError(e);
            }
        }
    
        /**
         * 获取数据库连接
         * @return
         */
        public static Connection getConnection(){
            if (connection==null) {
                try {
                    //获取连接
                    connection= DriverManager.getConnection(url,username,password);
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            return connection;
        }
    
        /**
         * 关闭连接 释放资源
         * @param con 连接对象
         * @param stmt 执行对象
         * @param rs 结果集
         */
        public static void closeConnection(Connection con, Statement stmt,ResultSet rs){
            if (rs!=null) {
                try {
                    rs.close();
                }catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
            if (stmt!=null) {
                try {
                    stmt.close();
                }catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con!=null) {
                try {
                    con.close();
                }catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }
    }

*  操作数据表：Dept部门表–Scott用户下测试表

-- Create table
    create table DEPT
    (
      deptno NUMBER(2) primary key,
      dname  VARCHAR2(20),
      loc    VARCHAR2(20)
    )

*  Dept.java实体类

package cn.demo;
    
    /**
     * 部门表
     */
    public class Dept {
    
        /**
         * 部门编号
         */
        private Integer deptNo;
    
        /**
         * 部门名称
         */
        private String dName;
    
        /**
         * 部门所在地区
         */
        private String loc;
    
        public Integer getDeptNo() {
            return deptNo;
        }
    
        public void setDeptNo(Integer deptNo) {
            this.deptNo = deptNo;
        }
    
        public String getdName() {
            return dName;
        }
    
        public void setdName(String dName) {
            this.dName = dName;
        }
    
        public String getLoc() {
            return loc;
        }
    
        public void setLoc(String loc) {
            this.loc = loc;
        }
    }

--------------------

**Demo1： 创建存储过程：获取所有部门信息（调用带有输出参数的存储过程）**

-- 获取全部部门
    create or replace procedure pro_findAllDept(
           cur_dept OUT sys_refcursor  --存放所有部门信息的游标
    )
    as
    begin
          open cur_dept for
               select * from scott.dept;
    end;

**注意创建后需对存储过程进行编译**

测试类

package cn.demo;
    
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
    import org.junit.jupiter.api.Test;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    public class DeptTest {
    
        /**
         * 获取所有部门
         * @return
         */
        @Test
        public void findAllDept(){
            //连接对象
            Connection connection=null;
            //创建调用存储过程的对象CallableStatement
            CallableStatement call=null;
            ResultSet resultSet=null;   //存放结果集
            //调用存储过程的sql语句
            String sql="{call pro_findAllDept(?)}";
            List<Dept> deptList=new ArrayList<>();  //存放所有部门
            try {
                //获取连接对象
                connection=BaseDao.getConnection();
                call=connection.prepareCall(sql);
                //申明输出参数类型：下标从1开始
                call.registerOutParameter(1, OracleTypes.CURSOR);   //返回多行：游标类型
                //执行调用
                call.execute();
    
                //接收返回的结果
                 resultSet=((OracleCallableStatement)call).getCursor(1);
                while (resultSet.next()){
                    Dept dept=new Dept();
                    dept.setDeptNo(resultSet.getInt("deptNo"));
                    dept.setdName(resultSet.getString("dName"));
                    dept.setLoc(resultSet.getString("loc"));
                    deptList.add(dept);
                }
                //遍历
                for (Dept dept:deptList){
                    System.out.println("部门编号："+dept.getDeptNo()+"\t"+"部门名称："+dept.getdName());
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                //释放资源
                BaseDao.closeConnection(connection,call,resultSet);
    
            }
        }
    }

运行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]

**Demo2：进行插入操作，新增部门（调用带有输入参数的存储过程）**

存储过程：

-- 新增部门
    create or replace procedure pro_saveDept(
           v_dName in varchar2, -- 部门名称
           v_loc   in varchar2  --所在地区
    )
    as
    begin
            insert into scott.dept(deptno,dname,loc) values(11,v_dName,v_loc);  
            commit;
      end;

测试方法：

/**
         * 新增部门
         */
        @Test
        public void addNewDept(){
            //连接对象
            Connection connection=null;
            //创建调用存储过程的对象CallableStatement
            CallableStatement call=null;
            String sql="{call pro_saveDept(?,?)}";
            try {
                connection=BaseDao.getConnection();
                call=connection.prepareCall(sql);
                //设置输入参数
                call.setString(1,"测试部门001");
                call.setString(2,"北京东城区");
    
                //执行存储过程
                call.execute();
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                BaseDao.closeConnection(connection,call,null);
            }
        }

**Demo3：根据部门编号获取执行部门信息（调用带有输入输出参数的存储过程）**

存储过程：

--根据部门编号查找部门
    create or replace procedure pro_findByDeptNo(
           v_deptNo in number,  --部门编号
           cur_dept out sys_refcursor   
    )
    as
    begin
           open cur_dept for
           select * from scott.dept d where d.deptno=v_deptNo;
      end;

测试方法：

/**
         * 根据部门编号获取部门对象
         */
        @Test
        public void findDeptByDeptNo(){
            //连接对象
            Connection connection=null;
            //创建调用存储过程的对象CallableStatement
            CallableStatement call=null;
            Dept dept=new Dept();
            String sql="{call pro_findByDeptNo(?,?)}";
            try {
                connection=BaseDao.getConnection();
                call=connection.prepareCall(sql);
                //传值
                call.setInt(1,11);
                //设置返回参数类型
                call.registerOutParameter(2, OracleTypes.CURSOR);
                //执行
                call.execute();
                //接收返回的dept
               ResultSet resultSet= (ResultSet)call.getObject(2);
                if (resultSet.next()){
                    dept.setDeptNo(resultSet.getInt("deptNo"));
                    dept.setdName(resultSet.getString("dName"));
                    dept.setLoc(resultSet.getString("loc"));
                }
                System.out.println("部门编号："+dept.getDeptNo()+"\t"+"部门名称："+dept.getdName());
    
            }catch (Exception e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeConnection(connection,call,null);
            }
    
        }

执行结果：  
![在这里插入图片描述][20190419220750138.png]

> 关注微信公众号【程序媛琬淇】，专注分享Java干货，给你意想不到的收获。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]: /images/20220217/2c537a4762c54dbe9ca843809a2c055e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]: /images/20220217/097a1d1405a5452591e2e48fcb87a198.png
[20190419220750138.png]: /images/20220217/b02882ae8eb6469280812c636a484319.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20220217/1506ddbdf1a64fa1bc17a024e5c21315.png