oracle 存储过程分页查询

 

oracle 10gR2

Spring JDBCTemplate

JDK 1.6.0_10

 

1. 定义返回结果集的游标

CREATE OR REPLACE PACKAGE PKG_QUERY_PAGE AS
  TYPE CUR_QUERY_PAGE IS REF CURSOR;
END PKG_QUERY_PAGE;
/

 2. 存储过程

CREATE OR REPLACE PROCEDURE PRC_QUERY_PAGE(P_TABLENAME    IN VARCHAR2, --表名
                                           P_STRWHERE     IN VARCHAR2, --查询条件
                                           P_ORDERCOLUMN  IN VARCHAR2, --排序的列
                                           P_ORDERSTYLE   IN VARCHAR2, -- 排序类型,ASC或DSC
                                           P_CURPAGE      IN OUT NUMBER, -- 当前第几页
                                           P_PAGESIZE     IN OUT NUMBER, -- 页面大小
                                           P_TOTALRECORDS OUT NUMBER, -- 总记录数
                                           P_TOTALPAGES   OUT NUMBER, -- 总页数
                                           V_CUR          OUT PKG_QUERY_PAGE.CUR_QUERY_PAGE 
/* 返回查询到的数据信息*/) IS

  V_SQL         VARCHAR2(1000) := ''; -- sql语句
  V_STARTRECORD NUMBER(4); -- 起始记录数
  V_ENDRECORD   NUMBER(4); -- 结束记录数

BEGIN
  -- 查询记录数
  V_SQL := 'SELECT TO_Number(count(1)) from ' || P_TABLENAME ||
           ' where 1=1 ';

  IF P_STRWHERE IS NOT NULL THEN
    V_SQL := V_SQL || P_STRWHERE;
  END IF;

  EXECUTE IMMEDIATE V_SQL
    INTO P_TOTALRECORDS;

  --验证页面记录大小
  IF P_PAGESIZE < 0 THEN
    P_PAGESIZE := 0;
  END IF;

  --根据页大小计算总页数
  IF MOD(P_TOTALRECORDS, P_PAGESIZE) = 0 THEN
    P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0);
  ELSE
    P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0) + 1;
  END IF;

  --验证页号
  IF P_CURPAGE < 1 THEN
    P_CURPAGE := 1;
  END IF;

  IF P_CURPAGE > P_TOTALPAGES THEN
    P_CURPAGE := P_TOTALPAGES;
  END IF;

  --实现分页查询
  V_STARTRECORD := (P_CURPAGE - 1) * P_PAGESIZE + 1;
  V_ENDRECORD   := P_CURPAGE * P_PAGESIZE;

  V_SQL := 'SELECT * FROM (SELECT A.*, rownum r FROM ' || '(SELECT * FROM ' ||
           P_TABLENAME;
  IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN
    V_SQL := V_SQL || ' WHERE 1=1 ' || P_STRWHERE;
  END IF;
  IF P_ORDERCOLUMN IS NOT NULL OR P_ORDERCOLUMN <> '' THEN
    V_SQL := V_SQL || ' ORDER BY ' || P_ORDERCOLUMN || ' ' || P_ORDERSTYLE;
  END IF;
  V_SQL := V_SQL || ') A WHERE rownum <= ' || V_ENDRECORD ||
           ') B WHERE r >= ' || V_STARTRECORD;
  DBMS_OUTPUT.PUT_LINE(V_SQL);
  OPEN V_CUR FOR V_SQL;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('query page fail:' || SQLERRM);

END PRC_QUERY_PAGE;
/

 

3. dao层代码

