`
xurichusheng
  • 浏览: 343899 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

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).

相关推荐

    oracle分页查询并返回总记录数据存储过程

    ### Oracle 分页查询并返回总记录数据存储过程 在数据库应用开发中,为了提高用户体验以及减少服务器负担,分页查询成为了一种常见的技术手段。Oracle 数据库提供了多种方法来实现分页查询,其中使用存储过程是一种...

    ibatis调用oracle存储过程分页

    ibatis调用oracle存储过程分页

    Oracle的分页查询语句 Oracle分页的存储过程

    本文将详细探讨Oracle的分页查询语句及其存储过程。 首先,Oracle的分页查询语句通常遵循一个标准格式,如下所示: ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ) A WHERE ...

    Oracle 存储过程分页

    Oracle 提供了多种方法实现分页查询,下面我们将详细探讨Oracle存储过程中的分页实现。 1. **ROWNUM伪列** 在早期的Oracle版本中,ROWNUM伪列被广泛用于分页。ROWNUM是在结果集生成时自动分配的,表示每一行的顺序...

    Oracle存储过程分页

    通过上述示例代码,我们了解了如何使用Oracle存储过程实现分页查询的功能。这种方法不仅提高了查询效率,而且使得代码更加清晰、易于维护。此外,还可以根据实际需求对存储过程进行扩展,例如添加更多参数或支持更...

    通用ORACLE存储过程实现分页和查询

    总结来说,Oracle存储过程实现分页查询的关键在于动态构造SQL语句,结合ROWNUM或ROW_NUMBER()来实现分页。理解这一机制有助于我们在处理大量数据时编写高效、可复用的代码。在实际应用中,根据具体需求和数据库设计...

    java Oracle存储过程分页整理

    本教程将深入探讨如何在Java中调用Oracle存储过程来执行分页查询。 首先,我们需要理解分页的基本概念。分页是将大结果集分割成多个小部分,每次只加载一部分数据到内存中,这样可以减少内存消耗,提高响应速度。在...

    asp.net使用oracle分页存储过程查询数据

    写好oracle的连接字符串和查询语句,调用程序中的方法可以很方便的实现分页功能。该方法中,将参数连接字符串,查询的sql语句,指定每页显示多少行,调用成功后,会返回页数,行数,还有查询的结果数据集。 使用...

    oracle 存储过程分页示例

    Oracle存储过程是数据库管理系统中的一种重要编程元素,用于封装一系列SQL语句和PL/SQL代码,以实现特定的业务逻辑或功能。在大型数据库应用中,分页查询是常见的需求,尤其是在展示大量数据时,避免一次性加载所有...

    oracle存储过程分页.docx

    Oracle存储过程是数据库管理系统中用于执行特定数据库操作的预编译SQL和PL/SQL代码集合。在Oracle中,存储过程可以被用来实现复杂的业务逻辑,提高数据库性能,并且减少网络流量。分页查询是数据库应用中常见的功能...

    Oracle分页+.cs代码+存储过程+Aspnetpager分页控件

    接下来,我们关注`.cs`代码部分,这部分通常涉及到C#后端如何调用Oracle存储过程并处理返回的数据。你可以使用Oracle Managed Data Access (ODP.NET)库来实现。以下是一个简单的示例: ```csharp using Oracle....

    oracle查询分页条件通用存储过程

    oracle数据库的查询分页加条件和排序的通用型存储过程,通过将表名以参数的形式传入到存储过程中做到多表通用,也可以是多表关联的sql语句 将其看作一个表也能调用该存储过程,分页只需要传与页数,和每页显示的行数...

    oracle分页存储过程

    oracle分页存储过程,oracle分页存储过程

    oracle 存储过程分页

    ### Oracle存储过程实现分页查询知识点详解 #### 一、背景与目的 在数据库操作中,分页查询是非常常见的需求之一。特别是在数据量较大的场景下,分页不仅可以提高查询效率,还能改善用户体验。Oracle数据库提供了...

    oracle存储过程分页

    根据提供的文件信息,我们可以深入探讨 Oracle 存储过程分页的相关知识点。首先,我们需要了解存储过程的基本概念,然后分析分页技术在 Oracle 中的具体实现。 ### 存储过程概述 存储过程是 SQL 语句与可选控制流...

    ORACLE存储过程分页

    ### ORACLE存储过程分页实现详解 在Oracle数据库中,实现数据分页是常见的需求,尤其是在处理大量数据时,为了提高查询效率和响应速度,分页技术显得尤为重要。本文将详细解析如何通过Oracle存储过程来实现分页功能...

    Oracle分页存储过程

    通过上述分析和示例,我们可以看到,利用Oracle存储过程进行分页查询不仅可以提高查询效率,还能增强代码的可读性和可维护性。在实际项目中,结合C#等高级语言的应用,可以进一步提升系统的性能和用户体验。

    oracle存储过程通用分页

    Oracle存储过程是数据库管理系统Oracle中实现复杂业务逻辑的重要工具,它可以封装一系列SQL语句和PL/SQL代码,形成可重复使用的程序单元。在处理大量数据时,为了提高性能和用户体验,通常会采用分页查询的方式来...

    SQL Server 存储过程及Oracle SQL语句分页

    在数据库管理中,存储过程和分页查询是两个非常重要的概念。存储过程是预编译的SQL语句集合,它可以封装一系列的操作,并且可以带有参数,以提高数据库操作的效率和安全性。而分页查询则是当数据量较大时,为了提高...

    Oracle 高效分页存储过程(修改)

    在Oracle数据库管理中,高效地实现分页查询是优化数据库性能的重要一环,尤其是在处理大量数据时。分页存储过程可以极大地提升用户体验,因为它允许用户逐步加载数据,而不是一次性加载所有记录,从而节省网络资源和...

Global site tag (gtag.js) - Google Analytics