常见问题之一:ORACLE 如何实现数据查询的高效分页?

Question

昨天刚写了一个存储过程的分页，oracel里面和sqlserver可真的不太一样，利用rownum完成。

Answer 1

(
  iPagesize number,--每页记录数
  iCurrentpage number,--当前页码
  strSql varchar2,    --最终语句
  iRecordCount out number, --总记录数
  iPagenum out number,  --输出总页码
  oFlag out number,    --操作正确标志
  sFlag out varchar2,
  v_cur out Pkg_cursor.cur
)
as
  tmpSql varchar2(500);
  tmpNum number(9,2);
  tmpRow number;
  tmpPageSize number;
begin  tmpSql:='select * from ('|| strSql ||')';
  execute immediate tmpSql;
  execute immediate 'select count(1) from ('|| tmpSql ||')' into iRecordCount;
  if iRecordCount=0 then  --没有记录
    oFlag:=16;
    iPagenum:=0;
    open v_cur for tmpSql;  --返回空纪录
    return;
  end if;  if iRecordCount<iPagesize then  --所有记录只有一页
    oFlag:=17;
    iPagenum:=1;
    open v_cur for tmpSql;
    return;
  end if;  --计算总页数
  tmpNum:=iRecordCount/iPagesize;
  if tmpNum>trunc(tmpNum) then
    iPagenum:=trunc(tmpNum)+1;
  else
    iPagenum:=trunc(tmpNum);
  end if;
  oFlag:=18;
  if(iCurrentpage>iPagenum or iCurrentpage<1) then  --页码不对
    oFlag:=19;
    oFlag:=3;   --页码不对
    open v_cur for 'select 1 from sys_users where rownum<1';
    return;
  end if;  --计算第一个iRow
  if iCurrentpage=1 then
    tmpRow:=1;
  else
    tmpRow:=(iCurrentpage-1)*iPagesize+1;
  end if;
  tmpPageSize:=iPagesize+1;
  tmpSql:=tmpSql || ' where rownum<'|| tmpPageSize ||' and iRow>='|| tmpRow;
  sFlag:=tmpSql;  
  open v_cur for tmpSql;
  oFlag:=iPagenum;  exception
    when others then
      begin
        --oFlag:=0;   --操作失败
        open v_cur for 'select 1 from sys_users where rownum<1';
      end;end;
已经测试通过，要求传入的sql:例如：select rownum rowid,a.col....from fds a........

Answer 2

关键是需不需要排序，不需要的话直接
select * from (select rownum id,tbname.* from tbname where rownum<21) t
where t.id>10;
排序的话需要再嵌套一层

Answer 3

改了一下，试试这个，对sql输入没有限制。
 (
  iPagesize number,--每页记录数
  iCurrentpage number,--当前页码
  strSql varchar2,    --最终语句
  iRecordCount out number, --总记录数
  iPagenum out number,  --输出总页码
  oFlag out number,    --返回操作标志
  v_cur out Pkg_cursor.cur
)
as
  tmpSql varchar2(500);
  tmpNum number(9,2);
  tmpRow number;
  tmpPageSize number;
begin  tmpSql:='select * from (select rownum iRow,a.* from ('|| strSql ||') a)';
  execute immediate tmpSql;
  execute immediate 'select count(1) from ('|| tmpSql ||')' into iRecordCount;
  if iRecordCount=0 then  --没有记录
    oFlag:=16;
    iPagenum:=0;
    open v_cur for 'select 1 from sys_users where rownum<1';  --返回空纪录
    return;
  end if;  if iRecordCount<iPagesize then  --所有记录只有一页
    oFlag:=17;
    iPagenum:=1;
    open v_cur for tmpSql;
    return;
  end if;  --计算总页数
  tmpNum:=iRecordCount/iPagesize;
  if tmpNum>trunc(tmpNum) then
    iPagenum:=trunc(tmpNum)+1;
  else
    iPagenum:=trunc(tmpNum);
  end if;
  oFlag:=18;
  if(iCurrentpage>iPagenum or iCurrentpage<1) then  --页码不对
    oFlag:=19;
    oFlag:=3;   --页码不对
    open v_cur for 'select 1 from sys_users where rownum<1';
    return;
  end if;  --计算第一个iRow
  if iCurrentpage=1 then
    tmpRow:=1;
  else
    tmpRow:=(iCurrentpage-1)*iPagesize+1;
  end if;
  tmpPageSize:=iPagesize+1;
  tmpSql:=tmpSql || ' where rownum<'|| tmpPageSize ||' and iRow>='|| tmpRow;  open v_cur for tmpSql;
  oFlag:=iPagenum;  exception
    when others then
      begin
        --oFlag:=0;   --操作失败
        open v_cur for 'select 1 from sys_users where rownum<1';
      end;end;

Answer 4

这个问题讨论多次了。
例如：我要取出emp表中sal排在第5-10位的信息SQL> select * from emp;    EMPNO ENAME      JOB             MGR HIREDATE         SAL      COMM    DEPTNO
--------- ---------- --------- --------- ---------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-12月-80       800                  20
     7499 ALLEN      SALESMAN       7698 20-2月 -81      1600       300        30
     7521 WARD       SALESMAN       7698 22-2月 -81      1250       500        30
     7566 JONES      MANAGER        7839 02-4月 -81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-9月 -81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-5月 -81      2850                  30
     7782 CLARK      MANAGER        7839 09-6月 -81      2450                  10
     7839 KING       PRESIDENT           17-11月-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-9月 -81      1500         0        30
     7900 JAMES      CLERK          7698 03-12月-81       950                  30
     7902 FORD       ANALYST        7566 03-12月-81      3000                  20
     7934 MILLER     CLERK          7782 23-1月 -82      1300                  10已选择12行。SQL> select rownum id,emp.ename,emp.sal from emp order by sal desc;       ID ENAME            SAL
--------- ---------- ---------
        8 KING            5000
       11 FORD            3000
        4 JONES           2975
        6 BLAKE           2850
        7 CLARK           2450
        2 ALLEN           1600
        9 TURNER          1500
       12 MILLER          1300
        3 WARD            1250
        5 MARTIN          1250
       10 JAMES            950
        1 SMITH            800已选择12行。SQL> select * from (select rownum id,emp.ename,emp.sal from emp order by sal desc) t
  2  where id between 5 and 10;       ID ENAME            SAL
--------- ---------- ---------
        8 KING            5000
        6 BLAKE           2850
        7 CLARK           2450
        9 TURNER          1500
        5 MARTIN          1250
       10 JAMES            950已选择6行。可见这样操作是错误的！
正确写法：
SQL> select * from (select rownum id,emp.ename,emp.sal from (select * from emp order by sal desc)
  2  emp where rownum<=10) where id>=5;       ID ENAME            SAL
--------- ---------- ---------
        5 CLARK           2450
        6 ALLEN           1600
        7 TURNER          1500
        8 MILLER          1300
        9 WARD            1250
       10 MARTIN          1250已选择6行。SQL> 

Answer 5

我发现： select t.* from (select rownum id,emp.ename,emp.sal from (select * from emp order by sal desc) emp) t where t.id between 5 and 10;与你的 select * from (select rownum id,emp.ename,emp.sal from (select * from emp order by sal desc)
  2  emp where rownum<=10) where id>=5;
我感觉是一样的。你觉得呢，如果一样哪个的效率高一点。

Answer 6

select t.* from (select rownum id,emp.ename,emp.sal from (select * from emp order by sal desc) emp) t where t.id between 5 and 10;
这个效率高？！