/**
	 * @Title: findUserByPage_proc
	 * @deprecated: 调用存储过程进行分页查询
	 * @param tableName
	 *            表名称
	 * @param where
	 *            查询条件(如： and ename = 'scott')
	 * @param orderCol
	 *            排序的列
	 * @param orderStyle
	 *            排序类型,ASC或DSC
	 * @param curPage
	 *            当前第几页
	 * @param pageSize
	 *            每页记录数
	 * @return PageResult
	 * @throws Exception
	 * @author 
	 * @date 2013年10月18日
	 */
	public PageResult findByPage_proc(final String tableName,
			final String where, final String orderCol, final String orderStyle,
			final int curPage, final int pageSize) throws Exception {

		final PageResult page = new PageResult();

		getJdbcTemplate().execute(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(
					final Connection con) throws SQLException {

				String proc = "{CALL PRC_QUERY_PAGE(?,?,?,?,?,?,?,?,?)}";

				CallableStatement cs = con.prepareCall(proc);

				// 设置入参
				cs.setString(1, tableName);
				cs.setString(2, where);
				cs.setString(3, orderCol);
				cs.setString(4, orderStyle);
				cs.setInt(5, curPage);
				cs.setInt(6, pageSize);

				// 设置出参
				cs.registerOutParameter(5, OracleTypes.INTEGER);
				cs.registerOutParameter(6, OracleTypes.INTEGER);
				cs.registerOutParameter(7, OracleTypes.INTEGER);
				cs.registerOutParameter(8, OracleTypes.INTEGER);
				cs.registerOutParameter(9, OracleTypes.CURSOR);

				return cs;
			}
		}, new CallableStatementCallback() {

			@Override
			public Object doInCallableStatement(final CallableStatement cs)
					throws SQLException, DataAccessException {

				// 执行存储过程
				cs.execute();

				/* 获取结果 */
				// 当前第几页
				int curPage = (Integer) cs.getObject(5);
				// 每页记录数
				int pageSize = (Integer) cs.getObject(6);
				// 总记录数
				int totalRecords = (Integer) cs.getObject(7);
				// 总页数
				int totalPages = (Integer) cs.getObject(8);
				// 结果集
				ResultSet datas = (ResultSet) cs.getObject(9);

				page.setCurPage(curPage);

				page.setPageSize(pageSize);

				page.setTotalRecords(totalRecords);

				page.setTotalPages(totalPages);

				List<Map<String, Object>> resultsMap = null;
				try {
					resultsMap = getResultSet(datas);
				} catch (Exception e) {
					throw new SQLException(e);
				}

				page.setDatas(resultsMap);

				datas.close();

				return resultsMap;
			}
		});

		return page;
	}

	/**
	 * @Title: getResultSet
	 * @deprecated: <p>
	 *              将分页取出的结果集ResultSet对象组装成
	 *              List<--Map<--(columnName:columnValue)
	 *              </p>
	 *              <p>
	 *              每一个map对应一条记录，map长度 == column数量
	 *              </p>
	 * @param rs
	 * @return List
	 * @throws Exception
	 * @author 
	 * @date 2013年10月18日
	 */
	private List<Map<String, Object>> getResultSet(ResultSet rs)
			throws Exception {

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(10);

		try {
			ResultSetMetaData rsmd = rs.getMetaData();

			Map<String, Object> map = null;
			int colCount = 0;
			String colName = null;

			// 每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
			while (rs.next()) {

				map = new HashMap<String, Object>(16);

				colCount = rsmd.getColumnCount();

				for (int i = 0; i < colCount; i++) {

					colName = rsmd.getColumnName(i + 1);

					map.put(colName, rs.getObject(i + 1));
				}

				list.add(map);
			}

		} catch (Exception e) {
			log.error("Get ResultSet fail:".concat(e.getMessage()));
			throw new Exception(e);
		}

		return list;
	}

 

4. 分页信息

import java.util.List;
import java.util.Map;

public class PageResult {
    // 当前第几页
    private int curPage;
    // 每页记录数
    private int pageSize;
    // 总记录数
    private int totalRecords;
    // 总页数
    private int totalPages;
    // 结果集
    private List<Map<String, Object>> datas;

    // 省略 getter/setter
}

 

 

评论

2 楼 xurichusheng 2015-09-29  

jjhe369 写道

感谢分享！但是发现一个小问题，就是第13,14行的V_STARTRECORD和V_ENDRECORD的长度问题。当数据量稍大一点，number(4)是不够的，我修改成了number(10).

谢谢指出。
在具体的使用中可进行调整的。

1 楼 jjhe369 2015-08-09  

感谢分享！但是发现一个小问题，就是第13,14行的V_STARTRECORD和V_ENDRECORD的长度问题。当数据量稍大一点，number(4)是不够的，我修改成了number(10).