java调用存储过程来分页:java.sql.SQLException: Ref 游标无效

Question

请各位帮忙，我看了两天也看出怎么错了。

 

plsql代码：

 

SQL> create or replace package testpackage as        --创建包用来放游标
  2  type test_cursor is ref cursor;                               --定义的游标test_cursor,ref curosr用法还不是很熟
  3  end testpackage;
  4  /

Package created

 

SQL> create or replace procedure fenye(
  2                                    v_tablename in varchar2,    --输入的表名,根据不同的表来分页
  3                                    v_pagenow   in number,      --分页时当前页码
  4                                    v_pagesize  in number,       --每页的页数
  5                                    v_mypagecount out number,        --计算出一共多少页
  6                                    v_myrows          out number,        --计算表中一共多少行
  7                                    p_cursor out testpackage.test_cursor      --要输出的游标
  8                                    )
  9  is
 10    v_sql   varchar2(1000);
 11    v_begin number:=(v_pagenow-1)*v_pagesize+1;   --计算初始页,既从哪页开始
 12    v_end   number:=v_pagenow*v_pagesize;              --计算结束页,既从哪页结束

 13  begin       --
 14    v_sql:='select *
 15                 from   (select b.*,
 16                                      rownum as rn
 17                            from  (select *
 18                                      from '||v_tablename||'
 19                                      order by sal desc) b
 20                            where rownum<='||v_end||')
 21                where  rn>='||v_begin;

 22    open p_cursor for v_sql;

 23    v_sql:='select count(*) from '||v_tablename;
 24    execute immediate v_sql into v_myrows;

 25    if mod(v_myrows,v_pagesize)=0 then
 26    v_mypagecount:=v_myrows/v_pagesize;
 27    else
 28    v_mypagecount:=v_myrows/v_pagesize+1;
 29    end if;

 30    close p_cursor;
 31  end;
 32  /

Procedure created

 

java代码:

 

import java.sql.*;

 

public class TestProcedure
{
  public static void main(String args[]){
     try{
        
         Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORACLE","SCOTT","TIGER");

         CallableStatement call=con.prepareCall("{call fenye(?,?,?,?,?,?)}");--分页过程的6个参数

         call.setString(1,"emp");
         call.setInt(2,3);
         call.setInt(3,2);

         call.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
         call.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
         call.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);--输出的游标

         call.execute();

         int rowNum=call.getInt(4);
         int pageCount=call.getInt(5);

        //System.out.println("rowNum:"+rowNum);
        //System.out.println("pageCount:"+pageCount);

         ResultSet rs=(ResultSet) ((OracleCallableStatement)call).getObject(6);   --提示有错误

 

 

         while(rs.next()){
             System.out.println("编号:"+rs.getInt(1)+" "+"名字:"+rs.getString(2));
        }
         rs.close();
         con.close();
         call.close();
     }
     catch(Exception e){
         e.printStackTrace();
     }
  }
}

 

错误Exception:

 

rowNum:7
pageCount:14

java.sql.SQLException: Ref 游标无效
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
 at oracle.jdbc.driver.OracleStatement.getCursorValue(OracleStatement.java:3211)
 at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5036)
 at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:4964)
 at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:586)
 at com.neusoft.TestProcedure.main(TestProcedure.java:26)

 

Answer 1

这个要分情况讨论的啦，你这里游标是当作输出参数的，所以不能手动关闭它，只要open一下就可以了，否则调用以后拿不到结果，oralce会自己进行管理的。

第2种情况就是游标并不是当作输出参数的，只是个普通的游标，比如，在有些情况下，需要打开游标，然后，遍历游标，根据具体业务需求更新业务表等等，在这种情况下的话，当游标用完后需要手动关闭，不然会占用资源。

还有一种情况是隐试游标，也不需要手动关闭，例如：

for rec_item in select * from tablename loop
...
end loop;

这种简化的遍历方式oralce其实是隐式地申明了一个游标，不用管，oracle也会自己管理。