Java调用Oracle存储过程，以及Oracle中游标的使用

Java调用存储过程查询用户信息

数据库以查询居多，现举例说明Java调用存储过程进行查询，其他可举一反三。
1. 导入Oracle数据库驱动包
2. 创建数据库操作工具类

public class JdbcUtil {

    private static String driver = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "system";
    private static String password = "123456";

    private static Connection connection;
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() {
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeConnection() {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.

• 通过连接调用oracle存储过程
• 设置存储过程输入的参数，输出参数的类型
• 执行查询
• 获取输出结果

public class TestProcedure {

    @Test
    public void test(){
        //得到连接
        Connection conn = JdbcUtil.getConn();
        try {
            //通过连接调用存储过程selectStudent
            CallableStatement cs = conn.prepareCall("{call selectStudent(?,?,?)}");
            //设置传入的参数
            cs.setInt(1, 1);
            //设置输出的参数类型
            cs.registerOutParameter(2, OracleTypes.INTEGER);
            cs.registerOutParameter(3, OracleTypes.CURSOR);
            //执行
            cs.execute();

            //得到输出操作码
            System.out.println("操作码为:"+cs.getInt(2));
            //取出游标，查询的结果
            ResultSet rs = (ResultSet)cs.getObject(3);
            //结果集遍历
            while(rs.next()){
                System.out.println(rs.getString(2)+","+rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. oracle存储过程代码

 

create or replace 

procedure selectStudent( 

v_id in student.id%type, 

v_result out number, 

v_list out SYS_REFCURSOR 

) is 

v_sql varchar2(200); 

begin 

    v_sql:='select * from student where id>'||v_id; 

    open v_list for v_sql; 

    v_result:=1; 

exception 

    when others then 

    begin 

    rollback; 

    v_result:=-1; 

    end; 

end selectStudent;