Oracle返回结果集用JDBC读取

废话不多说 进入正题： Oracle返回结果集然后用JDBC调用。 
方法有两种，以Oracle scott用户下的DEPT表为例。 

1. 程序包 

sql 代码

create
 
or
 
replace
 package Test_cursor 
as
  type my_cursor 
is
 ref 
cursor
;
  
procedure
 my_procedure(para 
out
 my_cursor);
end
 Test_cursor;

create
 
or
 
replace
 package body Test_cursor 
as
  
procedure
 my_procedure(para 
out
 my_cursor) 
is
  
begin
    
open
 para 
for
      
select
 d.deptno, d.dname 
from
 dept d;
  
end
 my_procedure;
end
 Test_cursor;

2. 程序包+存储过程

create
 
or
 
replace
 package types 
as
  type cursorType 
is
 ref 
cursor
;
end
;

create
 
or
 
replace
 
procedure
 getemps(p_cursor 
out
 types.cursorType) 
as
begin
  
open
 p_cursor 
for
    
select
 d.deptno, d.dname 
from
 dept d;
end
;

以此类推，用程序包+函数同样可以。 

下面是测试类： 

java 代码

package
 com.chnic.procedure;

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

import
 oracle.jdbc.driver.OracleTypes;

public
 
class
 TestProcedure {

private
 String path = 
"jdbc:oracle:thin:@localhost:1521:Oracle"
;
private
 String user = 
"Scott"
;
private
 String password = 
"tiger"
;
private
 Connection conn;
public
 TestProcedure(){
  
try
 {
   Class.forName(
"oracle.jdbc.driver.OracleDriver"
);
   conn = DriverManager.getConnection(path, user, password);
  } 
catch
 (ClassNotFoundException e) {
   e.printStackTrace();
  } 
catch
 (SQLException e) {
   e.printStackTrace();
  }
}

public
 
void
 readFromProcedure(){
  
try
 {
//   CallableStatement call = conn.prepareCall("call getemps(?)");   //测试方法 1
   CallableStatement call = conn.prepareCall(
"call TEST_CURSOR.MY_PROCEDURE(?)"
); 
//测试方法2
   call.registerOutParameter(
1
, OracleTypes.CURSOR);
   call.execute();
   ResultSet rs = (ResultSet)call.getObject(
1
);
   
while
(rs.next()){
    System.out.println(rs.getString(
1
) + 
" --- "
 + rs.getString(
2
));
   }
   
this
.releaseConnection(conn);
  } 
catch
 (SQLException e) {
   e.printStackTrace();
  }
}

public
 
void
 releaseConnection(Connection conn){
  
try
 {
   conn.close();
  } 
catch
 (SQLException e) {
   e.printStackTrace();
  }
}
public
 
static
 
void
 main(String[] args){
  TestProcedure tp = 
new
 TestProcedure();
  tp.readFromProcedure();
}
}

评论

2 楼 yso 2008-03-06  

  帮大忙了，谢了。

1 楼 gqf2008 2007-10-11  

存储过程和数据结构定义如下：
CREATE OR REPLACE PACKAGE SmsService
IS
TYPE ret_record is RECORD(
P1 VARCHAR2(20),
P2 VARCHAR2(10));
TYPE msg_array IS TABLE OF ret_record INDEX BY BINARY_INTEGER;
END;

CREATE OR REPLACE PACKAGE gqf
IS
PROCEDURE proc1(para1 in varchar2,para2 out SmsService.msg_array);
END;
=================================================================================================
java代码如下：
OracleCallableStatement proc = null;
try {
proc =(OracleCallableStatement)con.prepareCall("{ CALL gqf.proc1 (?,?) }");
proc.setString(1, req.getPhoneNumber());
proc.registerOutParameter(2, OracleTypes.ARRAY,"SmsService.msg_array");
//proc.registerOutParameter(2, OracleTypes.ARRAY,"MSG_ARRAY");
//proc.registerOutParameter(2, OracleTypes.CURSOR);
//proc.registerOutParameter(2, OracleTypes.PLSQL_INDEX_TABLE);
//proc.registerIndexTableOutParameter(2,100, OracleTypes.OTHER, 100);
proc.execute();
//proc.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (proc != null) {
try {
proc.close();
} catch (SQLException e1) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
}
}
}

我试了所有ORACLE的数据类型，要么是在registerOutParameter报错，要么是在execute时报错，错误类型大概就是数据类型错误，要么就是无效的参数名称，网上搜索了很多资料没有找到解决办法，所以来这里问问是否有大侠有类似的开发经验，给小弟我指点一二，不胜感激！