oracle如何查询分页 oracle怎么分页查询

求一个通用ORACLE存储过程，实现分页和查询？

create

为西湖等地区用户提供了全套网页设计制作服务，及西湖网站建设行业解决方案。主营业务为成都网站设计、网站建设、西湖网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！

or

replace

package

Tools

is

type

ResultData

is

ref

cursor;

procedure

sp_Page(p_PageSize

int,

--每页记录数

p_PageNo

int,

--当前页码,从

1

开始

p_SqlSelect

varchar2,

--查询语句,含排序部分

p_SqlCount

varchar2,

--获取记录总数的查询语句

p_OutRecordCount

out

int,--返回总记录数

p_OutCursor

out

ResultData);

end

Tools;

create

or

replace

package

body

Tools

is

procedure

sp_Page(p_PageSize

int,

--每页记录数

p_PageNo

int,

--当前页码,从

1

开始

p_SqlSelect

varchar2,

--查询语句,含排序部分

p_SqlCount

varchar2,

--获取记录总数的查询语句

p_OutRecordCount

out

int,--返回总记录数

p_OutCursor

out

ResultData)

as

v_sql

varchar2(3000);

v_count

int;

v_heiRownum

int;

v_lowRownum

int;

begin

----取记录总数

execute

immediate

p_SqlCount

into

v_count;

p_OutRecordCount

:=

v_count;

----执行分页查询

v_heiRownum

:=

p_PageNo

*

p_PageSize;

v_lowRownum

:=

v_heiRownum

-

p_PageSize

+1;

v_sql

:=

'SELECT

*

FROM

(

SELECT

A.*,

rownum

rn

FROM

('||

p_SqlSelect

||')

A

WHERE

rownum

=

'||

to_char(v_heiRownum)

||

'

)

B

WHERE

rn

=

'

||

to_char(v_lowRownum)

;

--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

OPEN

p_OutCursor

FOR

v_sql;

end

sp_Page;

end

Tools;

oracle怎么实现多表 连接查询 并分页。。。

oracle使用rownum伪列可以实现分页，三表连接分页示例代码如下：

select * from (select rownum r,k.kch,k.kcm,cj.cj,x.xh,x.xm from KCB  k,CJB  cj,XSB  x where k.kch   = cj.kch and cj.xh = x.xh and rownum=10) where r0

特别注意这样外层查询时由于内层查询的字段有重复列名，所以内层查询最后不要用*。取完每一个表字段，这样很容易报错（“无效字段”）

解决Oracle分页查询中排序与效率问题

原始未分页查询Sql代码如下  

select ROWNUM rn t id ID o name YYB u name XM t MC from tZDYSX   t tuser u lbanization o where t cjr=u id and id=code and t gx =    order by ID 

结果如下 RN    ID      YYB       XM     MC        某证券总部     管理员 测试      某证券总部     管理员 持有上港 股以上     某证券总部     管理员 十年规划    某证券总部     管理员 开发渠道为上海    某证券总部     管理员 万科A    某证券总部     管理员    某证券总部     管理员 今天过生日的客户      某证券总部     管理员 客户状态正常     某证券总部     管理员 无交易    某证券总部     管理员 OA     某证券总部     管理员 幸运客户    某证券总部     管理员 风险型    某证券总部     管理员 tst    白沙网上交易   安昌彪 安客户正常     某证券总部     管理员    某证券总部     管理员 客户号包含    某证券总部     管理员 aaa    某证券总部     管理员 ssssssss    某证券总部     管理员    某证券总部     管理员    某证券总部     管理员    某证券总部     管理员 aaaaaa

rows selected

最初我使用如下Sql代码查询  

select * from (select ROWNUM rn t id ID o name YYB u name XM t MC    from tZDYSX t tuser u lbanization o where t cjr=u id and id=code and    t gx = order by t ID )Where rn and rn= ; 

这种方法能成功分页 结果如下 RN    ID        YYB       XM      MC    某证券总部     管理员 开发渠道为上海    某证券总部     管理员 万科A    某证券总部     管理员    某证券总部     管理员 今天过生日的客户    某证券总部     管理员 客户号包含    某证券总部     管理员 aaa    某证券总部     管理员 ssssssss    某证券总部     管理员    某证券总部     管理员    某证券总部     管理员 aaaaaa

rows selected

从结果看来 有个问题 此语句Sql代码 order by CJSJ DESC  被执行 但是是在分后的第 到 条记录的结果集中再进行排序 而不是先排序后分页 （本来希望显示ID为 到 结果变为 到 ）

后来变为以下Sql代码查询  

SELECT *    FROM(    SELECT ROWNUM RN TA *    FROM(   select t id ID o name YYB u name XM t MC   from tZDYSX t tuser u lbanization o    where t cjr=u id and id=code and t gx = order by t ID   )TA WHERE ROWNUM =    )WHERE RN   SELECT * FROM( SELECT ROWNUM RN TA * FROM(select t id ID o name YYB u name XM t MCfrom tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by t ID)TA WHERE ROWNUM = )WHERE RN

结果如下 RN    ID     YYB           XM     MC    某证券总部     管理员 幸运客户    某证券总部     管理员 风险型    某证券总部     管理员 tst    白沙网上交易   安昌彪 安客户正常    某证券总部     管理员    某证券总部     管理员 客户号包含    某证券总部     管理员 aaa    某证券总部     管理员 ssssssss    某证券总部     管理员    某证券总部     管理员

rows selected

看来结果是正确的

总结 第二种方法其中最内层的查询Sql代码

select t id ID o name YYB u name XM t MC   from tZDYSX t tuser u lbanization o    where t cjr=u id and id=code and t gx = order by t ID 

表示不进行翻页的原始查询语句 ROWNUM = 和RN 控制分页查询的每页的范围 第二种方法在大多数情况拥有较高的效率 分页的目的就是控制输出结果集大小 在上面的分页查询语句中 这种考虑主要体现在WHERE ROWNUM = 这句上

选择第 到 条记录存在两种方法 第二种方法正是在查询的第二层通过ROWNUM = 来控制最大值 在查询的最外层控制最小值 而第一种方法是去掉查询第二层的WHERE ROWNUM = 语句 在查询的最外层控制分页的最小值和最大值

一般来说 第二个查询的效率比第一个高得多 这是由于CBO 优化模式下 Oracle可以将外层的查询条件推到内层查询中 以提高内层查询的执行效率 对于第二个查询语句 第 层的查询条件WHERE ROWNUM = 就可以被Oracle推入到内层查询中 这Oracle查询的结果一旦超过了ROWNUM限制条件 就终止查询将结果返回了

而第一个查询语句 由于查询条件Where rn and rn= 是存在于查询的第三层 而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义 因为最内层查询不知道RN代表什么） 因此 对于第一个查询语句 Oracle最内层返回给中间层的是所有满足条件的数据 而中间层返回给最外层的也是所有数据 数据的过滤在最外层完成 显然这个效率要比第二个查询低得多

lishixinzhi/Article/program/Oracle/201311/17706