oracle存储过程的分页及调用

劳言文

　　
　　一、定义
------------------------------------------------------
-- Export file for user 24365                       --
-- Created by Administrator on 2007-11-13, 22:07:04 --
------------------------------------------------------

spool ccp_news.log

prompt
prompt Creating package CCP_NEWS
prompt =========================
prompt
create or replace package 24365.CCP_NEWS is

  -- Author  : ADMINISTRATOR
  -- Created : 2007-11-13 21:32:33
  -- Purpose :
  
  -- Public type declarations
  type ccp is ref cursor;
  
  -- Public constant declarations
  p_code constant number := -1;

  -- Public variable declarations
  p_flag number;

  -- Public function and procedure declarations
     /**
     *标识码    [out]   p_code
*总数      [out]   p_count
*记录集    [out]   p_rcl
*开始索引  [in]    p_startIndex
*分页数    [in]    p_pageSize
*/
  procedure getUserList_proc(p_code out integer,
                             p_count out integer,
                             p_rcl out ccp,
                             p_startIndex in integer,
                             p_pageSize in integer);

end CCP_NEWS;
/

prompt
prompt Creating package body CCP_NEWS
prompt ==============================
prompt
create or replace package body 24365.CCP_NEWS is
  
procedure getUserList_proc(p_code out integer,
                            p_count out integer,
                            p_rcl out ccp,
                            p_startIndex in integer,
                            p_pageSize in integer) is
  begin
       select count(*)
           into p_count
       from tableName;
      
       open p_rcl for
         select * from (select *
                        from tableName u
                        order by u.add_date desc
                        )
         where rownum <= p_startIndex + p_pageSize and
               rownum >= p_startIndex;
         p_code := 1;
        exception
           when others then
           rollback;
           p_code := -1;
end getUserList_proc;
end CCP_NEWS;
/


spool off
二、调用
/**
     *标识码    [out]   p_code
*总数      [out]   p_count
*记录集    [out]   p_rcl
*开始索引  [in]    p_startIndex
*分页数    [in]    p_pageSize
*/
private static void getUserListProc(){
JdbcManager jdbc = null;
ResultSet rs = null;
try {
jdbc = new JdbcManager();
jdbc.createCallStatement("CCP_NEWS.getUserList_proc(?,?,?,?,?)");
jdbc.registerCInt(1);
jdbc.registerCInt(2);
jdbc.registerOutParameter(3);
jdbc.setCInt(4, 0);
jdbc.setCInt(5, 1);
jdbc.executeCQuery();
int flag = jdbc.getCInt(1);
rs = jdbc.getResultSet(3);
while(rs.next()){
System.out.println("用户名："+rs.getString("user_name"));
}
System.out.println("flag="+flag);
} catch (SQLException e) {
e.printStackTrace();
} finally{
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(jdbc!=null)
jdbc.close();
}
}