oracle java调用oracle11g几种不同类型的存储过程的方法，即没有返回值的过程、有返回值的过程、返回列表的过程、返回带分页的列表的过程

一.maven引入oracle11g驱动包依赖

  <dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
    <scope>runtime</scope>
  </dependency>

二.创建过程并使用java程序调用

package com.cz;

import java.io.Closeable;
import java.io.IOException;
import java.sql.*;


public class TestOra {
    String driverClass = "oracle.jdbc.driver.OracleDriver"; //oracle的驱动
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";  //连接oracle路径方式 “”gfs“”是要建立连接的数据库名 1521端口
    String user = "scott";   //user是数据库的用户名
    String password = "123456";  //用户登录密码
    Connection conn = null;
    CallableStatement callableStatement = null;
    ResultSet resultSet = null;

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        TestOra testOra = new TestOra();
        //testOra.testNoOutPar(1001, "uo", 400);
        //testOra.testNoOutParUpdate(1001,800);
         //testOra.testOutPar(1001);
        //  testOra.testOutResultSet();
        testOra.testOutPage(3, 2);
    }

    public void testNoOutPar(int a, String b, int c) {
        try {
            //首先建立驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //驱动成功后进行连接
            conn = DriverManager.getConnection(url, user, password);
            callableStatement = conn.prepareCall("{ call scott.testa1(?,?,?) }");
            callableStatement.setInt(1, a);
            callableStatement.setString(2, b);
            callableStatement.setInt(3, c);
            callableStatement.execute();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public void testNoOutParUpdate(int a, int c) {
        try {
            //首先建立驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //驱动成功后进行连接
            conn = DriverManager.getConnection(url, user, password);
            callableStatement = conn.prepareCall("{ call scott.testa2(?,?) }");
            callableStatement.setInt(1, a);
            callableStatement.setInt(2, c);
            callableStatement.execute();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet, callableStatement, conn);
        }

    }

    public void testOutPar(int a) {
        try {
            //首先建立驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //驱动成功后进行连接
            conn = DriverManager.getConnection(url, user, password);
            callableStatement = conn.prepareCall("{ call scott.testa3(?,?,?) }");
            callableStatement.setInt(1, a);
            callableStatement.registerOutParameter(2, Types.VARCHAR);
            callableStatement.registerOutParameter(3, Types.INTEGER);
            callableStatement.execute();
            String name = callableStatement.getString(2);
            Integer sal = callableStatement.getInt(3);
            System.out.println("姓名：" + name + " 薪水：" + sal);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet, callableStatement, conn);
        }

    }

    public void testOutResultSet() {
        try {
            //首先建立驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //驱动成功后进行连接
            conn = DriverManager.getConnection(url, user, password);
            callableStatement = conn.prepareCall("{ call scott.testa4(?) }");
            callableStatement.registerOutParameter(1, -10);
            callableStatement.execute();
            resultSet = (ResultSet) callableStatement.getObject(1);
            while (resultSet.next()) {
                System.out.println("姓名：" + resultSet.getString(2) + " 薪水：" + resultSet.getInt(6));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet, callableStatement, conn);
        }

    }

    public void testOutPage(int a, int b) {
        try {
            //首先建立驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //驱动成功后进行连接
            conn = DriverManager.getConnection(url, user, password);
            callableStatement = conn.prepareCall("{ call scott.testa5(?,?,?) }");
            callableStatement.setInt(1, a);
            callableStatement.setInt(2, b);
            callableStatement.registerOutParameter(3, -10);
            callableStatement.execute();
            resultSet = (ResultSet) callableStatement.getObject(3);
            while (resultSet.next()) {
                System.out.println("姓名：" + resultSet.getString(2) + " 薪水：" + resultSet.getInt(6));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet, callableStatement, conn);
        }

    }

    public void close(AutoCloseable... closeables) {
        if (closeables != null) {
            for (AutoCloseable closeable : closeables) {
                try {
                    if (closeable != null)
                        closeable.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

（1）testNoOutPar方法对应 没有返回值的过程

create or replace procedure testa1(p1 in int ,p2 in varchar2,p3 in  int)
	is
	begin 
	insert into scott.emp (EMPNO,ENAME,SAL)values(p1,p2,p3);
	commit;
	end;

（2）testNoOutParUpdate方法对应 没有返回值的过程

create or replace procedure testa2(p1 in int ,p3 in  int)
	is
	begin 
	update  scott.emp  set SAL=p3 where EMPNO=p1;
	commit;
	end;

（3）testOutPar方法对应 有返回值的过程

callableStatement.registerOutParameter(索引列,类型) 用于定义存储过程中的输出类型

create or replace procedure testa3(p1 in int ,p2 out  varchar2,p3 out  int)
	is
	begin 
	select ENAME,SAL into p2,p3 from   scott.emp  where EMPNO=p1;
	end;

（4）testOutResultSet方法对应 返回列表的过程

注： callableStatement.registerOutParameter(1, -10);
在代码中 -10 代表 REF CURSOR

create or replace package  testpack
	is 
	type test_cursor is ref cursor;
	end testpack;
	/

create or replace procedure testa4(p_cursor out testpack.test_cursor  )
	is
	begin 
	open p_cursor  for select * from emp;
	end;

（5）testOutPage方法对应 返回带分页的列表的过程

create or replace procedure testa5(ps int,cs int,p_cursor out testpack.test_cursor  )
	is
	begin 
	open p_cursor  for select * from (select e.*,rownum rn from scott.emp e order by e.EMPNO) f where rn>ps*(cs-1) and rn<=ps*cs;
	end;