Java 调用 Oracle 的存储过程

一、环境准备

需要的 jar 包：ojdbc14.jar，ojdbc6.jar
对应的 pom 配置：

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
</properties>

    <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

Oracle相关代码：

--将scott用户下的 emp 和 dept 表复制到当前用户下
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;

--创建存储过程：计算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
   s number(10);
   c emp.comm%type;
begin
   select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
   yearsal := s+c;
end;
--测试存储过程：查看号7369员工的年薪
declare
  yearsal number(10);
begin
  p_yearsal(7369, yearsal);
  dbms_output.put_line(yearsal);
end;

select * from emp;

--创建存储函数：计算年薪
create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);     
begin
  select sal*12+nvl(comm, 0) into s from emp where empno = eno;
  return s;
end;

--测试存储函数：查看号7369员工的年薪
--存储函数在调用的时候，返回值需要接收。
declare
  s number(10); 
begin
  s := f_yearsal(7369);
  dbms_output.put_line(s);
end;

二、Oracle连接测试

@Test
    public void javaCallOracle() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.245.128:1521:orcl", "orcluser", "root");
        //3.定义sql语句
        String sql = "select * from emp where empno = ?";
        //4.得到预编译的Statement对象
        PreparedStatement pstm = connection.prepareStatement(sql);
        //5.给参数赋值
        pstm.setObject(1, 7369);
        //6.执行数据库查询操作
        ResultSet rs = pstm.executeQuery();
        //7.输出结果
        while(rs.next()){
            System.out.println(rs.getString("ename"));
        }
        //8.释放资源
        rs.close();
        pstm.close();
        connection.close();
    }

三、Java 调用存储过程

{call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用

 @Test
    public void javaCallProcedure() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.245.128:1521:orcl", "orcluser", "root");
        //3.得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");
        //4.给参数赋值,第二个参数是一个输出类型的参数
        pstm.setObject(1, 7369);
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
        //执行数据库查询操作
        pstm.execute();
        //输出结果[第二个参数]
        System.out.println(pstm.getObject(2));
        //释放资源
        pstm.close();
        connection.close();
    }

四、Java 调用存储函数

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用

 	@Test
    public void javaCallFunction() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.245.128:1521:orcl", "orcluser", "root");
        //3.得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
        //4.给参数赋值,第1个参数是一个输出类型的参数
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        pstm.setObject(2, 7369);
        //执行数据库查询操作
        pstm.execute();
        //输出结果[第1个参数]
        System.out.println(pstm.getObject(1));
        //释放资源
        pstm.close();
        connection.close();
}