oracle分页

oracle分页与MySQL不同，MySQL是用关键字limit进行分页，oracle是根据行号：ROWNUM 进行分页。

关于效率问题，oracle分页有两种方式，一种between  and  方式，一种直接取rownum值的方式。between and 效率比较低，所以还是用直接取rownum值的方式最好。

分页sql-取rownum值方式示例：

select *
  from (select T.*, rownum rn
          from (select t.taaccountid as accountCode,
                       t.individualorinstitution as accountType,
                       t.investorname as accountName,
                       t.appsheetserialno as applicationNumber,
                       t.transactioncfmdate as applicationDate,
                       t.f_custtype as clientType,
                       f.key_name as clientTypeName,
                       p.intercountyname || d.intercountyname as provincCity,
                       t.province as provinceNum,
                       p.intercountyname as provinceName,
                       t.city as cityNum,
                       d.intercountyname as cityName,
                       t.investorsbirthday as birthDate,
                       t.create_user as founderPeople,
                       t.create_time as founderTime,
                       t.modify_user as modifyPeople,
                       t.modify_time as modifyTime
                  from tc_pf_pb_ta_account t
                  left join tp_gl_county_code_map p
                    on t.province = p.intercode
                  left join tp_gl_county_code_map d
                    on t.city = d.intercode
                  left join tp_gl_exhib_datadict f
                    on f.key_id = t.f_custtype
                 where 1 = 1
                 order by case
                            when t.modify_time is not null then
                             t.modify_time
                            else
                             t.create_time
                          end desc,
                          case
                            when t.individualorinstitution = '0' then
                             1
                            when t.individualorinstitution = '2' then
                             2
                            when t.individualorinstitution = '1' then
                             3
                          end) T
         where rownum <= 100) tt
 where rn >= 20

分页sql-between and 示例：

select T.*
  from (select rownum rn,
               t.taaccountid as accountCode,
               t.f_custtype as clientType,
               f.key_name as clientTypeName,
               p.intercountyname || d.intercountyname as provincCity,
               t.province as provinceNum,
               p.intercountyname as provinceName,
               t.city as cityNum,
               d.intercountyname as cityName,
               t.modify_time as modifyTime
          from tc_pf_pb_ta_account t
          left join tp_gl_county_code_map p
            on t.province = p.intercode
          left join tp_gl_county_code_map d
            on t.city = d.intercode
          left join tp_gl_exhib_datadict f
            on f.key_id = t.f_custtype
         where 1 = 1
           and (instr(t.taaccountid, '1') > 0 or
               instr(t.investorname, '1') > 0)
         order by case
                    when t.modify_time is not null then
                     t.modify_time
                    else
                     t.create_time
                  end desc) T
 where T.rn between '1' and '10'

 分页sql带参数示例：

select c.*
        from (select ROWNUM rn,
        b.ID AS IDA,
        b.PF_ID AS PF_ID,
        b.PRODUCT_FOR_SHORT as PRODUCT_FOR_SHORT,
        b.PF_NAME AS PF_NAME,
        b.PF_SETUP_DATE AS PF_SETUP_DATE,
        b.PF_END_DATE AS PF_END_DATE,
        b.PF_END_DATE_PRE AS PF_END_DATE_PREA,
        b.PF_REAL_END_DATE AS PF_REAL_END_DATE,
        b.CHG_DATE AS CHG_DATE,
        b.FUND_SUM_SHARE as FUND_SUM_SHARE,
        b.RAISE_MONEY as RAISE_MONEY,
        b.COLLECT_BEGIN_DATE AS COLLECT_BEGIN_DATE,
        b.COLLECT_END_DATE AS COLLECT_END_DATE,
        b.IS_USING as IS_USING,
        b.sta AS STAS,
        b.MEMO AS MEMOA,
        b.CREATE_PRSN AS CREATE_PRSNA,
        b.CREATE_TIME + 0 AS CREATE_TIMEA,
        b.MDFY_PRSON AS MDFY_PRSONA,
        b.MDFY_TIME + 0 AS MDFY_TIMEA,
        b.pf_cur_id_d AS PF_CUR_ID_D
        from tc_pf_bsc_info b
        where 1=1
        <if test="value != null and value != '' ">
            and b.PF_ID like '%'||#{value}||'%'
            or
            b.PF_NAME like '%'||#{value}||'%'
        </if>

        <if test="pfSetupDate != null and pfSetupDate != '' and pfEndDate != null and pfEndDate != '' ">
            and b.PF_SETUP_DATE between #{pfSetupDate} and #{pfEndDate}
        </if>

        <!--<if test="pfEndDate != null and pfEndDate != '' ">
            and b.PF_END_DATE &lt;= #{pfEndDate}
        </if>-->
        <if test="sta != null and sta != '' ">
            and b.STA = #{sta}
        </if>
        order by b.MDFY_TIME desc
        ) c
        where rn between #{beginValue} and #{lastValue}
-- beginValue是从第几条开始，lastvalue是截止到第几条

分页公共方法（根据每页显示的条数和页码计算从第几条开始到第几条结束）：

---

    /**
     * 根据前端传的第几页和每页显示的条数，计算分页的区间
     *
     * @param limit 每页显示的条数
     * @param page  第几页
     * @return   begin:第几条开始   last：第几条结束
     **/
    public static Map<String, Integer> getBetweenAndLast(Integer limit, Integer page) {
        Map<String, Integer> map = new HashMap<>();

        //从第几条开始
        Integer begin = (limit * (page - 1)) + 1;

        //第几条结束
        Integer last = (limit * (page - 1)) + limit;
        map.put("begin", begin);
        map.put("last", last);
        return map;
    }