java调用oracle 存储过程 返回集合

java调用oracle 存储过程 返回集合

 

sys_refcursor是oracle9i以后系统定义的一个refcursor，主要作用是用于存储过程返回结果集。

 

1、pl/sql 存储过程

 

 

CREATE OR REPLACE PROCEDURE retCursor(ret_cursor OUT SYS_REFCURSOR) IS
    ret_cursor_value SYS_REFCURSOR;
  BEGIN
    OPEN ret_cursor_value FOR SELECT * FROM emp;
    ret_cursor := ret_cursor_value;
END retCursor;

 

2、java处理方法

 

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

public class CommonJDBC {

	public static ResultSet callProcedure(){
		
		ResultSet rs = null;
		
		Connection conn = DB.getConnection();
		
		try {
			CallableStatement cs = conn.prepareCall("{call retCursor(?)}");
			
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			
			cs.execute();
			
			rs = ((OracleCallableStatement)cs).getCursor(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e){
			e.printStackTrace();
		} 
		
		return rs;
	}
}

 

3、测试

 

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class TestCommonJDBC {

	@Test
	public void callProcedure(){
		
		ResultSet rs = CommonJDBC.callProcedure();
		
		try {
			int count = 0;
			while (null!=rs && rs.next()){
				String empId = rs.getString(1);
				String empName = rs.getString(2);
				System.out.println(empId+"\t"+empName);
				count++;
			}
			System.out.println("count="+count);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			Connection conn = DB.getConnection();
			if (null != conn){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (null != rs){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

   

4、输出结果

 

 

7369	SMITH
7499	ALLEN
7521	WARD
7566	JONES
7654	MARTIN
7698	BLAKE
7782	CLARK
7788	SCOTT
7839	KING
7844	TURNER
7876	ADAMS
7900	JAMES
7902	FORD
7934	MILLER