Oracle分页查询sql语句

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;
    }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!