oracle 高效分页

来源：互联网发布：sqlserver价格编辑：程序博客网时间：2024/06/05 00:51

原文：http://blog.csdn.net/found2008/article/details/5784435

1. oracle分页

1. sql语句提取分页记录数

说明：主要是处理单个表、视图的分页说明

下面操作主要是说明over的使用，如果要进行分页高效的采用下面任意sql，数度都比较快。当然也可以对使用系列的id直接进行提取，前提是不删除或每次重写修改系列id。

select X.*

from(

select row_number() over(order by empno desc) as ROW_NO, emp.*from emp) X

where X.ROW_NO <= 10

and X.ROW_NO >= 1;

select X.*

from (select

row_number() over(order by emp.ODRM021_ORGID) as ROW_NO, emp.*

from ODRM021_AUTOORG emp) X

where X.ROW_NO <= 57320010

and X.ROW_NO >= 57320000;

--该语句只适合数据量少于1000000记录,

--100000 99000, 1001 65.548

--1000000 999900,101 95.126

--57320010 57320000,超过2分钟，而且temp空间消耗过快

下面是从表至上倒下提取记录，数度也是非常的快，不到2秒钟 *推荐使用*

SELECT /*+ FIRST_ROWS */ * FROM

(SELECT A.*, ROWNUM RN FROM (SELECT /*+ FIRST_ROWS */* FROMPSID110_MSLIPBUF) A

WHERE ROWNUM <= 1000000)

WHERE RN >= 999990 (order by RN desc)

select u.* --数度比上面的语句稍微快几百毫秒

from( select rownum r_,c.* from PSID110_MSLIPBUF c where

rownum <=1349990+10 )u

where u.r_ between 1349990 and 1349990+10

order by u.r_ (desc)

2. 分页实战代码

包

CREATE OR REPLACE PACKAGE pkg_test

TYPE myrctype IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);

PROCEDURE prc_query

( p_tableName in varchar2, --表名,该表最好已经得到或处理得到的统计表，这样便于该存储过程的更高效执行

p_tableColum in varchar2, --表列

p_strwhere in varchar2, --查询条件格式：where ...group by ... order by ...

p_curPage in out Number, --当前页

p_pageSize in out Number, --每页显示记录的条数

p_totalRecords out Number, --总记录数

p_totalPages out Number, -- 总页数

v_cur out pkg_test.myrctype --返回查询满足的结果集

);

function prc_querys

( p_tableName in varchar2, --表名,该表最好已经得到或处理得到的统计表，这样便于该存储过程的更高效执行

p_tableColum in varchar2, --表列

p_strwhere in varchar2, --查询条件格式：where ...group by ... order by ...

p_curPage in out Number, --当前页

p_pageSize in out Number, --每页显示记录的条数

p_totalRecords out Number, --总记录数

p_totalPages out Number -- 总页数

) return pkg_test.myrctype;--返回查询满足的结果集

function get(intID number) return myrctype;

END pkg_test;

主题

CREATE OR REPLACE PACKAGE BODY pkg_test

--高效分页函数

function prc_querys

(

p_tableName in varchar2, --表名

p_tableColum in varchar2, --表列

p_strwhere in varchar2, --查询条件格式：where ...group by ... order by ...

p_curPage in out Number, --当前页

p_pageSize in out Number, --每页显示记录的条数

p_totalRecords out Number, --总记录数

p_totalPages out Number -- 总页数

) return pkg_test.myrctype --返回查询满足的结果集

myrctypes pkg_test.myrctype;

v_sql varchar2(2000):=''; --sql语句

v_startRecord Number; --开始显示的记录数

v_endRecord Number; --结束显示的记录条数

p_table_true VARCHAR2(8); --判断是存在该表

BEGIN

--记录总记录条数

begin

select max(1) into p_table_true from user_all_tables ust whereust.table_name=upper(trim(p_tableName));

if trim(p_table_true) is not null then

--验证该存储过程数据参数

if (instr(upper(trim(p_strwhere)),'WHERE',1,1) = 0) and (instr(upper(trim(p_strwhere)),';',1,1) =0) and (trim(p_pageSize)>='1') then

v_sql:='select count(*) FROM '|| trim(p_tableName) ||' where '||trim(p_strwhere);

execute IMMEDIATE v_sql INTO p_totalRecords;

IF MOD(p_totalRecords,p_pageSize)=0 THEN

--得到整数月则直接取得到的页码数否在原来的基础上增加一个页码

p_totalPages:=p_totalRecords/p_pageSize;

ELSE

p_totalPages:=p_totalRecords/p_pageSize+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 '|| p_tableColum ||' from ( select rownum r_,c.* from '|| p_tableName ||' c where rownum <='

|| v_endRecord ||' and '|| trim(p_strwhere) ||') u where u.r_ between '|| v_startRecord ||' and '|| v_endRecord;

--DBMS_OUTPUT.put_line(v_sql);

p_totalPages:=ceil(p_totalPages); --去整数总页

OPEN myrctypes FOR v_sql;

return myrctypes;

else

dbms_output.put_line('输入的参数不合法条件，请核实后在执行该存储过程!');

end if;

else

dbms_output.put_line('输入的用户表名称在数据库里不存在!');

end if;

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

WHEN OTHERS THEN

BEGIN

--日志都必须写到专门记录日志表

dbms_output.put_line(SQLCODE || ',' || SUBSTR(SQLERRM,1,80));

END;

end;

end prc_querys;

END pkg_test;