存储过程:
create or replace procedure test( p_deptno IN number, p_cursor OUT SYS_REFCURSOR)
as
begin
open p_cursor FOR select * from emp where deptno = p_deptno;
end test;JAVA代码中调用存储过程:
public void method() throws SQLException{
Connection conn = getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
int deptno = 10;
Object temp;
try{
cstmt = conn.prepareCall("{call test(?,?)}");
cstmt.setInt(1, deptno);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
while (rs.next()){
for (int j=0;j< columnCount;j++){
temp = rs.getObject(j+1);
}
}
} finally {
if (!rs==null){
rs.close();
}
if (!stmt==null){
stmt.close();
}
if (!conn==null){
conn.close();
}
}
}但是:
假如我在存储过程中查询满足条件的记录数是0的话,那么OPEN P_CURSOR是不打开的,因为程序执行会发生异常----CURSOR IS CLOSED.
请问这种情况怎么处理?
create or replace procedure test( p_deptno IN number, p_cursor OUT SYS_REFCURSOR)
as
begin
open p_cursor FOR select * from emp where deptno = p_deptno;
end test;JAVA代码中调用存储过程:
public void method() throws SQLException{
Connection conn = getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
int deptno = 10;
Object temp;
try{
cstmt = conn.prepareCall("{call test(?,?)}");
cstmt.setInt(1, deptno);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
while (rs.next()){
for (int j=0;j< columnCount;j++){
temp = rs.getObject(j+1);
}
}
} finally {
if (!rs==null){
rs.close();
}
if (!stmt==null){
stmt.close();
}
if (!conn==null){
conn.close();
}
}
}但是:
假如我在存储过程中查询满足条件的记录数是0的话,那么OPEN P_CURSOR是不打开的,因为程序执行会发生异常----CURSOR IS CLOSED.
请问这种情况怎么处理?
你可以先 select count(*) from emp看下 如果取值>0的话,就声明游标,否则返回,不就搞定了吗?
如果我在存储过程里做了复杂的联合查询呢?
我计算了COUNT(*),这次复杂的连接查询不是白费了吗?
你说呢?
而且oracle里面 你第一次count(*)之后,数据就已经进入 date buffer cache了,你接下来的select * 就从这里面的data buffer cache取数据了,你与数据文件只打了一次交道。一般不会影响你的性能的吧,一家之言!