Oracle分页查询sql语句

最新推荐文章于 2025-06-24 14:42:49 发布

原创最新推荐文章于 2025-06-24 14:42:49 发布 · 2.3w 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #分页 #sql

oracle学习专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中实现分页查询的有效方法，并提供了一个具体的代码示例，展示了如何通过ROWNUM来限定查询结果的范围，同时实现了对码号相关信息的查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.oracle中的分页查询sql语句基本可以按照如下的模板来改写：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TABLE_NAME) A
         WHERE ROWNUM <= 40)
 WHERE RN > 20

其中的SELECT * FROM TABLE_NAME 表示没有进行分页时的sql查询语句，而ROWNUM 和RN 限定了查询数据的范围。

2.使用时的代码如下所示：

/** 查询码号相关信息的sql语句 **/
String sql_query = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT c.codename, case when (c.isopen = 0) then '不可用'"
            + " when (c.isopen = 1) then ((case when (c.codestatus = 0) then '空闲'"
            + " when (c.codestatus = 1) then '预占' when (c.codestatus = 2) then '占用'"
            + " when (c.enddate > sysdate) then '保留期' end)) end as status,t.name,( select p."
            + " party_name from CRM.PARTY p"
            + " where p.party_id=c.custid) as partyname, c.deviceid,(select l.name FROM"
            + " lgc_codetoclass_tbl l)as codeFeature,(SELECT g.name FROM LGC_CODEGRADE_TBL g"
            + " WHERE g.id=c.gradeid) as gradename,(select g.value "
            + " from LGC_CODEGRADE_TBL g where g.id = c.gradeid) as codePrice, c.comments"
            + " FROM LGC_CODESEG_TBL t, LGC_CODENUMBER_TBL c WHERE t.ID = c.CODESEGID and c.codename=?"
            + " ) a WHERE ROWNUM <= ?) WHERE rn > ?";

/** 用来查询码号查询页面数据的方法的实现 **/
public NumberRsp queryRecords(NumberEvt evt,int pageNo,int pageSize) {
        Map<String, Object> map = jdbcTemplate.queryForMap(sql_count);
        List<NumberVo> list = jdbcTemplate.query(sql_query, new Object[] { evt.getCode(),pageNo*pageSize,(pageNo-1)*pageSize}, new RowMapper<NumberVo>() {
            public NumberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                // TODO Auto-generated method stub
                NumberVo numberVo = new NumberVo();
                numberVo.setPhone(rs.getString(1));
                numberVo.setStatus(rs.getString(2));
                numberVo.setCodePartName(rs.getString(3));
                numberVo.setUnit(rs.getString(4));
                numberVo.setFacility(rs.getString(5));
                numberVo.setCodeFeature(rs.getString(6));
                numberVo.setPricingLevel(rs.getString(7));
                numberVo.setCodePrice(rs.getShort(8));
                numberVo.setRemark(rs.getString(9));
                return numberVo;
            }
        });
        NumberRsp numberRsp = new NumberRsp();
        if (map.get("total") != null) {
            numberRsp.setTotal(Integer.parseInt(map.get("total").toString()));
        }
        if (map.get("ky") != null) {
            numberRsp.setUsableNum(Integer.parseInt(map.get("ky").toString()));
        }
        if (map.get("bky") != null) {
            numberRsp.setUnUsableNum(Integer.parseInt(map.get("bky").toString()));
        }
        if (map.get("youhao") != null) {
            numberRsp.setYouhaoNum(Integer.parseInt(map.get("youhao").toString()));
        }
        if (map.get("puhao") != null) {
            numberRsp.setPuhaoNum(Integer.parseInt(map.get("puhao").toString()));
        }
        if (list != null) {
            numberRsp.setRecords(list);
        }
        return numberRsp;
    }