oracle 分页存储过程

 

查询oracle时，做存储过程实现分页

 

 

create or replace procedure query_by_page(
pagenum in number,
pagesql in varchar2,
pagesize in number,
pagecount out number,
allpagecount out number,
v_cur out querypage.type_cur
) as
v_sql varchar2(3000);
v_pb number;
v_pd number;
begin
     v_sql :='select count(*) from('||pagesql||')';
     execute immediate v_sql into allpagecount; -- 记录数
     pagecount :=ceil(allpagecount/pagesize);  -- 分页数
     v_pd := pagenum*pagesize +pagesize;
     v_pb := v_pd - pagesize + 1;
     v_sql := 'select * from ('||pagesql||') where rn between '||v_pb||' and '||v_pd;
     open v_cur for v_sql;
end;

 其中 这种写法效率不太好，因为内查询遍历了，过多无用的记录，所以后来把v_pd放到了内循环里，这样能减少数据量

 

 把我现在的过程贴出来了，感觉还是不太好，还不如直接传两句sql来的痛快

create or replace procedure query_by_page(
pagenum in number,
pagesql in varchar2,
pagesize in number,
pagecount out number,
allpagecount out number,
v_cur out querypage.type_cur
) as
v_sql varchar2(3000);
v_pb number;
v_pd number;
v_innner_sql varchar2(3000);
begin
     v_sql :='select count(*) from('||pagesql||')';
     execute immediate v_sql into allpagecount; -- 记录数
     pagecount :=ceil(allpagecount/pagesize);  -- 分页数
     v_pd := pagenum*pagesize +pagesize;
     v_pb := v_pd - pagesize + 1;
     if instr(pagesql,'group by') >= 1 then
      v_sql := 'select * from ('||pagesql||') where rn between '||v_pb||' and '||v_pd;
     else if instr(pagesql,'where') >= 1 
      then  v_innner_sql := pagesql||' and rownum <= '||v_pd; v_sql:='select * from ('||v_innner_sql||') where rn >='||v_pb;
     else
       v_innner_sql := pagesql || ' where rownum <= '|| v_pd; v_sql:='select * from ('||v_innner_sql||') where rn >='||v_pb;
     end if;
     end if;
     -- v_sql := 'select * from ('||pagesql||') where rn between '||v_pb||' and '||v_pd; --效率太慢
     open v_cur for v_sql;
end;

 

 

 顺便把java的部分代码贴出来

 

 

/**
 * 分页查询
 * @param sql
 * @param pageNum
 * @param pageSize
 * @param bean
 * @param datasource
 * @throws Exception 
 */
public static PageDataBaseBean queryUsingPaging(String sql,int pageNum,int pageSize,IDataBaseBean bean,SingleDataSource datasource) throws Exception{
	PageDataBaseBean pbl = new PageDataBaseBean();
	Connection conn = null;
	CallableStatement cst = null;
	ResultSet rs = null;
	try {
		conn = datasource.getConnection();
		cst = conn.prepareCall("call query_by_page(?,?,?,?,?,?)");
		cst.setInt(1, pageNum);
		cst.setString(2, sql);
		cst.setInt(3, pageSize);
		cst.registerOutParameter(4, Types.VARCHAR);
		cst.registerOutParameter(5, Types.INTEGER);
		cst.registerOutParameter(6, OracleTypes.CURSOR);
		cst.execute();
		int pageCount = cst.getInt(4);
		int allPageCount = cst.getInt(5);
		rs = (ResultSet) cst.getObject(6);
		//赋值
		pbl.setCurrentPageNum(pageNum);
		pbl.setPageCount(pageCount);
		pbl.setPageSize(pageSize);
		pbl.setTotalSize(allPageCount);
		IDataBaseBean databaseBean =  bean.getClass().newInstance();
		while(rs.next()){
			//赋值TODO
			databaseBean = bean.getClass().newInstance();
			databaseBean.loadFromResultSet(rs);
			pbl.insertBean(databaseBean);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		
		if(conn!=null)
			conn.close();
		if(cst!=null)
			cst.close();
		if(rs!=null)
			rs.close();
	}
	return pbl;
}

 

 

PageDataBaseBean是包含分页信息和实体集合的类

    IDataBaseBean是实体bean的接口