Oracle存储过程分页

 

转载

from: http://cid-4e5d038451e31a25.spaces.live.com/blog/cns!4E5D038451E31A25!140.entry create or replace procedure P_QuerySplit(       sqlscript varchar2,     --表名/SQL语句       pageSize integer,       --每页记录数       pageIndex integer,      --当前页       totalCount out number, --总记录数       totalPage out number, --总页数       v_cur out sys_refcursor --返回游标       ) is /**    * by chenjianxin 2008-5-3    *    */      v_PageSize number; v_PageIndex number; v_SQL_Count varchar2(4000); v_SQL varchar2(4000); v_StartIndex number; v_EndIndex number; begin v_PageSize:=pageSize; if v_PageSize=0 then     v_PageSize:=1; end if; --统计记录数量 v_SQL_Count := 'select count(*) from ('|| sqlscript ||') a '; execute immediate v_SQL_Count into totalCount; --计算总页数 totalPage:=CEIL(totalCount/v_PageSize); --验证页号 如果页号大余了最大页数，返回最后一页 v_PageIndex:=pageIndex; if v_PageIndex>totalPage then     v_PageIndex:=totalPage; end if; --计算开始的Index和结束的Index v_StartIndex:=(v_PageIndex-1)*v_PageSize+1; v_EndIndex:=v_PageIndex*v_PageSize; v_SQL:='SELECT /*+ FIRST_ROWS */* FROM ('; v_SQL:=v_SQL||' SELECT A.*, ROWNUM RN '; v_SQL:=v_SQL||' FROM ('||sqlscript||') A '; v_SQL:=v_SQL||' WHERE ROWNUM <= '||v_EndIndex; v_SQL:=v_SQL||')WHERE RN >= '||v_StartIndex; open v_cur for v_SQL; end P_QuerySplit; java代码：　 public List listSplit(StringBuffer sbSQL,SplitPageInfo splitPageInfo)throws Exception{       try {           java.sql.CallableStatement proc = this.con.prepareCall(                   "{call P_QuerySplit(?,?,?,?,?,?)}");           proc.setString(1, sbSQL.toString()); //sql代码           proc.setLong(2, splitPageInfo.getRowsPerPage()); //每页行数           proc.setLong(3, splitPageInfo.getCurPage()); //当前行数           proc.registerOutParameter(4, oracle.jdbc.driver.OracleTypes.NUMBER); //总行数           proc.registerOutParameter(5, oracle.jdbc.driver.OracleTypes.NUMBER); //总页数           proc.registerOutParameter(6, oracle.jdbc.driver.OracleTypes.CURSOR); //游标记录集           proc.execute();           int iCount = ((oracle.jdbc.driver.OracleCallableStatement) proc).                        getInt(4);           int rPages = ((oracle.jdbc.driver.OracleCallableStatement) proc).                        getInt(5);           ResultSet rs = ((oracle.jdbc.driver.OracleCallableStatement) proc).                          getCursor(6);           while (rs.next()) {               System.out.println(rs.getString(1));           }           rs.close();           proc.close();           return null;       } catch (Exception ex) {           throw ex;       } }       附注： ceil(x)的意思就是获取比X大的最小整数 同这个对应的还有floor(x)--> 获取比x小的最大整数 SQL> select ceil(3.2),floor(3.2) from dual; CEIL(3.2) FLOOR(3.2) ---------- ----------       4       3 参考oracle官方文档，有很详细的函数说明。   带排序的oracle分页存储过程                                             from: http://chengzhisheng.javaeye.com/blog/116905 　输入order by 的sqeuence是，应该为“ desc”或者“ asc” 　　若输入两个order by则，v_order_field=" a[sequence] ,order by b " CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size　　int, --the size of a page of list 　　　　　　　　　　　　　　　　　　　　　　 v_current_page int, --the current page of list 　　　　　　　　　　　　　　　　　　　　　　 v_table_name varchar2, --the talbe name 　　　　　　　　　　　　　　　　　　　　　　 v_order_field　varchar2,--the order field 　　　　　　　　　　　　　　　　　　　　　　 v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank. 　　　　　　　　　　　　　　　　　　　　　　 --v_sql_select　 varchar2, --the select sql for procedure 　　　　　　　　　　　　　　　　　　　　　　 --v_sql_count　　varchar2, --the count sql for procedure 　　　　　　　　　　　　　　　　　　　　　　 --v_out_recordcount OUT int, --the num of return rows 　　　　　　　　　　　　　　　　　　　　　　 p_cursor OUT refcursor_pkg.return_cursor) as 　v_sql　　　　 varchar2(3000); --the sql for select all rows of list 　v_sql_count　 varchar2(3000); --the count sql for procedure 　v_sql_order　 varchar2(2000); --the order of list 　v_count　　　 int; -- the amount rows fo original list 　v_endrownum　 int; --the end row num of the current page 　v_startrownum int; --the start row num of the current page BEGIN 　----set the order of list 　if　v_order_field!='NO' then 　　v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence; 　else 　　 v_sql_order :=''; 　end if; 　----catch the amount rows of list 　v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name; 　execute immediate v_sql_count into v_count; 　-- v_out_recordcount := v_count; 　----set the value of start and end row 　if v_order_sequence='desc' then 　　v_endrownum:=v_count-(v_current_page-1)*v_page_size; 　　v_startrownum:=v_endrownum - v_page_size + 1; 　else 　　v_endrownum:= v_current_page * v_page_size; 　　v_startrownum := v_endrownum - v_page_size + 1; 　end if; 　----the sql for page slide 　v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' WHERE rownum <= ' || 　　　　　 to_char(v_endrownum) ||' '|| v_sql_order||')　WHERE rn >= ' || 　　　　　 to_char(v_startrownum)||' '||v_sql_order; 　open p_cursor for v_sql; END TABLEPAGE_SELECT;                               /***************************************************************** *功能描述: 大数据量分页通用存储过程(重载2) *创 建 人: 夏春涛 *创建时间: 2005-07-01 *****************************************************************/ procedure sp_Page(p_PageSize int, --每页记录数 p_PageNo int, --当前页码,从 1 开始 p_SqlSelect varchar2, --查询语句,含排序部分 p_OutCursor out refCursorType) as v_sql varchar2(3000); --v_count int; v_heiRownum int; v_lowRownum int; begin /* ----取记录总数 v_sql := 'select count(*) from (' || p_SqlSelect || ')'; execute immediate v_sql into v_count; p_OutRecordCount := v_count; */ ----执行分页查询 v_heiRownum := p_PageNo * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; v_sql := 'SELECT * FROM ( SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) || ' ) B WHERE rn >= ' || to_char(v_lowRownum) ; --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql; end sp_Page;